« Temp落ち #2 - PGA (Program Global Area) | トップページ | Temp落ち #4 - 手動PGA管理で作業領域として指定可能な最大サイズ de Temp落ちの確認 »

2018年2月20日 (火)

Temp落ち #3 - 手動PGA管理で作業領域として指定可能な最大サイズ

自動PGA管理は12cでどうなんだっけ?という確認の前に、
いままで何度か質問されたことがあり、FAQだと思っているので

手動PGA管理で利用する以下パラメータの最大サイズはいくつ? 

HASH_AREA_SIZE
SORT_AREA_SIZE
BITMAP_MERGE_AREA_SIZE
CREATE_BITMAP_AREA_SIZE

ということを書いておきたいと思います。

これからしばらく続く Temp落ち ネタで利用する環境で固定部分は以下のとおり
(初期化パラメータ等は必要に応じて載せるつもりです。)


環境は以下のとおり。
host osとguest osのバージョンやメモリーサイズなど

discus:˜ oracle$ sw_vers
ProductName: Mac OS X
ProductVersion: 10.13.3
BuildVersion: 17D47

discus:˜ oracle$ system_profiler SPHardwareDataType | grep -E 'Processor Name|Cores|Memory'
Processor Name: 6-Core Intel Xeon
Total Number of Cores: 12
Memory: 32 GB

discus:˜ oracle$ VBoxManage -v
5.2.6r120293

discus:˜ oracle$ VBoxManage showvminfo e3d4f948-b2e6-4db3-a89d-df637d87a372 | grep -E 'Memory size|OS type|Number of CPUs'
Memory size: 23569MB
Number of CPUs: 12
OS type: Linux26_64


orcl12c@SYS> select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0


orcl12c@SYS> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO

さて、今日の本題

手動PGA管理で各SQL Work Area Sizeを決定する以下の初期化パラメータの最大サイズは? いくつでしょう? 
すでにご存知のかたはスキップしていいですよ:)

マニュアルを読んでみるときづくのですが、手動PGA管理で各SQL Work Area Sizeを決定する4パラメータとも、「0以上、上限はOS依存」のような記述になっています。

そう、マニュアルを読んだだけではまったく参考にならないわけです。(え〜〜っ!)

そこで、みなさんサポートを頼るなり、ご自分でMOSを検索するなり、そこそこの苦労をして入手することになります。
私みたいな性格だと、どのマニュアルでもいいからまとめて載せてよーめんどくさいから。と、めんどくさい病の発作がでたりしますw

なので、環境があるのなら、you 試しちゃいなよー。が手っ取り早いかなーと(最終的にMOSとかサポートを頼るにしてもw)

上限はOS依存とだけしか記載されていませんが、私の観測範囲では、2GB - 1 が上限 となっています。
以下、Linux/Solaris/Windowsでの検証ログ。

Oracle® Databaseリファレンス 12cリリース2 (12.2) E72905-03より

HASH_AREA_SIZE
https://docs.oracle.com/cd/E82638_01/REFRN/HASH_AREA_SIZE.htm

SORT_AREA_SIZE
https://docs.oracle.com/cd/E82638_01/REFRN/SORT_AREA_SIZE.htm

BITMAP_MERGE_AREA_SIZE
https://docs.oracle.com/cd/E82638_01/REFRN/BITMAP_MERGE_AREA_SIZE.htm

CREATE_BITMAP_AREA_SIZE
https://docs.oracle.com/cd/E82638_01/REFRN/CREATE_BITMAP_AREA_SIZE.htm


Linux

orcl12c@SYS> !uname -r; cat /etc/redhat-release /etc/oracle-release
4.1.12-94.3.6.el7uek.x86_64
Red Hat Enterprise Linux Server release 7.3 (Maipo)
Oracle Linux Server release 7.3

orcl12c@SYS> create pfile from spfile;

File created.

orcl12c@SYS> --2GB
orcl12c@SYS> select 2*1024*1024*1024 from dual;

2*1024*1024*1024
----------------
2147483648

orcl12c@SYS> alter system set hash_area_size = 2147483648 scope=spfile;
alter system set hash_area_size = 2147483648 scope=spfile
*
ERROR at line 1:
ORA-02017: integer value required

orcl12c@SYS> alter system set sort_area_size = 2147483648 scope=spfile;
alter system set sort_area_size = 2147483648 scope=spfile
*
ERROR at line 1:
ORA-02017: integer value required

orcl12c@SYS> alter system set bitmap_merge_area_size = 2147483648 scope=spfile;
alter system set bitmap_merge_area_size = 2147483648 scope=spfile
*
ERROR at line 1:
ORA-02017: integer value required

orcl12c@SYS> alter system set create_bitmap_area_size = 2147483648 scope=spfile;
alter system set create_bitmap_area_size = 2147483648 scope=spfile
*
ERROR at line 1:
ORA-02017: integer value required

orcl12c@SYS> --2GB - 1
orcl12c@SYS> select 2*1024*1024*1024-1 from dual;

2*1024*1024*1024-1
------------------
2147483647

orcl12c@SYS> alter system set hash_area_size = 2147483647 scope=spfile;

System altered.

orcl12c@SYS> alter system set sort_area_size = 2147483647 scope=spfile;

System altered.

orcl12c@SYS> alter system set bitmap_merge_area_size = 2147483647 scope=spfile;

System altered.

orcl12c@SYS> alter system set create_bitmap_area_size = 2147483647 scope=spfile;

System altered.

orcl12c@SYS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
orcl12c@SYS> create spfile from pfile;

File created.

orcl12c@SYS>


Solaris 11 (x86)

SQL> !uname -r; cat /etc/release
5.11
Oracle Solaris 11.3 X86
Copyright (c) 1983, 2015, Oracle and/or its affiliates. All rights reserved.
Assembled 06 October 2015

SQL>
SQL> create pfile from spfile;

ファイルが作成されました。

SQL> --2GB
SQL> select 2*1024*1024*1024 from dual;

2*1024*1024*1024
----------------
2147483648

SQL> alter system set hash_area_size = 2147483648 scope=spfile;
alter system set hash_area_size = 2147483648 scope=spfile
*
行1でエラーが発生しました。:
ORA-02017: 整数値が必要です。


SQL> alter system set sort_area_size = 2147483648 scope=spfile;
alter system set sort_area_size = 2147483648 scope=spfile
*
行1でエラーが発生しました。:
ORA-02017: 整数値が必要です。


SQL> alter system set bitmap_merge_area_size = 2147483648 scope=spfile;
alter system set bitmap_merge_area_size = 2147483648 scope=spfile
*
行1でエラーが発生しました。:
ORA-02017: 整数値が必要です。


SQL> alter system set create_bitmap_area_size = 2147483648 scope=spfile;
alter system set create_bitmap_area_size = 2147483648 scope=spfile
*
行1でエラーが発生しました。:
ORA-02017: 整数値が必要です。


SQL> --2GB - 1
SQL> select 2*1024*1024*1024-1 from dual;

2*1024*1024*1024-1
------------------
2147483647

SQL> alter system set hash_area_size = 2147483647 scope=spfile;

システムが変更されました。

SQL> alter system set sort_area_size = 2147483647 scope=spfile;

システムが変更されました。

SQL> alter system set bitmap_merge_area_size = 2147483647 scope=spfile;

システムが変更されました。

SQL> alter system set create_bitmap_area_size = 2147483647 scope=spfile;

システムが変更されました。

SQL> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません


警告: Oracleにはもう接続されていません。
SQL> conn sys/oracle@orcl122 as sysdba
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません


SQL> exit
bash-4.1$ export ORACLE_SID=orcl122
bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 月 2月 19 22:44:47 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

アイドル・インスタンスに接続しました。

SQL> startup
ORACLEインスタンスが起動しました。

Total System Global Area 838860800 bytes
Fixed Size 8790120 bytes
Variable Size 356519832 bytes
Database Buffers 465567744 bytes
Redo Buffers 7983104 bytes
データベースがマウントされました。
データベースがオープンされました。
SQL> show parameter _area_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 2147483647
create_bitmap_area_size integer 2147483647
hash_area_size integer 2147483647
sort_area_size integer 2147483647
workarea_size_policy string AUTO
SQL>
SQL> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
SQL>
SQL> create spfile from pfile;

File created.


Microsoft Windows

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。
SQL>
SQL> $ver

Microsoft Windows [Version 10.0.16299.125]

SQL> create pfile from spfile;

ファイルが作成されました。

SQL> --2GB
SQL> select 2*1024*1024*1024 from dual;

2*1024*1024*1024
----------------
2147483648

SQL> alter system set hash_area_size = 2147483648 scope=spfile;
alter system set hash_area_size = 2147483648 scope=spfile
*
行1でエラーが発生しました。:
ORA-02017: 整数値が必要です。

SQL> alter system set sort_area_size = 2147483648 scope=spfile;
alter system set sort_area_size = 2147483648 scope=spfile
*
行1でエラーが発生しました。:
ORA-02017: 整数値が必要です。

SQL> alter system set bitmap_merge_area_size = 2147483648 scope=spfile;
alter system set bitmap_merge_area_size = 2147483648 scope=spfile
*
行1でエラーが発生しました。:
ORA-02017: 整数値が必要です。

SQL> alter system set create_bitmap_area_size = 2147483648 scope=spfile;
alter system set create_bitmap_area_size = 2147483648 scope=spfile
*
行1でエラーが発生しました。:
ORA-02017: 整数値が必要です。


SQL> --2GB - 1
SQL> select 2*1024*1024*1024-1 from dual;

2*1024*1024*1024-1
------------------
2147483647

SQL> alter system set hash_area_size = 2147483647 scope=spfile;

システムが変更されました。

SQL> alter system set sort_area_size = 2147483647 scope=spfile;

システムが変更されました。

SQL> alter system set bitmap_merge_area_size = 2147483647 scope=spfile;

システムが変更されました。

SQL> alter system set create_bitmap_area_size = 2147483647 scope=spfile;

システムが変更されました。

SQL> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
ERROR:ORA-12514: TNSリスナーは接続記述子でリクエストされたサービスを現在認識していません
SQL> 警告: Oracleにはもう接続されていません。
SQL> exit
Disconnected
C:\Users\discus>set ORACLE_SID=orcl122SQL>
C:\Users\discus>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on 月 2月 19 22:28:45 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL>
アイドル・インスタンスに接続しました。
SQL> create spfile from pfile;
SQL>
File created.
SQL> exit
Disconnected

C:\Users\discus>


ということで、

手動PGA管理でSQL Work Area Sizeを制御する初期化パラメータで指定可能な最大サイズは

2GB -1

ということになります。(HP-UXやAIXは未確認なので情報いただけたら、ここに追記しまーす。:)

次回へつづく。




Temp落ち #1 - "Temp落ち" って?
Temp落ち #2 - PGA (Program Global Area)

|

トラックバック


この記事へのトラックバック一覧です: Temp落ち #3 - 手動PGA管理で作業領域として指定可能な最大サイズ:

コメント

コメントを書く