Oracle(10g+)常规诊断

1-------------------Oracle(10g+)常规诊断-------------------
2/*
3数据库突然变慢，普通用户权限，常规诊断
41.检查数据库的等待事件
52.检查锁
63.查看当前会话连接数，是否属于正常范围
74.检查行链接/迁移
85.检查表空间使用情况
9
10如果上面检查不出问题，建议申请权限做AWR报告分析。
11权限：
12GRANT ADVISOR TO user;
13GRANT SELECT_CATALOG_ROLE TO user;
14GRANT EXECUTE ON sys.dbms_workload_repository TO user;
15
16创建快照：
17exec sys.dbms_workload_repository.CREATE_SNAPSHOT;
18
19执行脚本awrrpt.sql($ORACLE_HOME/rdbms/admin/)
20--输入你想要的展现格式，html or text
21--输入你想要查看多少天内的snap_id
22Enter value for num_days: --这里过去几天
23--输入begin_snapid
24begin_snapid为显示过去几天信息中的Snap Id
25--输入end_snapid
26begin_snapid为显示过去几天信息中的Snap Id
27--输入要保存的文件名
28
29*/
30
31-------------------Oracle对象状态-------------------
32/*
33检查Oracle控制文件状态
34输出结果应该至少有2条，一般有3条以上（包含3条）的记录，“STATUS”应该为空。
35状态为空表示控制文件状态正常。
36*/
37select status, name from v$controlfile;
38
39/*
40检查Oracle在线日志状态
41输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。 
42注：“STATUS”显示为空表示正常
43*/
44select group#, status, type, member from v$logfile;
45
46/*
47检查Oracle表空间的状态
48输出结果中STATUS应该都为ONLINE。
49*/
50select tablespace_name, status from dba_tablespaces;
51
52/*
53检查Oracle所有数据文件状态
54输出结果中“STATUS”应该都为“ONLINE”。或者输出结果中“STATUS”应该都为“AVAILABLE”。
55*/
56select name, status from v$datafile;
57
58/*
59检查无效对象
60如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成这个对象
61*/
62select owner, object_name, object_type
63  from dba_objects
64 where status != 'VALID'
65   and owner != 'SYS'
66   and owner != 'SYSTEM';
67
68/*
69检索无效对象
70*/
71SELECT owner, object_name, object_type
72  FROM dba_objects
73 WHERE status = 'INVALID';
74
75/*
76检查所有回滚段状态
77在10G中会根据事务数量自动调整OFFLINE，ONLINE
78*/
79select segment_name, status from dba_rollback_segs;
80
81-------------------Oracle相关资源使用-------------------
82
83/*
84检查Oracle初始化文件中相关参数值
85若LIMIT_VALU-MAX_UTILIZATION<=5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。
86可以通过修改Oracle初始化参数文件$ORACLE_BASE/admin/CKDB/pfile/initORCL.ora来修改
87*/
88select resource_name, max_utilization, initial_allocation, limit_value
89  from v$resource_limit;
90
91/*
92查看当前会话连接数，是否属于正常范围。
93如果建立了过多的连接，会消耗数据库的资源，同时，对一些“挂死”的连接可能需要手工进行清理。
94*/
95select count(*) from v$session;
96
97/*
98如果用户使用的表空间空闲率%Free小于10%以上（包含10%），则注意要增加数据文件来扩展表空间而不要是用数据文件的自动扩展功能。
99*/
100select f.tablespace_name,
101       a.total,
102       f.free,
103       round((f.free / a.total) * 100) "% Free"
104  from (select tablespace_name, sum(bytes / (1024 * 1024)) total
105          from dba_data_files
106         group by tablespace_name) a,
107       (select tablespace_name, round(sum(bytes / (1024 * 1024))) free
108          from dba_free_space
109         group by tablespace_name) f
110 WHERE a.tablespace_name = f.tablespace_name(+)
111 order by "% Free";
112
113/*
114检查一些扩展异常的对象
115如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数。
116*/
117select Segment_Name,
118       Segment_Type,
119       TableSpace_Name,
120       (Extents / Max_extents) * 100 Percent
121  From sys.DBA_Segments
122 Where Max_Extents != 0
123   and (Extents / Max_extents) * 100 >= 95
124 order By Percent;
125
126/*
127检查system表空间内的内容
128如果记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。
129如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值。
130*/
131select distinct (owner)
132  from dba_tables
133 where tablespace_name = 'SYSTEM'
134   and owner != 'SYS'
135   and owner != 'SYSTEM'
136union
137select distinct (owner)
138  from dba_indexes
139 where tablespace_name = 'SYSTEM'
140   and owner != 'SYS'
141   and owner != 'SYSTEM';
142
143/*
144检查对象的下一扩展与表空间的最大扩展值
145如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需调整相应表空间的存储参数。
146*/
147select a.table_name, a.next_extent, a.tablespace_name
148  from all_tables a,
149       (select tablespace_name, max(bytes) as big_chunk
150          from dba_free_space
151         group by tablespace_name) f
152 where f.tablespace_name = a.tablespace_name
153   and a.next_extent > f.big_chunk
154union
155select a.index_name, a.next_extent, a.tablespace_name
156  from all_indexes a,
157       (select tablespace_name, max(bytes) as big_chunk
158          from dba_free_space
159         group by tablespace_name) f
160 where f.tablespace_name = a.tablespace_name
161   and a.next_extent > f.big_chunk;
162
163-------------------Oracle数据库性能-------------------
164
165/*
166检查数据库的等待事件
167如果数据库长时间持续出现大量像latch free，enqueue，buffer busy waits，
168db file sequential read，db file scattered read等等待事件时，需要对其进行分析，可能存在问题的语句。
169建议做AWR报告分析。
170*/
171select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT
172  from v$session_wait
173 where event not like 'SQL%'
174   and event not like 'rdbms%';
175
176/*
177查找前十条性能差的sql
178注：仅供参考,v$视图提供的不一定准确，以AWR报告分析为准。
179*/
180SELECT *
181  FROM (SELECT PARSING_USER_ID EXECUTIONS,
182               SORTS,
183               COMMAND_TYPE,
184               DISK_READS,
185               SQL_TEXT
186          FROM V$SQLAREA
187         ORDER BY DISK_READS DESC)
188 WHERE ROWNUM < 10;
189
190/*
191等待时间最多的5个系统等待事件的获取
192*/
193SELECT *
194  FROM (SELECT *
195          FROM V$SYSTEM_EVENT
196         WHERE EVENT NOT LIKE 'SQL%'
197         ORDER BY TOTAL_WAITS DESC)
198 WHERE ROWNUM <= 5;
199
200/*
201检查碎片程度高的表
202*/
203SELECT segment_name table_name, COUNT(*) extents
204  FROM dba_segments
205 WHERE owner NOT IN ('SYS', 'SYSTEM')
206 GROUP BY segment_name
207HAVING COUNT(*) = (SELECT MAX(COUNT(*))
208                     FROM dba_segments
209                    GROUP BY segment_name);
210
211/*
212检查表空间的 I/O 比例
213*/
214SELECT DF.TABLESPACE_NAME NAME,
215       DF.FILE_NAME       "FILE",
216       F.PHYRDS           PYR,
217       F.PHYBLKRD         PBR,
218       F.PHYWRTS          PYW,
219       F.PHYBLKWRT        PBW
220  FROM V$FILESTAT F, DBA_DATA_FILES DF
221 WHERE F.FILE# = DF.FILE_ID
222 ORDER BY DF.TABLESPACE_NAME;
223
224/*
225检查文件系统的I/O比例
226*/
227SELECT SUBSTR(A.FILE#, 1, 2) "#",
228       SUBSTR(A.NAME, 1, 30) "NAME",
229       A.STATUS,
230       A.BYTES,
231       B.PHYRDS,
232       B.PHYWRTS
233  FROM V$DATAFILE A, V$FILESTAT B
234 WHERE A.FILE# = B.FILE#;
235
236/*
237检测回滚段争用
238SUM(waits)值应小于SUM(gets)值的1%
239*/
240select sum(gets), sum(waits), sum(waits) / sum(gets) from v$rollstat;
241
242/*
243回卷段的竟争会降低系统的性能。如果GETS与WAITS的比大于2%表示存在竟争问题
244*/
245select rn.name,
246       rs.gets as 被访问次数,
247       rs.waits as 等待回退段块的次数,
248       (rs.waits / rs.gets) * 100 as 命中率
249  from v$rollstat rs, v$rollname rn;
250
251/*
252检查锁
253*/
254select sid,
255       serial#,
256       username,
257       SCHEMANAME,
258       osuser,
259       MACHINE,
260       terminal,
261       PROGRAM,
262       owner,
263       object_name,
264       object_type,
265       o.object_id
266  from dba_objects o, v$locked_object l, v$session s
267 where o.object_id = l.object_id
268   and s.sid = l.session_id;
269
270/*
271查看是否有僵死进程
272*/
273select spid from v$process where addr not in (select paddr from v$session);
274
275/*
276检查行链接/迁移
277*/
278select table_name, num_rows, chain_cnt
279  From dba_tables
280 Where owner = 'CTAIS2'
281   And chain_cnt <> 0;
282
283/*
284检查缓冲区命中率
285如果命中率低于90% 则需加大数据库参数db_cache_size
286*/
287SELECT a.VALUE + b.VALUE logical_reads,
288       c.VALUE phys_reads,
289       round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio
290  FROM v$sysstat a, v$sysstat b, v$sysstat c
291 WHERE a.NAME = 'db block gets'
292   AND b.NAME = 'consistent gets'
293   AND c.NAME = 'physical reads';
294
295/*
296检查共享池命中率
297如低于95%，则需要调整应用程序使用绑定变量，或者调整数据库参数shared pool的大小。
298*/
299select sum(pinhits) / sum(pins) * 100 from v$librarycache;
300
301/*
302检查排序区
303如果disk/(memoty+row)的比例过高，则需要调整sort_area_size(workarea_size_policy=false)
304或pga_aggregate_target(workarea_size_policy=true)。
305*/
306select name, value from v$sysstat where name like '%sort%';
307
308/*
309检查日志缓冲区
310如果redo buffer allocation retries/redo entries 超过1% ，则需要增大log_buffer。
311*/
312select name, value
313  from v$sysstat
314 where name in ('redo entries', 'redo buffer allocation retries');
315
316-------------------Oracle数据库其他检查-------------------
317
318/*
319检查失效的索引
320注：分区表上的索引status为N/A是正常的，如有失效索引则对该索引做rebuild，
321如：alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME;
322*/
323select index_name, table_name, tablespace_name, status
324  From dba_indexes
325 Where owner = 'CTAIS2'
326   And status <> 'VALID';
327
328/*
329检查不起作用的约束
330如有失效约束则启用，如：
331alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;
332*/
333SELECT owner, constraint_name, table_name, constraint_type, status
334  FROM dba_constraints
335 WHERE status = 'DISABLE'
336   and constraint_type = 'P';
337
338/*
339检查无效的trigger
340alter Trigger TRIGGER_NAME Enable;
341*/
342SELECT owner, trigger_name, table_name, status
343  FROM dba_triggers
344 WHERE status = 'DISABLED';