Output from XPLAN (below) does indeed show cardinality feedback. Unfortunately it produces a worse query plan (query runs in 3mins 50 secs rather than 172ms) for these parameters. It expects to find practically no data in ARTICLE_REFERENCE when there are actually 4k rows that would be picked up in a single dip with the original plan.
This is another degree of freedom in the system that I need to manage (or at least think about) (
http://en.wikipedia.org/wiki/Variety_%28cybernetics%29 )
Q1. Can this feature be disabled? Not sure if this is the right thing but I want to know what options I have.
Q2. If bind variables were used then are we less likely to see the feature in action?
Initial Plan (SGA Flushed)...
SQL_ID 8c2h5htdx25z5, child number 0
-------------------------------------
SELECT DISTINCT COUNT(DISTINCT ARTICLE.ARTICLE_ID) , DIM_DATE.YEAR
, PUBLICATION.TITLE , max( PUBLISHER.PUBLISHER_NAME ) ,
Count(ARTICLE_REFERENCE.PUBLICATION_ID) FROM ARTICLE_REFERENCE INNER
JOIN ARTICLE ON (ARTICLE.ARTICLE_ID=ARTICLE_REFERENCE.ARTICLE_ID and
ARTICLE.PUBLICATION_ID=ARTICLE_REFERENCE.PUBLICATION_ID) INNER JOIN
VW_PUBLICATION_DATE ON (VW_PUBLICATION_DATE.PUBLICATION_ID=ARTICLE.PUBLI
CATION_ID and VW_PUBLICATION_DATE.PUBLICATION_DATE_ID=ARTICLE.PUBLICATIO
N_DATE_ID) INNER JOIN PUBLICATION ON
(PUBLICATION.PUBLICATION_ID=VW_PUBLICATION_DATE.PUBLICATION_ID)
INNER JOIN PUBLISHER ON (PUBLISHER.PUBLISHER_ID=PUBLICATION.PUBLICATION_
COPYRIGHT_ORG_ID) INNER JOIN DIM_DATE ON
(DIM_DATE.DATE_ID=VW_PUBLICATION_DATE.PUBLICATION_DATE_ID) WHERE (
( ARTICLE.PUBLICATION_ID IN ( select publication_id from
PUBLICATION_ACAD_SUBJ where academic_subject_id in (11359221) ) )
AND DIM_DATE.YEAR >= 2000 AND ARTICLE.DOCUMENT_TYPE_ID IN
( 7847539
Plan hash value: 3391746572
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 24674 (100)| |
| 1 | HASH UNIQUE | | 98758 | 14M| | 24674 (1)| 00:04:57 |
| 2 | SORT GROUP BY | | 98758 | 14M| 15M| 24674 (1)| 00:04:57 |
| 3 | NESTED LOOPS | | 98758 | 14M| | 21425 (1)| 00:04:18 |
| 4 | NESTED LOOPS | | 4395 | 588K| | 8237 (1)| 00:01:39 |
| 5 | NESTED LOOPS | | 4395 | 442K| | 8236 (1)| 00:01:39 |
| 6 | NESTED LOOPS | | 4395 | 248K| | 8236 (1)| 00:01:39 |
|* 7 | HASH JOIN | | 4773 | 223K| 3224K| 8236 (1)| 00:01:39 |
| 8 | JOIN FILTER CREATE | :BF0000 | 68615 | 2412K| | 3684 (1)| 00:00:45 |
| 9 | NESTED LOOPS | | 68615 | 2412K| | 3684 (1)| 00:00:45 |
|* 10 | INDEX RANGE SCAN | PUBLICATION_ACAD_SUBJ_PK | 80 | 960 | | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PK__ARTICLE | 855 | 20520 | | 12 (0)| 00:00:01 |
| 12 | VIEW | VW_PUBLICATION_DATE | 565K| 6629K| | 3750 (1)| 00:00:45 |
| 13 | HASH UNIQUE | | 565K| 6629K| 10M| 3750 (1)| 00:00:45 |
| 14 | JOIN FILTER USE | :BF0000 | 565K| 6629K| | 1167 (1)| 00:00:15 |
| 15 | INDEX FAST FULL SCAN| PUBLICATION_DATE | 565K| 6629K| | 1167 (1)| 00:00:15 |
|* 16 | INDEX UNIQUE SCAN | DIM_DATE_PK | 1 | 10 | | 0 (0)| |
|* 17 | INDEX UNIQUE SCAN | PUBLICATION_PK | 1 | 45 | | 0 (0)| |
|* 18 | INDEX UNIQUE SCAN | PK_PUBLISHER | 1 | 34 | | 0 (0)| |
|* 19 | INDEX RANGE SCAN | PK_ARTICLE_REFERENCE | 22 | 264 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("VW_PUBLICATION_DATE"."PUBLICATION_ID"="ARTICLE"."PUBLICATION_ID" AND
"VW_PUBLICATION_DATE"."PUBLICATION_DATE_ID"="ARTICLE"."PUBLICATION_DATE_ID")
10 - access("ACADEMIC_SUBJECT_ID"=11359221)
11 - access("ARTICLE"."PUBLICATION_ID"="PUBLICATION_ID")
filter(("ARTICLE"."DOCUMENT_TYPE_ID"=7847539 OR "ARTICLE"."DOCUMENT_TYPE_ID"=7848189))
16 - access("DIM_DATE"."DATE_ID"="VW_PUBLICATION_DATE"."PUBLICATION_DATE_ID")
filter("DIM_DATE"."YEAR">=2000)
17 - access("PUBLICATION"."PUBLICATION_ID"="VW_PUBLICATION_DATE"."PUBLICATION_ID")
18 - access("PUBLISHER"."PUBLISHER_ID"="PUBLICATION"."PUBLICATION_COPYRIGHT_ORG_ID")
19 - access("ARTICLE"."PUBLICATION_ID"="ARTICLE_REFERENCE"."PUBLICATION_ID" AND
"ARTICLE"."ARTICLE_ID"="ARTICLE_REFERENCE"."ARTICLE_ID")
With Cardinality Feedback...
SQL_ID 8c2h5htdx25z5, child number 1
-------------------------------------
SELECT DISTINCT COUNT(DISTINCT ARTICLE.ARTICLE_ID) , DIM_DATE.YEAR
, PUBLICATION.TITLE , max( PUBLISHER.PUBLISHER_NAME ) ,
Count(ARTICLE_REFERENCE.PUBLICATION_ID) FROM ARTICLE_REFERENCE INNER
JOIN ARTICLE ON (ARTICLE.ARTICLE_ID=ARTICLE_REFERENCE.ARTICLE_ID and
ARTICLE.PUBLICATION_ID=ARTICLE_REFERENCE.PUBLICATION_ID) INNER JOIN
VW_PUBLICATION_DATE ON (VW_PUBLICATION_DATE.PUBLICATION_ID=ARTICLE.PUBLI
CATION_ID and VW_PUBLICATION_DATE.PUBLICATION_DATE_ID=ARTICLE.PUBLICATIO
N_DATE_ID) INNER JOIN PUBLICATION ON
(PUBLICATION.PUBLICATION_ID=VW_PUBLICATION_DATE.PUBLICATION_ID)
INNER JOIN PUBLISHER ON (PUBLISHER.PUBLISHER_ID=PUBLICATION.PUBLICATION_
COPYRIGHT_ORG_ID) INNER JOIN DIM_DATE ON
(DIM_DATE.DATE_ID=VW_PUBLICATION_DATE.PUBLICATION_DATE_ID) WHERE (
( ARTICLE.PUBLICATION_ID IN ( select publication_id from
PUBLICATION_ACAD_SUBJ where academic_subject_id in (11359221) ) )
AND DIM_DATE.YEAR >= 2000 AND ARTICLE.DOCUMENT_TYPE_ID IN
( 7847539
Plan hash value: 82017851
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 4168 (100)| |
| 1 | HASH UNIQUE | | 7 | 1043 | | 4168 (1)| 00:00:51 |
| 2 | SORT GROUP BY | | 7 | 1043 | | 4168 (1)| 00:00:51 |
| 3 | NESTED LOOPS | | 2 | 298 | | 4167 (1)| 00:00:51 |
| 4 | NESTED LOOPS | | 1 | 137 | | 4164 (1)| 00:00:50 |
| 5 | NESTED LOOPS | | 1 | 103 | | 4164 (1)| 00:00:50 |
| 6 | NESTED LOOPS | | 1 | 58 | | 4164 (1)| 00:00:50 |
| 7 | NESTED LOOPS | | 1 | 48 | | 4164 (1)| 00:00:50 |
| 8 | NESTED LOOPS | | 9 | 324 | | 4164 (1)| 00:00:50 |
| 9 | VIEW | VW_PUBLICATION_DATE | 9 | 108 | | 3750 (1)| 00:00:45 |
| 10 | HASH UNIQUE | | 9 | 108 | 10M| 3750 (1)| 00:00:45 |
| 11 | INDEX FAST FULL SCAN| PUBLICATION_DATE | 565K| 6629K| | 1167 (1)| 00:00:15 |
|* 12 | INDEX RANGE SCAN | PK__ARTICLE | 1 | 24 | | 12 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PUBLICATION_ACAD_SUBJ_PK | 1 | 12 | | 0 (0)| |
|* 14 | INDEX UNIQUE SCAN | DIM_DATE_PK | 1 | 10 | | 0 (0)| |
|* 15 | INDEX UNIQUE SCAN | PUBLICATION_PK | 1 | 45 | | 0 (0)| |
|* 16 | INDEX UNIQUE SCAN | PK_PUBLISHER | 1 | 34 | | 0 (0)| |
|* 17 | INDEX RANGE SCAN | PK_ARTICLE_REFERENCE | 22 | 264 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
12 - access("VW_PUBLICATION_DATE"."PUBLICATION_ID"="ARTICLE"."PUBLICATION_ID")
filter((INTERNAL_FUNCTION("ARTICLE"."DOCUMENT_TYPE_ID") AND
"VW_PUBLICATION_DATE"."PUBLICATION_DATE_ID"="ARTICLE"."PUBLICATION_DATE_ID"))
13 - access("ACADEMIC_SUBJECT_ID"=11359221 AND "ARTICLE"."PUBLICATION_ID"="PUBLICATION_ID")
14 - access("DIM_DATE"."DATE_ID"="VW_PUBLICATION_DATE"."PUBLICATION_DATE_ID")
filter("DIM_DATE"."YEAR">=2000)
15 - access("PUBLICATION"."PUBLICATION_ID"="VW_PUBLICATION_DATE"."PUBLICATION_ID")
16 - access("PUBLISHER"."PUBLISHER_ID"="PUBLICATION"."PUBLICATION_COPYRIGHT_ORG_ID")
17 - access("ARTICLE"."PUBLICATION_ID"="ARTICLE_REFERENCE"."PUBLICATION_ID" AND
"ARTICLE"."ARTICLE_ID"="ARTICLE_REFERENCE"."ARTICLE_ID")
Note
-----
- cardinality feedback used for this statement