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" <[EMAIL PROTECTED]>
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 [EMAIL PROTECTED] for clarification.
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
