| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> three reads to get 64 blocks with dfmrc=32
Hi,
Linux, Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
I have a table with 24 extents each has 64 blocks (16K), the dfmrc is set to 32.
If I read the table serial, each extend is red with two multiblocks reads of 32 and 31 blocks - see extract of tkprof 10046 level 8
select /*+ PARALLEL(x,1) */ count(*) from x;
WAIT #2: nam='db file scattered read' ela= 10281 p1=8 p2=5064 p3=32
WAIT #2: nam='db file scattered read' ela= 9469 p1=8 p2=5096 p3=29
WAIT #2: nam='db file scattered read' ela= 44145 p1=8 p2=71174 p3=32
WAIT #2: nam='db file scattered read' ela= 10020 p1=8 p2=71206 p3=31
WAIT #2: nam='db file scattered read' ela= 10228 p1=8 p2=71238 p3=32
WAIT #2: nam='db file scattered read' ela= 19536 p1=8 p2=71270 p3=31
WAIT #2: nam='db file scattered read' ela= 14996 p1=8 p2=71302 p3=32
If I switch to PX there are 3 read per extend with 32, 29 and 2 block - see the extracts of the same event for the two PX slaves:
select /*+ PARALLEL(x,2) */ count(*) from x;
P002
WAIT #1: nam='direct path read' ela= 53 p1=8 p2=5064 p3=32
WAIT #1: nam='direct path read' ela= 24 p1=8 p2=5096 p3=29
WAIT #1: nam='PX Deq: Execution Msg' ela= 529 p1=268566527 p2=1 p3=0
WAIT #1: nam='direct path read' ela= 26 p1=8 p2=71235 p3=2
WAIT #1: nam='PX Deq: Execution Msg' ela= 3270 p1=268566527 p2=1 p3=0
WAIT #1: nam='direct path read' ela= 52 p1=8 p2=71238 p3=32
WAIT #1: nam='direct path read' ela= 12 p1=8 p2=71270 p3=29
WAIT #1: nam='PX Deq: Execution Msg' ela= 1916 p1=268566527 p2=1 p3=0
WAIT #1: nam='direct path read' ela= 27 p1=8 p2=71299 p3=2
WAIT #1: nam='PX Deq: Execution Msg' ela= 7530 p1=268566527 p2=1 p3=0
WAIT #1: nam='direct path read' ela= 50 p1=8 p2=71302 p3=32
WAIT #1: nam='direct path read' ela= 14 p1=8 p2=71334 p3=29
P001
WAIT #1: nam='direct path read' ela= 53 p1=8 p2=71174 p3=32
WAIT #1: nam='direct path read' ela= 7 p1=8 p2=71206 p3=29
WAIT #1: nam='PX Deq: Execution Msg' ela= 8318 p1=268566527 p2=1 p3=0
WAIT #1: nam='direct path read' ela= 29 p1=8 p2=71363 p3=2
WAIT #1: nam='PX Deq: Execution Msg' ela= 43306 p1=268566527 p2=1 p3=0
WAIT #1: nam='direct path read' ela= 32 p1=8 p2=71427 p3=2
WAIT #1: nam='PX Deq: Execution Msg' ela= 9092 p1=268566527 p2=1 p3=0
WAIT #1: nam='direct path read' ela= 27 p1=8 p2=71491 p3=2
WAIT #1: nam='PX Deq: Execution Msg' ela= 45026 p1=268566527 p2=1 p3=0
WAIT #1: nam='direct path read' ela= 31 p1=8 p2=71555 p3=2
WAIT #1: nam='PX Deq: Execution Msg' ela= 9513 p1=268566527 p2=1 p3=0
WAIT #1: nam='direct path read' ela= 27 p1=8 p2=71619 p3=2
WAIT #1: nam='PX Deq: Execution Msg' ela= 44602 p1=268566527 p2=1 p3=0
WAIT #1: nam='direct path read' ela= 31 p1=8 p2=71683 p3=2
Any idea where this third read per extent comes from?
The execution plans are
parallel
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | | 1 | | 91 (16)| | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | SORT AGGREGATE | | 1 | | | 43,00 | P->S | QC (RAND) |
| 3 | TABLE ACCESS FULL | X | 2031K| | 91 (16)| 43,00 | PCWP | |
PX Slave SQL Information (identified by operation id):
2 - SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) FROM (SELECT /*+ NO_EXPAND ROWID(A2) */ 0 FROM "X" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A2) A1 serial
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
| 0 | SELECT STATEMENT | | 1 | | 183 (16)|
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL | X | 2031K| | 183 (16)|
Regards,
Jaromir D.B. Nemec
-- http://www.freelists.org/webpage/oracle-lReceived on Mon May 16 2005 - 17:06:17 CDT
 |
 |