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

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Fri, 31 Aug 2012 01:33:56 +0000
Message-ID: <A250F0C68C23514CA9F3DF63D60EE10E1A8257E0_at_onews32>

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_at_poderc.com [mailto:tanel_at_poderc.com] 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.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Aug 30 2012 - 20:33:56 CDT