Modifications to hidden parameters between 12.1.0.2 and 12.2.0.1
By Martin | April 26th, 2018 | Category: 12cR1, 12cR2, Oracle Database, Uncategorized | No Comments »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 ; |
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 |
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; |
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. |
