| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> SGA polling
List,
Several vendors market some tools (extremely expensive ones) which poll the SGA at a subsecond rate by attaching directly to the SGA, since polling the V$ views as fast is out of question. Their argument is that this is the only way not to miss anything. I don't object to that, except that I have always been extremely dubious about the real practical use of such a technical feat. I am ready to miss on 10%, when I am called in for a performance problem it's usually because things are 2 or 3 times slower at least than expected ... My reasoning has always been that, either you have a dreadful query which you can't miss, or queries executed very repetitively which will stay forever in the SGA. The only problem are unbinded fast queries; but first you will certainly catch quite a number, if not all, of them at any moment, and second a high hard-parse rate is easy to spot and is, anyhow, the very first thing to check before proceeding further.
To test my hypothesis, I have run every 5 minutes the following sql script, imbededded in a shell script :
set linesize 250
set pagesize 0
set feedback off
set recsep off
set colsep ' ' -- Tab, to make loading into Excel easier
select sum(decode(s.name, 'logons cumulative', s.value, 0))
logons_cumul,
sum(decode(s.name, 'logons current', s.value, 0)) logons_current,
sum(decode(s.name, 'session logical reads', s.value, 0)) lios,
m.sga_lios,
sum(decode(s.name, 'physical reads', s.value, 0)) pios,
m.sga_pios,
sum(decode(s.name, 'parse count (total)', s.value, 0)) parsing,
m.sga_parse,
sum(decode(s.name, 'execute count', s.value, 0)) executions,
m.sga_exec,
sum(decode(s.name, 'sorts (memory)', s.value,
'sorts (disk)', s.value,
0)) sorts,
m.sga_sort
from v$sysstat s,
(select sum(buffer_gets) SGA_LIOS,
sum(disk_reads) SGA_PIOS,
sum(executions) SGA_EXEC,
sum(parse_calls) SGA_PARSE,
sum(sorts) SGA_SORT
from v$sql
where command_type in (2, 3, 6, 7)) m
where s.name in ('logons cumulative',
'logons current',
'session logical reads',
'physical reads',
'parse count (total)',
'execute count',
'sorts (memory)',
'sorts (disk)')
group by m.sga_lios,
m.sga_pios,
m.sga_parse,
m.sga_exec,
m.sga_sort
Basically the idea is to compare the values recorded in V$SYSSTAT to the values obtained by summing up what we find in V$SQL. Note that I have restrained my query to SELECTs, INSERTs, UPDATEs and DELETEs (that's what
command_type in (...)
means). I was wary of including PL/SQL blocks (command_type 47), which
already aggregate all the 'basic' queries they execute (I know how to
relate them, but this is the type of query I am uncomfortable running
against a production database), as well as invalid rows (command_type
0). I therefore do a 'systemic' error, not counting (especially) COMMITs
nor DDL (I am unsure whether COMMITs really show up as executions too in
V$SYSSTAT).
I have run this against a true production database (not a toy), loaded
the results into Excel, and begun computing for each 5mn slice the
values according to V$SYSSTAT and V$SQL. A funny thing to notice is that
with V$SQL you sometimes have negative values - A function replaces them
with 0 in the spreadsheet (mostly to have scales starting from 0 in
charts). However, it's obvious that a mere interpolation (averaging he
two nearest values) would yield a correct result. I guess it comes from
some periodic 'garbage collection'.
I have uploaded the result at this address :
http://www.roughsea.com/public/SGA_POLL.zip
Bar the 'accidents', the LIO count is close within less than one percent. There is a delay in the PIO count, probably because V$SYSSTAT is updated asynchronously. Interestingly, V$SQL shows more PIOs than V$SYSSTAT. The difference in the number of executions is very small, and I was expecting *some* anyway. The only place where there is any noticeable difference is with sorts. Unfortunately, V$SQL (at least in 9.2) doesn't make any difference between memory and disk sorts. My feeling is that missed sorts belong to this myriad of small memory sorts. As you can see from the figures, that was a 'serious' database (telco).
My conclusion is that my gut feeling was good, and that you can have, let's say in 99.99% of cases (always let the door open to the unexpected :-)) a perfectly valid image of what is occurring (once again, assuming no massive hard-parse problem) by querying V$SQL at a relatively sedate rate. And that anyway you can have a fair estimate of what you are missing by checking V$SYSSTAT.
Would be glad to hear of your views, and even gladder if you could run the same test on some of your bases (the query runs reasonably fast).
And thanks for having taken the pain to read so far ...
Stephane Faroult
Oriole Ltd
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon May 17 2004 - 03:31:38 CDT
 |
 |