Hi
I have a large cdr_unbilled table (7M recs) that is joined to a much larger one cdr_data (60M recs) using an index. I must read every record of the smaller table, code below (The actual code is larger than this excerpt, but the theory of the problem remains the same with this code, I have tested).
SELECT --+ parallel_index(cu,8) index(cd)
cd.external_id cli
,cd.element_id
,cd.type_id_usg
,cd.trans_dt
,cd.primary_units
,cd.unrounded_amount
FROM cdr_data cd
,cdr_unbilled cu
WHERE cd.msg_id=cu.msg_id
and cd.msg_id2=cu.msg_id2
and cd.msg_id_serv=cu.msg_id_serv
and cd.split_row_num = cu.split_row_num;
Now, i have this code in two systems. each system has roughly the same number of records in each table and the same indexes and table structure. The only difference is the Oracle version. 11.1.07 vs 11.2.02.
In the 11.1.07 database, the explain plan looks as I would expect (shown below)
SELECT STATEMENT ALL_ROWSCost: 34 M Bytes: 64 G Cardinality: 946 M
8 PX COORDINATOR
7 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10000 :Q1000
6 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1000
4 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1000Cost: 34 M Bytes: 64 G Cardinality: 946 M
2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1000
1 INDEX FAST FULL SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT ARBOR.CDR_UNBILLED_PK :Q1000Cost: 11 K Bytes: 122 M Cardinality: 9 M
3 INDEX RANGE SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT ARBOR.CDR_DATA_PK :Q1000Cost: 3 Cardinality: 1
5 TABLE ACCESS BY INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT ARBOR.CDR_DATA :Q1000Cost: 4 Bytes: 6 K Cardinality: 111
Yet in the 11.2.02 system I get the below. Notice there is no parallel processing here.
Plan
SELECT STATEMENT ALL_ROWSCost: 29 M Bytes: 68 G Cardinality: 968 M
5 NESTED LOOPS
3 NESTED LOOPS Cost: 29 M Bytes: 68 G Cardinality: 968 M
1 TABLE ACCESS FULL TABLE ARBOR.CDR_UNBILLED Cost: 8 K Bytes: 112 M Cardinality: 7 M
2 INDEX RANGE SCAN INDEX (UNIQUE) ARBOR.CDR_DATA_PK Cost: 3 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID TABLE ARBOR.CDR_DATA Cost: 4 Bytes: 8 K Cardinality: 132
Only when I alter the query to force a Fast Full Scan using the index_ffs on CDR_UNBILLED does my Plan from 11.2.02 match 11.1.07
SELECT --+ parallel_index(cu,8) index_ffs(cu,cdr_unbilled_pk) index(cd)
cd.external_id cli
,cd.element_id
,cd.type_id_usg
,cd.trans_dt
,cd.primary_units
,cd.unrounded_amount
FROM cdr_data cd
,cdr_unbilled cu
WHERE cd.msg_id=cu.msg_id
and cd.msg_id2=cu.msg_id2
and cd.msg_id_serv=cu.msg_id_serv
and cd.split_row_num = cu.split_row_num;
returned plan:
Plan
SELECT STATEMENT ALL_ROWSCost: 29 M Bytes: 68 G Cardinality: 968 M
8 PX COORDINATOR
7 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10000 :Q1000
6 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1000
4 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1000Cost: 29 M Bytes: 68 G Cardinality: 968 M
2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1000
1 INDEX FAST FULL SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT ARBOR.CDR_UNBILLED_PK :Q1000Cost: 9 K Bytes: 112 M Cardinality: 7 M
3 INDEX RANGE SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT ARBOR.CDR_DATA_PK :Q1000Cost: 3 Cardinality: 1
5 TABLE ACCESS BY INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT ARBOR.CDR_DATA :Q1000Cost: 4 Bytes: 8 K Cardinality: 132
I cannot explain why the code on the 11.2.02 system is performing a full table scan (without the additional ffs hint). I am assuming that this is due to the way the database has been configured but would like to know for sure. I am also aware that there were some changes to parallel_index in 11g2, the below is taken from the What's new Section of the SQL Reference for 11g2.
Quote:
Beginning with Oracle Database 11g Release 2, the PARALLEL, PARALLEL_INDEX, NO_PARALLEL, and NO_PARALLEL_INDEX hints are statement-level hints and supersede the earlier object-level hints
[Updated on: Wed, 23 November 2011 10:52] by Moderator
Report message to a moderator
] 
Blog
Search
Help
Members
Register
Login
Home
