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.<\/p>\n
This SQL Statement shows all parameters, including hidden parameters:<\/p>\n
\r\n-- file pd2.sql\r\nset lines 3000 pages 0 feedback off trimspool on\r\nselect\r\n n.ksppinm ||'|'||\r\n c.ksppstvl ||'|'||\r\n n.ksppdesc\r\nfrom sys.x$ksppi n, sys.x$ksppcv c\r\nwhere n.indx=c.indx\r\n;\r\n<\/pre>\nThen, I spooled Pipe-seperated output to text files for both releases:<\/p>\n
\r\n. ~\/121.env\r\nsqlplus \/ as sysdba <\nFinally, I set up External Tables for accessing these files with SQL:<\/p>\n
\r\nsqlplus \/ as sysdba\r\n\r\ndrop table db122 purge;\r\ndrop table db121 purge;\r\n\r\nCREATE TABLE db122 (\r\n param VARCHAR2(2000),\r\n param_val VARCHAR2(2000),\r\n param_desc VARCHAR2(2000)\r\n)\r\nORGANIZATION EXTERNAL (\r\n TYPE ORACLE_LOADER\r\n DEFAULT DIRECTORY tmp_dir\r\n ACCESS PARAMETERS (\r\n RECORDS DELIMITED BY NEWLINE\r\n FIELDS TERMINATED BY '|'\r\n MISSING FIELD VALUES ARE NULL\r\n (\r\n param CHAR(2000) , \r\n param_val CHAR(2000), \r\n param_desc CHAR(2000)\r\n )\r\n )\r\n LOCATION ('122.txt')\r\n)\r\nPARALLEL 1\r\nREJECT LIMIT UNLIMITED;\r\n\r\nCREATE TABLE db121 (\r\n param VARCHAR2(2000),\r\n param_val VARCHAR2(2000),\r\n param_desc VARCHAR2(2000)\r\n)\r\nORGANIZATION EXTERNAL (\r\n TYPE ORACLE_LOADER\r\n DEFAULT DIRECTORY tmp_dir\r\n ACCESS PARAMETERS (\r\n RECORDS DELIMITED BY NEWLINE\r\n FIELDS TERMINATED BY '|'\r\n MISSING FIELD VALUES ARE NULL\r\n (\r\n param CHAR(2000) , \r\n param_val CHAR(2000), \r\n param_desc CHAR(2000)\r\n )\r\n )\r\n LOCATION ('121.txt')\r\n)\r\nPARALLEL 1\r\nREJECT LIMIT UNLIMITED;\r\n<\/pre>\nThe final query now compares hidden parameters:<\/p>\n
\r\nset lines 500 pages 2000 tab off\r\ncol param format a56\r\ncol db121_value format a20\r\ncol db122_value format a20\r\ncol param_desc heading DESCRIPTION format a100 word_wrap\r\nselect db121.param, db121.param_val as db121_value, db122.param_val as db122_value, db122.param_desc from \r\n(select param, param_val, param_desc from db121) db121,\r\n(select param, param_val, param_desc from db122) db122\r\nwhere db121.param = db122.param and db121.param_val != db122.param_val and db121.param like '\\_%' escape '\\'\r\nand db121.param not IN (\r\n '__db_cache_size',\r\n '__pga_aggregate_target',\r\n '__pga_aggregate_target',\r\n '__shared_pool_size',\r\n '__streams_pool_size',\r\n '_diag_adr_trace_dest',\r\n '__large_pool_size')\r\norder by db121.param; \r\n\r\n\r\nPARAM DB121_VALUE DB122_VALUE DESCRIPTION\r\n-------------------------------------------------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------\r\n_adaptive_scalable_log_writer_disable_worker_threshold 90 50 Percentage of overlap across multiple outstanding writes\r\n_advanced_index_compression_options_value 20 0 advanced index compression options2\r\n_aq_latency_absolute_threshold 100 300 Absolute threshold greater than average latency\r\n_aq_latency_relative_threshold 30 100 Relative threshold of average latency\r\n_aq_lb_stats_collect_cycle 120 45 Time(seconds) between consecutive AQ load statistics collection\r\n_aq_lookback_size 30 60 AQ PT Look Back Size\r\n_aq_pt_shrink_frequency 30 1450 PT shrink window Size\r\n_aq_pt_statistics_window 30 60 PT statistics sample window Size\r\n_asm_compatibility 10.1 11.2.0.2 default ASM compatibility level\r\n_asm_max_cod_strides 5 10 maximum number of COD strides\r\n_asm_scrub_unmatched_dba 3 1024 Scrub maximum number of blocks with unmatched DBA\r\n_asm_xrov_nvios 4 8 Specify number of VIO processes\r\n_asm_xrov_single TRUE FALSE Enable single issues of IOs\r\n_bigdata_external_table FALSE TRUE enables use of ORACLE_HIVE and ORACLE_HDFS access drivers\r\n_cache_fusion_pipelined_updates FALSE TRUE Block ping without wait for log force\r\n_catalog_foreign_restore TRUE FALSE catalog foreign file restore\r\n_column_tracking_level 1 21 column usage tracking\r\n_compression_compatibility 12.1.0.2.0 12.2.0 Compression compatibility\r\n_controlfile_enqueue_holding_time_tracking_size 10 0 control file enqueue holding time tracking size\r\n_cursor_db_buffers_pinned 1387 1375 additional number of buffers a cursor can pin at once\r\n_cursor_obsolete_threshold 1024 8192 Number of cursors per parent before obsoletion.\r\n_db_block_buffers 444542 440775 Number of database blocks cached in memory: hidden parameter\r\n_db_block_cache_protect FALSE false protect database blocks (for debugging only)\r\n_dbg_scan 0 2048 generic scan debug\r\n_dbrm_dynamic_threshold 33490396 989922280 DBRM dynamic threshold setting\r\n_dbrm_runchk 0 32769000 Resource Manager Diagnostic Running Thread Check\r\n_dbwr_stall_write_detection_interval 0 900 dbwriter stall write detection interval\r\n_enable_pdb_close_abort FALSE TRUE Enable PDB shutdown abort (close abort)\r\n_gc_async_send FALSE TRUE if TRUE, send blocks asynchronously\r\n_gc_element_percent 120 105 global cache element percent\r\n_gc_latches 8 32 number of latches per LMS process\r\n_gc_max_downcvt 256 1024 maximum downconverts to process at one time\r\n_gc_msgq_buffers 128 0 set number of MSGQ buffers\r\n_gc_policy_time 10 20 how often to make object policy decisions in minutes\r\n_gc_trace_freelist_empty TRUE FALSE if TRUE, dump a trace when we run out of lock elements\r\n_gc_transfer_ratio 2 75 dynamic object read-mostly transfer ratio\r\n_gcs_latches 0 128 number of gcs resource hash latches to be allocated per LMS process\r\n_hang_base_file_space_limit 20000000 100000000 File space limit for current normal base trace file\r\n_hang_int_spare2 FALSE 0 Hang Management int 2\r\n_hang_lws_file_space_limit 20000000 100000000 File space limit for current long waiting session trace file\r\n_hang_resolution_scope PROCESS OFF Hang Management hang resolution scope\r\n_hang_terminate_session_replay_enabled FALSE TRUE Hang Management terminates sessions allowing replay\r\n_ilmstat_memlimit 10 5 Percentage of shared pool for use by ILM Statistics\r\n_inmemory_fs_blk_inv_blk_percent 20 50 in-memory faststart CU invalidation threshold(blocks)\r\n_inmemory_fs_enable FALSE TRUE in-memory faststart enable\r\n_inmemory_imcu_target_rows 1048576 0 IMCU target number of rows\r\n_inmemory_private_journal_maxexts 32000 5000 Max number of extents per PJ\r\n_inmemory_private_journal_sharedpool_quota 64 20 quota for transaction in-memory objects\r\n_inmemory_repopulate_invalidate_rate_percent 100 0 In-memory repopulate invalidate rate percent\r\n_inmemory_repopulate_threshold_scans 2048 0 In-memory repopulate threshold number of scans\r\n_inmemory_strdlxid_timeout 327680 0 max time to determine straddling transactions\r\n_ipddb_enable TRUE FALSE Enable IPD\/DB data collection\r\n_kebm_suspension_time 82800 104400 kebm auto suspension time in seconds\r\n_kgh_restricted_subheaps 60 180 number of subheaps in heap restricted mode\r\n_kgl_message_locks 64 256 RAC message lock count\r\n_kqr_optimistic_reads FALSE TRUE optimistic reading of row cache objects\r\n_kse_snap_ring_suppress 608 1403 6510 942 1403 List of error numbers to suppress in the snap error history\r\n_ksi_clientlocks_enabled TRUE FALSE if TRUE, DLM-clients can provide the lock memory\r\n_ksxp_ipclw_enabled 0 1 enable ipclw for KSXP\r\n_ktb_debug_flags 0 8 ktb-layer debug flags\r\n_lm_comm_channel ksxp msgq GES communication channel type\r\n_lm_comm_tkts_calc_period_length 5 1000 Weighted average calculation interval length (us)\r\n_lm_drm_min_interval 300 600 minimum interval in secs between two consecutive drms\r\n_lm_drmopt12 26 56 enable drm scan optimizations in 12\r\n_lm_enable_aff_benefit_stats TRUE FALSE enables affinity benefit computations if TRUE\r\n_lm_procs 320 1088 number of client processes configured for cluster database\r\n_lm_xids 352 1196 number of transaction IDs configured for cluster database\r\n_lock_next_constraint_count 120 3 max number of attempts to lock _NEXT_CONSTRAINT\r\n_lthread_cleanup_intv_secs 5 1 interval for cleaning lightweight threads in secs\r\n_max_fsu_segments 4096 1024 Maximum segments to track for fast space usage\r\n_max_services 1024 8200 maximum number of database services\r\n_multi_instance_pmr FALSE TRUE enable multi instance redo apply\r\n_multiple_char_set_cdb FALSE TRUE Multiple character sets enabled in CDB\r\n_number_cached_group_memberships 32 2048 maximum number of cached group memberships\r\n_number_group_memberships_per_cache_line 3 6 maximum number of group memberships per cache line\r\n_optimizer_ads_use_partial_results FALSE TRUE Use partial results of ADS queries\r\n_optimizer_dsdir_usage_control 126 0 controls optimizer usage of dynamic sampling directives\r\n_optimizer_undo_cost_change 12.1.0.2 12.2.0.1 optimizer undo cost change\r\n_parallel_server_sleep_time 10 1 sleep time between dequeue timeouts (in 1\/100ths)\r\n_pmon_dead_blkrs_max_blkrs 200 50 max blockers to check during cleanup\r\n_posix_spawn_enabled FALSE TRUE posix_spawn enabled\r\n_rcfg_parallel_verify TRUE FALSE if TRUE enables parallel verify at reconfiguration\r\n_reliable_block_sends TRUE FALSE if TRUE, no side channel on reliable interconnect\r\n_rowsets_max_rows 200 256 maximum number of rows in a rowset\r\n_side_channel_batch_size 200 240 number of messages to batch in a side channel message (DFS)\r\n_skip_trstamp_check FALSE TRUE Skip terminal recovery stamp check\r\n_small_table_threshold 8890 8815 lower threshold level of table size for direct reads\r\n_sql_plan_directive_mgmt_control 3 67 controls internal SQL Plan Directive management activities\r\n_step_down_limit_in_pct 1 20 step down limit in percentage\r\n_target_log_write_size_timeout 1 0 How long LGWR will wait for redo to accumulate (msecs)\r\n\r\n90 rows selected.\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[58,61,5,1],"tags":[],"class_list":["post-1518","post","type-post","status-publish","format-standard","hentry","category-12c","category-12cr2","category-oracle-database","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=1518"}],"version-history":[{"count":2,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1518\/revisions"}],"predecessor-version":[{"id":1520,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1518\/revisions\/1520"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=1518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=1518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=1518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}