Oracle常用性能監控SQL語(yǔ)句 1. --查看表鎖 SELECT * FROM SYS.V_$SQLAREA WHERE DISK_READS > 100; 2. --監控事例的等待 SELECT EVENT, SUM(DECODE(WAIT_TIME, 0, 0, 1)) "Prev", SUM(DECODE(WAIT_TIME, 0, 1, 0)) "Curr", COUNT(*) "Tot" FROM V$SESSION_WAIT GROUP BY EVENT ORDER BY 4; 3. --回滾段的爭用情況 SELECT NAME, WAITS, GETS, WAITS / GETS "Ratio" FROM V$ROLLSTAT A, V$ROLLNAME B WHERE A.USN = B.USN; 4. --查看前臺正在發(fā)出的SQL語(yǔ)句 SELECT USER_NAME, SQL_TEXT FROM V$OPEN_CURSOR WHERE SID IN (SELECT SID FROM (SELECT SID, SERIAL#, USERNAME, PROGRAM FROM V$SESSION WHERE STATUS = 'ACTIVE')); 5. --數據表占用空間大小情況 SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE' ORDER BY BYTES DESC, BLOCKS DESC; 6. --查看表空間碎片大小 SELECT TABLESPACE_NAME, ROUND(SQRT(MAX(BLOCKS) / SUM(BLOCKS)) * (100 / SQRT(SQRT(COUNT(BLOCKS)))), 2) FSFI FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY 1; 7. --查看表空間占用磁盤(pán)情況 SELECT B.FILE_ID 文件ID號, B.TABLESPACE_NAME 表空間名, B.BYTES 字節數, (B.BYTES - SUM(NVL(A.BYTES, 0))) 已使用, SUM(NVL(A.BYTES, 0)) 剩余空間, SUM(NVL(A.BYTES, 0)) / (B.BYTES) * 100 剩余百分比 FROM DBA_FREE_SPACE A, DBA_DATA_FILES B WHERE A.FILE_ID = B.FILE_ID GROUP BY B.TABLESPACE_NAME, B.FILE_ID, B.BYTES ORDER BY B.FILE_ID; 8. --查看session使用回滾段 SELECT R.NAME 回滾段名, S.SID, S.SERIAL#, S.USERNAME 用戶(hù)名, T.STATUS, T.CR_GET, T.PHY_IO, T.USED_UBLK, T.NOUNDO, SUBSTR(S.PROGRAM, 1, 78) 操作程序 FROM SYS.V_$SESSION S, SYS.V_$TRANSACTION T, SYS.V_$ROLLNAME R WHERE T.ADDR = S.TADDR AND T.XIDUSN = R.USN ORDER BY T.CR_GET, T.PHY_IO; 9. --查看SGA區剩余可用內存 SELECT NAME, SGASIZE / 1024 / 1024 "Allocated(M)", BYTES / 1024 "**空間(K)", ROUND(BYTES / SGASIZE * 100, 2) "**空間百分比(%)" FROM (SELECT SUM(BYTES) SGASIZE FROM SYS.V_$SGASTAT) S, SYS.V_$SGASTAT F WHERE F.NAME = 'free memory'; 10. --監控表空間I/O比例 SELECT DF.TABLESPACE_NAME NAME, DF.FILE_NAME "file", F.PHYRDS PYR, F.PHYBLKRD PBR, F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME; 11. --監控SGA命中率 SELECT A.VALUE + B.VALUE "logical_reads", C.VALUE "phys_reads", ROUND(100 * ((A.VALUE + B.VALUE) - C.VALUE) / (A.VALUE + B.VALUE)) "BUFFER HIT RATIO" FROM V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C WHERE A.STATISTIC# = 38 AND B.STATISTIC# = 39 AND C.STATISTIC# = 40; 12. --監控 SGA 中字典緩沖區的命中率 SELECT PARAMETER, GETS, GETMISSES, GETMISSES / (GETS + GETMISSES) * 100 "miss ratio", (1 - (SUM(GETMISSES) / (SUM(GETS) + SUM(GETMISSES)))) * 100 "Hit ratio" FROM V$ROWCACHE WHERE GETS + GETMISSES <> 0 GROUP BY PARAMETER, GETS, GETMISSES; 13. --監控 SGA **享緩存區的命中率，應該小于1% SELECT SUM(PINS) "Total Pins", SUM(RELOADS) "Total Reloads", SUM(RELOADS) / SUM(PINS) * 100 LIBCACHE FROM V$LIBRARYCACHE; 14. --監控 SGA 中重做日志緩存區的命中率，應該小于1% SELECT NAME, GETS, MISSES, IMMEDIATE_GETS, IMMEDIATE_MISSES, DECODE(GETS, 0, 0, MISSES / GETS * 100) RATIO1, DECODE(IMMEDIATE_GETS + IMMEDIATE_MISSES, 0, 0, IMMEDIATE_MISSES / (IMMEDIATE_GETS + IMMEDIATE_MISSES) * 100) RATIO2 FROM V$LATCH WHERE NAME IN ('redo allocation', 'redo copy'); 15. --監控內存和硬盤(pán)的排序比率，最好使它小于 .10 SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('sorts (memory)', 'sorts (disk)'); 16. --監控字典緩沖區 SELECT SUM(GETS) "DICTIONARY GETS", SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE; 17. --非系統用戶(hù)建在SYSTEM表空間中的表 SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME IN ('SYSTEM', 'USER_DATA') AND OWNER NOT IN ('SYSTEM', 'SYS', 'OUTLN', 'ORDSYS', 'MDSYS', 'SCOTT', 'HOSTEAC'); 18. --性能最差的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 < 100; --用下列SQL 工具找出低效SQL ： SELECT EXECUTIONS, DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) HIT_RADIO, ROUND(DISK_READS / EXECUTIONS, 2) READS_PER_RUN, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS > 0 AND BUFFER_GETS > 0 AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8 ORDER BY 4 DESC; 19. --讀磁盤(pán)數超100次的sql SELECT * FROM SYS.V_$SQLAREA WHERE DISK_READS > 100; 20. --最頻繁執行的sql SELECT * FROM SYS.V_$SQLAREA WHERE EXECUTIONS > 100; 21. --查詢(xún)使用CPU多的用戶(hù)session SELECT A.SID, SPID, STATUS, SUBSTR(A.PROGRAM, 1, 40) PROG, A.TERMINAL, OSUSER, VALUE / 60 / 100 VALUE FROM V$SESSION A, V$PROCESS B, V$SESSTAT C WHERE C.STATISTIC# = 12 AND C.SID = A.SID AND A.PADDR = B.ADDR ORDER BY VALUE DESC; 22. --當前每個(gè)會(huì )話(huà)使用的對象數 SELECT A.SID, S.TERMINAL, S.PROGRAM, COUNT(A.SID) FROM V$ACCESS A, V$SESSION S WHERE A.OWNER <> 'SYS' AND S.SID = A.SID GROUP BY A.SID, S.TERMINAL, S.PROGRAM ORDER BY COUNT(A.SID);
本站僅提供存儲服務(wù)，所有內容均由用戶(hù)發(fā)布，如發(fā)現有害或侵權內容，請
點(diǎn)擊舉報。
