Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Can I tryst in "physical ... direct" figures?

Re: Can I tryst in "physical ... direct" figures?

From: <>
Date: Thu, 7 Oct 2004 02:12:18 +0300
Message-ID: <>

Many thanks to peoples how some time ago have taken part in this thread. Thanks to zhu, Jonathan and Terry.
I would like to share my experience and final result ;)

At the beginning I was impressed by the figures and wondered if there any system, which can have the figures, like I provided.

Especially those figures looked exotic in OLTP system, which I was asked to tune.
>Statistic Total per Second per
>--------------------------------- ------------------ --------------
>physical writes 16,465,203 365.9 175.3
>physical writes direct 16,343,723 363.2 174.0

After some investigation and tuning figures dropped to
>physical writes 839,229 18.7 9.3
> physical writes direct 719,063 16.0 8.0
For the identical time period of the day and the same load of users.

And I have achieved more then it can seams from the first look. By reducing load on IO subsystem I achieved other IO related waits reduction.
Take a look:

                                                     Total Wait   wait 
Event                               Waits   Timeouts   Time (s)   (ms) 
---------------------------- ------------ ---------- ---------- ------ 
db file sequential read         2,916,769          0     10,719      4 
log file sync                      88,952      2,480      6,046     68  
db file sequential read 2,634,909 0 5,039 2 29.3
log file sync                      85,041        137        843     10  

The root of the problem was inefficient SQL, which used HASH JOIN on the xM rows big tables. This SQL run have taken 2s from the user response time, but it was very popular (almost each form have run it).

Interested part of my story is the path how I have found the SQL. Oracle doesn’t provide the Write statistics for each SQL in v$sqlarea and v$sql (the only view I have found with Writes statistics per SQL is V$SQL_PLAN_STATISTICS, which not felled until your init.ora parameter statistics_level is set to ALL).
So it was a little bit challenging to find write intensive SQL. Oracle have the information regarding sorting per SQL, but in my case there HASH has place.

I have taken Terry advice and find most intensive write session (even it is J2EE application on front-end with connection polling) and traced it with Cary.
Then I was able to identified couple of suspicious SQL and deliver them to the developers.

Interesting how one small SQL can be so inefficient.

Hope my experience will help to anybody, Jurijs
+371 9268222 (+2 GMT)

Thank you for teaching me.

1. Full Statspack report
Before After

2. How dramatically figures can drop

On 24.09.2004 01:09:44 oracle-l-bounce wrote:

> EE on Lintel RH AS 2.1
>OLTP ~200 connections.
>Today a have observed figures below in statspack report
>Statistic Total per Second per
>--------------------------------- ------------------ --------------
>physical reads 19,327,036 429.5 205.8
>physical reads direct 10,482,393 232.9 111.6
>physical writes 16,465,203 365.9 175.3
>physical writes direct 16,343,723 363.2 174.0
>It seams very strange to me that almost all writes and more then 1/2 of
>reads is direct.
>What do you think, can I trust on those figures? Can it be truth that all
>writes are direct “bypassing the buffer cache, as written in Docs?
>PS Report interval 750.02 (mins)
>PS I know - SP aggregates live. Don’t tell me do not look on it.
>+371 9268222 (+2 GMT)
>Thank you for teaching me.

Received on Wed Oct 06 2004 - 18:09:30 CDT

Original text of this message