实用的数据库检查程序(3)

  1<name> coalesce" to coalesce 
  2
  3prompt &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; NOTE: &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
  4
  5prompt 
  6
  7ttitle off 
  8
  9prompt 
 10
 11prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++ 
 12
 13prompt + 11.0 Latch Contention  \+ 
 14
 15prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++ 
 16
 17prompt 
 18
 19ttitle left "******** Latch Information ******" Skip 1 
 20
 21column "Redo Type" format A20 
 22
 23column gets format 9,999,990 
 24
 25column WILLING_TO_WAIT format 9,999,990 
 26
 27column misses format 9,999,990 
 28
 29column "IMMEDIATE" FORMAT 999.99 
 30
 31select Name "Redo Type", gets , misses, 
 32
 33decode(gets,0,0,(100*(misses/(gets + misses)))) WILLING_TO_WAIT, 
 34
 35sleeps,immediate_gets,immediate_misses, 
 36
 37decode(immediate_gets,0,0,(100*(immediate_misses/(immediate_gets + immediate_misses)))) "IMMEDIATE" 
 38
 39from v$latch 
 40
 41where name like 'redo%' 
 42
 43order by name 
 44
 45/ 
 46
 47prompt 
 48
 49prompt &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; NOTE: &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
 50
 51prompt 11.0 Investigation 
 52
 53prompt If Willing_to_wait and Immediate is greater than 1%, 
 54
 55prompt increase Log_Simultaneous_copies to twice # of CPUs, 
 56
 57prompt and decrease Log_Small_Entry_Max_Size in init.ora file 
 58
 59prompt &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; NOTE: &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
 60
 61prompt 
 62
 63ttitle off 
 64
 65prompt 
 66
 67prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++ 
 68
 69prompt + 11.1 Latch Contention (Reveal Net)  \+ 
 70
 71prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++ 
 72
 73prompt 
 74
 75ttitle left "******** Latch Information ******" Skip 1 
 76
 77SELECT a.name, 
 78
 79100\. * b.sleeps / b.gets ratio1, 
 80
 81100\. * b.immediate_misses / DECODE ( (b.immediate_misses + b.immediate_gets), 0, 1) ratio2 
 82
 83FROM v$latchname a, v$latch b 
 84
 85WHERE a.latch# = b.latch# 
 86
 87AND b.sleeps &gt; 0 
 88
 89/ 
 90
 91prompt 
 92
 93prompt &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; NOTE: &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
 94
 95prompt 11.1 Investigation 
 96
 97prompt 
 98
 99prompt &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; NOTE: &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
100
101prompt 
102
103ttitle off 
104
105prompt 
106
107prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++ 
108
109prompt + 12.0 Tablespace Usage  \+ 
110
111prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++ 
112
113prompt 
114
115set pagesize 66 
116
117clear breaks 
118
119clear computes 
120
121column "Total Bytes" format 9,999,999,999,999 
122
123column "SQL Blocks" format 9,999,999,999 
124
125column "Bytes Free" format 9,999,999,999,999 
126
127column "Bytes Used" format 9,999,999,999,999 
128
129column "% Free" format 9999.999 
130
131column "% Used" format 9999.999 
132
133break on report 
134
135compute sum of "Total Bytes" on report 
136
137compute sum of "SQL Blocks" on report 
138
139compute sum of "Bytes Free" on report 
140
141compute sum of "Bytes Used" on report 
142
143compute sum of "% Free" on report 
144
145compute sum of "% Used" on report 
146
147TTitle left "*** Database: "xdbname", Current Tablespace Usage (As of: "xdate") ***" skip 1 
148
149select substr(fs.FILE_ID,1,3) "ID#", 
150
151fs.tablespace_name, 
152
153df.bytes "Total Bytes", 
154
155df.blocks "SQL Blocks", 
156
157sum(fs.bytes) "Bytes Free", 
158
159(100*((sum(fs.bytes))/df.bytes)) "% Free", 
160
161df.bytes-sum(fs.bytes) "Bytes Used", 
162
163100*((df.bytes-sum(fs.bytes))/df.bytes) "% Used" 
164
165from sys.dba_data_files df,sys.dba_free_space fs 
166
167where df.file_id(+) = fs.file_id 
168
169group by fs.file_id,fs.tablespace_name,df.bytes,df.blocks 
170
171order by fs.tablespace_name 
172
173/ 
174
175prompt 
176
177prompt &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; NOTE: &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
178
179prompt 12.0 Invesigation 
180
181prompt if a tablespace has all datafiles with % Used greater 
182
183prompt &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; NOTE: &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
184
185prompt 
186
187ttitle off 
188
189prompt 
190
191prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++ 
192
193prompt + 13.0 Disk Activity  \+ 
194
195prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++ 
196
197prompt 
198
199column "File Name" format a35 
200
201column "File Total" format 999,999,999,990 
202
203set pagesize 33 
204
205ttitle "*** Database: "xdbname", Datafile Disk Activity (As of : "xdate") ***" 
206
207select substr(df.file#,1,2) "ID", 
208
209rpad(name,35,'.') "File Name", 
210
211rpad(substr(phyrds,1,10),10,'.') "Phy Reads", 
212
213rpad(substr(phywrts,1,10),10,'.') "Phy Writes", 
214
215rpad(substr(phyblkrd,1,10),10,'.') "Blk Reads", 
216
217rpad(substr(phyblkwrt,1,10),10,'.') "Blk Writes", 
218
219rpad(substr(readtim,1,9),9,'.') "Read Time", 
220
221rpad(substr(writetim,1,10),10,'.') "Write Time", 
222
223sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim) "File Total" 
224
225from v$filestat fs,v$datafile df 
226
227where fs.file# = df.file# 
228
229group by df.file#,df.name,phyrds,phywrts,phyblkrd, 
230
231phyblkwrt,readtim, writetim 
232
233order by sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim) desc , df.name 
234
235/ 
236
237prompt 
238
239prompt &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; NOTE: &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
240
241prompt 13.0 Investigation 
242
243prompt To reduce disk contention , insure that datafiles 
244
245prompt with the greatest activity are not on the same disk . 
246
247prompt &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; NOTE: &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
248
249prompt 
250
251ttitle off 
252
253prompt 
254
255prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++ 
256
257prompt + 14.0 Fragmentation Need  \+ 
258
259prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++ 
260
261prompt 
262
263set heading on 
264
265set termout on 
266
267set pagesize 66 
268
269ttitle left "*** Database: "xdbname", DEFRAGMENTATION NEED AS OF: "xdate" ***" 
270
271select substr(de.owner,1,8) "Owner", 
272
273substr(de.segment_type,1,8) "Seg Type", 
274
275substr(de.segment_name,1,35) "Table Name (Segment)", 
276
277substr(de.tablespace_name,1,20) "TableSpace Name", 
278
279COUNT(*) "Frag Need", 
280
281substr(df.name,1,40) "Datafile Name" 
282
283from sys.dba_extents de,v$datafile df 
284
285where de.owner &lt;&gt; 'SYS' 
286
287and de.file_id = df.file# 
288
289and de.segment_type = 'TABLE' 
290
291group by de.owner,de.segment_name,de.segment_type,de.tablespace_name,df.name 
292
293having count(*) &gt; 1 
294
295order by count(*) desc 
296
297/ 
298
299prompt 
300
301prompt &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; NOTE: &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;
302
303prompt 14.0 Investigation 
304
305prompt The more fragmented a segment is , the more I/O needed to read 
306
307prompt that info . Defragments this table regularly to insure extents 
308
309prompt ('Frag NEED') do not get much above 2. 
310
311prompt &lt;&lt;&lt;&lt;</name>