Oracle-L: Re: Please help resolving report generation performance problem...

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Sun, 07 Apr 2002 12:13:22 -0800
Message-ID: <F001.0043E029.20020407121322@fatcity.com>

Denmark,

Sifting through the clues in your email, it is almost dead certain that the SQL statements in your overnight reports must be tuned. Adding rollback segments has no effect on reports (usually). Modifying initialization parameters is unlikely to affect such specific problems also.

To isolate the most egregiously awful SQL statements, the best tool is to query the V$SQLAREA view and sort by the value in the column BUFFER_GETS (a.k.a. "logical reads" or reads against buffers in the SGA's Buffer Cache) and the column DISK_READS (a.k.a. "physical reads" or misses against the Buffer Cache resulting in actual I/O to disk). I usually like to do this with a query similar to the following:

SELECT SQL_TEXT,

                      DISK_READS,
                      BUFFER_GETS
    FROM       V$SQLAREA

ORDER BY (DISK_READS * 100) + BUFFER_GETS DESC; The query looks at the information currently in the V$SQLAREA view (which is a present-time snapshot of the contents of the Shared SQL Area cache in the Library Cache of the Shared Pool of the SGA). Because V$SQLAREA shows the contents of a cache and because caches may get "flushed" depending on the activity in the system, I would recommend running this query either during the reports you are having trouble with or soon after they complete. Be sure to let the reports run for a bit and build up some history; don't run this report immediately after they are started...

The ORDER BY clause sorts the report with an emphasis on queries that produce a lot of "cache misses" on the Buffer Cache. However, high numbers of BUFFER_GETS should not be disregarded either, as millions of BUFFER_GETS can produce poor performance too. Thus, instead of just saying "ORDER BY DISK_READS DESC, BUFFER_GETS DESC", I find it useful to simply "weight" the physical reads as 100x "heavier" than logical reads, allowing consideration of both factors...

If this query on V$SQLAREA takes too long or if it returns too much information (the latter condition is almost certain), then you may want to include a WHERE clause that filters out the "inoffensive" little SQL statements that are not a problem. For example:

SELECT SQL_TEXT,

                      DISK_READS,
                      BUFFER_GETS
    FROM       V$SQLAREA
    WHERE    BUFFER_GETS < 1000
    AND         DISK_READS < 10

ORDER BY (DISK_READS * 100) + BUFFER_GETS DESC; Of course, you may want to play with those "thresholds" a little depending on how busy your system is. Set the values in the WHERE clause too high, and you might filter everything out. Set them too low and you'll still get too much data returned...

I've got (what I think is) a nice version of this basic query on my website at www.EvDBT.com/library.htm. It is entitled TOP_STMT2 and it should be located about 2/3rds the way down that page. It can be used either as a PL/SQL stored procedure (i.e. script "top_stmt2.sql" is the DDL to create the report and "run_top_stmt2.sql" is the SQL*Plus script to run the stored procedure) or as an "anonymous PL/SQL block" (i.e. SQL*Plus script "temp_top_stmt2.sql").

If you chose the route involving the creation of the stored procedure TOP_STMT2, then the DDL script "top_stmt2.sql" would need to be run either as SYS (or INTERNAL) or they would need to be run under users who have been granted explicit SELECT permissions on five V$ views: V$SESSION, V$SQLAREA, V$SQLTEXT, V$SYSSTAT, and V$THREAD. If you don't have access to the SYS schema to create the stored procedure or grant the explicit SELECT permissions, then the "anonymous PL/SQL block" script "temp_top_stmt2.sql" is a handy alternative...

The report coming out of the TOP_STMT2 report shows the percentage share of total instance physical and logical reads, allowing some idea of the "impact" of each SQL statement on overall system load.

Using these reports, once you have identified the most awful SQL statements, you can now use tools like SQL*Plus's AUTOTRACE facility or the TKPROF report to tune them. This is a huge topic, but Guy Harrison's book on "High Performance SQL Tuning in Oracle" covers it very nicely, I think.

The very best tool you can use (for free) is the STATSPACK package (shipped with v8.1.x but available for v8.0.x also) and then post-process the not-very-useful STATSPACK report using the YAPP performance profiler available (for free) on the www.oraperf.com website. If you don't have time or inclination to install STATSPACK, then the www.oraperf.com site will accept BSTAT/ESTAT reports. This combination (STATSPACK and YAPP) will give you an extremely accurate image of performance on your system. But this is a topic for another email, sometime...

Hope this helps...

-Tim

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Sunday, April 07, 2002 12:18 PM

> Hi DBA's,
>
>
> I've been trying to isolate the bottleneck with our Oracle database.
> I work as an Oracle DBA for the Government of a developing country
(Belize).
> Recently, as it is income tax time, the department has to reconcile all
> witholdings by the employer with their payment receipt
> records. This involves some data entry and a report generation by
> employer (witholder) which lists all witholdings by each employee.
> This report can generate lots of pages depending on the number of
employees.
> In some cases, the report has to be run overnight, as it takes too long
> (several hours) to generate.
> I've tried giving more resources to Oracle. I've tried creating a copy
> of the production database on another machine to use only for generating
> reports. I've increased the size and number of rollback segments. I've
tuned
> some parameters. However, I have not observed
> any significant improvement in the report generation performance.
> I know tuning the SQL might be required, however, I don't have much
> experience in this area. The SQL statemements were written by consultants
> who have long left. We do have the source code though.
>
> We are running Oracle 8.0.5.2.1 on NT 4.0
> The NT server is a Dell 4400 with Dual CPU and 1GB RAM
> We are using hardware RAID 5.
> Our database is OLTP with reporting.
> It is a small database (exported data is about 150 MB).
>
> I would appreciate your recommendations and advice.
>
> Thanks in advance,
>
> Denmark Weatherburne
>
> _________________________________________________________________
> Chat with friends online, try MSN Messenger: http://messenger.msn.com
>

> The previous attachment was filtered out by the ListGuru mailing
> software at fatcity.com because binary attachments are not appropriate
> for mailing lists. If you want a copy of the attachment which was
> removed, contact the sender directly and ask for it to be sent to
> you by private E-mail.
>
> This warning is inserted into all messages containing binary
> attachments which have been removed by ListGuru. If you have questions
> about this message, contact Postmaster_at_fatcity.com for clarification.
>

-- 
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).

Received on Sun Apr 07 2002 - 15:13:22 CDT