Learning Bee,
Here are two scripts that will help you out better. IMO
this first one will tell you how many sorts are taking place
As you can see most of the information that my users want is already in memory.Code:select a.value "Disk Sorts",b.value "Memory Sorts",round(100*b.value)/decode((a.value+b.value), 0,1,(a.value+b.value)) "Pct Memory Sorts" from V$sysstat a, V$sysstat b where a.name='sorts (disk)' and b.name='sorts (memory)'; Disk Sorts Memory Sorts Pct Memory Sorts ---------- ------------ ---------------- 56864 186844662 99.9695754
Now this one will tell you what your db_cache hit ratio is.
Using these two queries should tell you without a shadow of doubt if your db_cache is big enough or not.Code:select 1-(sum(decode(name,'physical reads',value,0))/ (sum(decode(name,'db block gets',value,0)) + (sum(decode(name,'consistent gets',value,0))))) "Hit Ratio" from V$sysstat; Hit Ratio ---------- .958470875
Have that Oracle Kind of day!




Reply With Quote