2022年oracle数据库性能监控 .pdf

《2022年oracle数据库性能监控 .pdf》由会员分享，可在线阅读，更多相关《2022年oracle数据库性能监控 .pdf（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 FROMV$SESSION_WAIT GROUP BY EVENT ORDER BY 4; 2. 回滚段的争用情况SQL SELECTNAME , 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.BYTE

3、S,B.PHYRDS,B.PHYWRTS FROM V$DATAFILE A, V$FILESTAT B WHERE 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.TABL

4、E_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 FR

5、OM 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 GET

6、S+GETMISSES 0 GROUP BYPARAMETER, GETS, GETMISSES; 8. 监控 SGA 中共享缓存区的命中率，应该小于1% 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 7 页 - - - - - - - - - SQL SELECTSUM (PINS) TOTAL PINS, SUM (RELOADS) TOTAL RELOADS, SUM (RELOADS)/ SUM (PINS) *100 LIBCACHE FROM V$LIBRA

7、RYCACHE; SQL SELECTSUM (PINHITS-RELOADS)/ SUM (PINS) HIT RADIO,SUM (RELOADS)/ SUM (PINS) RELOAD PERCENT FROM V$LIBRARYCACHE; 9. 显示所有数据库对象的类别和大小SQL SELECTCOUNT (NAME ) NUM_INSTANCES , TYPE , SUM (SOURCE_SIZE) SOURCE_SIZE,SUM (PARSED_SIZE) PARSED_SIZE , SUM (CODE_SIZE) CODE_SIZE , SUM (ERROR_SIZE) ERR

8、OR_SIZE,SUM (SOURCE_SIZE) +SUM (PARSED_SIZE) + SUM (CODE_SIZE) + SUM (ERROR_SIZE) SIZE_REQUIRED FROMDBA_OBJECT_SIZE GROUP BY TYPEORDER BY 2; 10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%SQL SELECTNAME , GETS, MISSES, IMMEDIATE_GETS, IMMEDIATE_MISSES, DECODE(GETS, 0, 0, MISSES / GETS * 100) RATIO1, DECODE(IMMEDIATE

9、_GETS + IMMEDIATE_MISSES, 0, 0, IMMEDIATE_MISSES / (IMMEDIATE_GETS + IMMEDIATE_MISSES) * 100) RATIO2 FROM V$LATCH WHERE NAMEIN (REDO ALLOCATION, REDO COPY); 11. 监控内存和硬盘的排序比率，最好使它小于 .10 ，增加 sort_area_size SQL SELECTNAME , VALUEFROM V$SYSSTAT WHERE NAME IN (SORTS (MEMORY), SORTS (DISK); 12. 监控当前数据库谁在运

10、行什么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. 监控字典缓冲区SQL SELECT ( SUM (PINS - RELOADS) / SUM (PINS) LIB CACHE FROMV$LIBRARYCACHE; SQL SELECT ( SUM (GETS - GETMISSES - USAGE - FIXED) / SUM (GETS) ROW CACHE FROM

11、V$ROWCACHE; SQL SELECTSUM (PINS) EXECUTIONS, SUM (RELOADS) CACHE MISSES WHILE EXECUTING FROM V$LIBRARYCACHE;(后者除以前者 , 此比率小于 1%,接近 0% 为好) SQL SELECT SUM (GETS) DICTIONARY GETS,SUM (GETMISSES) DICTIONARY CACHE 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 7 页 -

12、- - - - - - - - GET MISSES FROM V$ROWCACHE; 14. 查找 ORACLE 字符集SQL SELECT * FROM SYS .PROPS$ WHERE NAME =NLS_CHARACTERSET; 15. 监控 MTS SQL SELECT BUSY/(BUSY+IDLE) SHARED SERVERS BUSY FROM V$DISPATCHER; ( 此值大于 0.5 时，参数需加大 ) SQL SELECT SUM ( WAIT )/ SUM (TOTALQ) DISPATCHER WAITS FROM V$QUEUE WHERETYPE =D

13、ISPATCHER; SQL SELECTCOUNT(*) FROM V$DISPATCHER; SQL SELECT SERVERS_HIGHWATER FROM V$MTS; (servers_highwater接近 mts_max_servers 时，参数需加大 ) 16. 碎片程度SQL SELECT TABLESPACE_NAME,COUNT(TABLESPACE_NAME) FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME HAVINGCOUNT(TABLESPACE_NAME)10; SQL ALTERTABLESPACE NAME COA

14、LESCE; SQL ALTERTABLENAME DEALLOCATE UNUSED; SQL CREATE ORREPLACE VIEW TS_BLOCKS_V ASSELECT TABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS,FREE SPACE SEGMENT_NAME FROM DBA_FREE_SPACE UNIONALLSELECT TABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS,SEGMENT_NAME FROMDBA_EXTENTS; SELECT * FROM TS_BLOCKS_V; SQL SELECT TABLE

15、SPACE_NAME,SUM (BYTES),MAX (BYTES),COUNT(BLOCK_ID) FROMDBA_FREE_SPACE GROUP BY TABLESPACE_NAME; 查看碎片程度高的表SQL SELECT SEGMENT_NAME TABLE_NAME,COUNT (*) EXTENTSFROM DBA_SEGMENTS WHERE OWNER NOTIN (SYS, SYSTEM) GROUP BYSEGMENT_NAME HAVINGCOUNT(*)=( SELECTMAX (COUNT (*) FROM DBA_SEGMENTS GROUP BY SEGMENT

16、_NAME); 17. 表、索引的存储情况检查SQL SELECT SEGMENT_NAME,SUM (BYTES),COUNT(*) EXT_QUAN FROM DBA_EXTENTS WHERE TABLESPACE_NAME=&TABLESPACE_NAME ANDSEGMENT_TYPE=TABLE GROUPBY TABLESPACE_NAME,SEGMENT_NAME; SQL SELECT SEGMENT_NAME,COUNT(*) FROM DBA_EXTENTS WHERESEGMENT_TYPE=INDEX AND OWNER=&OWNER GROUP BY SEGMENT

17、_NAME; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 7 页 - - - - - - - - - 18、找使用 CPU 多的用户 session SQL SELECT A.SID,SPID,STATUS,SUBSTR(A.PROGRAM,1,40) PROG,A.TERMINAL,OSUSER,VALUE /60/100 VALUEFROM V$SESSION A,V$PROCESS B,V$SESSTAT C WHERE C.STATISTIC#=12 ANDC

18、.SID=A.SID AND A.PADDR=B.ADDR ORDER BYVALUEDESC ; (12?OCPU USED BY THIS SESSION ) 表空间统计A、脚本说明：这是我最常用的一个脚本， 用它可以显示出数据库中所有表空间的状态，如表空间的大小、 已使用空间、 使用的百分比、 空闲空间数及现在表空间的最大块是多大。B、脚本原文 : SELECT UPPER(F.TABLESPACE_NAME) 表空间名 D.TOT_GROOTTE_MB 表空间大小 (M), D.TOT_GROOTTE_MB - F.TOTAL_BYTES 已使用空间 (M), TO_CHAR(ROUN

19、D(D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),990.99) 使用比 , F.TOTAL_BYTES 空闲空间 (M), F.MAX_BYTES 最大块 (M) FROM( SELECT TABLESPACE_NAME, ROUND( SUM (BYTES)/(1024*1024),2) TOTAL_BYTES, ROUND( MAX (BYTES)/(1024*1024),2) MAX_BYTES FROM SYS .DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, ( S

20、ELECT DD.TABLESPACE_NAME, ROUND(SUM (DD.BYTES)/(1024*1024),2) TOT_GROOTTE_MB FROMSYS .DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC ; 查看无法扩展的段A、 脚本说明：ORACLE 对一个段比如表段或索引无法扩展时，取决的并不是表空间中剩余的空间是多少，而是取于这些剩余空间中最大的块是否够表比索引的“NEXT ”值大，所以有时一个表空间剩余

21、几个G的空闲空间，在你使用时ORACLE 还是提示某个表或索引无法扩展， 就是由于这一点， 这时说明空间的碎片太多了。这个脚本是找出无法扩展的段的一些信息。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - B、脚本原文：SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER, A.TABLESPACE_NAME TABLESPACENAME, INITIAL_EXTENT/1024 INITAL_EXTE

22、NT(K), NEXT_EXTENT/1024 NEXT_EXTENT(K), PCT_INCREASE, B.BYTES/1024 TABLESPACE MAX FREE SPACE(K), B.SUM_BYTES/1024 TABLESPACE TOTAL FREE SPACE(K) FROM DBA_SEGMENTS A, ( SELECT TABLESPACE_NAME,MAX (BYTES) BYTES, SUM (BYTES) SUM_BYTES FROMDBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NA

23、ME=B.TABLESPACE_NAME AND NEXT_EXTENTB.BYTES ORDER BY 4,3,1; 查看段 ( 表段、索引段 )所使用空间的大小A、 脚本说明：有时你可能想知道一个表或一个索引占用多少M的空间，这个脚本就是满足你的要求的，把 中的内容替换一下就可以了。B、脚本原文：SELECT OWNER, SEGMENT_NAME, SUM (BYTES)/1024/1024 FROM DBA_SEGMENTS WHERE OWNER= AND SEGMENT_NAME= GROUP BY OWNER,SEGMENT_NAME ORDER BY 3 DESC ; 查看数据

24、库中的表锁A、 脚本说明：这方面的语句的样式是很多的，各式一样， 不过我认为这个是最实用的，不信你就用一下， 无需多说， 锁是每个 DBA一定都涉及过的内容， 当你相知道某个表被哪个 session 锁定了，你就用到了这个脚本。B、脚本原文SELECT A.OWNER, A.OBJECT_NAME, B.XIDUSN, 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 7 页 - - - - - - - - - B.XIDSLOT, B.XIDSQN, B.SESSION_

25、ID, B.ORACLE_USERNAME, B.OS_USER_NAME, B.PROCESS, B.LOCKED_MODE, C.MACHINE, C.STATUS, C.SERVER, C.SID, C.SERIAL#, C.PROGRAM FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS .GV_$SESSION C WHERE ( A.OBJECT_ID = B.OBJECT_ID ) AND (B.PROCESS = C.PROCESS ) - AND ORDER BY 1,2 ; 处理存储过程被锁A、 脚本说明：实际过程中可能你要重新编译某个存

26、储过程理总是处于等待状态，最后会报无法锁定对象，这时你就可以用这个脚本找到锁定过程的那个sid ，需要注意的是查 v$access 这个视图本来就很慢，需要一些布耐心。B、脚本原文：SELECT * FROM V$ACCESS WHERE OWNER= ANDOBJECT? 查看回滚段状态A、 脚本说明这也是 DBA经常使用的脚本，因为回滚段是 online还是 full是他们的关怀之列嘛B、脚本原文：SELECT A.SEGMENT_NAME,B.STATUS FROM DBA_ROLLBACK_SEGS A, V$ROLLSTAT B WHERE A.SEGMENT_ID=B.USN 名师

27、资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 7 页 - - - - - - - - - ORDER BY 2 看哪些 session 正在使用哪些回滚段A、 脚本说明：当你发现一个回滚段处理full状态，你想使它变回 online 状态，这时你便会用alter rollback segment rbs_seg_name shrink,可很多时侯确 shrink不回来，主要是由于某个 session 在用， 这时你就用到了这个脚本， 找到了 sid 的 serial#余下的

28、事就不用我说了吧。B、脚本原文SELECT R. NAME回滚段名 , S.SID, S.SERIAL#, S.USERNAME 用户名, S.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 - AND R.NAME IN (ZHYZ_RBS)ORDER? BY T.

29、CR_GET,T.PHY_IO 查看正在使用临时段的session A、 脚本说明：许多的时侯你在查看哪些段无法扩展时，回显的结果是临时段， 或你做表空间统计时发现临段表空间的可用空间几乎为0, 这时按 oracle 的说法是你只有重新启动数据库才能回收这部分空间。 实际过程中没那么复杂， 使用以下这段脚本把占用临时段的 session 杀掉，然后用 alter tablespace temp coalesce;这个语句就把 temp 表空间的空间回收回来了。B、 脚本原文SELECT USERNAME,SID,SERIAL#,SQL_ADDRESS,MACHINE,PROGRAM, TABLESPACE,SEGTYPE, CONTENTS FROM V$SESSION SE,V$SORT_USAGE SU WHERE SE.SADDR=SU.SESSION_ADDR; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -