Greetings sir,
After I generated 10053 EVENT, I executed query below.
Script below is a part of 10053 trace of that query.
What I¡¯m wondering is marked from ////// to //////// under this script.
My question are
1.in NL join, especially using COST_PROD_BIX index, RSC_IO was
zero. Is it possible?? And If so, could you tell me How RSC_IO
became zero ?
2.There is ¡°Not believed to be index-only¡± in the middle of the
part of closed as ///////. Despite I analyzed all index in COST
and PRODUCTS table, why did that message come?? And Could you tell
me when that message is generated generally??
3. When optimizer considered NL join using COST_PROD_BIX index,
TB_SEL was 8.3570e-005. In my opinion, TB_SEL should have
1.9912e-004. Because TB_SEL can obtain 1 / NDV . The NDV of Column
PROD_ID in COST table is 5022. So 1/5022 = 1.9912e-004. Could
you tell me how TB_SEL became 1.9912e-004.??
4. Finally, If you look down this script, you can find out ¡°BITMAP
ACCESS PATH¡±. What¡¯s the rule of estimating the cost of BITMAP
ACCESS PATH???



Best regards,
Thank you so much.


select count(*)
from costs, products
where costs.prod_id=products.prod_id and
costs.unit_price < 20 and
prod_subcategory = 'Shoes - Girls'


***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 9.2.0
OPTIMIZER_MODE/GOAL = Choose
_OPTIMIZER_PERCENT_PARALLEL = 101
HASH_AREA_SIZE = 1048576
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
SORT_AREA_SIZE = 524288
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = TRUE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = TRUE
_PUSH_JOIN_PREDICATE = TRUE
PARALLEL_BROADCAST_ENABLED = TRUE
OPTIMIZER_MAX_PERMUTATIONS = 2000
OPTIMIZER_INDEX_CACHING = 0
_SYSTEM_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
OPTIMIZER_DYNAMIC_SAMPLING = 1
_OPTIMIZER_DYN_SMP_BLKS = 32
QUERY_REWRITE_ENABLED = FALSE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = TRUE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = TRUE
ALWAYS_ANTI_JOIN = CHOOSE
ALWAYS_SEMI_JOIN = CHOOSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = TRUE
_PUSH_JOIN_UNION_VIEW = TRUE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = TRUE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = TRUE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
_OPTIMIZER_COST_MODEL = CHOOSE
_GSETS_ALWAYS_USE_TEMPTABLES = FALSE
DB_FILE_MULTIBLOCK_READ_COUNT = 16
_NEW_SORT_COST_ESTIMATE = TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
_CPU_TO_IO = 0
_PRED_MOVE_AROUND = TRUE
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: PRODUCTS Alias: PRODUCTS
TOTAL :: CDN: 10000 NBLKS: 369 AVG_ROW_LEN: 255
Column: PROD_ID Col#: 1 Table: PRODUCTS Alias: PRODUCTS
NDV: 10000 NULLS: 0 DENS: 1.0000e-004 LO: 5 HI: 50000
NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
INDEX NAME: PRODUCTS_PK COL#: 1
TOTAL :: LVLS: 1 #LB: 22 #DK: 10000 LB/K: 1 DB/K: 1 CLUF: 436
INDEX NAME: PRODUCTS_PROD_CAT_IX COL#: 6
TOTAL :: LVLS: 1 #LB: 23 #DK: 4 LB/K: 5 DB/K: 358 CLUF: 1435
INDEX NAME: PRODUCTS_PROD_SUBCAT_IX COL#: 4
TOTAL :: LVLS: 1 #LB: 40 #DK: 37 LB/K: 1 DB/K: 117 CLUF: 4344
INDEX NAME: PRODUCTS_STATUS_BIX COL#: 12
TOTAL :: LVLS: 0 #LB: 1 #DK: 5 LB/K: 1 DB/K: 1 CLUF: 5
***********************
Table stats Table: COSTS Alias: COSTS
TOTAL :: CDN: 787766 NBLKS: 2650 AVG_ROW_LEN: 19
Column: PROD_ID Col#: 1 Table: COSTS Alias: COSTS
NDV: 5022 NULLS: 0 DENS: 1.9912e-004 LO: 5 HI: 49980
NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
INDEX NAME: COSTS_UNIT_COST_IX COL#: 3
TOTAL :: LVLS: 2 #LB: 1628 #DK: 208 LB/K: 7 DB/K: 1240
CLUF: 257995
INDEX NAME: COST_PROD_BIX COL#: 1
TOTAL :: LVLS: 1 #LB: 340 #DK: 5022 LB/K: 1 DB/K: 1
CLUF: 5022
INDEX NAME: COST_TIME_BIX COL#: 2
TOTAL :: LVLS: 1 #LB: 22 #DK: 1030 LB/K: 1 DB/K: 1 CLUF: 1030
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column: UNIT_PRICE Col#: 4 Table: COSTS Alias: COSTS
NDV: 1878 NULLS: 0 DENS: 5.3248e-004 LO: 2 HI: 1200
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: COSTS ORIG CDN: 787766 ROUNDED CDN: 11966 CMPTD
CDN: 11966
Access path: tsc Resc: 256 Resp: 256
Access path: index (no sta/stp keys)
Index: COSTS_UNIT_COST_IX
TABLE: COSTS
RSC_CPU: 0 RSC_IO: 1630
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: COST_PROD_BIX
TABLE: COSTS
RSC_CPU: 0 RSC_IO: 341
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: COST_TIME_BIX
TABLE: COSTS
RSC_CPU: 0 RSC_IO: 23
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
Access path: index (no sta/stp keys)
Index: COST_TIME_BIX
TABLE: COSTS
RSC_CPU: 0 RSC_IO: 23
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
******** Bitmap access path rejected ********
Cost: 3481 Cost_io: 3481 Cost_cpu: 0 Selectivity: 1
Not believed to be index-only.
BEST_CST: 256.00 PATH: 2 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
Column: PROD_SUBCA Col#: 4 Table: PRODUCTS Alias: PRODUCTS
NDV: 37 NULLS: 0 DENS: 2.7027e-002
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: PRODUCTS ORIG CDN: 10000 ROUNDED CDN: 270 CMPTD CDN: 270
Access path: tsc Resc: 37 Resp: 37
Access path: index (equal)
Index: PRODUCTS_PROD_SUBCAT_IX
TABLE: PRODUCTS
RSC_CPU: 0 RSC_IO: 120
IX_SEL: 0.0000e+000 TB_SEL: 2.7027e-002
Access path: index (equal)
Index: PRODUCTS_PROD_SUBCAT_IX
TABLE: PRODUCTS
RSC_CPU: 0 RSC_IO: 2
IX_SEL: 2.7027e-002 TB_SEL: 2.7027e-002
Access path: index (no sta/stp keys)
Index: PRODUCTS_PK
TABLE: PRODUCTS
RSC_CPU: 0 RSC_IO: 23
IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000
******** Cost index join ********
Ix HA Join
Outer table:
resc: 3 cdn: 270 rcz: 28 deg: 1 resp: 3
Inner table: <no name>
resc: 29 cdn: 10000 rcz: 15 deg: 1 resp: 29
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 60 (max=307) buildfrag: 2 probefrag:
33 ppasses: 1
Hash join Resc: 32 Resp: 32
******** Index join cost ********
Cost: 33
******** Index join OK ********
BEST_CST: 33.00 PATH: 34 Degree: 1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: PRODUCTS [PRODUCTS] COSTS [COSTS]
Now joining: COSTS [COSTS] *******
NL Join
Outer table: cost: 33 cdn: 270 rcz: 23 resp: 33
Inner table: COSTS
Access path: tsc Resc: 256
Join: Resc: 69153 Resp: 69153
/////////////////////////////////////////////////////////////////////-
////////////////////////
Access path: index (unique)
Index: COST_PROD_BIX
TABLE: COSTS
RSC_CPU: 0 RSC_IO: 0
IX_SEL: 8.3570e-005 TB_SEL: 8.3570e-005
Join: resc: 33 resp: 33
******** Bitmap access path accepted ********
Cost: 10164 Cost_io: 10164 Cost_cpu: 0.000000 Selectivity: 0.000219
Not believed to be index-only.
Join cardinality: 708 = outer (270) * inner (11966) * sel
(2.1896e-004) [flag=0]
Best NL cost: 10164 resp: 10164
///////////////////////////////////////////////////////////////////////-
////////////////////////////
/* omission */

--
Posted via http://dbforums.com