I recently troubleshooted an issue with RMAN catalog database using seperate schemas for each target database and more than 300 schemas present. Catalog Database was on 12.2.0.1 and suffering from severe mutex contention because of huge version counts for RMAN catalog SQL statements. I then realized that the default number of max child cursors was increased from 1024 to 8192 in 12.2.0.1. This made me check what other parameters were changed. In my opinion, especially interesting are parameters, that were OFF / FALSE in 12.1.0.2 and ON / TRUE in 12.2.0.1 as this probably activates new code.
This SQL Statement shows all parameters, including hidden parameters:
1
2
3
4
5
6
7
8
9
| -- file pd2.sql
SET LINES 3000 pages 0 feedback off trimspool ON
SELECT
n.ksppinm ||'|'||
c.ksppstvl ||'|'||
n.ksppdesc
FROM sys.x$ksppi n, sys.x$ksppcv c
WHERE n.indx=c.indx
; |
-- file pd2.sql
set lines 3000 pages 0 feedback off trimspool on
select
n.ksppinm ||'|'||
c.ksppstvl ||'|'||
n.ksppdesc
from sys.x$ksppi n, sys.x$ksppcv c
where n.indx=c.indx
;
Then, I spooled Pipe-seperated output to text files for both releases:
1
2
3
4
5
6
7
8
9
10
11
| . ~/121.env
sqlplus / as sysdba <<EOF
spool /tmp/121.txt
@/tmp/pd2.sql
EOF
. ~/122.env
sqlplus / as sysdba <<EOF
spool /tmp/122.txt
@/tmp/pd2.sql
EOF |
. ~/121.env
sqlplus / as sysdba <<EOF
spool /tmp/121.txt
@/tmp/pd2.sql
EOF
. ~/122.env
sqlplus / as sysdba <<EOF
spool /tmp/122.txt
@/tmp/pd2.sql
EOF
Finally, I set up External Tables for accessing these files with SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
| sqlplus / as sysdba
drop table db122 purge;
drop table db121 purge;
CREATE TABLE db122 (
param VARCHAR2(2000),
param_val VARCHAR2(2000),
param_desc VARCHAR2(2000)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY tmp_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
(
param CHAR(2000) ,
param_val CHAR(2000),
param_desc CHAR(2000)
)
)
LOCATION ('122.txt')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;
CREATE TABLE db121 (
param VARCHAR2(2000),
param_val VARCHAR2(2000),
param_desc VARCHAR2(2000)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY tmp_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
(
param CHAR(2000) ,
param_val CHAR(2000),
param_desc CHAR(2000)
)
)
LOCATION ('121.txt')
)
PARALLEL 1
REJECT LIMIT UNLIMITED; |
sqlplus / as sysdba
drop table db122 purge;
drop table db121 purge;
CREATE TABLE db122 (
param VARCHAR2(2000),
param_val VARCHAR2(2000),
param_desc VARCHAR2(2000)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY tmp_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
(
param CHAR(2000) ,
param_val CHAR(2000),
param_desc CHAR(2000)
)
)
LOCATION ('122.txt')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;
CREATE TABLE db121 (
param VARCHAR2(2000),
param_val VARCHAR2(2000),
param_desc VARCHAR2(2000)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY tmp_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
(
param CHAR(2000) ,
param_val CHAR(2000),
param_desc CHAR(2000)
)
)
LOCATION ('121.txt')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;
The final query now compares hidden parameters:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
| SET LINES 500 pages 2000 tab off
col param format a56
col db121_value format a20
col db122_value format a20
col param_desc heading DESCRIPTION format a100 word_wrap
SELECT db121.param, db121.param_val AS db121_value, db122.param_val AS db122_value, db122.param_desc FROM
(SELECT param, param_val, param_desc FROM db121) db121,
(SELECT param, param_val, param_desc FROM db122) db122
WHERE db121.param = db122.param AND db121.param_val != db122.param_val AND db121.param LIKE '\_%' escape '\'
and db121.param not IN (
'__db_cache_size',
'__pga_aggregate_target',
'__pga_aggregate_target',
'__shared_pool_size',
'__streams_pool_size',
'_diag_adr_trace_dest',
'__large_pool_size')
order by db121.param;
PARAM DB121_VALUE DB122_VALUE DESCRIPTION
-------------------------------------------------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------
_adaptive_scalable_log_writer_disable_worker_threshold 90 50 Percentage of overlap across multiple outstanding writes
_advanced_index_compression_options_value 20 0 advanced index compression options2
_aq_latency_absolute_threshold 100 300 Absolute threshold greater than average latency
_aq_latency_relative_threshold 30 100 Relative threshold of average latency
_aq_lb_stats_collect_cycle 120 45 Time(seconds) between consecutive AQ load statistics collection
_aq_lookback_size 30 60 AQ PT Look Back Size
_aq_pt_shrink_frequency 30 1450 PT shrink window Size
_aq_pt_statistics_window 30 60 PT statistics sample window Size
_asm_compatibility 10.1 11.2.0.2 default ASM compatibility level
_asm_max_cod_strides 5 10 maximum number of COD strides
_asm_scrub_unmatched_dba 3 1024 Scrub maximum number of blocks with unmatched DBA
_asm_xrov_nvios 4 8 Specify number of VIO processes
_asm_xrov_single TRUE FALSE Enable single issues of IOs
_bigdata_external_table FALSE TRUE enables use of ORACLE_HIVE and ORACLE_HDFS access drivers
_cache_fusion_pipelined_updates FALSE TRUE Block ping without wait for log force
_catalog_foreign_restore TRUE FALSE catalog foreign file restore
_column_tracking_level 1 21 column usage tracking
_compression_compatibility 12.1.0.2.0 12.2.0 Compression compatibility
_controlfile_enqueue_holding_time_tracking_size 10 0 control file enqueue holding time tracking size
_cursor_db_buffers_pinned 1387 1375 additional number of buffers a cursor can pin at once
_cursor_obsolete_threshold 1024 8192 Number of cursors per parent before obsoletion.
_db_block_buffers 444542 440775 Number of database blocks cached in memory: hidden parameter
_db_block_cache_protect FALSE false protect database blocks (for debugging only)
_dbg_scan 0 2048 generic scan debug
_dbrm_dynamic_threshold 33490396 989922280 DBRM dynamic threshold setting
_dbrm_runchk 0 32769000 Resource Manager Diagnostic Running Thread Check
_dbwr_stall_write_detection_interval 0 900 dbwriter stall write detection interval
_enable_pdb_close_abort FALSE TRUE Enable PDB shutdown abort (close abort)
_gc_async_send FALSE TRUE if TRUE, send blocks asynchronously
_gc_element_percent 120 105 global cache element percent
_gc_latches 8 32 number of latches per LMS process
_gc_max_downcvt 256 1024 maximum downconverts to process at one time
_gc_msgq_buffers 128 0 set number of MSGQ buffers
_gc_policy_time 10 20 how often to make object policy decisions in minutes
_gc_trace_freelist_empty TRUE FALSE if TRUE, dump a trace when we run out of lock elements
_gc_transfer_ratio 2 75 dynamic object read-mostly transfer ratio
_gcs_latches 0 128 number of gcs resource hash latches to be allocated per LMS process
_hang_base_file_space_limit 20000000 100000000 File space limit for current normal base trace file
_hang_int_spare2 FALSE 0 Hang Management int 2
_hang_lws_file_space_limit 20000000 100000000 File space limit for current long waiting session trace file
_hang_resolution_scope PROCESS OFF Hang Management hang resolution scope
_hang_terminate_session_replay_enabled FALSE TRUE Hang Management terminates sessions allowing replay
_ilmstat_memlimit 10 5 Percentage of shared pool for use by ILM Statistics
_inmemory_fs_blk_inv_blk_percent 20 50 in-memory faststart CU invalidation threshold(blocks)
_inmemory_fs_enable FALSE TRUE in-memory faststart enable
_inmemory_imcu_target_rows 1048576 0 IMCU target number of rows
_inmemory_private_journal_maxexts 32000 5000 Max number of extents per PJ
_inmemory_private_journal_sharedpool_quota 64 20 quota for transaction in-memory objects
_inmemory_repopulate_invalidate_rate_percent 100 0 In-memory repopulate invalidate rate percent
_inmemory_repopulate_threshold_scans 2048 0 In-memory repopulate threshold number of scans
_inmemory_strdlxid_timeout 327680 0 max time to determine straddling transactions
_ipddb_enable TRUE FALSE Enable IPD/DB data collection
_kebm_suspension_time 82800 104400 kebm auto suspension time in seconds
_kgh_restricted_subheaps 60 180 number of subheaps in heap restricted mode
_kgl_message_locks 64 256 RAC message lock count
_kqr_optimistic_reads FALSE TRUE optimistic reading of row cache objects
_kse_snap_ring_suppress 608 1403 6510 942 1403 List of error numbers to suppress in the snap error history
_ksi_clientlocks_enabled TRUE FALSE if TRUE, DLM-clients can provide the lock memory
_ksxp_ipclw_enabled 0 1 enable ipclw for KSXP
_ktb_debug_flags 0 8 ktb-layer debug flags
_lm_comm_channel ksxp msgq GES communication channel type
_lm_comm_tkts_calc_period_length 5 1000 Weighted average calculation interval length (us)
_lm_drm_min_interval 300 600 minimum interval in secs between two consecutive drms
_lm_drmopt12 26 56 enable drm scan optimizations in 12
_lm_enable_aff_benefit_stats TRUE FALSE enables affinity benefit computations if TRUE
_lm_procs 320 1088 number of client processes configured for cluster database
_lm_xids 352 1196 number of transaction IDs configured for cluster database
_lock_next_constraint_count 120 3 max number of attempts to lock _NEXT_CONSTRAINT
_lthread_cleanup_intv_secs 5 1 interval for cleaning lightweight threads in secs
_max_fsu_segments 4096 1024 Maximum segments to track for fast space usage
_max_services 1024 8200 maximum number of database services
_multi_instance_pmr FALSE TRUE enable multi instance redo apply
_multiple_char_set_cdb FALSE TRUE Multiple character sets enabled in CDB
_number_cached_group_memberships 32 2048 maximum number of cached group memberships
_number_group_memberships_per_cache_line 3 6 maximum number of group memberships per cache line
_optimizer_ads_use_partial_results FALSE TRUE Use partial results of ADS queries
_optimizer_dsdir_usage_control 126 0 controls optimizer usage of dynamic sampling directives
_optimizer_undo_cost_change 12.1.0.2 12.2.0.1 optimizer undo cost change
_parallel_server_sleep_time 10 1 sleep time between dequeue timeouts (in 1/100ths)
_pmon_dead_blkrs_max_blkrs 200 50 max blockers to check during cleanup
_posix_spawn_enabled FALSE TRUE posix_spawn enabled
_rcfg_parallel_verify TRUE FALSE if TRUE enables parallel verify at reconfiguration
_reliable_block_sends TRUE FALSE if TRUE, no side channel on reliable interconnect
_rowsets_max_rows 200 256 maximum number of rows in a rowset
_side_channel_batch_size 200 240 number of messages to batch in a side channel message (DFS)
_skip_trstamp_check FALSE TRUE Skip terminal recovery stamp check
_small_table_threshold 8890 8815 lower threshold level of table size for direct reads
_sql_plan_directive_mgmt_control 3 67 controls internal SQL Plan Directive management activities
_step_down_limit_in_pct 1 20 step down limit in percentage
_target_log_write_size_timeout 1 0 How long LGWR will wait for redo to accumulate (msecs)
90 rows selected. |
set lines 500 pages 2000 tab off
col param format a56
col db121_value format a20
col db122_value format a20
col param_desc heading DESCRIPTION format a100 word_wrap
select db121.param, db121.param_val as db121_value, db122.param_val as db122_value, db122.param_desc from
(select param, param_val, param_desc from db121) db121,
(select param, param_val, param_desc from db122) db122
where db121.param = db122.param and db121.param_val != db122.param_val and db121.param like '\_%' escape '\'
and db121.param not IN (
'__db_cache_size',
'__pga_aggregate_target',
'__pga_aggregate_target',
'__shared_pool_size',
'__streams_pool_size',
'_diag_adr_trace_dest',
'__large_pool_size')
order by db121.param;
PARAM DB121_VALUE DB122_VALUE DESCRIPTION
-------------------------------------------------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------
_adaptive_scalable_log_writer_disable_worker_threshold 90 50 Percentage of overlap across multiple outstanding writes
_advanced_index_compression_options_value 20 0 advanced index compression options2
_aq_latency_absolute_threshold 100 300 Absolute threshold greater than average latency
_aq_latency_relative_threshold 30 100 Relative threshold of average latency
_aq_lb_stats_collect_cycle 120 45 Time(seconds) between consecutive AQ load statistics collection
_aq_lookback_size 30 60 AQ PT Look Back Size
_aq_pt_shrink_frequency 30 1450 PT shrink window Size
_aq_pt_statistics_window 30 60 PT statistics sample window Size
_asm_compatibility 10.1 11.2.0.2 default ASM compatibility level
_asm_max_cod_strides 5 10 maximum number of COD strides
_asm_scrub_unmatched_dba 3 1024 Scrub maximum number of blocks with unmatched DBA
_asm_xrov_nvios 4 8 Specify number of VIO processes
_asm_xrov_single TRUE FALSE Enable single issues of IOs
_bigdata_external_table FALSE TRUE enables use of ORACLE_HIVE and ORACLE_HDFS access drivers
_cache_fusion_pipelined_updates FALSE TRUE Block ping without wait for log force
_catalog_foreign_restore TRUE FALSE catalog foreign file restore
_column_tracking_level 1 21 column usage tracking
_compression_compatibility 12.1.0.2.0 12.2.0 Compression compatibility
_controlfile_enqueue_holding_time_tracking_size 10 0 control file enqueue holding time tracking size
_cursor_db_buffers_pinned 1387 1375 additional number of buffers a cursor can pin at once
_cursor_obsolete_threshold 1024 8192 Number of cursors per parent before obsoletion.
_db_block_buffers 444542 440775 Number of database blocks cached in memory: hidden parameter
_db_block_cache_protect FALSE false protect database blocks (for debugging only)
_dbg_scan 0 2048 generic scan debug
_dbrm_dynamic_threshold 33490396 989922280 DBRM dynamic threshold setting
_dbrm_runchk 0 32769000 Resource Manager Diagnostic Running Thread Check
_dbwr_stall_write_detection_interval 0 900 dbwriter stall write detection interval
_enable_pdb_close_abort FALSE TRUE Enable PDB shutdown abort (close abort)
_gc_async_send FALSE TRUE if TRUE, send blocks asynchronously
_gc_element_percent 120 105 global cache element percent
_gc_latches 8 32 number of latches per LMS process
_gc_max_downcvt 256 1024 maximum downconverts to process at one time
_gc_msgq_buffers 128 0 set number of MSGQ buffers
_gc_policy_time 10 20 how often to make object policy decisions in minutes
_gc_trace_freelist_empty TRUE FALSE if TRUE, dump a trace when we run out of lock elements
_gc_transfer_ratio 2 75 dynamic object read-mostly transfer ratio
_gcs_latches 0 128 number of gcs resource hash latches to be allocated per LMS process
_hang_base_file_space_limit 20000000 100000000 File space limit for current normal base trace file
_hang_int_spare2 FALSE 0 Hang Management int 2
_hang_lws_file_space_limit 20000000 100000000 File space limit for current long waiting session trace file
_hang_resolution_scope PROCESS OFF Hang Management hang resolution scope
_hang_terminate_session_replay_enabled FALSE TRUE Hang Management terminates sessions allowing replay
_ilmstat_memlimit 10 5 Percentage of shared pool for use by ILM Statistics
_inmemory_fs_blk_inv_blk_percent 20 50 in-memory faststart CU invalidation threshold(blocks)
_inmemory_fs_enable FALSE TRUE in-memory faststart enable
_inmemory_imcu_target_rows 1048576 0 IMCU target number of rows
_inmemory_private_journal_maxexts 32000 5000 Max number of extents per PJ
_inmemory_private_journal_sharedpool_quota 64 20 quota for transaction in-memory objects
_inmemory_repopulate_invalidate_rate_percent 100 0 In-memory repopulate invalidate rate percent
_inmemory_repopulate_threshold_scans 2048 0 In-memory repopulate threshold number of scans
_inmemory_strdlxid_timeout 327680 0 max time to determine straddling transactions
_ipddb_enable TRUE FALSE Enable IPD/DB data collection
_kebm_suspension_time 82800 104400 kebm auto suspension time in seconds
_kgh_restricted_subheaps 60 180 number of subheaps in heap restricted mode
_kgl_message_locks 64 256 RAC message lock count
_kqr_optimistic_reads FALSE TRUE optimistic reading of row cache objects
_kse_snap_ring_suppress 608 1403 6510 942 1403 List of error numbers to suppress in the snap error history
_ksi_clientlocks_enabled TRUE FALSE if TRUE, DLM-clients can provide the lock memory
_ksxp_ipclw_enabled 0 1 enable ipclw for KSXP
_ktb_debug_flags 0 8 ktb-layer debug flags
_lm_comm_channel ksxp msgq GES communication channel type
_lm_comm_tkts_calc_period_length 5 1000 Weighted average calculation interval length (us)
_lm_drm_min_interval 300 600 minimum interval in secs between two consecutive drms
_lm_drmopt12 26 56 enable drm scan optimizations in 12
_lm_enable_aff_benefit_stats TRUE FALSE enables affinity benefit computations if TRUE
_lm_procs 320 1088 number of client processes configured for cluster database
_lm_xids 352 1196 number of transaction IDs configured for cluster database
_lock_next_constraint_count 120 3 max number of attempts to lock _NEXT_CONSTRAINT
_lthread_cleanup_intv_secs 5 1 interval for cleaning lightweight threads in secs
_max_fsu_segments 4096 1024 Maximum segments to track for fast space usage
_max_services 1024 8200 maximum number of database services
_multi_instance_pmr FALSE TRUE enable multi instance redo apply
_multiple_char_set_cdb FALSE TRUE Multiple character sets enabled in CDB
_number_cached_group_memberships 32 2048 maximum number of cached group memberships
_number_group_memberships_per_cache_line 3 6 maximum number of group memberships per cache line
_optimizer_ads_use_partial_results FALSE TRUE Use partial results of ADS queries
_optimizer_dsdir_usage_control 126 0 controls optimizer usage of dynamic sampling directives
_optimizer_undo_cost_change 12.1.0.2 12.2.0.1 optimizer undo cost change
_parallel_server_sleep_time 10 1 sleep time between dequeue timeouts (in 1/100ths)
_pmon_dead_blkrs_max_blkrs 200 50 max blockers to check during cleanup
_posix_spawn_enabled FALSE TRUE posix_spawn enabled
_rcfg_parallel_verify TRUE FALSE if TRUE enables parallel verify at reconfiguration
_reliable_block_sends TRUE FALSE if TRUE, no side channel on reliable interconnect
_rowsets_max_rows 200 256 maximum number of rows in a rowset
_side_channel_batch_size 200 240 number of messages to batch in a side channel message (DFS)
_skip_trstamp_check FALSE TRUE Skip terminal recovery stamp check
_small_table_threshold 8890 8815 lower threshold level of table size for direct reads
_sql_plan_directive_mgmt_control 3 67 controls internal SQL Plan Directive management activities
_step_down_limit_in_pct 1 20 step down limit in percentage
_target_log_write_size_timeout 1 0 How long LGWR will wait for redo to accumulate (msecs)
90 rows selected.
