oracle性能监控

1. 性能监控之监控SQL语句
2.  
3. 分析表
4. analyze TABLE tablename compute statistics FOR ALL indexes;
5. analyze TABLE tablename compute statistics FOR ALL indexed COLUMNS;
6. analyze TABLE tablename compute statistics FOR TABLE;
7.  
8. 监控事例的等待
9.  
10. SELECT event ,sum (decode (wait_Time ,0 ,0 , 1 ) ) "Prev" ,
11. sum (decode (wait_Time ,0 , 1 ,0 ) ) "Curr" ,count ( * ) "Tot"
12. FROM v$session_Wait
13. GROUP BY event ORDER BY 4;
14.  
15. 查看碎片程度高的表
16.  
17. SELECT segment_name table_name , COUNT ( * ) extents
18. FROM dba_segments WHERE owner NOT IN (SYS , SYSTEM ) GROUP BY segment_name
19. HAVING COUNT ( * ) = ( SELECT MAX ( COUNT ( * ) FROM dba_segments GROUP BY segment_name );
20.  
21. 表、索引的存储情况检查
22.  
23. SELECT segment_name ,sum (bytes ) ,count ( * ) ext_quan FROM dba_extents WHERE
24. tablespace_name =&tablespace_name AND segment_type = TABLE GROUP BY tablespace_name ,segment_name;
25.  
26. SELECT segment_name ,count ( * ) FROM dba_extents WHERE segment_type = INDEX AND owner =&owner
27. GROUP BY segment_name;
28.  
29. 找使用CPU多的用户session
30.  
31. 12是cpu used BY this session
32.  
33. SELECT a .sid ,spid , STATUS ,substr (a .program , 1 , 40 ) prog ,a .terminal ,osuser ,value / 60 / 100 value
34. FROM v$session a ,v$process b ,v$sesstat c
35. WHERE c .statistic #=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
36.  
37. 监控表空间的 I /O 比例
38.  
39. SELECT df .tablespace_name name ,df .file_name "file" ,f .phyrds pyr ,
40. f .phyblkrd pbr ,f .phywrts pyw , f .phyblkwrt pbw
41. FROM v$filestat f , dba_data_files df
42. WHERE f .file # = df.file_id
43. ORDER BY df .tablespace_name;
44.  
45.  
46. 回滚段的争用情况
47.  
48. SELECT name , waits , gets , waits /gets "Ratio"
49. FROM v$rollstat a , v$rollname b
50. WHERE a .usn = b .usn;
51.  
52. 在某个用户下找所有的索引
53.  
54. SELECT user_indexes .table_name , user_indexes .index_name ,uniqueness , column_name
55. FROM user_ind_columns , user_indexes
56. WHERE user_ind_columns .index_name = user_indexes .index_name
57. AND user_ind_columns .table_name = user_indexes .table_name
58. ORDER BY user_indexes .table_type , user_indexes .table_name ,
59. user_indexes .index_name , column_position;
60.  
61.  
62. 监控文件系统的 I /O 比例
63.  
64. SELECT substr (a .file #,1,2) "#", substr(a.name,1,30) "Name",
65. a . STATUS , a .bytes , b .phyrds , b .phywrts
66. FROM v$datafile a , v$filestat b
67. WHERE a .file # = b.file#;
68.  
69. 监控 SGA 中字典缓冲区的命中率
70.  
71. SELECT parameter , gets ,Getmisses , getmisses / (gets +getmisses ) * 100 "miss ratio" ,
72. ( 1 - (sum (getmisses ) / (sum (gets ) +sum (getmisses ) ) ) ) * 100 "Hit ratio"
73. FROM v$rowcache
74. WHERE gets +getmisses <>0
75. GROUP BY parameter , gets , getmisses;
76.  
77. 监控 SGA 中共享缓存区的命中率，应该小于 1％
78.  
79. SELECT sum (pins ) "Total Pins" , sum (reloads ) "Total Reloads" ,
80. sum (reloads ) /sum (pins ) * 100 libcache
81. FROM v$librarycache;
82.  
83. SELECT sum (pinhits -reloads ) /sum (pins ) "hit radio" ,sum (reloads ) /sum (pins ) "reload percent"
84. FROM v$librarycache;
85.  
86. 监控 SGA 的命中率
87.  
88. SELECT a .value + b .value "logical_reads" , c .value "phys_reads" ,
89. round ( 100 * ( (a .value +b .value ) -c .value ) / (a .value +b .value ) ) "BUFFER HIT RATIO"
90. FROM v$sysstat a , v$sysstat b , v$sysstat c
91. WHERE a .statistic # = 38 and b.statistic# = 39
92. AND c .statistic # = 40;
93.  
94. 监控 SGA 中重做日志缓存区的命中率，应该小于 1％
95.  
96. SELECT name , gets , misses , immediate_gets , immediate_misses ,
97. Decode (gets ,0 ,0 ,misses /gets * 100 ) ratio1 ,
98. Decode (immediate_gets +immediate_misses ,0 ,0 ,
99. immediate_misses / (immediate_gets +immediate_misses ) * 100 ) ratio2
100. FROM v$latch WHERE name IN (redo allocation , redo copy );
101.  
102. 显示所有数据库对象的类别和大小
103.  
104. SELECT count (name ) num_instances ,type ,sum (source_size ) source_size ,
105. sum (parsed_size ) parsed_size ,sum (code_size ) code_size ,sum (error_size ) error_size ,
106. sum (source_size ) +sum (parsed_size ) +sum (code_size ) +sum (error_size ) size_required
107. FROM dba_object_size
108. GROUP BY type ORDER BY 2;
109.  
110. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
111.  
112. SELECT name , value FROM v$sysstat WHERE name IN (sorts (memory ) , sorts (disk ) );
113.  
114.  
115. 监控当前数据库谁在运行什么SQL语句
116.  
117. SELECT osuser , username , sql_text FROM v$session a , v$sqltext b
118. WHERE a .sql_address =b .address ORDER BY address , piece;
119.  
120. 监控字典缓冲区
121.  
122. SELECT (SUM (PINS - RELOADS ) ) / SUM (PINS ) "LIB CACHE" FROM V$LIBRARYCACHE;
123. SELECT (SUM (GETS - GETMISSES - USAGE - FIXED ) ) / SUM (GETS ) "ROW CACHE" FROM V$ROWCACHE;
124. SELECT SUM (PINS ) "EXECUTIONS" , SUM (RELOADS ) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
125.  
126. 后者除以前者 ,此比率小于 1％ ,接近0％为好。
127.  
128. SELECT SUM (GETS ) "DICTIONARY GETS" ,SUM (GETMISSES ) "DICTIONARY CACHE GET MISSES"
129. FROM V$ROWCACHE
130.  
131. 监控 MTS
132.  
133. SELECT busy / (busy +idle ) "shared servers busy" FROM v$dispatcher;
134.  
135. 此值大于 0.5时，参数需加大
136.  
137. SELECT sum (wait ) /sum (totalq ) "dispatcher waits" FROM v$queue WHERE type =dispatcher;
138. SELECT count ( * ) FROM v$dispatcher;
139. SELECT servers_highwater FROM v$mts;
140.  
141. servers_highwater接近mts_max_servers时，参数需加大
142.  
143. 碎片程度
144.  
145. SELECT tablespace_name ,count (tablespace_name ) FROM dba_free_space GROUP BY tablespace_name
146. HAVING count (tablespace_name ) > 10;
147.  
148. ALTER tablespace name coalesce;
149. ALTER TABLE name deallocate unused;
150.  
151. CREATE OR REPLACE VIEW ts_blocks_v AS
152. SELECT tablespace_name ,block_id ,bytes ,blocks ,free space segment_name FROM dba_free_space
153. union ALL
154. SELECT tablespace_name ,block_id ,bytes ,blocks ,segment_name FROM dba_extents;
155.  
156. SELECT * FROM ts_blocks_v;
157.  
158. SELECT tablespace_name ,sum (bytes ) ,max (bytes ) ,count (block_id ) FROM dba_free_space
159. GROUP BY tablespace_name;