Re: package in sqlarea

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" <ORACLE-L_at_fatcity.com>
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 <bigplanet34_at_HOTMAIL.COM> 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

> --

> Author: Mohammed Shakir

>   INET: mshakir08816_at_yahoo.com

>

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).