Re: SGA polling
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 17 May 2004 10:59:59 +0100
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
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
----- Original Message -----
From: "Stephane Faroult" <sfaroult@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, May 17, 2004 9:33 AM
Subject: 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 :
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
