《监测数据库性能的oracle-sql》由会员分享，可在线阅读，更多相关《监测数据库性能的oracle-sql（7页珍藏版）》请在金锄头文库上搜索。
1、oracle数据库性能监控的 SQL1. 监控事例的等待SQL 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;2. 回滚段的争用情况 SQL SELECT NAME, WAITS, GETS, WAITS/GETS RATIO FROM V$ROLLSTAT A, V$ROLLNAME B WHERE A.USN = B.USN; 3. 监控表空间的 I/O 比例SQ
2、L 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; 4. 监控文件系统的 I/O 比例 SQL SELECT SUBSTR(A.FILE#,1,2) #, SUBSTR(A.NAME,1,30) NAME, A.STATUS,A.BYTES,B
3、.PHYRDS,B.PHYWRTS FROM V$DATAFILE A, V$FILESTAT BWHERE A.FILE# = B.FILE#; 5.在某个用户下找所有的索引SQL SELECT USER_INDEXES.TABLE_NAME, USER_INDEXES.INDEX_NAME,UNIQUENESS, COLUMN_NAME FROM USER_IND_COLUMNS, USER_INDEXES WHERE USER_IND_COLUMNS.INDEX_NAME = USER_INDEXES.INDEX_NAME AND USER_IND_COLUMNS.TABLE_NAME
4、= USER_INDEXES.TABLE_NAME ORDER BY USER_INDEXES.TABLE_TYPE, USER_INDEXES.TABLE_NAME, USER_INDEXES.INDEX_NAME, COLUMN_POSITION; 6. 监控 SGA 的命中率 SQL 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,
5、V$SYSSTAT B, V$SYSSTAT C WHERE A.STATISTIC# = 38 AND B.STATISTIC# = 39 AND C.STATISTIC# = 40;7. 监控 SGA 中字典缓冲区的命中率 SQL 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
6、BY PARAMETER, GETS, GETMISSES; 8. 监控 SGA 中共享缓存区的命中率，应该小于 1% SQL SELECT SUM(PINS) TOTAL PINS, SUM(RELOADS) TOTAL RELOADS, SUM(RELOADS)/SUM(PINS) *100 LIBCACHE FROM V$LIBRARYCACHE;SQL SELECT SUM(PINHITS-RELOADS)/SUM(PINS) HIT RADIO,SUM(RELOADS)/SUM(PINS) RELOAD PERCENT FROM V$LIBRARYCACHE;9. 显示所有数据库对象
7、的类别和大小 SQL SELECT COUNT(NAME) NUM_INSTANCES ,TYPE ,SUM(SOURCE_SIZE) SOURCE_SIZE,SUM(PARSED_SIZE) PARSED_SIZE ,SUM(CODE_SIZE) CODE_SIZE ,SUM(ERROR_SIZE) ERROR_SIZE,SUM(SOURCE_SIZE) +SUM(PARSED_SIZE) +SUM(CODE_SIZE) +SUM(ERROR_SIZE) SIZE_REQUIRED FROM DBA_OBJECT_SIZE GROUP BY TYPE ORDER BY 2;10. 监控 SG
8、A 中重做日志缓存区的命中率，应该小于 1% SQL 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) RATIO2FROM V$LATCHWHERE NAME IN (REDO ALLOCATION, REDO COPY);11
9、. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size SQL SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN (SORTS (MEMORY), SORTS (DISK); 12. 监控当前数据库谁在运行什么 SQL语句 SQL SELECT OSUSER, USERNAME, SQL_TEXT FROM V$SESSION A, V$SQLTEXT B WHERE A.SQL_ADDRESS =B.ADDRESS ORDER BY ADDRESS, PIECE; 13. 监控字典缓冲区 SQLSELECT (SU
10、M(PINS - RELOADS) / SUM(PINS) LIB CACHE FROM V$LIBRARYCACHE; SQLSELECT (SUM(GETS - GETMISSES - USAGE - FIXED) / SUM(GETS) ROW CACHE FROM V$ROWCACHE; SQLSELECT SUM(PINS) EXECUTIONS, SUM(RELOADS) CACHE MISSES WHILE EXECUTING FROM V$LIBRARYCACHE;(后者除以前者,此比率小于 1%,接近 0%为好) SQLSELECT SUM(GETS) DICTIONARY
11、GETS,SUM(GETMISSES) DICTIONARY CACHE GET MISSES FROM V$ROWCACHE; 14. 查找 ORACLE字符集 SQLSELECT * FROM SYS.PROPS$ WHERE NAME=NLS_CHARACTERSET; 15. 监控 MTS SQLSELECT BUSY/(BUSY+IDLE) SHARED SERVERS BUSY FROM V$DISPATCHER; (此值大于 0.5时，参数需加大) SQLSELECT SUM(WAIT)/SUM(TOTALQ) DISPATCHER WAITS FROM V$QUEUE WHER
12、E TYPE=DISPATCHER; SQLSELECT COUNT(*) FROM V$DISPATCHER; SQLSELECT SERVERS_HIGHWATER FROM V$MTS; (servers_highwater接近 mts_max_servers时，参数需加大) 16. 碎片程度 SQLSELECT TABLESPACE_NAME,COUNT(TABLESPACE_NAME) FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME HAVING COUNT(TABLESPACE_NAME)10; SQLALTER TABLESPACE NA
13、ME COALESCE; SQLALTER TABLE NAME DEALLOCATE UNUSED; SQLCREATE OR REPLACE VIEW TS_BLOCKS_V AS SELECT TABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS,FREE SPACE SEGMENT_NAME FROM DBA_FREE_SPACE UNION ALL SELECT TABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS,SEGMENT_NAME FROM DBA_EXTENTS; SELECT * FROM TS_BLOCKS_V; SQLSE
14、LECT TABLESPACE_NAME,SUM(BYTES),MAX(BYTES),COUNT(BLOCK_ID) FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME; 查看碎片程度高的表 SQLSELECT SEGMENT_NAME TABLE_NAME,COUNT(*) EXTENTS FROM DBA_SEGMENTS WHERE OWNER NOT IN (SYS, SYSTEM) GROUP BY SEGMENT_NAME HAVING COUNT(*)=(SELECT MAX(COUNT(*) FROM DBA_SEGMENTS GROUP
15、BY SEGMENT_NAME); 17. 表、索引的存储情况检查 SQLSELECT SEGMENT_NAME,SUM(BYTES),COUNT(*) EXT_QUAN FROM DBA_EXTENTS WHERE TABLESPACE_NAME=&TABLESPACE_NAME AND SEGMENT_TYPE=TABLE GROUP BY TABLESPACE_NAME,SEGMENT_NAME; SQLSELECT SEGMENT_NAME,COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE=INDEX AND OWNER= 18、找使用 CPU多
16、的用户 session SQLSELECT 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;(12OCPU USED BY THIS SESSION)表空间统计A、 脚本说明：这是我最常用的一个脚本，用它可以显示出数据库中所有表空间的状态，如表空间的大小、已使用空间、使用的百分比、空闲空间数及现在表空间的最大块是多大。B、脚本原文:SELECT UPPER(F.TABLE
