oracle优化2

数据库对象下一扩展与表空间的 free 扩展值的检查：
SQL >SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
FROM ALL_TABLES A,(SELECT TABLESPACE_NAME, MAX (BYTES) AS BIG_CHUNK
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK
UNION SELECT A.INDEX_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
FROM ALL_INDEXES A,(SELECT TABLESPACE_NAME, MAX (BYTES) AS BIG_CHUNK
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK;

Disk Read 最高的 SQL 语句的获取：
SQL >SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM <= 5 ;

查找前十条性能差的 sql
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC )
WHERE ROWNUM < 10 ;

等待时间最多的 5 个系统等待事件的获取：
SQL >SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC ) WHERE ROWNUM <= 5 ;

查看当前等待事件的会话 :
COL USERNAME FORMAT A10
SET LINE 120
COL EVENT FORMAT A30
SELECT SE.SID,S.USERNAME,SE.EVENT,SE.TOTAL_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAIT
FROM V$SESSION S,V$SESSION_EVENT SE WHERE S.USERNAME IS NOT NULL AND SE.SID=S.SID
AND S.STATUS= 'ACTIVE' AND SE.EVENT NOT LIKE '%SQL*NET%' ;

SELECT SID, EVENT, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, STATE FROM V$SESSION_WAIT WHERE EVENT NOT LIKE '%MESSAGE%' AND EVENT NOT LIKE 'SQL*NET%' AND EVENT NOT LIKE '%TIMER%' AND EVENT != 'WAKEUP TIME MANAGER' ;