Oracle 隠しパラメータの確認と変更方法

今回は隠しパラメータの確認と変更方法についてで、対象は10g以降になります。

デフォルト値等も含めて、隠しパラメータを確認します。

ワインセット赤送料無料第155弾タカムラスタッフ厳選！！自慢の金賞ボルドー6本赤ワインセット（追加6本同梱可） | [T]

価格: 7221 円
楽天で詳細を見る

隠しパラメータはshow parameterやv$parameterでは確認できません。

隠しなのだから当然ですが。

但し、明示的に設定してあればshow parameterでも参照できます。

以下、隠されているパラメータの確認方法です。

せっかくなので、値だけでなくパラメータの説明やセッション単位で変更可能か等々も見ておきます。

以下、全てsysdba権限を持つユーザで実行します。

SQL> SELECT a.ksppinm "Parameter", b.KSPPSTDF "IS_DEFAULT",b.ksppstdfl "Default Value",
2 b.ksppstvl "Session Value",
3 c.ksppstvl "Instance Value",
4 decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
5 decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE,
6 a.KSPPDESC DESCRIPTION
7 FROM x$ksppi a,
8 x$ksppcv b,
9 x$ksppsv c
10 WHERE a.indx = b.indx
11 AND a.indx = c.indx
12 AND a.ksppinm LIKE '/_%' escape '/'
13 AND a.KSPPINM in ('_shutdown_completion_timeout_mins');

Parameter IS_DEFAULT Default Value Session Value Instance Value
--------------------------------------------- ---------- -------------------- -------------------- --------------------
IS_SESSION IS_SYSTEM_
---------- ----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
_shutdown_completion_timeout_mins TRUE 60 60 60
TRUE IMMEDIATE
minutes for shutdown operation to wait for sessions to complete

_shutdown_completion_timeout_minsの所に調べたいパラメータを指定します。in句なので、複数指定できます。

一つずつ説明すると、

Parameter：パラメータ名：_shutdown_completion_timeout_mins

IS_DEFAULT：デフォルト値か？：TRUE

Session Value：このセッションの設定値：60

Instance Value：このインスタンスの設定値：60

IS_SESSION_MODIFIABLE：セッション単位で変更可能か？：TRUE

IS_SYSTEM_MODIFIABLE：システム単位で変更可能か？：IMMEDIATE

DESCRIPTION：パラメータの説明：シャットダウン時にセッションが完了するのを待機する時間を分で指定する

尚、以下のドキュメントではKSPPSTDFが"default value"だと記載されているのですが、実際に値を見ると、KSPPSTDFはデフォルト値と言うより、デフォルト値かどうかがtrueまたはfalseで設定されており、デフォルト値そのものはksppstdflの方ですね。

How To Query And Change The Oracle Hidden Parameters In Oracle 10g and Later (ドキュメントID 315631.1)

セッション単位で変更可能なパラメータなので、まずはセッション単位で変更してみます。

隠しパラメータ名の前後は「"」(ダブルクォート)でくくります。

尚、RACの場合にはsid='*'を付けないと、自インスタンスのみの変更になります。

SQL> alter session set "_shutdown_completion_timeout_mins"=30;

Session altered.

Parameter IS_DEFAULT Default Value Session Value Instance Value
--------------------------------------------- ---------- -------------------- -------------------- --------------------
IS_SESSION IS_SYSTEM_
---------- ----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
_shutdown_completion_timeout_mins TRUE 60 30 60
TRUE IMMEDIATE
minutes for shutdown operation to wait for sessions to complete

Session Valueのみが30に変更され、 Instance Valueは60のままですね。

セッション単位の変更なので、ログインしなおせば当然、元に戻ります。

SQL> conn / as sysdba
Connected.
SQL> SELECT a.ksppinm "Parameter", b.KSPPSTDF "IS_DEFAULT",b.ksppstdfl "Default Value",
2 b.ksppstvl "Session Value",
3 c.ksppstvl "Instance Value",
4 decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
5 decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE,
6 a.KSPPDESC DESCRIPTION
7 FROM x$ksppi a,
8 x$ksppcv b,
9 x$ksppsv c
10 WHERE a.indx = b.indx
11 AND a.indx = c.indx
12 AND a.ksppinm LIKE '/_%' escape '/'
13 AND a.KSPPINM in ('_shutdown_completion_timeout_mins');

次にシステム単位で変更してみます。

IS_SYSTEM_MODIFIABLEがIMMEDIATEなので、変更は即座に反映されます。

SQL> alter system set "_shutdown_completion_timeout_mins"=40;

System altered.

Parameter IS_DEFAULT Default Value Session Value Instance Value
--------------------------------------------- ---------- -------------------- -------------------- --------------------
IS_SESSION IS_SYSTEM_
---------- ----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
_shutdown_completion_timeout_mins TRUE 60 40 40
TRUE IMMEDIATE
minutes for shutdown operation to wait for sessions to complete

元に戻す方法は以下の通りです。

但し、alter system resetはspファイルのエントリを削除するだけなので、即時反映はされません。

SQL> alter system reset "_shutdown_completion_timeout_mins";

System altered.

SQL> sho parameter _shutdown_completion_timeout_mins

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_shutdown_completion_timeout_mins integer 40

spファイルからpfileを作成し、_shutdown_completion_timeout_minsの設定が削除されている事を確認します。

SQL> create pfile='/scskwork/testinit.ora' from spfile;

File created.

SQL> !grep _shutdown_completion_timeout_mins /work/testinit.ora

再起動し、設定が元に戻っているのを確認します。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 373293160 bytes
Database Buffers 683671552 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.
SQL> SELECT a.ksppinm "Parameter", b.KSPPSTDF "IS_DEFAULT",b.ksppstdfl "Default Value",
2 b.ksppstvl "Session Value",
3 c.ksppstvl "Instance Value",
4 decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
5 decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE,
6 a.KSPPDESC DESCRIPTION
7 FROM x$ksppi a,
8 x$ksppcv b,
9 x$ksppsv c
10 WHERE a.indx = b.indx
11 AND a.indx = c.indx
12 AND a.ksppinm LIKE '/_%' escape '/'
13 AND a.KSPPINM in ('_shutdown_completion_timeout_mins');

次はIS_SYSTEM_MODIFIABLEがDEFERREDの隠しパラメータの変更方法です。

即時変更できないパラメータなので、先ほどと同じように変更しようとするとエラーになります。

Parameter IS_DEFAULT Default Value Session Value Instance Value
--------------------------------------------- ---------- -------------------- -------------------- --------------------
IS_SESSION IS_SYSTEM_
---------- ----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
_db_file_direct_io_count TRUE 1048576 1048576 1048576
FALSE DEFERRED
Sequential I/O buf size

SQL> alter system set "_db_file_direct_io_count"=500000;
alter system set "_db_file_direct_io_count"=500000
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

IS_SYSTEM_MODIFIABLEがDEFERREDのパラメータなので、spfileのみを変更し、spfileの値が変更されている事を確認します。

この時点ではまだ変更が反映されていないので、sho parameterで見ても何も表示されません。

SQL> alter system set "_db_file_direct_io_count"=500000 scope=spfile;

System altered.

SQL> sho parameter _db_file_direct_io_count

SQL> create pfile='/work/testinit.ora' from spfile;

File created.

SQL> !grep _db_file_direct_io_count /work/testinit.ora
*._db_file_direct_io_count=500000

DB再起動後に、値が有効になります。

パラメータ値が変わっているだけでなく、IS_DEFAULTもfalseになっていますね。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_db_file_direct_io_count integer 500000

Parameter IS_DEFAULT Default Value Session Value Instance Value
--------------------------------------------- ---------- -------------------- -------------------- --------------------
IS_SESSION IS_SYSTEM_
---------- ----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
_db_file_direct_io_count FALSE 1048576 500000 500000
FALSE DEFERRED
Sequential I/O buf size

元に戻すには先ほど同様resetすれば良いのですが、デフォルト値と同じ値で上書きするとどうなるか、確認してみます。

SQL> alter system set "_db_file_direct_io_count"=1048576 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_db_file_direct_io_count integer 1048576
SQL>
SQL> SELECT a.ksppinm "Parameter", b.KSPPSTDF "IS_DEFAULT",b.ksppstdfl "Default Value",
2 b.ksppstvl "Session Value",
3 c.ksppstvl "Instance Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE,
a.KSPPDESC DESCRIPTION
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%' escape '/'
AND a.KSPPINM in ('_db_file_direct_io_count');
4 5 6 7 8 9 10 11 12 13
Parameter IS_DEFAULT Default Value Session Value Instance Value
--------------------------------------------- ---------- -------------------- -------------------- --------------------
IS_SESSION IS_SYSTEM_
---------- ----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
_db_file_direct_io_count FALSE 1048576 1048576 1048576
FALSE DEFERRED
Sequential I/O buf size

spfileにエントリが残っているので sho parameterで値が確認でき、なおかつIS_DEFAULTはfalseのままです。

IS_DEFAULT=TRUEとは、pfileにもspfileにも明示的に設定されていない状態を示すので、デフォルト値と同じ値で上書きしても、IS_DEFAULT=FALSEになります。

また、IS_SYSTEM_MODIFIABLE=FALSEのパラメータは、DB起動時にspfileが使用されていればDEFERREDと同じ方法で変更できますが、使用されていなければalter systemでは変更できません。

以上、隠しパラメータの確認と変更方法でしたが、変更方法については隠されていないパラメータでも同じです。

おらくるのいる生活

OracleのDBAとしての、障害対応やらパフォーマンス・チューニングやらの日々を綴っています

隠しパラメータの確認と変更方法