I am using Oracle 11.2..0.3. For the below execution plan below, how can I use OPT_ESTIMATE or CARDINALITY hint to instruct optimization that E-Rows for ID 9( Nested Loop) should be 30553 instead of 6.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4604 (100)| | 30553 |00:00:02.56 | 208K| | | |
| 1 | SORT ORDER BY | | 1 | 6 | 7044 | 4604 (1)| 00:01:05 | 30553 |00:00:02.56 | 208K| 41M| 2086K| 36M (0)|
|* 2 | HASH JOIN OUTER | | 1 | 6 | 7044 | 4603 (1)| 00:01:05 | 30553 |00:00:02.10 | 208K| 38M| 3120K| 39M (0)|
|* 3 | HASH JOIN OUTER | | 1 | 6 | 6870 | 4599 (1)| 00:01:05 | 30553 |00:00:01.97 | 208K| 38M| 3120K| 39M (0)|
|* 4 | HASH JOIN OUTER | | 1 | 6 | 6744 | 4591 (1)| 00:01:05 | 30553 |00:00:01.85 | 208K| 37M| 3121K| 39M (0)|
|* 5 | HASH JOIN OUTER | | 1 | 6 | 6570 | 4584 (1)| 00:01:05 | 30553 |00:00:01.74 | 208K| 37M| 3121K| 38M (0)|
|* 6 | HASH JOIN OUTER | | 1 | 6 | 6414 | 4576 (1)| 00:01:05 | 30553 |00:00:01.60 | 208K| 37M| 3121K| 38M (0)|
| 7 | NESTED LOOPS | | 1 | | | | | 30553 |00:00:01.44 | 208K| | | |
| 8 | NESTED LOOPS | | 1 | 6 | 6318 | 4572 (1)| 00:01:05 | 30553 |00:00:01.31 | 182K| | | |
| 9 | NESTED LOOPS | | 1 | 6 | 1830 | 4568 (1)| 00:01:04 | 30553 |00:00:01.11 | 124K| | | |
|* 10 | HASH JOIN | | 1 | 2069 | 270K| 2499 (1)| 00:00:35 | 30646 |00:00:00.46 | 23738 | 6539K| 2033K| 7965K (0)|
|* 11 | TABLE ACCESS BY INDEX ROWID | DSCLR | 1 | 5079 | 158K| 533 (1)| 00:00:08 | 91395 |00:00:00.28 | 6460 | | | |
| 12 | BITMAP CONVERSION TO ROWIDS | | 1 | | | | | 197K|00:00:00.16 | 615 | | | |
| 13 | BITMAP AND | | 1 | | | | | 4 |00:00:00.14 | 615 | | | |
| 14 | BITMAP OR | | 1 | | | | | 4 |00:00:00.07 | 276 | | | |
| 15 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 2 |00:00:00.02 | 69 | | | |
|* 16 | INDEX RANGE SCAN | XIF913DSCLR | 1 | 274K| | 14 (0)| 00:00:01 | 68407 |00:00:00.01 | 69 | | | |
| 17 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | | | 4 |00:00:00.05 | 207 | | | |
|* 18 | INDEX RANGE SCAN | XIF913DSCLR | 1 | 274K| | 42 (0)| 00:00:01 | 209K|00:00:00.03 | 207 | | | |
| 19 | BITMAP CONVERSION FROM ROWIDS | | 1 | | | | | 5 |00:00:00.06 | 339 | | | |
|* 20 | INDEX RANGE SCAN | XIF910DSCLR | 1 | 274K| | 67 (0)| 00:00:01 | 239K|00:00:00.04 | 339 | | | |
| 21 | NESTED LOOPS | | 1 | | | | | 21749 |00:00:00.10 | 17278 | | | |
| 22 | NESTED LOOPS | | 1 | 3404 | 339K| 1965 (1)| 00:00:28 | 22772 |00:00:00.03 | 1246 | | | |
| 23 | TABLE ACCESS FULL | TMP_RPT_BD_STATE_DATA_DWNLD | 1 | 3255 | 257K| 11 (0)| 00:00:01 | 3255 |00:00:00.01 | 27 | | | |
|* 24 | INDEX RANGE SCAN | XIE2OCRN | 3255 | 7 | | 1 (0)| 00:00:01 | 22772 |00:00:00.03 | 1219 | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | OCRN | 22772 | 1 | 21 | 1 (0)| 00:00:01 | 21749 |00:00:00.06 | 16032 | | | |
|* 26 | VIEW PUSHED PREDICATE | | 30646 | 1 | 171 | 1 (0)| 00:00:01 | 30553 |00:00:00.63 | 101K| | | |
| 27 | WINDOW BUFFER | | 30646 | 1 | 21 | 1 (0)| 00:00:01 | 46946 |00:00:00.59 | 101K| 2048 | 2048 | 2048 (0)|
|* 28 | TABLE ACCESS BY INDEX ROWID | DSCLR_FLNG | 30646 | 1 | 21 | 1 (0)| 00:00:01 | 46946 |00:00:00.32 | 101K| | | |
|* 29 | INDEX RANGE SCAN | XIE1DSCLR_FLNG | 30646 | 2 | | 1 (0)| 00:00:01 | 46946 |00:00:00.21 | 59862 | | | |
|* 30 | INDEX RANGE SCAN | XPKH760_RGLTY_ACTN_DSCLR | 30553 | 1 | | 1 (0)| 00:00:01 | 30553 |00:00:00.18 | 57541 | | | |
| 31 | TABLE ACCESS BY INDEX ROWID | H760_RGLTY_ACTN_DSCLR | 30553 | 1 | 748 | 1 (0)| 00:00:01 | 30553 |00:00:00.11 | 26218 | | | |
| 32 | TABLE ACCESS FULL | DSCLR_ST_TYPE_TBL | 1 | 4 | 64 | 4 (0)| 00:00:01 | 4 |00:00:00.01 | 5 | | | |
| 33 | TABLE ACCESS FULL | RGLTY_RSLTN_TYPE_TBL | 1 | 12 | 312 | 7 (0)| 00:00:01 | 12 |00:00:00.01 | 10 | | | |
| 34 | TABLE ACCESS FULL | INTTR_TYPE_TBL | 1 | 14 | 406 | 7 (0)| 00:00:01 | 14 |00:00:00.01 | 10 | | | |
| 35 | TABLE ACCESS FULL | SNCTN_TYPE_TBL | 1 | 15 | 315 | 7 (0)| 00:00:01 | 15 |00:00:00.01 | 10 | | | |
| 36 | TABLE ACCESS FULL | PRDCT_TYPE_TBL | 1 | 25 | 725 | 4 (0)| 00:00:01 | 25 |00:00:00.01 | 5 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$A8B7A3F4
11 - SEL$A8B7A3F4 / D@SEL$13
23 - SEL$A8B7A3F4 / OH@SEL$1
24 - SEL$A8B7A3F4 / O@SEL$13
25 - SEL$A8B7A3F4 / O@SEL$13
26 - SEL$13512960 / DF@SEL$13
27 - SEL$13512960
28 - SEL$13512960 / DF1@SEL$14
29 - SEL$13512960 / DF1@SEL$14
30 - SEL$A8B7A3F4 / H7@SEL$2
31 - SEL$A8B7A3F4 / H7@SEL$2
32 - SEL$A8B7A3F4 / DS@SEL$9
33 - SEL$A8B7A3F4 / RST@SEL$11
34 - SEL$A8B7A3F4 / ITT@SEL$3
35 - SEL$A8B7A3F4 / ST@SEL$5
36 - SEL$A8B7A3F4 / PT@SEL$7
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_index_cost_adj' 20)
ALL_ROWS
OUTLINE_LEAF(@"SEL$13512960")
PUSH_PRED(@"SEL$A8B7A3F4" "DF"@"SEL$13" 11)
OUTLINE_LEAF(@"SEL$A8B7A3F4")
ELIMINATE_JOIN(@"SEL$2D47CDEA" "DTT"@"SEL$13")
OUTLINE(@"SEL$14")
OUTLINE(@"SEL$A8B7A3F4")
ELIMINATE_JOIN(@"SEL$2D47CDEA" "DTT"@"SEL$13")
OUTLINE(@"SEL$2D47CDEA")
MERGE(@"SEL$3FAACB6D")
OUTLINE(@"SEL$15")
OUTLINE(@"SEL$3FAACB6D")
MERGE(@"SEL$11")
MERGE(@"SEL$A1A6E401")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$A1A6E401")
MERGE(@"SEL$9")
MERGE(@"SEL$BFB1842A")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$BFB1842A")
MERGE(@"SEL$7")
MERGE(@"SEL$EEDE2B8C")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$EEDE2B8C")
MERGE(@"SEL$5")
MERGE(@"SEL$A6B38458")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$A6B38458")
MERGE(@"SEL$171BE69E")
MERGE(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$171BE69E")
MERGE(@"SEL$AF02BF1E")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$AF02BF1E")
MERGE(@"SEL$13")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$13")
FULL(@"SEL$A8B7A3F4" "OH"@"SEL$1")
INDEX(@"SEL$A8B7A3F4" "O"@"SEL$13" ("OCRN"."ORG_PK" "OCRN"."DSCLR_DSCLB_FL" "OCRN"."DSCLR_RPTBL_FL"))
BITMAP_TREE(@"SEL$A8B7A3F4" "D"@"SEL$13" AND(OR(1 1 ("DSCLR"."FORM_TYPE_CD") 2 ("DSCLR"."FORM_TYPE_CD")) ("DSCLR"."DSCLR_TYPE_CD")))
NO_ACCESS(@"SEL$A8B7A3F4" "DF"@"SEL$13")
INDEX(@"SEL$A8B7A3F4" "H7"@"SEL$2" ("H760_RGLTY_ACTN_DSCLR"."FLNG_PK" "H760_RGLTY_ACTN_DSCLR"."DSCLR_PK" "H760_RGLTY_ACTN_DSCLR"."REC_SEQ_NB"))
FULL(@"SEL$A8B7A3F4" "DS"@"SEL$9")
FULL(@"SEL$A8B7A3F4" "RST"@"SEL$11")
FULL(@"SEL$A8B7A3F4" "ITT"@"SEL$3")
FULL(@"SEL$A8B7A3F4" "ST"@"SEL$5")
FULL(@"SEL$A8B7A3F4" "PT"@"SEL$7")
LEADING(@"SEL$A8B7A3F4" "OH"@"SEL$1" "O"@"SEL$13" "D"@"SEL$13" "DF"@"SEL$13" "H7"@"SEL$2" "DS"@"SEL$9" "RST"@"SEL$11" "ITT"@"SEL$3" "ST"@"SEL$5" "PT"@"SEL$7")
USE_NL(@"SEL$A8B7A3F4" "O"@"SEL$13")
NLJ_BATCHING(@"SEL$A8B7A3F4" "O"@"SEL$13")
USE_HASH(@"SEL$A8B7A3F4" "D"@"SEL$13")
USE_NL(@"SEL$A8B7A3F4" "DF"@"SEL$13")
USE_NL(@"SEL$A8B7A3F4" "H7"@"SEL$2")
NLJ_BATCHING(@"SEL$A8B7A3F4" "H7"@"SEL$2")
USE_HASH(@"SEL$A8B7A3F4" "DS"@"SEL$9")
USE_HASH(@"SEL$A8B7A3F4" "RST"@"SEL$11")
USE_HASH(@"SEL$A8B7A3F4" "ITT"@"SEL$3")
USE_HASH(@"SEL$A8B7A3F4" "ST"@"SEL$5")
USE_HASH(@"SEL$A8B7A3F4" "PT"@"SEL$7")
SWAP_JOIN_INPUTS(@"SEL$A8B7A3F4" "D"@"SEL$13")
INDEX_RS_ASC(@"SEL$13512960" "DF1"@"SEL$14" ("DSCLR_FLNG"."DSCLR_PK" "DSCLR_FLNG"."BLLBL_FL"))
END_OUTLINE_DATA
*/