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:
- » "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Sayan Malakshinov
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Sayan Malakshinov
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Tanel Poder
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Alex Fatkulin
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g - Allen, Brandon
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Alex Fatkulin
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Mark W. Farnham
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Kellyn Pot'vin
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Gaja Krishna Vaidyanatha
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Jonathan Lewis
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- bill thater
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Mark W. Farnham
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Gaja Krishna Vaidyanatha
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Jonathan Lewis
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Gaja Krishna Vaidyanatha
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Tanel Poder
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon