隐藏参数概览

最新推荐文章于 2023-09-18 19:14:34 发布

cuian7084

最新推荐文章于 2023-09-18 19:14:34 发布

阅读量992

点赞数

文章标签：数据库

大家都知道,Oracle数据库存在很多隐藏参数.这些参数在特定情况下使用,会收到特别的效果：
*启动/关闭指定特性，如_addm_auto_enable
*启动/关闭特定跟踪，如_trace_pin_time
*修改缺省参数数值,如_spin_count，_db_block_hash_buckets
*允许部分数据不一致,以便恢复，如_corrupted_rollback_segments
几乎所有特性,模块,进程都有对应的参数,浏览一下就会对部分特性有更深刻的理解.如需使用，需要留意每个隐藏参数的副作用。

--1,版本
select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

--2,所有隐藏参数
SELECT x.indx||'|'||x.ksppinm||'|'||x.ksppdesc||'|'||y.ksppstvl||'|'||y.ksppstdvl||'|'||y.ksppstdf
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx
AND ((translate(x.ksppinm, '_', '#') LIKE '##%')
OR (translate(x.ksppinm, '_', '#') LIKE '#%'))
ORDER BY x.indx;
总共1127个

--设置隐藏参数:
alter system set "_trace_pin_time"=1 scope=spfile;
--清空隐藏参数,sid参数是必输项:
alter system reset "_trace_pin_time" scope=spfile sid='*';

--3,部分参数

--跟踪文件属性
1 _trace_files_public | Create publicly accessible trace files | FALSE | FALSE | TRUE

--latch
6 _spin_count | Amount to spin waiting for a latch | 2000 | 2000 | TRUE
8 _max_sleep_holding_latch | max time to sleep while holding a latch | 4 | 4 | TRUE
9 _max_exponential_sleep | max sleep during exponential backoff | 0 | 0 | TRUE

--check oracle image
27 _disable_image_check | Disable Oracle executable image checking | FALSE | FALSE | TRUE

--longops
29 _longops_enabled | longops stats enabled | TRUE | TRUE | TRUE

--os priority
31 _os_sched_high_priority | OS high priority level | 1 | 1 | TRUE

--system state
33 _disable_system_state | disable system state dump | 4294967294 | 4294967294 | TRUE

--v$session_wait_history
34 _session_wait_history | enable session wait history collection | 10 | 10 | TRUE

--Resource Manager
35 _session_idle_check_interval | Resource Manager session idle limit check interval in seconds | 60 | 60 | TRUE

--sga
58 _lock_sga_areas | Lock specified areas of the SGA in physical memory | 0 | 0 | TRUE

--4031,sga分配
83 _4031_dump_bitvec | bitvec to specify dumps prior to 4031 error | 67194879 | 67194879 | TRUE
84 _4031_max_dumps | Maximum number of 4031 dumps for this process | 100 | 100 | TRUE
85 _4031_dump_interval | Dump 4031 error once for each n-second interval | 300 | 300 | TRUE
86 _4031_sga_dump_interval | Dump 4031 SGA heapdump error once for each n-second interval | 3600 | 3600 | TRUE
87 _4031_sga_max_dumps | Maximum number of SGA heapdumps | 10 | 10 | TRUE

--large pool
93 _large_pool_min_alloc | minimum allocation size in bytes for the large allocation pool | 16000 | 16000 | TRUE

--shared pool
94 _shared_pool_reserved_pct | percentage memory of the shared pool allocated for the reserved area | 5 | 5 | TRUE
95 _shared_pool_reserved_min_alloc | minimum allocation size in bytes for reserved area of shared pool | 4400 | 4400 | TRUE

106 _kse_die_timeout | amount of time a dying process is spared by PMON (in centi-secs) | 60000 | 60000 | TRUE

--lock
110 _enqueue_locks | locks for managed enqueues | 4330 | 4330 | TRUE
111 _enqueue_resources | resources for enqueues | 1692 | 1692 | TRUE
112 _enqueue_hash | enqueue hash table length | 705 | 705 | TRUE
114 _enqueue_hash_chain_latches | enqueue hash chain latches | 4 | 4 | TRUE

--omf
129 _omf | enable/disable OMF | enabled | enabled | TRUE

--io
130 _aiowait_timeouts | Number of aiowait timeouts before error is reported | 100 | 100 | TRUE
131 _io_shared_pool_size | Size of I/O buffer pool from SGA | 4194304 | 4M | TRUE
132 _db_file_direct_io_count | Sequential I/O buf size | 1048576 | 1048576 | TRUE
133 _ioslave_issue_count | IOs issued before completion check | 500 | 500 | TRUE
134 _ioslave_batch_count | Per attempt IOs picked | 1 | 1 | TRUE
135 _io_slaves_disabled | Do not use I/O slaves | FALSE | FALSE | TRUE
136 _lgwr_io_slaves | LGWR I/O slaves | 0 | 0 | TRUE
137 _arch_io_slaves | ARCH I/O slaves | 0 | 0 | TRUE
138 _backup_disk_io_slaves | BACKUP Disk I/O slaves | 0 | 0 | TRUE
139 _backup_io_pool_size | memory to reserve from the large pool | 1048576 | 1048576 | TRUE

--skgxp
153 _skgxp_reaping | tune skgxp OSD reaping limit | 1000 | 1000 | TRUE
154 _skgxp_udp_hiwat_warn | ach hiwat mark warning interval | 1000 | 1000 | TRUE
155 _skgxp_udp_ach_reaping_time | time in minutes before idle ach's are reaped | 120 | 120 | TRUE
156 _skgxp_udp_timed_wait_seconds | time in seconds before timed wait is invoked | 5 | 5 | TRUE
157 _skgxp_udp_timed_wait_buffering | diagnostic log buffering space (in bytes) for timed wait (0 means unbufferd | 1024 | 1024 | TRUE
158 _skgxp_udp_keep_alive_ping_timer_secs | connection idle time in seconds before keep alive is initiated. min: 30 sec max: 1800 sec default: 300 sec | 300 | 300 | TRUE
162 _skgxp_udp_interface_detection_time_secs | time in seconds between interface detection checks | 60 | 60 | TRUE

168 _filemap_dir | FILEMAP directory | | | TRUE

--memory management
279 _memory_management_tracing | trace memory management activity | 0 | 0 | TRUE
280 _memory_sanity_check | partial granule sanity check | 0 | 0 | TRUE

--cr per dba
346 _db_block_max_cr_dba | Maximum Allowed Number of CR buffers per dba | 6 | 6 | TRUE

--temp block redo
355 _db_block_temp_redo | generate redo for temp blocks | FALSE | FALSE | TRUE

--full mbreads
359 _db_block_do_full_mbreads | do full block read even if some blocks are in cache | FALSE | FALSE | TRUE

--system tablespace
381 _db_always_check_system_ts | Always perform. block check and checksum for System tablespace | TRUE | TRUE | TRUE

--Asyn lgwr
409 _lgwr_async_io | LGWR Asynchronous IO enabling boolean flag | FALSE | FALSE | TRUE

--nologging
426 _disable_logging | Disable logging | FALSE | FALSE | TRUE

449 _interconnect_checksum | if TRUE, checksum interconnect blocks | TRUE | TRUE | TRUE

457 _kcl_undo_locks | number of locks per undo segment | 128 | 128 | TRUE
458 _kcl_undo_grouping | grouping for undo block locks | 32 | 32 | TRUE

--hot backup results in more redo:
476 _log_blocks_during_backup | log block images when changed during backup | TRUE | TRUE | TRUE

--BCT（block change tracking）used for faster incremental backup
502 _bct_public_dba_buffer_size | total size of all public change tracking dba buffers, in bytes | 0 | 0 | TRUE
503 _bct_initial_private_dba_buffer_size | initial number of entries in the private change tracking dba buffers | 0 | 0 | TRUE
504 _bct_bitmaps_per_file | number of bitmaps to store for each datafile | 8 | 8 | TRUE
505 _bct_file_block_size | block size of change tracking file, in bytes | 0 | 0 | TRUE
506 _bct_file_extent_size | extent size of change tracking file, in bytes | 0 | 0 | TRUE
507 _bct_chunk_size | change tracking datafile chunk size, in bytes | 0 | 0 | TRUE
508 _bct_crash_reserve_size | change tracking reserved crash recovery SGA space, in bytes | 262144 | 262144 | TRUE
509 _bct_buffer_allocation_size | size of one change tracking buffer allocation, in bytes | 2097152 | 2097152 | TRUE
510 _bct_buffer_allocation_max | maximum size of all change tracking buffer allocations, in bytes | 104857600 | 104857600 | TRUE
511 _bct_buffer_allocation_min_extents | mininum number of extents to allocate per buffer allocation | 1 | 1 | TRUE
512 _bct_fixtab_file | change tracking file for fixed tables | | | TRUE
513 _block_change_tracking | change tracking possible | TRUE | TRUE | TRUE

--row lock
535 _row_locking | row-locking | always | always | TRUE

--block cleanout
537 _log_committime_block_cleanout | Log commit-time block cleanout | FALSE | FALSE | TRUE

--缺省assm
545 _assm_default | ASSM default | TRUE | TRUE | TRUE

--process seq cache?
589 _use_seq_process_cache | whether to use process local seq cache | TRUE | TRUE | TRUE

--cursor stats
615 _cursor_stats_bucket | Hash table buckets for cursor stats (2^). | 15 | 15 | TRUE
616 _cursor_stats_heap | Heaps/latches for cursor stats. | 4 | 4 | TRUE

--job queue co_ordinator
637 _job_queue_interval | Wakeup interval in seconds for job queue co-ordinator | 5 | 5 | TRUE

--system triggers
639 _system_trig_enabled | are system triggers enabled | TRUE | TRUE | TRUE

--sqlplus
641 _adjust_literal_replacement | If TRUE, we will adjust the SQL/PLUS output | FALSE | FALSE | TRUE

--hash join
663 _hash_join_enabled | enable/disable hash join | TRUE | TRUE | TRUE

--创建数据库运行脚本
690 _init_sql_file | File containing SQL statements to execute upon database creation | ?/rdbms/admin/sql.bsq | ?/rdbms/admin/sql.bsq | TRUE

--dml monitoring
879 _dml_monitoring_enabled | enable modification monitoring | TRUE | TRUE | TRUE

--partition
963 _very_large_partitioned_table | very_large_partitioned_table | 1024 | 1024 | TRUE

--asm
1089 _asm_stripewidth | ASM file stripe width | 8 | 8 | TRUE
1090 _asm_stripesize | ASM file stripe size | 131072 | 131072 | TRUE
1091 _asm_droptimeout | timeout before offlined disks get dropped (in 3s ticks) | 60 | 60 | TRUE
1092 _asm_emulmax | max number of concurrent disks to emulate I/O errors | 10000 | 10000 | TRUE
1093 _asm_emultimeout | timeout before emulation begins (in 3s ticks) | 0 | 0 | TRUE
1094 _asm_kfdpevent | KFDP event | 0 | 0 | TRUE

--alert
1095 _alert_expiration | seconds before an alert message is moved to exception queue | 604800 | 604800 | TRUE
1096 _alert_message_cleanup | Enable Alert Message Cleanup | 1 | 1 | TRUE
1097 _alert_message_purge | Enable Alert Message Purge | 1 | 1 | TRUE
1098 _alert_post_background | Enable Background Alert Posting | 1 | 1 | TRUE
--启用阈值警告
1124 _threshold_alerts_enable | if 1, issue threshold-based alerts | 1 | 1 | TRUE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-710363/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-710363/

cuian7084

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
隐藏参数概览

大家都知道,Oracle数据库存在很多隐藏参数.这些参数在特定情况下使用,会收到特别的效果：*启动/关闭指定特性，如_addm_auto_enable*启动/关闭特定跟踪，如_trace_pin_time*修改缺省参数数值,如_...
复制链接

扫一扫