Temp落ち #3 - 手動PGA管理で作業領域として指定可能な最大サイズ Tweet
自動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パラメータとも、「０以上、上限は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)
| 固定リンク | 0
コメント