oracle调优使用到相关sql

select * from v$session where username is not null;
select username,count(username) from v$session where username is not null group by username;
select count(*) from v$process;
select value from v$parameter where name = 'processes';
Select count(*) from v$session where status='ACTIVE';
SELECT NAME, VALUE, DISPLAY_VALUE FROM V$PARAMETER WHERE NAME ='spfile';
SELECT COUNT(1) FROM v$spparameter where value is not null;
select user_name, count(user_name) from v$open_cursor group by user_name;
SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR
FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P WHERE A.STATISTIC# = B.STATISTIC#
AND B.NAME = 'opened cursors current' AND P.NAME = 'open_cursors' GROUP BY P.VALUE;
SELECT A.VALUE, S.USERNAME, S.SID, S.SERIAL# FROM V$SESSTAT A, V$STATNAME B, V$SESSION S WHERE A.STATISTIC# = B.STATISTIC#
AND S.SID = A.SID AND B.NAME = 'opened cursors curent';

SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%cursor%';
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%parse%';
SELECT 'session_cached_cursors' PARAMETER, LPAD(VALUE, 5) VALUE,
DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE
FROM (SELECT MAX(S.VALUE) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME = 'session cursor cache count' AND S.STATISTIC# = N.STATISTIC#),
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors', LPAD(VALUE, 5), TO_CHAR(100 * USED / VALUE, '990') || '%' FROM (SELECT MAX(SUM(S.VALUE)) USED
FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME IN ('opened cursors current', 'session cursor cache count')
AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID), (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');

原文地址:https://www.cnblogs.com/qishiguilai/p/4250068.html