oracle kkoqbc-subheap,How come the optimizer uses this approach?

KarlTrumstedt wrote:

Could you describe how to do the 10053 trace? I can not get this to work. I flush the shared pool (or restart the database), issue "alter session set events .... ", run my query, issue "alter session .... trace off..." and disconnect from SQL*plus. But no trace file is generated in the trace directory where all the usual trace files go

This is how I did it.

SQL> select * from v$version ;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE10.2.0.4.0Production

TNS for Solaris: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

SQL> alter session set tracefile_identifier = 'tr53' ;

Session altered.

SQL> alter session set events '10053 trace name context forever, level 1' ;

Session altered.

SQL> select count(*) from t where object_type = 'TABLE' ;

COUNT(*)

----------

1875

SQL> exitThis generated following tracefile in the directory mentioned as the value of USER_DUMP_DEST parameter.

/ora/app/oracle/admin/ORCL/udump/ORCL_ora_11642_tr53.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u0/app/oracle/product/10.2.0/db_2

System name:SunOS

Node name:crbdev5

Release:5.10

Version:Generic_127111-11

Machine:sun4u

Instance name: ORCL

Redo thread mounted by this instance: 1

Oracle process number: 21

Unix process pid: 11642, image: [email protected]

*** 2010-07-01 13:53:24.927

*** ACTION NAME:() 2010-07-01 13:53:24.922

*** MODULE NAME:(SQL*Plus) 2010-07-01 13:53:24.922

*** SERVICE NAME:(ORCL) 2010-07-01 13:53:24.922

*** SESSION ID:(135.51036) 2010-07-01 13:53:24.922

Registered qb: SEL$1 0x7ae19350 (PARSER)

signature (): qb_name=SEL$1 nbfros=1 flg=0

fro(0): flg=4 objn=76306 hint_alias="T"@"SEL$1"

**************************

Predicate Move-Around (PM)

**************************

PM: Considering predicate move-around in SEL$1 (#0).

PM: Checking validity of predicate move-around in SEL$1 (#0).

CBQT: Validity checks failed for 7k1kd2arxbfsa.

CVM: Considering view merge in query block SEL$1 (#0)

CBQT: Validity checks failed for 7k1kd2arxbfsa.

***************

Subquery Unnest

***************

SU: Considering subquery unnesting in query block SEL$1 (#0)

*************************

Set-Join Conversion (SJC)

*************************

SJC: Considering set-join conversion in SEL$1 (#0).

**************************

Predicate Move-Around (PM)

**************************

PM: Considering predicate move-around in SEL$1 (#0).

PM: Checking validity of predicate move-around in SEL$1 (#0).

PM: PM bypassed: Outer query contains no views.

FPD: Considering simple filter push in SEL$1 (#0)

FPD: Current where clause predicates in SEL$1 (#0) :

"T"."OBJECT_TYPE"='TABLE'

kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)

predicates with check contraints: "T"."OBJECT_TYPE"='TABLE'

after transitive predicate generation: "T"."OBJECT_TYPE"='TABLE'

finally: "T"."OBJECT_TYPE"='TABLE'

apadrv-start: call(in-use=592, alloc=16344), compile(in-use=37216, alloc=37312)

kkoqbc-start

: call(in-use=592, alloc=16344), compile(in-use=38104, alloc=41456)

kkoqbc-subheap (create addr=ffffffff7ae1b9c8)

******************************************

Current SQL statement for this session:

select count(*) from t where object_type = 'TABLE'

*******************************************

Legend

The following abbreviations are used by optimizer trace.

CBQT - cost-based query transformation

JPPD - join predicate push-down

FPD - filter push-down

PM - predicate move-around

CVM - complex view merging

SPJ - select-project-join

SJC - set join conversion

SU - subquery unnesting

OBYE - order by elimination

ST - star transformation

qb - query block

LB - leaf blocks

DK - distinct keys

LB/K - average number of leaf blocks per key

DB/K - average number of data blocks per key

CLUF - clustering factor

NDV - number of distinct values

Resp - response cost

Card - cardinality

Resc - resource cost

NL - nested loops (join)

SM - sort merge (join)

HA - hash (join)

CPUCSPEED - CPU Speed

IOTFRSPEED - I/O transfer speed

IOSEEKTIM - I/O seek time

SREADTIM - average single block read time

MREADTIM - average multiblock read time

MBRC - average multiblock read count

MAXTHR - maximum I/O system throughput

SLAVETHR - average slave I/O throughput

dmeth - distribution method

1: no partitioning required

2: value partitioned

4: right is random (round-robin)

512: left is random (round-robin)

8: broadcast right and partition left

16: broadcast left and partition right

32: partition left using partitioning of right

64: partition right using partitioning of left

128: use hash partitioning dimension

256: use range partitioning dimension

2048: use list partitioning dimension

1024: run the join in serial

0: invalid distribution method

sel - selectivity

ptn - partition

*******************************************

Peeked values of the binds in SQL statement

*******************************************

***************************************

PARAMETERS USED BY THE OPTIMIZER

********************************

*************************************

PARAMETERS WITH ALTERED VALUES

******************************

*********************************

Bug Fix Control Environment

***************************

fix 4611850 = enabled

fix 4663804 = enabled

fix 4663698 = enabled

fix 4545833 = enabled

fix 3499674 = disabled

fix 4584065 = enabled

fix 4602374 = enabled

fix 4569940 = enabled

fix 4631959 = enabled

fix 4519340 = enabled

fix 4550003 = enabled

fix 4488689 = enabled

fix 3118776 = enabled

fix 4519016 = enabled

fix 4487253 = enabled

fix 4556762 = 15

fix 4728348 = enabled

fix 4723244 = enabled

fix 4554846 = enabled

fix 4175830 = enabled

fix 4722900 = enabled

fix 5094217 = enabled

fix 4904890 = enabled

fix 4483286 = disabled

fix 4969880 = disabled

fix 4711525 = enabled

fix 4717546 = enabled

fix 4904838 = enabled

fix 5005866 = enabled

fix 4600710 = enabled

fix 5129233 = enabled

fix 5195882 = enabled

fix 5084239 = enabled

fix 4595987 = enabled

fix 4134994 = enabled

fix 5104624 = enabled

fix 4908162 = enabled

fix 5015557 = enabled

fix 5263572 = enabled

fix 4483240 = enabled

fix 5099909 = enabled

fix 5650477 = enabled

fix 4273361 = enabled

fix 5694984 = enabled

fix 5449488 = enabled

fix 5236908 = enabled

fix 5618040 = enabled

fix 5634346 = enabled

fix 5220356 = enabled

fix 5611962 = enabled

fix 5741121 = enabled

fix 5547058 = enabled

fix 5762598 = enabled

fix 5509293 = enabled

fix 5396162 = enabled

fix 5891471 = enabled

fix 4872602 = disabled

fix 5882954 = enabled

fix 5884780 = enabled

fix 5680702 = enabled

fix 5240607 = enabled

fix 4924149 = enabled

fix 4752814 = enabled

fix 4583239 = enabled

fix 5949981 = enabled

fix 5096560 = enabled

fix 5838613 = enabled

fix 5482831 = enabled

fix 5624216 = enabled

fix 5976822 = enabled

fix 5741044 = enabled

fix 5385629 = enabled

fix 5705630 = disabled

fix 5483301 = enabled

fix 6122894 = enabled

fix 5842686 = disabled

fix 6006300 = disabled

fix 6070954 = enabled

fix 2492766 = enabled

fix 6042205 = enabled

fix 5302124 = enabled

fix 6051211 = enabled

fix 5620485 = enabled

fix 4545802 = enabled

fix 4716096 = enabled

fix 5259048 = enabled

fix 6163564 = enabled

fix 6082745 = enabled

fix 5944076 = enabled

fix 4878299 = enabled

fix 5288623 = enabled

fix 5570494 = enabled

fix 5387148 = enabled

fix 4605810 = enabled

fix 4704779 = enabled

fix 5547895 = enabled

fix 6188881 = enabled

fix 5872956 = enabled

fix 4708389 = enabled

fix 3151991 = enabled

fix 3426050 = enabled

fix 599680 = enabled

fix 5505157 = enabled

fix 5996801 = enabled

fix 5765456 = 0

fix 6494943 = enabled

fix 6251917 = enabled

fix 6087237 = enabled

fix 6239971 = enabled

fix 6062266 = enabled

fix 5520732 = 0

fix 6151963 = enabled

fix 4567767 = enabled

fix 6007259 = enabled

fix 6694548 = enabled

*************************************

PARAMETERS WITH DEFAULT VALUES

******************************

optimizer_mode_hinted = false

optimizer_features_hinted = 0.0.0

parallel_execution_enabled = true

parallel_query_forced_dop = 0

parallel_dml_forced_dop = 0

parallel_ddl_forced_degree = 0

parallel_ddl_forced_instances = 0

_query_rewrite_fudge = 90

optimizer_features_enable = 10.2.0.4

_optimizer_search_limit = 5

cpu_count = 8

active_instance_count = 1

parallel_threads_per_cpu = 2

hash_area_size = 131072

bitmap_merge_area_size = 1048576

sort_area_size = 65536

sort_area_retained_size = 0

_sort_elimination_cost_ratio = 0

_optimizer_block_size = 8192

_sort_multiblock_read_count = 2

_hash_multiblock_io_count = 0

_db_file_optimizer_read_count = 16

_optimizer_max_permutations = 2000

pga_aggregate_target = 153600 KB

_pga_max_size = 204800 KB

_query_rewrite_maxdisjunct = 257

_smm_auto_min_io_size = 56 KB

_smm_auto_max_io_size = 248 KB

_smm_min_size = 153 KB

_smm_max_size = 30720 KB

_smm_px_max_size = 76800 KB

_cpu_to_io = 0

_optimizer_undo_cost_change = 10.2.0.4

parallel_query_mode = enabled

parallel_dml_mode = disabled

parallel_ddl_mode = enabled

optimizer_mode = all_rows

sqlstat_enabled = false

_optimizer_percent_parallel = 101

_always_anti_join = choose

_always_semi_join = choose

_optimizer_mode_force = true

_partition_view_enabled = true

_always_star_transformation = false

_query_rewrite_or_error = false

_hash_join_enabled = true

cursor_sharing = exact

_b_tree_bitmap_plans = true

star_transformation_enabled = false

_optimizer_cost_model = choose

_new_sort_cost_estimate = true

_complex_view_merging = true

_unnest_subquery = true

_eliminate_common_subexpr = true

_pred_move_around = true

_convert_set_to_join = false

_push_join_predicate = true

_push_join_union_view = true

_fast_full_scan_enabled = true

_optim_enhance_nnull_detection = true

_parallel_broadcast_enabled = true

_px_broadcast_fudge_factor = 100

_ordered_nested_loop = true

_no_or_expansion = false

optimizer_index_cost_adj = 100

optimizer_index_caching = 0

_system_index_caching = 0

_disable_datalayer_sampling = false

query_rewrite_enabled = true

query_rewrite_integrity = enforced

_query_cost_rewrite = true

_query_rewrite_2 = true

_query_rewrite_1 = true

_query_rewrite_expression = true

_query_rewrite_jgmigrate = true

_query_rewrite_fpc = true

_query_rewrite_drj = true

_full_pwise_join_enabled = true

_partial_pwise_join_enabled = true

_left_nested_loops_random = true

_improved_row_length_enabled = true

_index_join_enabled = true

_enable_type_dep_selectivity = true

_improved_outerjoin_card = true

_optimizer_adjust_for_nulls = true

_optimizer_degree = 0

_use_column_stats_for_function = true

_subquery_pruning_enabled = true

_subquery_pruning_mv_enabled = false

_or_expand_nvl_predicate = true

_like_with_bind_as_equality = false

_table_scan_cost_plus_one = true

_cost_equality_semi_join = true

_default_non_equality_sel_check = true

_new_initial_join_orders = true

_oneside_colstat_for_equijoins = true

_optim_peek_user_binds = true

_minimal_stats_aggregation = true

_force_temptables_for_gsets = false

workarea_size_policy = auto

_smm_auto_cost_enabled = true

_gs_anti_semi_join_allowed = true

_optim_new_default_join_sel = true

optimizer_dynamic_sampling = 2

_pre_rewrite_push_pred = true

_optimizer_new_join_card_computation = true

_union_rewrite_for_gs = yes_gset_mvs

_generalized_pruning_enabled = true

_optim_adjust_for_part_skews = true

_force_datefold_trunc = false

statistics_level = typical

_optimizer_system_stats_usage = true

skip_unusable_indexes = true

_remove_aggr_subquery = true

_optimizer_push_down_distinct = 0

_dml_monitoring_enabled = true

_optimizer_undo_changes = false

_predicate_elimination_enabled = true

_nested_loop_fudge = 100

_project_view_columns = true

_local_communication_costing_enabled = true

_local_communication_ratio = 50

_query_rewrite_vop_cleanup = true

_slave_mapping_enabled = true

_optimizer_cost_based_transformation = linear

_optimizer_mjc_enabled = true

_right_outer_hash_enable = true

_spr_push_pred_refspr = true

_optimizer_cache_stats = false

_optimizer_cbqt_factor = 50

_optimizer_squ_bottomup = true

_fic_area_size = 131072

_optimizer_skip_scan_enabled = true

_optimizer_cost_filter_pred = false

_optimizer_sortmerge_join_enabled = true

_optimizer_join_sel_sanity_check = true

_mmv_query_rewrite_enabled = true

_bt_mmv_query_rewrite_enabled = true

_add_stale_mv_to_dependency_list = true

_distinct_view_unnesting = false

_optimizer_dim_subq_join_sel = true

_optimizer_disable_strans_sanity_checks = 0

_optimizer_compute_index_stats = true

_push_join_union_view2 = true

_optimizer_ignore_hints = false

_optimizer_random_plan = 0

_query_rewrite_setopgrw_enable = true

_optimizer_correct_sq_selectivity = true

_disable_function_based_index = false

_optimizer_join_order_control = 3

_optimizer_cartesian_enabled = true

_optimizer_starplan_enabled = true

_extended_pruning_enabled = true

_optimizer_push_pred_cost_based = true

_sql_model_unfold_forloops = run_time

_enable_dml_lock_escalation = false

_bloom_filter_enabled = true

_update_bji_ipdml_enabled = 0

_optimizer_extended_cursor_sharing = udo

_dm_max_shared_pool_pct = 1

_optimizer_cost_hjsmj_multimatch = true

_optimizer_transitivity_retain = true

_px_pwg_enabled = true

optimizer_secure_view_merging = true

_optimizer_join_elimination_enabled = true

flashback_table_rpi = non_fbt

_optimizer_cbqt_no_size_restriction = true

_optimizer_enhanced_filter_push = true

_optimizer_filter_pred_pullup = true

_rowsrc_trace_level = 0

_simple_view_merging = true

_optimizer_rownum_pred_based_fkr = true

_optimizer_better_inlist_costing = all

_optimizer_self_induced_cache_cost = false

_optimizer_min_cache_blocks = 10

_optimizer_or_expansion = depth

_optimizer_order_by_elimination_enabled = true

_optimizer_outer_to_anti_enabled = true

_selfjoin_mv_duplicates = true

_dimension_skip_null = true

_force_rewrite_enable = false

_optimizer_star_tran_in_with_clause = true

_optimizer_complex_pred_selectivity = true

_optimizer_connect_by_cost_based = true

_gby_hash_aggregation_enabled = true

_globalindex_pnum_filter_enabled = true

_fix_control_key = 0

_optimizer_skip_scan_guess = false

_enable_row_shipping = false

_row_shipping_threshold = 80

_row_shipping_explain = false

_optimizer_rownum_bind_default = 10

_first_k_rows_dynamic_proration = true

_px_ual_serial_input = true

_optimizer_native_full_outer_join = off

_optimizer_star_trans_min_cost = 0

_optimizer_star_trans_min_ratio = 0

_optimizer_fkr_index_cost_bias = 10

_optimizer_connect_by_combine_sw = true

_optimizer_use_subheap = true

_optimizer_or_expansion_subheap = true

_optimizer_sortmerge_join_inequality = true

_optimizer_use_histograms = true

_optimizer_enable_density_improvements = false

*********************************

Bug Fix Control Environment

***************************

fix 4611850 = enabled

fix 4663804 = enabled

fix 4663698 = enabled

fix 4545833 = enabled

fix 3499674 = disabled

fix 4584065 = enabled

fix 4602374 = enabled

fix 4569940 = enabled

fix 4631959 = enabled

fix 4519340 = enabled

fix 4550003 = enabled

fix 4488689 = enabled

fix 3118776 = enabled

fix 4519016 = enabled

fix 4487253 = enabled

fix 4556762 = 15

fix 4728348 = enabled

fix 4723244 = enabled

fix 4554846 = enabled

fix 4175830 = enabled

fix 4722900 = enabled

fix 5094217 = enabled

fix 4904890 = enabled

fix 4483286 = disabled

fix 4969880 = disabled

fix 4711525 = enabled

fix 4717546 = enabled

fix 4904838 = enabled

fix 5005866 = enabled

fix 4600710 = enabled

fix 5129233 = enabled

fix 5195882 = enabled

fix 5084239 = enabled

fix 4595987 = enabled

fix 4134994 = enabled

fix 5104624 = enabled

fix 4908162 = enabled

fix 5015557 = enabled

fix 5263572 = enabled

fix 4483240 = enabled

fix 5099909 = enabled

fix 5650477 = enabled

fix 4273361 = enabled

fix 5694984 = enabled

fix 5449488 = enabled

fix 5236908 = enabled

fix 5618040 = enabled

fix 5634346 = enabled

fix 5220356 = enabled

fix 5611962 = enabled

fix 5741121 = enabled

fix 5547058 = enabled

fix 5762598 = enabled

fix 5509293 = enabled

fix 5396162 = enabled

fix 5891471 = enabled

fix 4872602 = disabled

fix 5882954 = enabled

fix 5884780 = enabled

fix 5680702 = enabled

fix 5240607 = enabled

fix 4924149 = enabled

fix 4752814 = enabled

fix 4583239 = enabled

fix 5949981 = enabled

fix 5096560 = enabled

fix 5838613 = enabled

fix 5482831 = enabled

fix 5624216 = enabled

fix 5976822 = enabled

fix 5741044 = enabled

fix 5385629 = enabled

fix 5705630 = disabled

fix 5483301 = enabled

fix 6122894 = enabled

fix 5842686 = disabled

fix 6006300 = disabled

fix 6070954 = enabled

fix 2492766 = enabled

fix 6042205 = enabled

fix 5302124 = enabled

fix 6051211 = enabled

fix 5620485 = enabled

fix 4545802 = enabled

fix 4716096 = enabled

fix 5259048 = enabled

fix 6163564 = enabled

fix 6082745 = enabled

fix 5944076 = enabled

fix 4878299 = enabled

fix 5288623 = enabled

fix 5570494 = enabled

fix 5387148 = enabled

fix 4605810 = enabled

fix 4704779 = enabled

fix 5547895 = enabled

fix 6188881 = enabled

fix 5872956 = enabled

fix 4708389 = enabled

fix 3151991 = enabled

fix 3426050 = enabled

fix 599680 = enabled

fix 5505157 = enabled

fix 5996801 = enabled

fix 5765456 = 0

fix 6494943 = enabled

fix 6251917 = enabled

fix 6087237 = enabled

fix 6239971 = enabled

fix 6062266 = enabled

fix 5520732 = 0

fix 6151963 = enabled

fix 4567767 = enabled

fix 6007259 = enabled

fix 6694548 = enabled

***************************************

PARAMETERS IN OPT_PARAM HINT

****************************

***************************************

Column Usage Monitoring is ON: tracking level = 1

***************************************

****************

QUERY BLOCK TEXT

****************

select count(*) from t where object_type = 'TABLE'

*********************

QUERY BLOCK SIGNATURE

*********************

qb name was generated

signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0

fro(0): flg=0 objn=76306 hint_alias="T"@"SEL$1"

*****************************

SYSTEM STATISTICS INFORMATION

*****************************

Using NOWORKLOAD Stats

CPUSPEED: 647 millions instruction/sec

IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

IOSEEKTIM: 10 milliseconds (default is 10)

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: T Alias: T

#Rows: 51417 #Blks: 729 AvgRowLen: 94.00

***************************************

SINGLE TABLE ACCESS PATH

-----------------------------------------

BEGIN Single Table Cardinality Estimation

-----------------------------------------

Column (#6): OBJECT_TYPE(VARCHAR2)

AvgLen: 9.00 NDV: 29 Nulls: 0 Density: 1.0917e-04

Histogram: Freq #Bkts: 29 UncompBkts: 4580 EndPtVals: 29

Table: T Alias: T

Card: Original: 51417 Rounded: 1796 Computed: 1796.23 Non Adjusted: 1796.23

-----------------------------------------

END Single Table Cardinality Estimation

-----------------------------------------

Access Path: TableScan

Cost: 163.66 Resp: 163.66 Degree: 0

Cost_io: 161.00 Cost_cpu: 20616630

Resp_io: 161.00 Resp_cpu: 20616630

Best:: AccessPath: TableScan

Cost: 163.66 Degree: 1 Resp: 163.66 Card: 1796.23 Bytes: 0

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

***********************

Join order[1]: T[T]#0

***********************

Best so far: Table#: 0 cost: 163.6572 card: 1796.2271 bytes: 16164

(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000

*********************************

Number of join permutations tried: 1

*********************************

Final - All Rows Plan: Best join order: 1

Cost: 163.6572 Degree: 1 Card: 1796.0000 Bytes: 16164

Resc: 163.6572 Resc_io: 161.0000 Resc_cpu: 20616630

Resp: 163.6572 Resp_io: 161.0000 Resc_cpu: 20616630

kkoipt: Query block SEL$1 (#0)

******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SCOTT"."T" "T" WHERE "T"."OBJECT_TYPE"='TABLE'

kkoqbc-subheap (delete addr=ffffffff7ae1b9c8, in-use=11608, alloc=11920)

kkoqbc-end

: call(in-use=10248, alloc=32712), compile(in-use=38936, alloc=41456)

apadrv-end: call(in-use=10248, alloc=32712), compile(in-use=39720, alloc=41456)

sql_id=7k1kd2arxbfsa.

Current SQL statement for this session:

select count(*) from t where object_type = 'TABLE'

============

Plan Table

============

--------------------------------------+-----------------------------------+

| Id | Operation | Name | Rows | Bytes | Cost | Time |

--------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 164 | |

| 1 | SORT AGGREGATE | | 1 | 9 | | |

| 2 | TABLE ACCESS FULL | T | 1796 | 16K | 164 | 00:00:02 |

--------------------------------------+-----------------------------------+

Predicate Information:

----------------------

2 - filter("OBJECT_TYPE"='TABLE')

Content of other_xml column

===========================

db_version : 10.2.0.4

parse_schema : SCOTT

plan_hash : 1842905362

Outline Data:

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1" "T"@"SEL$1")

END_OUTLINE_DATA

*/

.

.

.