查询oracle隐含参数

来源：互联网发布：值得买的电子产品知乎编辑：程序博客网时间：2024/06/06 07:02

我都是放在$ORACLE_HOME/rdbms/admin下，看起来像个Oracle提供的脚本
$ cat show_para.sql
col p_name for a40
col p_DESCRIPTION for a50
col p_value for a30
set linesize 10000
SELECT i.ksppinm p_name,
i.ksppdesc p_description,
CV.ksppstvl p_VALUE,
CV.ksppstdf isdefault,
DECODE (BITAND (CV.ksppstvf, 7),1, 'MODIFIED',4, 'SYSTEM_MOD', 'FALSE')
ismodified,
DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted
FROM sys.x$ksppi i, sys.x$ksppcv CV
WHERE i.inst_id = USERENV ('Instance')
AND CV.inst_id = USERENV ('Instance')
AND i.indx = CV.indx
AND upper(i.ksppinm) LIKE upper('%&p%')
ORDER BY REPLACE (i.ksppinm, '_', '');

比如我现在想查CBC Latch的数量，如下操作：

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 16 18:51:17 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @?/rdbms/admin/show_para　　--@代表运行，?代表$ORACLE_HOME
Enter value for p: latch --输入latch，如果不记得CBC Latch的参数，那就做糊涂查询
old 12: AND upper(i.ksppinm) LIKE upper('%&p%')
new 12: AND upper(i.ksppinm) LIKE upper('%latch%')

P_NAME P_DESCRIPTION P_VALUE ISDEFAULT ISMODIFIED ISADJ
---------------------------------------- -------------------------------------------------- ------------------------------ --------- ---------- -----
_cp_num_hash_latches connection pool number of hash latches 1 TRUE FALSE FALSE
_db_block_hash_latches Number of database block hash latches 4096 TRUE FALSE FALSE--这个参数就是CBC Latch
_db_block_lru_latches number of lru latches 16 　TRUE FALSE FALSE
_db_blocks_per_hash_latch Number of blocks per hash latch TRUE FALSE FALSE
_deq_ht_child_latches deq ht child latches 8 　　TRUE FALSE FALSE
_disable_latch_free_SCN_writes_via_32cas disable latch-free SCN writes using 32-bit compare FALSE TRUE FALSE FALSE

Oracle中有很多隐藏参数，一般不建议修改，默认值为佳。

0 0