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.
Regards
Suhen
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suhen Pather
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).
