The parameters affecting CBO are included in Event 10053 trace files.
Sample:
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL> select f1 from test10053 where f1=23;
F1
----------
23
Trace file is generated under USER_DUMP_DEST. Here is an excerpt from the trace file:
ubTools Support - 15/Jul/07 02:37 PM The parameters affecting CBO are included in Event 10053 trace files.
Sample:
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL> select f1 from test10053 where f1=23;
F1
----------
23
Trace file is generated under USER_DUMP_DEST. Here is an excerpt from the trace file:
Oracle - SQL Tuning
Closed
Major
Which parameters affect CBO ?
Sample:
SQL> alter session set events '10053 trace name context forever, level 1'; Session altered. SQL> select f1 from test10053 where f1=23; F1 ---------- 23Trace file is generated under USER_DUMP_DEST. Here is an excerpt from the trace file:
*** 2005-01-10 13:09:03.010 *** SESSION ID:(8.1072) 2005-01-10 13:09:03.008 QUERY select f1 from test10053 where f1=23 *************************************** 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: TEST10053 Alias: TEST10053 TOTAL :: CDN: 1000 NBLKS: 2 AVG_ROW_LEN: 7 -- Index stats INDEX NAME: I_TEST10053 COL#: 1 TOTAL :: LVLS: 1 #LB: 3 #DK: 1000 LB/K: 1 DB/K: 1 CLUF: 2 _OPTIMIZER_PERCENT_PARALLEL = 0 *************************************** SINGLE TABLE ACCESS PATH Column: F1 Col#: 1 Table: TEST10053 Alias: TEST10053 NDV: 1000 NULLS: 0 DENS: 1.0000e-03 LO: 1 HI: 1000 NO HISTOGRAM: #BKT: 1 #VAL: 2 TABLE: TEST10053 ORIG CDN: 1000 ROUNDED CDN: 1 CMPTD CDN: 1 Access path: tsc Resc: 2 Resp: 2 Access path: index (iff) Index: I_TEST10053 TABLE: TEST10053 RSC_CPU: 0 RSC_IO: 2 IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00 Access path: iff Resc: 2 Resp: 2 Access path: index (equal) Index: I_TEST10053 TABLE: TEST10053 RSC_CPU: 0 RSC_IO: 1 IX_SEL: 0.0000e+00 TB_SEL: 1.0000e-03 BEST_CST: 1.00 PATH: 4 Degree: 1 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *********************** Join order[1]: TEST10053 [TEST10053] Best so far: TABLE#: 0 CST: 1 CDN: 1 BYTES: 3 Final: CST: 1 CDN: 1 RSC: 1 RSP: 1 BYTES: 3 IO-RSC: 1 IO-RSP: 1 CPU-RSC: 0 CPU-RSP: 0Warnings: