| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Can I tryst in "physical ... direct" figures?
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
>Trans
>--------------------------------- ------------------ --------------
>------------
>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:
Before
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------ -------- db file sequential read 2,916,769 0 10,719 4 31.1 log file sync 88,952 2,480 6,046 680.9
log file sync 85,041 137 843 100.9
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)
PS FYI
1. Full Statspack report
Before
ftp://ftp.alise.lv/pub/oracle/to_oral/perf_IKSP_20040923.txt
After
ftp://ftp.alise.lv/pub/oracle/to_oral/perf_IKSP_20040927.txt
2. How dramatically figures can drop
ftp://ftp.alise.lv/pub/oracle/to_oral/dwr_new.JPG
On 24.09.2004 01:09:44 oracle-l-bounce wrote:
>9.2.0.5 EE on Lintel RH AS 2.1
>OLTP ~200 connections.
>Today a have observed figures below in statspack report
>Statistic Total per Second per
>Trans
>--------------------------------- ------------------ --------------
>------------
>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.
>
>
>Jurijs
>+371 9268222 (+2 GMT)
>============================================
>Thank you for teaching me.
>http://otn.oracle.com/ocm/jvelikanovs.html
>
>
>--
>http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 06 2004 - 18:09:30 CDT
 |
 |
