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

  • From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
  • To: Tanel Poder <tanel@xxxxxxxxxxxxxx>, "xt.and.r@xxxxxxxxx" <xt.and.r@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 31 Aug 2012 01:33:56 +0000

Thank you Tanel!  I'm watching/listening to Enkitec TV right now and love it!  
This came just in time since Jersey Shore was cancelled :)
Unfortunately this system is 11.2.0.1 so the ALWAYS value doesn't work for 
_serial_direct_read - it gives me ORA-922 as shown below.

I ran the query again with PARALLEL 4 set on the table to verify it is doing a 
full table scan as you can see below, but it's still doing mostly "db file 
sequential read".  It does do always do some "direct path read" as well, but 
I'm assuming that's just for the sort.

Any idea why the parallel full table scan wouldn't do direct path reads even 
though I have parallel_degree_policy=MANUAL?

SQL> show parameter parallel_degree_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
parallel_degree_policy               string      MANUAL

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  
---------------------------------------------------
         0          0          0  PX COORDINATOR  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0   PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 
time=0 us cost8989 size 0820978 card`3066)
         0          0          0    SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us 
cost8989 size 0820978 card`3066)
         0          0          0     PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us 
cost122 size 0820978 card`3066)
         0          0          0      PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 
time=0 us cost122 size 0820978 card`3066)
     52998      52998      52998       PX BLOCK ITERATOR (cr5524 pr1207 pw=0 
timeR989576 us cost122 size 0820978 card`3066)
     52998      52998      52998        TABLE ACCESS FULL T_TRIWORKTASK 
(cr5524 pr1207 pw=0 timeS351952 us cost122 size 0820978 card`3066)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PX Deq: Execution Msg                           6        0.01          0.01
  Disk file operations I/O                        4        0.00          0.00
  direct path read                              221        0.02          0.72
  db file sequential read                     18989        0.03         53.21
  PX Deq: Table Q Get Keys                        2        0.00          0.00


SQL> alter session set "_serial_direct_read"=ALWAYS;
alter session set "_serial_direct_read"=ALWAYS
                                        *
ERROR at line 1:
ORA-00922: missing or invalid option

SQL> alter session set "_serial_direct_read"=TRUE;

Session altered.


From: tanel@xxxxxxxxxx [mailto:tanel@xxxxxxxxxx] On Behalf Of Tanel Poder
Sent: Thursday, August 30, 2012 3:40 PM

The direct path read thingy affects only full table (segment) scans. So even 
with PX, if you get a parallel index range scan (on partitioned index) for 
example, you end up with good old buffered single block reads.


________________________________

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.

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 08-2012