Thanks Mohammed!
Some comments: tools that query from the V$SQLAREA seeking inefficient SQL
by looking at DISK_READS (i.e. physical reads or cache misses on the Buffer
Cache) and BUFFER_GETS (i.e. logical reads) can only be as effective as the
Shared SQL Area cache in the Shared Pool. That is, your mileage may
certainly vary!
If the Shared SQL Area is being "abused" as a cache in any way, which can
happen multiple ways, then the effectiveness of queries against V$SQLAREA
can be severely limited. For example, if the Shared SQL Area is being
flooded with many similar SQL statements that are only being used once and
once only, due to the lack of use of "bind variables" by the application,
then the cache of SQL statements may only provide you with the last 30
seconds of information in the database. This is not very useful. I try to
kludge around this in TOP_STMT2 by attempting to aggregate statistics for
all SQL statements which are the same to the first 60 characters of the SQL
text, but this brings up other possible distortions.
With a well-behaved application that is utilizing bind-variables, allowing
SQL statements to be cached for long periods of time, queries against
V$SQLAREA (such as in TOP_STMT2 and many of Steve Adam's scripts on IXORA)
work like magic. The TOP_STMT2 procedure attempts to gauge the "impact" of
a SQL statement by comparing the values in DISK_READS against the total
physical reads (from V$SYSSTAT) and BUFFER_GETS against total logical reads
(from V$SYSSTAT). While this estimated "impact" is fraught with all kinds
of inaccuracy, it still provides a pretty good indication of just how
debilitating a specific SQL statement might be on overall system resource
consumption.
---
The very best utility for SQL tuning is STATSPACK. STATSPACK takes
"snapshots" of the V$ views (including V$SQLAREA) on a periodic basis
(default: 60 mins) all the time. So, it keeps a long-term history SQL
utilization, across instance restarts and such. Now, you can really analyze
SQL utilization and get a good measure across longer time periods.
Problem is, out of the box you only get STATSPACK's single canned report,
which only compares differences between two separate snapshots. If these
two snapshots are intersected by an instance restart, the report does not
handle it well. Also, in my opinion the standard STATSPACK report is about
as useful to the average person as the old BSTAT/ESTAT report. That is, not
very useful. It's just too much information, much of it irrelevant. So,
just as with BSTAT/ESTAT, I consider the YAPP report from the
www.oraperf.com website to be an integral part of STATSPACK (as with
BSTAT/ESTAT). Reading the YAPP report provides insight into whether SQL
tuning is the top issue or not, or whether wait-event bottlenecks are the
top issue or not. Thanks Anjo!
I've customized STATSPACK to add additional columns to it's tables to store
"delta" or "incremental change" values between snapshots. These values have
to be calculated separately from the STATSPACK.SNAP packaged procedure or
you have to be willing to modify the STATSPACK package. Since STATSPACK is
essentially "open source", this isn't hard. It becomes a matter of "style".
I prefer to leave STATSPACK components as pristine as possible to facilitate
upgrade.
Anyway, once you have calculated "delta" values between snapshots, you can
treat the STATSPACK tables almost like a portion of a data warehouse and do
some cool analysis queries against it. I've adapted a version of TOP_STMT2
(called it TOP_STMT3 -- originality is not my strongpoint!) to run against
these customized "delta" values in STATSPACK and I'm able to accurate
monitor for "offensive SQL" across months of gathered data. Cool stuff...
What a great time to be alive (and not working on AS/400!)...
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, April 10, 2002 8:28 PM
> You need to check the SQL in the procedure.
>
> I have the same situation. I run Tim Gornman's temp_top_stmt2.sql
> script to identify the SQL within the procedure. Statspack also
> identifies the high resource consuming SQL. IXORA also provides scripts
> to identify them.
>
> Shakir
>
>
> --- Big Planet <[EMAIL PROTECTED]> wrote:
> > Hi List ,
> > While searching for poor sqls , I am getting a stored procedure name
> > in v$sqlarea with high value in disk_reads . What does it mean and
> > how can I reduce the disk read . Yeah ..one more thing does the case
> > of stored proc and name of bind varibales creats different entry in
> > sqlarea .
> >
> > TIA
> > Bp
> >
> >
>
>
> =
> Mohammed Shakir
> CompuSoft, Inc.
> 11 Heather Way
> East Brunswick, NJ 08816-2825
> (732) 672-0464 (Cell)
> (732) 257-6001 (Home)
>
> __
> Do You Yahoo!?
> Yahoo! Tax Center - online filing with TurboTax
> http://taxes.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
