You Asked
***Code from Oracle Performance Tuning Tips & Techniques
- Richard J Niemiec
set serveroutput on
Declare
cursor c1 is
SELECT buffer_gets
FROM v$sqlarea
ORDER BY buffer_gets DESC ;
cursor c2 is
SELECT sum(buffer_gets)
FROM v$sqlarea ;
sumof10 Number:= 0;
mybg Number:= 0;
mytotbg Number:= 0;
Begin
dbms_output.put_line('Percent');
dbms_output.put_line('-------');
Open c1 ;
for i in 1..10 loop
fetch c1 into mybg ;
sumof10 := sumof10 + mybg ;
end loop ;
close c1 ;
open c2 ;
fetch c2 into mytotbg ;
close c2 ;
dbms_output.put_line(round((sumof10/mytotbg*100),2));
End;
/
" Top Memory Abusers as a Percent of all Statements"
Percent
-------
87.93
The Question is the above percentage bad ?
Will it affect the queries running against the database ?
or does it not affect at all ?
Thanks in advance .
and Tom said...
The answer is: insufficient data to arrive at a conclusive answer.
The only thing the above percentage tells you is that the 10 queries with the MOST cumulative buffer gets have done 88 percent of your buffer gets.
Is that bad? Well, it depends on...
o how many items do you have in your sqlarea. the more then have, the lower you might expect this percentage to be. In a well tuned system, with maximal use of shared sql, this percentage could be very high (as you have fewer sql statements floating around). On the other hand, if you have 10's of thousands of sql statements in the shared pool, this high percentage might be a flag (but then again, if you have 10's of thousands of queries in the shared pool you've probably forgot to use bind variables and your tuning lies in that direction first!)
o how many times are those queries executed (especially in respect to how many times other queries are executed). I would rather tune the query that is executed 10 times and hits 1,000,000 buffers (100,000 buffer gets/query on avg) rather then tune the query that is executed 1,000 times and hits 2,000,000 buffers (2,000 buffer gets/query on avg).
o whether you consider buffer gets a bad thing. sometimes they are unavoidable.
By itself, this number is rather meaningless in my opinion. You might use it to look at queries that take alot of resources but you really need to consider how many statements you have, the number of times these "bad" ones are actually executed and so on. Each query must be looked at in isolation then. There is no threshold I am aware of you want to be hitting with this.
Is this answer out of date? If it is, please let us know via a Comment