DIRECT PATH READ wait events

From: Suhen Pather <Suhen.Pather_at_strandbags.com.au>
Date: Tue, 21 May 2002 22:03:19 -0800
Message-ID: <F001.004671E1.20020521220319@fatcity.com>

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: Suhen.Pather_at_strandbags.com.au

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). Received on Wed May 22 2002 - 01:03:19 CDT