Re: DIRECT PATH READ wait events

Based on the size and scatter of the direct path reads (which means I am making a couple of drastic assumptions) I think your path looks like an optimum 2-hash path, but the direct reads suggest that the hash_area_size is either too small to hold the hash table needed for table PRDPLVEE, or the statistics on the table are not accurate enough for Oracle to get the correct number of hash partitions in the table. Consequently large amounts of the INVBALEE table are being rewritten to disc and then reloaded for 'phase 2' hash activity.

Resolution:
Check (and improved) the stats on the smaller hash tables. Increase the hash_area_size to allow Oracle to hash both the build tables in memory.

Alternatively
If the query is supposed to return only 8,911 rows, then somewhere in the join you are eliminating lots of the 1.3M rows - in which case you may get a better path by switching to a join order that causes the elimination earlier.

On the other hand, the fetch count vs fetch rows suggests you are select all the data from an SQL*Plus session with an arraysize of 15, and this trace has just 'stopped' the fetch.. As a longshort you may find that the direct reads can be reduced as a side-effect of increasing the SQL*Plus arraysize.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 22 May 2002 06:43

|List,
|
|I am trying to tune a SQL query.
|Oracle 8163 , Windows NT 6 SP6.
|
|How can I eliminate DIRECT PATH READ wait events.
|I have traced a session (10046, level 12) and find a whole lot of
waits for
|DIRECT PATH READ.
|It waits 200 seconds for this event.
|
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=172112 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=176074 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=176720 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=176377 p3=1
|WAIT #1: nam='direct path read' ela= 3 p1=101 p2=169868 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=171692 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=171902 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=170439 p3=1
|WAIT #1: nam='direct path read' ela= 1 p1=101 p2=170230 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=172730 p3=1
|WAIT #1: nam='direct path read' ela= 4 p1=101 p2=168510 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=172578 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=175744 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=175588 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=174067 p3=1
|WAIT #1: nam='direct path read' ela= 0 p1=101 p2=173318 p3=1
|.......
|
|I have moved the TEMP tablespace to a faster disk - slight
improvement in
|performance.
|
|I also increased SORT_AREA_SIZE and HASH_AREA_SIZE, no change in
|performance.
|
|I noticed many sorts being done. 2 to Disk, most in memory.
|The SORT to disk was very large (>1000MB).
|
|SORT_AREA_SIZE=2M
|
|SELECT orgplvee.org_lvl_parent,
| prdplvee.prd_lvl_parent,
|

NVL(invbalee.on_hand_qty,0)+NVL(to_intrn_qty,0),
|

NVL(invbalee.on_hand_retl,0)+NVL(to_intrn_retl,0),
|

NVL(invbalee.on_hand_cost,0)+NVL(to_intrn_cost,0)
| FROM invbalee,orgplvee,prdplvee
| WHERE orgplvee.org_lvl_child =
invbalee.org_lvl_child
| AND prdplvee.prd_lvl_child =
invbalee.prd_lvl_child
| ORDER BY
| orgplvee.org_lvl_parent,
| prdplvee.prd_lvl_parent
|
|call count cpu elapsed disk query current
|rows
|------- ------ -------- ---------- ---------- ---------- ----------
|----------
|Parse 1 0.01 0.01 0 0 0
|0
|Execute 2 0.06 0.07 0 0 0
|0
|Fetch 595 334.33 584.37 100370 12736 646
|8911
|------- ------ -------- ---------- ---------- ---------- ----------
|----------
|total 598 334.40 584.45 100370 12736 646
|8911
|
|Misses in library cache during parse: 1
|Optimizer goal: CHOOSE
|Parsing user id: 20
|
|Rows Row Source Operation
|------- ---------------------------------------------------
| 8911 SORT ORDER BY
|31070088 HASH JOIN
| 1077 INDEX FAST FULL SCAN (object id 23589)
|7767522 HASH JOIN
| 102080 INDEX FAST FULL SCAN (object id 143358)
|1294587 TABLE ACCESS FULL INVBALEE
|
|
|Total rows
|INVBALEE = 1.3 million rows
|PRDPLVEE = 102 000 rows
|ORGPLVEE = 1077 rows
|
|Any ideas how to reduce this event and tune this statement.
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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