欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

oracle 查看隐含参数

程序员文章站 2022-05-18 18:01:10
...

v$parameter视图中查询参数的时候其实都是通过x$ksppi和x$ksppcv这两个内部视图中得到的。 可以通过如下方式查询当前实例的所有隐含参数: select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SY

v$parameter视图中查询参数的时候其实都是通过x$ksppi和x$ksppcv这两个内部视图中得到的。

可以通过如下方式查询当前实例的所有隐含参数:

  select

  x.ksppinm name,

  y.ksppstvl value,

  y.ksppstdf isdefault,

  decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,

  decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj

  from

  sys.x$ksppi x,

  sys.x$ksppcv y

  where

  x.inst_id = userenv('Instance') and

  y.inst_id = userenv('Instance') and

  x.indx = y.indx

  order by

  translate(x.ksppinm, ' _', ' ')

  /

修改隐含参数的方法:alter system set "_cursor_bind_capture_interval"=5 (记得加引号)

-----下面部分给出了如何知道隐含参数查询的方法-------

我们在查看内存参数时会看到如下执行计划:

SQL> set autotrace traceonly
SQL> select * from v$parameter;

342 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1128103955

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4414 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 4414 | 1 (100)| 00:00:01 |
|* 2 | FIXED TABLE FULL| X$KSPPI | 1 | 249 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL| X$KSPPCV | 100 | 406K| 0 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("X"."INDX"="Y"."INDX")
filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
"KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
2 - filter("X"."INST_ID"=USERENV('INSTANCE') AND
BITAND("KSPPIFLG",268435456)=0 AND TRANSLATE("KSPPINM",'_','#') NOT
LIKE '##%')


Statistics
----------------------------------------------------------
64 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
35905 bytes sent via SQL*Net to client
661 bytes received via SQL*Net from client
24 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
342 rows processed

SQL> desc x$ksppi;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KSPPINM VARCHAR2(80)
KSPPITY NUMBER
KSPPDESC VARCHAR2(255)
KSPPIFLG NUMBER
KSPPILRMFLG NUMBER
KSPPIHASH NUMBER

SQL> desc x$ksppcv
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KSPPSTVL VARCHAR2(4000)
KSPPSTDVL VARCHAR2(4000)
KSPPSTDF VARCHAR2(9)
KSPPSTVF NUMBER
KSPPSTCMNT VARCHAR2(255)

由此可见,可以通过下面方法查询所有隐含参数

  select

  x.ksppinm name,

  y.ksppstvl value,

  y.ksppstdf isdefault,

  decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,

  decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj

  from

  sys.x$ksppi x,

  sys.x$ksppcv y

  where

  x.inst_id = userenv('Instance') and

  y.inst_id = userenv('Instance') and

  x.indx = y.indx

  order by

  translate(x.ksppinm, ' _', ' ')

  /