Requête Hit ratios et shared pool

**bibi92** · 02/09/2008, 10h27

Bonjour,

Je suis à la recherche de requêtes interrogeant les tables stats$ pour avoir le BUFFER CACHE HIT RATIO, CACHE HIT RATIO, RBS PERFORMANCE HIT RATIO ainsi que la shared pool.

Merci

Bonne journée

Bibi92

**billharry** · 03/09/2008, 10h18

Bonjour,

Pour le BUFFER CACHE HIT RATIO :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
      clear breaks
      clear computes
      clear columns
      column pct heading "Hit Ratio (%)" format 999.9
 
      ttitle  'Buffer Cache Checks - Goal, above 95%' skip
 
      select  ((1- (sum(decode(a.name,'physical reads',value,0)))/
               (sum(decode(a.name,'db block gets',value,0)) +
                sum(decode(a.name,'consistent gets',value,0)))) * 100) "PERCENT"
      from    v$sysstat a;

Pour le LIBRARY CACHE HIT RATIO :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
      clear breaks
      clear computes
      clear columns
      set heading off
 
      column sum_pins format 999,999,999  
      column sum_reloads format 999,999,999  
      column hit_ratio format 999.99999
      ttitle 'PINS and Library Cache' skip
 
      select 'PINS - # of times an item in the library cache was executed - '||
              sum(pins) sum_pins, 
              'RELOADS - # of library cache misses on execution steps - '||
              sum(reloads) sum_reloads,  
              'Pin hit ratio should be close to 1.0  - '||
              ROUND((sum(reloads)/sum(pins)),6) hit_ratio
      from   v$librarycache;

Pour le RBS PERFORMANCE HIT RATIO

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
      clear breaks
      clear computes
      clear columns
 
      ttitle 'GET WAIT RATIO ROLLBACK REPORT'
      column "Ratio" format 999.99999999 
      column name format A15
      column "PERCENT" 
      select  name, 
              waits, 
              gets, 
              100-(waits/gets) "Ratio",
              (waits/gets)*100 "PERCENT"
      from    v$rollstat a, v$rollname b  
      where   a.usn = b.usn;

**billharry** · 03/09/2008, 10h19

Pour un script de tuning bien complet:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
spool tfsrstat.lst 
set echo off  
set feedback off 
 
prompt**************************************************** 
prompt Hit Ratio Section  
prompt**************************************************** 
prompt  
prompt  =========================  
prompt  BUFFER HIT RATIO  
prompt  =========================  
prompt (should be > 70, else increase db_block_buffers in init.ora)  
 
select trunc((1-(sum(decode(name,'physical reads',value,0))/  
             (sum(decode(name,'db block gets', value,0))+ 
    (sum(decode(name,'consistent gets',value,0)))))  
              )* 100) "Buffer Hit Ratio"  
 from v$sysstat;  
 
column "logical_reads" format 99,999,999,999  
column "phys_reads" format 999,999,999  
column "phy_writes" format 999,999,999  
select a.value + b.value "logical_reads",  
                c.value   "phys_reads",  
                d.value   "phy_writes",  
                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, v$sysstat d  
where  
        a.name = 'db block gets'  
and  
        b.name = 'consistent gets'  
and  
        c.name = 'physical reads'
and  
        d.name = 'physical writes';  
 
prompt  
prompt  
prompt  =========================  
prompt  DATA DICT HIT RATIO  
prompt  =========================  
prompt (should be higher than 90 else increase shared_pool_size in init.ora)  
prompt  
 
column "Data Dict. Gets"   format 999,999,999  
column "Data Dict. cache misses" format 999,999,999  
select sum(gets) "Data Dict. Gets",  
      sum(getmisses) "Data Dict. cache misses",  
      trunc((1-(sum(getmisses)/sum(gets)))*100)  
      "DATA DICT CACHE HIT RATIO"  
from v$rowcache;  
 
prompt  
prompt  =========================  
prompt  LIBRARY CACHE MISS RATIO  
prompt  =========================  
prompt (If > .1, i.e., more than 1% of the pins resulted in reloads, then  
prompt increase the shared_pool_size in init.ora)  
prompt  
column "LIBRARY CACHE MISS RATIO" format 99.9999  
column "executions" format 999,999,999  
column "Cache misses while executing" format 999,999,999  
select sum(pins) "executions", sum(reloads) "Cache misses while executing",  
                (((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"  
from v$librarycache;  
 
prompt  
prompt  =========================  
prompt  Library Cache Section  
prompt  =========================  
prompt hit ratio should be > 70, and pin ratio > 70 ...  
prompt  
 
column "reloads" format 999,999,999  
select namespace, trunc(gethitratio * 100) "Hit ratio",  
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"  
from v$librarycache;  
prompt  
prompt  
prompt  =========================  
prompt  REDO LOG BUFFER  
prompt  =========================  
prompt  
set heading off  
column value format 999,999,999  
select substr(name,1,30),  
                value  
from v$sysstat where name = 'redo log space requests';  
 
set heading on  
prompt ****************************************************
prompt Pool Free Memory
prompt ****************************************************
 
column bytes format 999,999,999,999  
select pool,name, bytes from v$sgastat where name = 'free memory'; 
 
prompt  
prompt****************************************************  
prompt SQL Summary Section  
prompt****************************************************  
prompt  
column "Tot SQL run since startup" format 999,999,999  
column "SQL executing now"  format 999,999,999  
select sum(executions) "Tot SQL run since startup",  
                sum(users_executing) "SQL executing now"  
                from v$sqlarea;  
 
prompt  
prompt  
prompt****************************************************  
prompt Lock Section  
prompt****************************************************  
prompt  
prompt  =========================  
prompt SYSTEM-WIDE LOCKS - all requests for locks or latches  
prompt  =========================  
prompt 
prompt Processing Locks and Latches, please standby...
 
select /*+ ordered */ substr(username,1,12) "User",  
          substr(lock_type,1,18) "Lock Type",  
                substr(mode_held,1,18) "Mode Held"  
        from v$session b ,sys.dba_lock a
        where lock_type not in ('Media Recovery','Redo Thread')  
        and a.session_id = b.sid;  
prompt  
prompt  =========================  
prompt DDL LOCKS - These are usually triggers or other DDL  
prompt  =========================  
prompt  
select substr(username,1,12) "User",  
                substr(owner,1,8) "Owner",  
     substr(name,1,15) "Name",  
                substr(a.type,1,20) "Type",  
             substr(mode_held,1,11) "Mode held"  
from sys.dba_ddl_locks a, v$session b  
        where a.session_id = b.sid;  
 
prompt  
prompt  =========================  
prompt  DML LOCKS - These are table and row locks...  
prompt  =========================  
prompt  
select substr(username,1,12) "User",  
 substr(owner,1,8) "Owner",  
                substr(name,1,20) "Name",  
        substr(mode_held,1,21) "Mode held"  
from sys.dba_dml_locks a, v$session b  
        where a.session_id = b.sid;  
 
prompt  
prompt  
prompt****************************************************  
prompt Latch Section  
prompt****************************************************  
prompt if miss_ratio or immediate_miss_ratio > 1 then latch  
prompt contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora  
prompt  
column "miss_ratio" format 999.99  
column "immediate_miss_ratio" format 999.99  
select substr(l.name,1,30) name,  
        (misses/(gets+.001))*100 "miss_ratio",  
        (immediate_misses/(immediate_gets+.001))*100  
        "immediate_miss_ratio"  
        from v$latch l, v$latchname ln  
where l.latch# = ln.latch#  
        and (  
        (misses/(gets+.001))*100 > .2  
or  
        (immediate_misses/(immediate_gets+.001))*100 > .2  
)  
order by l.name;  
 
prompt  
prompt  
prompt****************************************************  
prompt Rollback Segment Section  
prompt****************************************************  
prompt if any count below is > 1% of the total number of requests for data  
prompt then more rollback segments are needed  
 
 --column count format 999,999,999  
select class, count  
        from v$waitstat  
where class in ('free list','system undo header','system undo block',  
                        'undo header','undo block')  
group by class,count;  
 
column "Tot # of Requests for Data" format 999,999,999  
 
select sum(value) "Tot # of Requests for Data" from v$sysstat where  
name in ('db block gets', 'consistent gets');  
 
 
prompt  
prompt  =========================  
prompt  ROLLBACK SEGMENT CONTENTION  
prompt  =========================  
prompt  
prompt   If any ratio is > .01 then more rollback segments are needed  
 
column "Ratio" format 99.99999  
select name, waits, gets, waits/gets "Ratio"  
        from v$rollstat a, v$rollname b  
where a.usn = b.usn;  
 
column "total_waits" format 999,999,999  
column "total_timeouts" format 999,999,999  
prompt  
prompt  
set feedback on;  
prompt****************************************************  
prompt Session Event Section  
prompt****************************************************  
prompt if average-wait > 20 then contention might exists  
prompt  
        select substr(event,1,30) event,  
                total_waits, total_timeouts, average_wait  
 from v$session_event  
where average_wait > 0 
and sid not in (select s.sid from v$session s
            where s.paddr in (select p.addr from v$process p
                              where p.background='1'))
and event not like '%SQL%from%'
and event not like '%rdbms%'
and event not like '%wake%'
/  
 
prompt  
prompt  
prompt****************************************************  
prompt Queue Section  
prompt****************************************************  
prompt average wait for queues should be near zero ...  
prompt  
column "totalq" format 999,999,999  
column "# queued" format 999,999,999  
select paddr, type "Queue type", queued "# queued", wait, totalq,  
decode(totalq,0,0,wait/totalq) "AVG WAIT" from v$queue;  
 
set feedback on;  
prompt  
prompt  
prompt****************************************************  
prompt Multi-threaded Server Section  
prompt****************************************************  
prompt  
prompt If the following number is > 1  
prompt then increase MTS_MAX_SERVERS parm in init.ora  
prompt  
 select decode( totalq, 0, 'No Requests',  
    wait/totalq || ' hundredths of seconds')  
    "Avg wait per request queue"  
 from v$queue  
 where type = 'COMMON';  
 
set feedback off
 
prompt  
prompt If the following number increases, consider adding dispatcher processes 
prompt   
prompt  
 select decode( sum(totalq), 0, 'No Responses',  
      sum(wait)/sum(totalq) || ' hundredths of seconds')  
              "Avg wait per response queue"  
 from v$queue q, v$dispatcher d  
 where q.type = 'DISPATCHER'  
      and q.paddr = d.paddr;  
 
 
prompt  
prompt  
prompt        =========================  
prompt        DISPATCHER USAGE  
prompt        =========================  
prompt (If Time Busy > 50, then change 
prompt        MTS_MAX_DISPATCHERS in init.ora)  
column "Time Busy" format 999,999.999  
column busy  format 999,999,999  
column idle  format 999,999,999  
select name, status, idle, busy,  
 (busy/(busy+idle))*100 "Time Busy"  
from v$dispatcher;  
 
 prompt  
 prompt  
 select count(*) "Shared Server Processes"  
  from v$shared_server  
  where status = 'QUIT';  
 
 prompt  
 prompt  
 prompt high-water mark for the multi-threaded server  
 prompt  
 
 select * from v$mts;  
 
 prompt  
 prompt****************************************************  
 prompt file i/o should be evenly distributed across drives.  
 prompt  
 
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 b  
where a.file# = b.file#;  
 
column value format 999,999,999,999  
select substr(name,1,55) system_statistic, value  
 from v$sysstat  
 order by name;  
 
spool off;

**bibi92** · 04/09/2008, 17h55

Bonjour,

Merci, mais je cherchais en fait la possibilité d'avoir le même résultat à l'aide des tables de statpack stat$.

Merci

Bonne soirée

Bibi92

**bibi92** · 05/09/2008, 08h00

Bonjour,

Merci, mais je cherchais en fait la possibilité d'avoir le même résultat à l'aide des tables de statpack stat$.

Merci

Bonne soirée

Bibi92