Re: "direct path read" and "db file sequential read" used for full table scans in 11g

From: Alex Fatkulin <afatkulin_at_gmail.com>
Date: Thu, 30 Aug 2012 21:25:38 -0400
Message-ID: <CAMVw97LtJUjv=GMnV4WdPW7ne6L=CgVHTEF7xEvDnrH44K4pBQ_at_mail.gmail.com>

From the events it looks like direct path read did happen - you can see both object level checkpoint as well as direct path read events. What's your DBFMRC set to? And what's your SQL*Plus arraysize set to? Last, if you look into raw trace - are db file sequential read blocks indeed belong to the table?

>
> Plan and wait events from tkprof:
> --------------------------------
>
> Number of plan statistics captured: 1
>
> Rows (1st) Rows (avg) Rows (max) Row Source Operation
> ---------- ---------- ----------
> ---------------------------------------------------
> 600815 600815 600815 SORT ORDER BY (cr=1352370 pr=180699
> pw=24956 time=777998 us cost=69947 size=199593540 card=599380)
> 600815 600815 600815 TABLE ACCESS FULL T_TRIWORKTASK
> (cr=1352370 pr=155743 pw=0 time=96932776 us cost=27251 size=199593540
> card=599380)
>
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> SQL*Net message to client 40056 0.00
> 0.02
> db file sequential read 32667 0.02
> 74.75
> reliable message 1 0.00
> 0.00
> enq: KO - fast object checkpoint 1 0.01
> 0.01
> db file scattered read 1 0.01
> 0.01
> direct path read 1798 0.01
> 5.29
> direct path write temp 807 0.02
> 1.53
> asynch descriptor resize 3 0.00
> 0.00
> direct path read temp 346 0.08
> 1.19
> SQL*Net message from client 40056 29.28
> 311.98
>
>
>
> ________________________________
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do
> not consent to Internet email for messages of this kind. Opinions,
> conclusions and other information in this message that do not relate to the
> official business of this company shall be understood as neither given nor
> endorsed by it.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Alex Fatkulin,
http://afatkulin.blogspot.com

Enkitec,
http://www.enkitec.com


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 30 2012 - 20:25:38 CDT