Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> [Tuning] Understanding direct path write event

[Tuning] Understanding direct path write event

From: SL <sebastien_louchart_at_yahoo.fr>
Date: 6 Dec 2005 01:53:17 -0800
Message-ID: <1133862797.400572.60420@o13g2000cwo.googlegroups.com>


Hello,

I'm currently working on a database part of a design projet. Version is 8174 operating on Solaris 8 on a V880 box with 8 CPU. DB is in NOARCHIVELOG mode.

Our purpose is to measure overall performance of the software we're designing before releasing it to production.

The DB is mostly use to store functional data but it also hosts some webMethods data (we use Process Runtime component from Integration Server Product).

It appears that many transactions insert LOB into a certain table of WM schemas, table containing two IDs (NUMBER) and a LOB. While reading perfstat reports, we find this in the top 5 events : Event=direct path write (lob)
Waits=20,738
Timeouts=1
Total Wait Time (cs)=18,517,848
Avg wait (ms)=8929
Waits/tx=0.2

I did some online monitoring of the instance, mainly by querying v$session_wait to get the associated parameters of this event and for buffer busy waits as well.

So far, I think that write contention exists when writing into LOBs mainly because parameters of buffer busy waits told me so (I identified the segments using this event parameters).

However, my analysis stopped here because I don't grok parameters associated with direct path write .
TFM I read tells me that :

P1 is 'descriptor address'
P2 is 'data block address'
P3 is 'block cnt'

I wonder how I can use the values of those to identify precisely the LOB segments involved in the contentions.

BTW, I've modified the LOB columns I've found by setting freelists to 4 instead of 1 and by setting caching and logging options to NOCACHE and NOLOGGING respectively which drives to better performances (halved avg wait).

My questions are :
1) How to interpret parameters value of direct path write to get segment name involved by this wait event ? 2) Are my settings correct ?
bonus :
3) Is there any explanation why direct path insert is used while insertions are made by Java code using 'insert into values' syntax ?

TIA, HAND

-- 
SL
Received on Tue Dec 06 2005 - 03:53:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US