Some DB File Parallel Read Against An Empty Partition (Doc ID 1367059.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.1.0.7 [Release 11.1]
Information in this document applies to any platform.
Reviewed 14-Feb-2014.

Symptoms

While querying in a partitioned table, then some "db file parallel read" work against object of an empty partition.

For example:

SQL> alter session set timed_statistics = true;

Session altered.

SQL> alter session set statistics_level=typical;

Session altered.

SQL> alter session set MAX_DUMP_FILE_SIZE=UNLIMITED;

Session altered.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> select /*+INDEX(ern ern_bus_fk)*/ count(*) from lntp.earning ern
where ern.per_id = 200710
and ern.ern_date >= '14-DEC-2006'
AND ern.ern_date < '19-JAN-2007'
and cob_id_earning_type=165
and bus_id_sapphire_to in (4854,4855)

2 3 4 5 6 7 SQL>
SQL> /

COUNT(*)
----------
482548



In tkprof
=========

select /*+INDEX(ern ern_bus_fk)*/ count(*) from lntp.earning ern
where ern.per_id = 200710
and ern.ern_date >= '14-DEC-2006'
AND ern.ern_date < '19-JAN-2007'
and cob_id_earning_type=165
and bus_id_sapphire_to in (4854,4855)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.03 0.12 3 18 0 0
Fetch 2 24.84 378.33 464541 464545 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 24.87 378.45 464544 464563 0
1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 46

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=464563 pr=464544 pw=0 time=0 us)
1 PX COORDINATOR (cr=464563 pr=464544 pw=0 time=0 us)
1 PX SEND QC (RANDOM) :TQ10000 (cr=464545 pr=464541 pw=0 time=0 us)
1 SORT AGGREGATE (cr=464545 pr=464541 pw=0 time=0 us)
482548 PX PARTITION RANGE ITERATOR PARTITION: 2 5 (cr=464545 pr=464541 pw=0 time=449954 us cost=325521 size=3903627 card=185887)
482548 INLIST ITERATOR (cr=464545 pr=464541 pw=0 time=567246 us)
482548 TABLE ACCESS BY LOCAL INDEX ROWID EARNING PARTITION: 2 5 (cr=464545 pr=464541 pw=0 time=684508 us cost=325521 size=3903627 card=185887)
482550 INDEX RANGE SCAN ERN_BUS_FK PARTITION: 2 5 (cr=3632 pr=3628 pw=0 time=254518 us cost=7851 size=0 card=1115344)(object id 18744)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file parallel read 11974 0.39 329.12
db file sequential read 15136 0.38 40.16
os thread startup 1 0.04 0.04
PX Deq Credit: send blkd 1 0.00 0.00
PX Deq: Join ACK 1 0.00 0.00
enq: PS - contention 1 0.00 0.00
SQL*Net message to client 2 0.00 0.00
db file scattered read 92 0.01 0.18
SQL*Net message from client 2 992.51 992.51



In 10046 trace
==============

....
....
WAIT #1: nam='db file parallel read' ela= 32438 files=1 blocks=39 requests=39
obj#=18739 tim=3381366189363

WAIT #1: nam='db file parallel read' ela= 21606 files=1 blocks=39 requests=39
obj#=18739 tim=3381366216589

WAIT #1: nam='db file parallel read' ela= 55022 files=1 blocks=39 requests=39
obj#=18739 tim=3381366276292

WAIT #1: nam='db file parallel read' ela= 73113 files=1 blocks=39 requests=39
obj#=18739 tim=3381366357110

WAIT #1: nam='db file parallel read' ela= 48182 files=1 blocks=39 requests=39
obj#=18739 tim=3381366407198
....
....

 

SQL> select object_name, subobject_name, object_type from dba_objects where object_id=18739;

OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
------------ ---------------- ---------------
EARNING ERND_TEMPL TABLE PARTITION

SQL> select count(*) from EARNING partition(ERND_TEMPL);

COUNT(*)
----------
0

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms