监测数据库性能的oracle-sql.doc

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 比例SQL 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.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 = 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, 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 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. 显示所有数据库对象的类别和大小 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_