Monday, August 31, 2009

ORA-00704 Bootstrap process failure during the DBCA

After 11g1 Patchset 1 installation, i decided to to created a new database using DBCA with "General Purpose" template, error ORA-00740 raised around 40% of completion time.



Errors in file /u02/app/oracle/diag/rdbms/orcl11/ORCL11/trace/ORCL11_ora_25029.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Error 704 happened during db open, shutting down database
USER (ospid: 25029): terminating the instance due to error 704
Instance terminated by USER, pid = 25029
ORA-1092 signalled during: alter database "ORCL11" open resetlogs...
ORA-1092 : opiodr aborting process unknown ospid (25029_2201808)
Mon Aug 31 00:14:53 2009
ORA-1092 : opitsk aborting process



This was caused by incorrect permission during the installation. (Oracle owner should have "write" permission on these template files. (Similar issue occurs for 10.2.0.3, refer to notes 471765.1)


[oracle11g@vmxdb01 templates]$ cd $ORACLE_HOME/assistants/dbca/templates
[oracle11g@vmxdb01 templates]$ ls -lh
total 204M
-rw-r--r-- 1 oracle11g oinstall 5.2K Sep 10 2008 Data_Warehouse.dbc
-r-xr-xr-x 1 oracle11g oinstall 21M Aug 4 2007 example01.dfb
-r-xr-xr-x 1 oracle11g oinstall 1.1M Aug 4 2007 example.dmp
-rw-r--r-- 1 oracle11g oinstall 5.1K Sep 10 2008 General_Purpose.dbc
-rw-r--r-- 1 oracle11g oinstall 12K Jun 21 2007 New_Database.dbt
-r-xr-xr-x 1 oracle11g oinstall 9.3M Aug 4 2007 Seed_Database.ctl
-r-xr-xr-x 1 oracle11g oinstall 173M Aug 4 2007 Seed_Database.dfb


These write-protected files have different size comparing to these coming with 11.1.0.7 patchset.


[oracle11g@vmxdb01 templates]$ ls -lh /u02/stage/Disk1/stage/Patches/\
> oracle.rdbms.install.seeddb/11.1.0.7.0/1/DataFiles/Expanded/filegroup1/
total 186M
-rwxr-xr-x 1 oracle11g oinstall 9.3M Sep 12 2008 Seed_Database.ctl
-rwxr-xr-x 1 oracle11g oinstall 176M Sep 12 2008 Seed_Database.dfb

[oracle11g@vmxdb01 templates]$ ls -lh /u02/stage/Disk1/stage/Patches/\
> oracle.rdbms.install.seeddb.sample_schema/11.1.0.7.0/1/DataFiles/Expanded/filegroup1/
total 22M
-rwxr-xr-x 1 oracle11g oinstall 21M Sep 12 2008 example01.dfb
-rwxr-xr-x 1 oracle11g oinstall 1.2M Sep 12 2008 example.dmp


Solutions: Copy these files from patchset and re-run DBCA.



[oracle11g@vmxdb01 templates]$ chmod u+w *

[oracle11g@vmxdb01 templates]$ cp /u02/stage/Disk1/stage/Patches/\
>oracle.rdbms.install.seeddb/11.1.0.7.0/1/DataFiles/Expanded/filegroup1/* .
[oracle11g@vmxdb01 templates]$ cp /u02/stage/Disk1/stage/Patches/\
>oracle.rdbms.install.seeddb.sample_schema/11.1.0.7.0/1/DataFiles/Expanded/filegroup1/* .


[oracle11g@vmxdb01 templates]$ ls -lh
total 207M
-rw-r--r-- 1 oracle11g oinstall 5.2K Sep 10 2008 Data_Warehouse.dbc
-rwxr-xr-x 1 oracle11g oinstall 21M Aug 31 00:23 example01.dfb
-rwxr-xr-x 1 oracle11g oinstall 1.2M Aug 31 00:23 example.dmp
-rw-r--r-- 1 oracle11g oinstall 5.1K Sep 10 2008 General_Purpose.dbc
-rw-r--r-- 1 oracle11g oinstall 12K Jun 21 2007 New_Database.dbt
-rwxr-xr-x 1 oracle11g oinstall 9.3M Aug 31 00:22 Seed_Database.ctl
-rwxr-xr-x 1 oracle11g oinstall 176M Aug 31 00:22 Seed_Database.dfb

Sunday, August 30, 2009

Troubleshooting Oracle Performance - Shared Cursors

Both parent cursor and child curor are stored in the library cache during the parse phase. Key parent cursor information is the SQL text itself. And child cursor is assosicated with parent cursor and contain execution plan and execution envrionment.

Below lab shows how to identity parent and child cursor and find out why they are not sharing.

Flush shared pool and exeute 4 querys, first 3 queries are different in lower/upper cases, and extra blank space. The forth sql statement is identical to first statement.



SQL> conn donghua/donghua
Connected.

SQL> alter system flush shared_pool;

System altered.

SQL> select * from t;

no rows selected

SQL> SELECT * FROM T;

no rows selected

SQL> select * from t;

no rows selected

SQL> select * from t;

no rows selected




Now check how many parent cursor has been created.



SQL> col sql_text for a30
SQL> select sql_id, sql_text, executions, version_count from v$sqlarea
2 where lower(sql_text) like 'select * from%t%';

SQL_ID SQL_TEXT EXECUTIONS VERSION_COUNT
------------- ------------------------------ ---------- -------------
89km4qj1thh13 select * from t 2 1
6k1fc899x4ud2 SELECT * FROM T 1 1
3k2ncgn581pdv select * from t 1 1


We also need to understand that not every parent cursor has a unique sql plan. From below, they share the same sql execution plan.



SQL> select sql_id, sql_text, plan_hash_value from v$sqlarea
2 where lower(sql_text) like 'select * from%t%';

SQL_ID SQL_TEXT PLAN_HASH_VALUE
------------- ------------------------------ ---------------
89km4qj1thh13 select * from t 1601196873
6k1fc899x4ud2 SELECT * FROM T 1601196873
3k2ncgn581pdv select * from t 1601196873


Execution envrionment changing will create new child child cursor, for example, new optimizer_mode value.



SQL> alter session set optimizer_mode='FIRST_ROWS_1';

Session altered.

SQL> select * from t;

no rows selected

SQL> select sql_id, sql_text, executions, version_count from v$sqlarea
2 where lower(sql_text) like 'select * from%t%';

SQL_ID SQL_TEXT EXECUTIONS VERSION_COUNT
------------- ------------------------------ ---------- -------------
89km4qj1thh13 select * from t 3 2
6k1fc899x4ud2 SELECT * FROM T 1 1
3k2ncgn581pdv select * from t 1 1


Using synonym will not create new child cursor, as long as they refer to the same based object.



SQL> conn donghua1/donghua1@orcl
Connected.

SQL> create synonym t for donghua.t;

Synonym created.

SQL> select * from t;

no rows selected

SQL> select sql_id, sql_text, executions, version_count from v$sqlarea
2 where lower(sql_text) like 'select * from%t%';

SQL_ID SQL_TEXT EXECUTIONS VERSION_COUNT
------------- ------------------------------ ---------- -------------
89km4qj1thh13 select * from t 4 2
6k1fc899x4ud2 SELECT * FROM T 1 1
3k2ncgn581pdv select * from t 1 1


Enable sql trace will cause sql statement parse again. And new child cursor will created. (Sometimes this is the reason we can not reproceduce some performance issue related to binding variable peeking by turn on sql trace.)



SQL> conn donghua/donghua@orcl
Connected.
SQL> alter session set sql_trace=true;

Session altered.

SQL> select * from t;

no rows selected

SQL> select sql_id, sql_text, executions, version_count from v$sqlarea
2 where lower(sql_text) like 'select * from%t%';

SQL_ID SQL_TEXT EXECUTIONS VERSION_COUNT
------------- ------------------------------ ---------- -------------
89km4qj1thh13 select * from t 5 3
6k1fc899x4ud2 SELECT * FROM T 1 1
3k2ncgn581pdv select * from t 1 1


To find out why child cursor is not shared by querying v$sql_shared_cursor.



SQL> select sql_text, child_number, optimizer_mode, plan_hash_value
2 from v$sql where sql_id='89km4qj1thh13';

SQL_TEXT CHILD_NUMBER OPTIMIZER_ PLAN_HASH_VALUE
------------------------------ ------------ ---------- ---------------
select * from t 0 ALL_ROWS 1601196873
select * from t 1 FIRST_ROWS 1601196873
select * from t 2 ALL_ROWS 1601196873

QL> select child_number, child_address, stats_row_mismatch, optimizer_mode_mismatch
2 from v$sql_shared_cursor where sql_id='89km4qj1thh13';

HILD_NUMBER CHILD_AD S O
----------- -------- - -
0 2AD9B784 N N
1 2AC7B1B4 N Y
2 2ACA0C9C Y N



Possible reasons for a child cursor incompatible with anonther one listed below, (63 reasons in 11gR2).


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.0.1 - Beta
PL/SQL Release 11.2.0.0.1 - Beta
CORE 11.2.0.0.1 Beta
TNS for 32-bit Windows: Version 11.2.0.0.1 - Beta
NLSRTL Version 11.2.0.0.1 - Beta

SQL> desc v$sql_shared_cursor
Name Null? Type
----------------------------------------- -------- ----------------
SQL_ID VARCHAR2(13)
ADDRESS RAW(4)
CHILD_ADDRESS RAW(4)
CHILD_NUMBER NUMBER
UNBOUND_CURSOR VARCHAR2(1)
SQL_TYPE_MISMATCH VARCHAR2(1)
OPTIMIZER_MISMATCH VARCHAR2(1)
OUTLINE_MISMATCH VARCHAR2(1)
STATS_ROW_MISMATCH VARCHAR2(1)
LITERAL_MISMATCH VARCHAR2(1)
FORCE_HARD_PARSE VARCHAR2(1)
EXPLAIN_PLAN_CURSOR VARCHAR2(1)
BUFFERED_DML_MISMATCH VARCHAR2(1)
PDML_ENV_MISMATCH VARCHAR2(1)
INST_DRTLD_MISMATCH VARCHAR2(1)
SLAVE_QC_MISMATCH VARCHAR2(1)
TYPECHECK_MISMATCH VARCHAR2(1)
AUTH_CHECK_MISMATCH VARCHAR2(1)
BIND_MISMATCH VARCHAR2(1)
DESCRIBE_MISMATCH VARCHAR2(1)
LANGUAGE_MISMATCH VARCHAR2(1)
TRANSLATION_MISMATCH VARCHAR2(1)
BIND_EQUIV_FAILURE VARCHAR2(1)
INSUFF_PRIVS VARCHAR2(1)
INSUFF_PRIVS_REM VARCHAR2(1)
REMOTE_TRANS_MISMATCH VARCHAR2(1)
LOGMINER_SESSION_MISMATCH VARCHAR2(1)
INCOMP_LTRL_MISMATCH VARCHAR2(1)
OVERLAP_TIME_MISMATCH VARCHAR2(1)
EDITION_MISMATCH VARCHAR2(1)
MV_QUERY_GEN_MISMATCH VARCHAR2(1)
USER_BIND_PEEK_MISMATCH VARCHAR2(1)
TYPCHK_DEP_MISMATCH VARCHAR2(1)
NO_TRIGGER_MISMATCH VARCHAR2(1)
FLASHBACK_CURSOR VARCHAR2(1)
ANYDATA_TRANSFORMATION VARCHAR2(1)
INCOMPLETE_CURSOR VARCHAR2(1)
TOP_LEVEL_RPI_CURSOR VARCHAR2(1)
DIFFERENT_LONG_LENGTH VARCHAR2(1)
LOGICAL_STANDBY_APPLY VARCHAR2(1)
DIFF_CALL_DURN VARCHAR2(1)
BIND_UACS_DIFF VARCHAR2(1)
PLSQL_CMP_SWITCHS_DIFF VARCHAR2(1)
CURSOR_PARTS_MISMATCH VARCHAR2(1)
STB_OBJECT_MISMATCH VARCHAR2(1)
CROSSEDITION_TRIGGER_MISMATCH VARCHAR2(1)
PQ_SLAVE_MISMATCH VARCHAR2(1)
TOP_LEVEL_DDL_MISMATCH VARCHAR2(1)
MULTI_PX_MISMATCH VARCHAR2(1)
BIND_PEEKED_PQ_MISMATCH VARCHAR2(1)
MV_REWRITE_MISMATCH VARCHAR2(1)
ROLL_INVALID_MISMATCH VARCHAR2(1)
OPTIMIZER_MODE_MISMATCH VARCHAR2(1)
PX_MISMATCH VARCHAR2(1)
MV_STALEOBJ_MISMATCH VARCHAR2(1)
FLASHBACK_TABLE_MISMATCH VARCHAR2(1)
LITREP_COMP_MISMATCH VARCHAR2(1)
PLSQL_DEBUG VARCHAR2(1)
LOAD_OPTIMIZER_STATS VARCHAR2(1)
ACL_MISMATCH VARCHAR2(1)
FLASHBACK_ARCHIVE_MISMATCH VARCHAR2(1)
LOCK_USER_SCHEMA_FAILED VARCHAR2(1)
REMOTE_MAPPING_MISMATCH VARCHAR2(1)
LOAD_RUNTIME_HEAP_FAILED VARCHAR2(1)
HASH_MATCH_FAILED VARCHAR2(1)
PURGED_CURSOR VARCHAR2(1)
BIND_LENGTH_UPGRADEABLE VARCHAR2(1)

Check MySQL storage engine option


mysql> show storage engines;
+------------+---------+---------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+---------+--------------+------+------------+
| InnoDB | YES | | YES | YES | YES |
| MRG_MYISAM | YES | | NO | NO | NO |
| BLACKHOLE | YES | | NO | NO | NO |
| CSV | YES | | NO | NO | NO |
| MEMORY | YES | | NO | NO | NO |
| FEDERATED | NO | | NULL | NULL | NULL |
| ARCHIVE | YES | | NO | NO | NO |
| MyISAM | DEFAULT | | NO | NO | NO |
+------------+---------+---------+--------------+------+------------+
8 rows in set (0.00 sec)

Monday, August 17, 2009

Use trace 10132 to capture SQL execution plan

About trace 10132

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)

Saturday, August 1, 2009

How to: enable oracle sql trace part 2 - other session

Method 1: using dbms_support package


SQL> @?/rdbms/admin/dbmssupp.sql
Package created.
Package body created.

SQL> begin
2 dbms_support.start_trace_in_session(
3 sid=>159,
4 serial=>16,
5 waits=>true,
6 binds=>true);
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_support.stop_trace_in_session(
3 sid=>159,
4 serial=>16);
5 end;
6 /
PL/SQL procedure successfully completed.


Method 2: using dbms_system.set_ev to set event 10046
(Important: nm is '', instead of null).


SQL> begin
2 dbms_system.set_ev(
3 si=>159,
4 se=>16,
5 ev=>10046,
6 le=>12,
7 nm=>'');
8 end;
9 /
PL/SQL procedure successfully completed.

SQL> begin
2 dbms_system.set_ev(
3 si=>159,
4 se=>16,
5 ev=>10046,
6 le=>0,
7 nm=>'');
8 end;
9 /
PL/SQL procedure successfully completed.


Method 3: using dbms_system package


SQL> begin
2 dbms_system.set_sql_trace_in_session(
3 sid=>159,
4 serial#=>16,
5 sql_trace=>true);
6 end;
7 /
PL/SQL procedure successfully completed.


SQL> begin
2 dbms_system.set_sql_trace_in_session(
3 sid=>159,
4 serial#=>16,
5 sql_trace=>false);
6 end;
7 /
PL/SQL procedure successfully completed.


Method 4: using dbms_monitor package (10g onwards)


SQL> begin
2 dbms_monitor.session_trace_enable(
3 session_id=>159,
4 serial_num=>16,
5 waits=>true,
6 binds=>true);
7 end;
8 /
PL/SQL procedure successfully completed.

SQL> begin
2 dbms_monitor.session_trace_disable(
3 session_id=>159,
4 serial_num=>16);
5 end;
6 /
PL/SQL procedure successfully completed.


Method 5: using oradebug to turn on event 10046


SQL> oradebug setospid 9718
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL> oradebug event 10046 trace name context off
Statement processed.