Trace event 10132 can dump information after a SQL execution plan generated, which means trace information only written to trace file after hard parsing.
10132, 00000, "dump plan after compilation"
// *Cause:
// *Action: set this event only under the supervision of Oracle development
Turn on at session level
To turn on this trace at session level, user must have “alter session” privilege. In 9i “alter session” privilege is part of “connect” role.
alter session set events '10132 trace name context forever, level 1';
Analyze the trace file
Following items are included in the trace file:
Trace file header
Session information
SQL ID (10g)
SQL Text
SQL Plan
Peak binding variable value (10g)
Optimizer environment (10g)
Bug fix control environment (10g)
Schema name is part of the file name (customization)
Sample output in 9i
Dump file c:\oracle\admin\orcl9i\udump\orcl9i_ora_3520_hr.trc
Thu Sep 17 14:57:47 2009
ORACLE V9.2.0.7.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: orcl9i
Redo thread mounted by this instance: 1
Oracle process number: 10
Windows thread id: 3520, image: ORACLE.EXE
*** 2009-09-17 14:57:47.495
*** SESSION ID:(9.63) 2009-09-17 14:57:47.463
Current SQL statement for this session:
select employee_id, first_name||','||last_name from employees
where last_name=:last_name
Plan Table
--------
-------------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | 0 | 0 | 2 | | | | | |
| TABLE ACCESS FULL | EMPLOYEES | 1 | 19 | 2 | | | | | |
-------------------------------------------------------------------------------------------------------------------------
Sample output in 10g
[oracle@b1156rh5 udump]$ cat nik90_ora_3124.trc
/u01/app/oracle/admin/NIK90/udump/nik90_ora_3124.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: b1156rh5.ncs.com.sg
Release: 2.6.18-128.el5
Version: #1 SMP Wed Jan 21 07:58:05 EST 2009
Machine: i686
Instance name: NIK90
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 3124, image: oracle@b1156rh5.ncs.com.sg (TNS V1-V3)
*** 2009-08-20 21:00:21.951
*** ACTION NAME:() 2009-08-20 21:00:21.940
*** MODULE NAME:(SQL*Plus) 2009-08-20 21:00:21.940
*** SERVICE NAME:(SYS$USERS) 2009-08-20 21:00:21.940
*** SESSION ID:(159.5) 2009-08-20 21:00:21.940
*** 2009-08-20 21:01:10.919
sql_id=cdfjgg431ckgv.
Current SQL statement for this session:
select * from test where name=:t
============
Plan Table
============
-----------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 8000 | 3 | 00:00:01 |
| 2 | INDEX RANGE SCAN | TEST_N1 | 1 | | 1 | 00:00:01 |
-----------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("NAME"=:T)
Content of other_xml column
===========================
db_version : 10.2.0.4
parse_schema : DONGHUA
plan_hash : 3523414459
Peeked Binds
============
Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=31
char format=1
max length=32
value=a
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."NAME"))
END_OUTLINE_DATA
*/
Optimizer environment:
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 = 1
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 = 92160 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 = 128 KB
_smm_max_size = 18432 KB
_smm_px_max_size = 46080 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
Query Block Registry:
*********************
SEL$1 0xb7f666a0 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=12112, alloc=32736), compile(in-use=52412, alloc=100060)
No comments:
Post a Comment