| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SGA polling
Jonathan - Thank you for your as-always excellent remarks.
Stephane - Cary also discusses this topic in his book, Optimizing Oracle
Performance, comparing the usefulness of these tools to the trace file
method.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
Sent: Monday, May 17, 2004 5:00 AM
To: oracle-l_at_freelists.org
Subject: Re: SGA polling
A quick comment on the sorts.
The column in v$sql is defined as 16 bit, so
it rolls over to negative at 32768. This may
account for the silly numbers. I haven't
checked if it's been fixed in 10.1
To address the more general question:
Don't buy a scalpel to chop down a tree. If you
want a quick overview of how much work the system
is doing, then v$sysstat seems a perfectly viable
starting point. (And if you want an overview of
how much time is lost in what type of contention,
then v$system_event).
The purpose of the sga-attach tools is, I think, to get a particular slice into a particular kind of issue that might not otherwise be easy to identify. For example: why is every session losing a lot of time on DX locks. With sql_trace, you might see that every DX lock wait is very short, and which SQL statement is waiting - but that doesn't tell you which session is being waited for, and what SQL that session is running.
Like you, I find that a very large fraction of the performance issues I see can be identified without using high-precision tools. Just occasionally, I've felt the need for something like Precise to take the guess work out of identifying a root cause.
By the way: I've been on a couple of systems where I wouldn't like to run your query against v$sql, even once every 5 minutes - the impact on the library cache latch would be too severe.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
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 :
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |