Temp落ち #7 - 自動PGA管理で到達可能な最大サイズ de Temp落ちの確認 12.2.0.1版 Tweet
Previously on Mac De Oracle
自動PGA管理で利用可能なSQL Work Areaサイズはいくつなのか？の確認でした。
これまで同様、隠しパラメータ等の変更をしないデフォルトの設定では最大1GBまで到達することを確認しました。
また、手動PGA管理で利用可能な最大サイズより小さいことも再確認しました。
今日は自動PGA管理下ではオンメモリーで処理可能なサイズは1GBまでなのか、それを超えた場合はもれなく "Temp落ち" なのか確認することにします。
自動PGA管理でGlobal Memory Bound = 1GB（自動PGA管理での最大 Sort作業領域サイズ）
メモリーソートで1GBぐらいまで利用していることが確認できます。（ソートされるデータ量は1GB以下程度に制限しています。）
ORCL@SCOTT> @auto_sortwk1gb_optimal
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 4398046511103
NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ---------- ------------ ----------
global memory bound 1073741824 bytes 0
1 SELECT
2 /*+
3 MONITOR
4 */
5 *
6 FROM
7 m1
8 WHERE
9 id <= 'C045000'
10 ORDER BY
11 id
12* ,rev#
old 1: select dbms_sqltune.report_sql_monitor(sql_id=>'&1', type=>'text') from dual
new 1: select dbms_sqltune.report_sql_monitor(sql_id=>'', type=>'text') from dual
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'',TYPE=>'TEXT')
------------------------------------------------------------------------------------------
SQL Monitoring Report
・・・中略・・・
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 4.78 | 3.85 | 0.34 | 0.59 | 30001 | 334K | 2633 | 3GB |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=3534657201)
==================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
==================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 10 | +2 | 1 | 450K | | | . | 33.33 | Cpu (2) |
| 1 | SORT ORDER BY | | 456K | 302K | 11 | +1 | 1 | 450K | | | 1GB | 33.33 | Cpu (2) |
| 2 | TABLE ACCESS FULL | M1 | 456K | 90827 | 3 | +2 | 1 | 450K | 2633 | 3GB | . | 33.33 | Cpu (2) |
==================================================================================================================================================Temp落ちする程度のデータ量でソートさせた場合も、PGAの作業領域は最大1GBまで利用されていたことが確認できます。
ORCL@SCOTT> @auto_sortwk1gb_mpass
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 4398046511103
NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ---------- ------------ ----------
global memory bound 1073741824 bytes 0
1 SELECT
2 /*+
3 MONITOR
4 */
5 *
6 FROM
7 m1
8 WHERE
9 id <= 'C050000'
10 ORDER BY
11 id
12* ,rev#
old 1: select dbms_sqltune.report_sql_monitor(sql_id=>'&1', type=>'text') from dual
new 1: select dbms_sqltune.report_sql_monitor(sql_id=>'', type=>'text') from dual
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'',TYPE=>'TEXT')
------------------------------------------------------------------------------------------
SQL Monitoring Report
・・・中略・・・
Global Stats
===========================================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
===========================================================================================
| 16 | 6.91 | 7.91 | 0.71 | 33335 | 334K | 7996 | 4GB | 4431 | 1GB |
===========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3534657201)
=====================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
=====================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 22 | +2 | 1 | 500K | | | | | . | . | 6.25 | Cpu (1) |
| 1 | SORT ORDER BY | | 507K | 326K | 22 | +2 | 1 | 500K | 5363 | 1GB | 4431 | 1GB | 1GB | 1GB | 68.75 | Cpu (4) |
| | | | | | | | | | | | | | | | | direct path read temp (1) |
| | | | | | | | | | | | | | | | | direct path write temp (6) |
| 2 | TABLE ACCESS FULL | M1 | 507K | 90828 | 13 | +1 | 1 | 500K | 2633 | 3GB | | | . | . | 25.00 | Cpu (3) |
| | | | | | | | | | | | | | | | | direct path read (1) |
=====================================================================================================================================================================================
自動PGA管理でGlobal Memory Bound = 1GB（自動PGA管理での最大 Hash作業領域サイズ）
Hash Joinの場合も、自動PGA管理の最大サイズ程度まで利用されていることが確認できます。（Temp落ちしない程度のデータ量にしています。）Hash Joinの場合もSort時と同じように、一時表領域も利用が必要となるデータ量になると、一旦、自動PGA管理の最大サイズ程度まで利用したうえでTemp落ちしていることが確認できます。
ORCL@SCOTT> @auto_hashwk1gb_optimal
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 4398046511103
NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ---------- ------------ ----------
global memory bound 1073741824 bytes 0
1 SELECT
2 /*+
3 MONITOR
4 LEADING(m1 m2)
5 USE_HASH(m2)
6 */
7 *
8 FROM
9 m1
10 INNER JOIN m2
11 ON
12 m1.id = m2.id
13 AND m1.rev# = m2.rev#
14 WHERE
15* m1.id <= 'C042000'
old 1: select dbms_sqltune.report_sql_monitor(sql_id=>'&1', type=>'text') from dual
new 1: select dbms_sqltune.report_sql_monitor(sql_id=>'', type=>'text') from dual
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'',TYPE=>'TEXT')
------------------------------------------------------------------------------------------
SQL Monitoring Report
・・・中略・・・
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 7.29 | 5.05 | 0.68 | 1.55 | 28001 | 695K | 5251 | 5GB |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=1822065247)
==================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
==================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 14 | +2 | 1 | 420K | | | . | 22.22 | Cpu (2) |
| 1 | HASH JOIN | | 423K | 268K | 15 | +1 | 1 | 420K | | | 1GB | 22.22 | Cpu (2) |
| 2 | TABLE ACCESS FULL | M1 | 432K | 90827 | 2 | +2 | 1 | 420K | 2633 | 3GB | . | 11.11 | Cpu (1) |
| 3 | TABLE ACCESS FULL | M2 | 423K | 90580 | 13 | +3 | 1 | 420K | 2618 | 3GB | . | 44.44 | Cpu (4) |
==================================================================================================================================================
ORCL@SCOTT> @auto_hashwk1gb_mpass
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 4398046511103
NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ---------- ------------ ----------
global memory bound 1073741824 bytes 0
1 SELECT
2 /*+
3 MONITOR
4 LEADING(m1 m2)
5 USE_HASH(m2)
6 */
7 *
8 FROM
9 m1
10 INNER JOIN m2
11 ON
12 m1.id = m2.id
13 AND m1.rev# = m2.rev#
14 WHERE
15* m1.id <= 'C055000'
old 1: select dbms_sqltune.report_sql_monitor(sql_id=>'&1', type=>>'text') from dual
new 1: select dbms_sqltune.report_sql_monitor(sql_id=>'', type=>'text') from dual
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'',TYPE=>'TEXT')
------------------------------------------------------------------------------------------
SQL Monitoring Report
・・・中略・・・
Global Stats
============================================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
============================================================================================
| 24 | 7.18 | 15 | 1.99 | 36668 | 687K | 10829 | 6GB | 5578 | 1GB |
============================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1822065247)
======================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
======================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 33 | +2 | 1 | 550K | | | | | . | . | 14.29 | Cpu (4) |
| 1 | HASH JOIN | | 551K | 293K | 33 | +2 | 1 | 550K | 5578 | 1GB | 5578 | 1GB | 1GB | 1GB | 67.86 | Cpu (8) |
| | | | | | | | | | | | | | | | | direct path read temp (1) |
| | | | | | | | | | | | | | | | | direct path write temp (10) |
| 2 | TABLE ACCESS FULL | M1 | 551K | 90828 | 16 | +1 | 1 | 550K | 2633 | 3GB | | | . | . | 7.14 | Cpu (1) |
| | | | | | | | | | | | | | | | | direct path read (1) |
| 3 | TABLE ACCESS FULL | M2 | 557K | 90580 | 16 | +16 | 1 | 550K | 2618 | 3GB | | | . | . | 10.71 | Cpu (3) |
======================================================================================================================================================================================
PGAの最大サイズは異なりますが、自動PGA管理、手動PGA管理いずれでの場合でも制限値を超えた場合は、もれなく "Temp落ち" するということはご理解いただけたのではないかと思います。
では、次回は恒例？w の隠しパラメータと戯れた場合、自動PGA管理下ではどこまで増加させることができるのか？。。。。確認してみたいと思います。
・Temp落ち #1 - "Temp落ち" って？
・Temp落ち #2 - PGA (Program Global Area)
・Temp落ち #3 - 手動PGA管理で作業領域として指定可能な最大サイズ
・Temp落ち #4 - 手動PGA管理で作業領域として指定可能な最大サイズ de Temp落ちの確認
・Temp落ち #5 - pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? 12.2.0.1版 （その前に少し脱線）
・Temp落ち #6 - pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? 12.2.0.1版
| 固定リンク | 0
コメント