« 2017年12月 | トップページ | 2018年3月 »

2018年2月24日 (土)

Temp落ち #5 - pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? 12.2.0.1版 (その前に少し脱線)


Previously on Mac De Oracle

手動PGA管理下で利用される4つのパラメータ(HASH_AREA_SIZE / SORT_AREA_SIZE / BITMAP_MERGE_AREA_SIZE / CREATE_BITMAP_AREA_SIZE)は、Integer型であり2,147,483,647バイト(つまり2GB - 1)までは指定可能、かつ、個々の作業領域は同程度確保されること。そのサイズを超えるデータ量の場合は一時表領域が利用される。つまり"Temp落ち"が発生するというところまでした。

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

64bit環境でInteger型なので、そんなもんでしょうね。 仕様ですからね、こればっかりはどうにもならない。


で、自動PGA管理で確認しようとパタパタ準備していたところ、12.1.0.2まではなかった12.2.0.1での変更点に気付いたので、そちらを先に。
(少し脱線)

pga_aggregate_targetをscope=memoryで設定する際、物理メモリーの空きサイズ以上に設定しようとするとORA-00855エラーなり設定できないよう動作が変更されたようです。

また、spfileにのみ設定する場合には動作が異なり、設定時には、ORA-00855は発生しません。
アラートログファイルには、起動時のワーニングとして記録され、アラートログにもなにも記録されれず、正常起動しpga_aggregate_targetには指定したサイズで設定されるという、
少々分かりづらい仕様に変わったようです。 (今頃気づくとは、遅い!w

(18cではどうなるんだろう。。なんとなく、Autonomousを意識してるようなアトモスフィアを感じます..が、どうなんでしょうw?)

以下、動作確認の記録。
Guest OSのメモリーサイズは以下の通り。空いているメモリーサイズは500MB〜600MB程度です。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。
orcl12c@SYS> !cat /proc/meminfo | grep Mem
MemTotal: 3781732 kB
MemFree: 43300 kB
MemAvailable: 559076 kB


orcl12c@SYS> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 2G
sga_min_size big integer 0
sga_target big integer 2G
unified_audit_sga_queue_size integer 1048576

orcl12c@SYS> show parameter pga_agg

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 200M


この状態で、pga_aggregate_limitの50%のサイズでpga_aggregate_limitの制限内、pga_aggregate_target=1g としてメモリー上でのみ変更してみると

orcl12c@SYS> alter system set pga_aggregate_target=1g scope=memory;
alter system set pga_aggregate_target=1g scope=memory
*
行1でエラーが発生しました。:
ORA-02097: 指定した値が無効なので、パラメータを変更できません。
ORA-00855: PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory.


空きメモリーサイズ以下ならどうなるか試して見ます。

orcl12c@SYS> alter system set pga_aggregate_target=500m scope=memory;

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


空きメモリーサイズを多少上回るサイズでは。。。(やはり、ORA-00855となります)

orcl12c@SYS> alter system set pga_aggregate_target=700m scope=memory;
alter system set pga_aggregate_target=700m scope=memory
*
行1でエラーが発生しました。:
ORA-02097: 指定した値が無効なので、パラメータを変更できません。
ORA-00855: PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory.


scope=bothとなる状態で、空きメモリー以上のサイズを設定しようとしても同様のエラーになりますが.....

orcl12c@SYS> alter system set pga_aggregate_target=1g;
alter system set pga_aggregate_target=1g
*
行1でエラーが発生しました。:
ORA-02097: 指定した値が無効なので、パラメータを変更できません。
ORA-00855: PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory.


scope=spfileとした場合には、エラーにならず変更可能です。このあたりの動きは、他のパラメータと同様、起動時にエラーで起動しないという動きになるのかな??? と思いきや.....
なんと、正常起動し、pga_aggregate_targetにはしっかり、1GBが設定されています。!!!!! (動きが違う!!!)

orcl12c@SYS> alter system set pga_aggregate_target=1g scope=spfile;

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

orcl12c@SYS> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
orcl12c@SYS> startup
ORACLEインスタンスが起動しました。

Total System Global Area 2147483648 bytes
Fixed Size 8794848 bytes
Variable Size 603983136 bytes
Database Buffers 1526726656 bytes
Redo Buffers 7979008 bytes
データベースがマウントされました。
データベースがオープンされました。
orcl12c@SYS>
orcl12c@SYS> show parameter pga_agg

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 1G

なぜ、動作が違うんだろう........


2018/2/25訂正
以下のメッセージよくよく見たら、pga_aggregate_limitパラメータへの警告でpga_aggregate_targetのORA-00855とは無関係なワーニングでした。orz.

pga_aggregate_limitの下限値は2G、sea_max_size=2Gなのに、Guest OSへのメモリー割り当て4Gなのでそりゃでるわね。というこでした。

とはいえ、 pga_aggreagte_target scope=memoryとspfileではORA-00855エラーの有無の違いはあるようで、spfileに設定した場合にはORA-00855は発生せず、アラートログファイルには何も記録されていない(謎


なお、起動時のアラートログファイルには以下の"WARNING"メッセージが記録されます。しかも、メッセージを見る限り、pga_aggregate_limit へのワーニングと読めるが....

2018-02-24T22:16:38.907017+09:00
WARNING: pga_aggregate_limit value is too high for the
amount of physical memory on the system
PGA_AGGREGATE_LIMIT is 2048 MB
PGA_AGGREGATE_TARGET is 1024 MB.
physical memory size is 3693 MB
limit based on physical memory and SGA usage is 1275 MB
SGA_TARGET is 2048 MB

おまけ


12.1.0.2までは該当するメッセージはありません。
[oracle@vbgeneric ˜]$ sqlplus -version

SQL*Plus: Release 12.1.0.2.0 Production

[oracle@vbgeneric ˜]$ oerr ORA 855
[oracle@vbgeneric ˜]$



12.2.0.1で実装されたメッセージと機能だということがわかります。
[oracle@localhost ˜]$ sqlplus -version

SQL*Plus: Release 12.2.0.1.0 Production

[oracle@localhost ˜]$ oerr ORA 855
00855, 00000, "PGA_AGGREGATE_TARGET cannot be set because of insufficient physical memory."
// *Cause: PGA_AGGREGATE_TARGET value was too high for the current system global area (SGA) size and amount of physical memory available.
// *Action: Reduce the SGA size or increase the physical memory size.

ということで、次回こそw 自動PGA管理下での確認へ

続く。


Temp落ち #1 - "Temp落ち" って?
Temp落ち #2 - PGA (Program Global Area)
Temp落ち #3 - 手動PGA管理で作業領域として指定可能な最大サイズ
Temp落ち #4 - 手動PGA管理で作業領域として指定可能な最大サイズ de Temp落ちの確認

| | コメント (0) | トラックバック (0)

2018年2月22日 (木)

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


Previously on Mac De Oracle
手動PGA管理で作業領域として指定可能な最大サイズは、2GB - 1までということの確認でした。
本当に、その程度のサイズまでPGAの作業領域が利用されるのでしょうか?。 念のために確認しておきましょう。
実は、それより少ないサイズで頭打ちなんてことは、ないかな〜 と わざとらしく言ってみたりして(意味深w

その前に、指定した作業領域を使い切れるぐらい(つまり、Temp落ちさせられる程度)のデータ量の表を準備しておきます。
今回は、Nested Loop Join(NLJ)やソート回避などのための索引は作成しません。Temp落ちのネタなので。

M1とM2の2表を作成し、それぞれ、2.5GB程度のセグメントサイズにしておきます。
なお、以下の無名PL/SQLブロックでは、FORALLを利用して1000行単位でバルク処理しています。配列を利用するのでメモリー使用量にはそれなりに配慮が必要ですが。:)
単純なぐるぐる系INSERTにしてしまうとデータ量が多い場合、性能的に辛くなってしまうので、ここ重要!

ORCL@SCOTT> l
1 CREATE TABLE m1
2 (
3 id CHAR(7) NOT NULL
4 ,rev# NUMBER NOT NULL
5 ,value NUMBER NOT NULL
6 ,description VARCHAR2(4000)
7 ,additional_info CHAR(200)
8* ) NOLOGGING
ORCL@SCOTT> /

Table created.

ORCL@SCOTT> l
1 DECLARE
2 TYPE IDS_t IS TABLE OF m1.id%TYPE INDEX BY PLS_INTEGER;
3 TYPE REV#S_t IS TABLE OF m1.rev#%TYPE INDEX BY PLS_INTEGER;
4 TYPE VALS_t IS TABLE OF m1.value%TYPE INDEX BY PLS_INTEGER;
5 IDs IDS_t;
6 REV#s REV#S_t;
7 VALs VALS_t;
8 k PLS_INTEGER := 1;
9 BEGIN
10 FOR i IN 1..100000 LOOP
11 FOR j IN 1..10 LOOP
12 IDs(k) := 'C' || TO_CHAR(i, 'FM000000');
13 REV#s(k) := j;
14 VALs(k) := i + j;
15 k := k + 1;
16 END LOOP;
17 IF MOD(i, 100) = 0 THEN
18 FORALL l in 1..1000 EXECUTE IMMEDIATE
19 'INSERT /*+ APPEND_VALUE NO_GATHER_OPTIMIZER_STATISTICS */ INTO m1 '
20 || 'VALUES(:1, :2, :3, LPAD(''X'',2000, ''X''), LPAD(''9'',200,''9''))'
21 USING IDs(l), REV#s(l), VALs(l);
22 COMMIT;
23 k := 1;
24 END IF;
25 END LOOP;
26* END;
ORCL@SCOTT> /

PL/SQL procedure successfully completed.

Elapsed: 00:01:22.45
ORCL@SCOTT> select count(1) from m1;

COUNT(1)
----------
1000000

ORCL@SCOTT> select segment_name,sum(bytes)/1024/1024/1024 "GB" from user_segments where segment_name='M1' group by segment_name;

SEGMENT_NAME GB
------------------------------ ----------
M1 2.5625

ORCL@SCOTT> l
1 CREATE TABLE m2
2 (
3 id CHAR(7) NOT NULL
4 ,rev# NUMBER NOT NULL
5 ,value NUMBER NOT NULL
6 ,description VARCHAR2(4000)
7* ) NOLOGGING
ORCL@SCOTT> /

Table created.

ORCL@SCOTT> l
1 INSERT /*+ APPEND NO_GATHER_OPTIMIZER_STATISTICS */ INTO m2
2* SELECT id,rev#,value,description FROM m1
ORCL@SCOTT> /

1000000 rows created.

Elapsed: 00:00:40.22
ORCL@SCOTT> commit;

Commit complete.

ORCL@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'M1',no_invalidate=>false,method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed.

ORCL@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'M2',no_invalidate=>false,method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed.

これで、準備完了。
ちなみに、NO_GATHER_OPTIMIZER_STATISTICSヒントを利用していますが、データ登録後に、ヒストグラムも含めて取得したかったため、バルクロード時のオンラインオプティマイザ統計収集を行わないようにするためのヒントです。
データ登録後オプティマイザ統計を取得するため、データ登録時のオンラインオプティマイザ統計のオーバーヘッドは無駄となるためです。利用できるなら利用したほうがよいとは思いますが、制限もあるのでご一読を(バルク・ロードのためのオンライン統計収集



手動PGA管理で2GB - 1(手動PGA管理での最大 Sort作業領域サイズ)


メモリーソートで2GBぐらいまで利用していることが確認できます。(ソートされるデータ量が2GB以下程度に制限しています。)
ORCL@SCOTT> @manual_sortwk2gb_optimal    
1* alter session set workarea_size_policy=manual

Session altered.

1* alter session set sort_area_size = 2147483647

Session altered.

1 SELECT
2 /*+
3 MONITOR
4 */
5 *
6 FROM
7 m1
8 WHERE
9 id <= 'C075000'
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 | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 5.42 | 4.13 | 1.29 | 50001 | 334K |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=3534657201)
===================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
===================================================================================================================================
| 0 | SELECT STATEMENT | | | | 15 | +2 | 1 | 750K | . | 20.00 | Cpu (1) |
| 1 | SORT ORDER BY | | 752K | 439K | 16 | +1 | 1 | 750K | 2GB | 60.00 | Cpu (3) |
| 2 | TABLE ACCESS FULL | M1 | 752K | 90828 | 3 | +2 | 1 | 750K | . | 20.00 | Cpu (1) |
===================================================================================================================================

Temp落ちする程度のデータ量でソートさせた場合も、PGAの作業領域は一旦、2GBまで利用されていることが確認できます。

ORCL@SCOTT> @manual_sortwk2gb
1* alter session set workarea_size_policy=manual

Session altered.

1* alter session set sort_area_size = 2147483647

Session altered.

1 SELECT
2 /*+
3 MONITOR
4 */
5 *
6 FROM
7 m1
8 ORDER BY
9 id
10* ,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 |
===========================================================================================
| 46 | 11 | 15 | 20 | 66668 | 334K | 2148 | 2GB | 2146 | 2GB |
===========================================================================================

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 | | | | 60 | +2 | 1 | 1M | | | | | . | . | 6.38 | Cpu (2) |
| | | | | | | | | | | | | | | | | PGA memory operation (1) |
| 1 | SORT ORDER BY | | 1M | 553K | 61 | +1 | 1 | 1M | 2148 | 2GB | 2146 | 2GB | 2GB | 2GB | 91.49 | Cpu (32) |
| | | | | | | | | | | | | | | | | direct path read temp (10) |
| | | | | | | | | | | | | | | | | direct path write temp (1) |
| 2 | TABLE ACCESS FULL | M1 | 1M | 90822 | 27 | +2 | 1 | 1M | | | | | . | . | 2.13 | Cpu (1) |
=====================================================================================================================================================================================

手動PGA管理で2GB - 1(手動PGA管理での最大 Hash作業領域サイズ)
Hash Joinの場合も、手動PGA管理で設定可能な最大サイズ程度まで利用されていることが確認できます。(Temp落ちしない程度のデータ量にしています。)

ORCL@SCOTT> @manual_hashwk2gb_optimal
1* alter session set workarea_size_policy = manual

Session altered.

1* alter session set hash_area_size = 2147483647

Session altered.

1 SELECT
2 /*+
3 MONITOR
4 LEADING(m1 m2)
5 USE_HASH(m1 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 <= 'C075000'
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 |
===========================================================================
| 8.05 | 4.66 | 0.16 | 3.23 | 50001 | 717K | 2618 | 3GB |
===========================================================================

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 | | | | 21 | +2 | 1 | 750K | | | . | 12.50 | Cpu (1) |
| 1 | HASH JOIN | | 752K | 181K | 22 | +1 | 1 | 750K | | | 2GB | 25.00 | Cpu (2) |
| 2 | TABLE ACCESS FULL | M1 | 752K | 90828 | 2 | +2 | 1 | 750K | | | . | 25.00 | Cpu (2) |
| 3 | TABLE ACCESS FULL | M2 | 759K | 90581 | 20 | +3 | 1 | 750K | 2618 | 3GB | . | 37.50 | Cpu (3) |
==================================================================================================================================================

Hash Joinの場合もSort時と同じように、一時表領域も利用させる程度のデータ量になると、一旦、手動PGA管理で設定可能な最大サイズ程度まで利用したうえでTemp落ちしていることが確認できます。

ORCL@SCOTT> @manual_hashwk2gb
1* alter session set workarea_size_policy = manual

Session altered.

1* alter session set hash_area_size = 2147483647

Session altered.

1 SELECT
2 /*+
3 MONITOR
4 LEADING(m1 m2)
5 USE_HASH(m1 m2)
6 */
7 *
8 FROM
9 m1
10 INNER JOIN m2
11 ON
12 m1.id = m2.id
13* AND m1.rev# = m2.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 |
===========================================================================================
| 12 | 5.68 | 2.17 | 4.59 | 66668 | 706K | 3133 | 3GB | 515 | 511MB |
===========================================================================================

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 | | | | 30 | +2 | 1 | 1M | | | | | . | . | 25.00 | Cpu (4) |
| 1 | HASH JOIN | | 1M | 189K | 31 | +1 | 1 | 1M | 515 | 511MB | 515 | 511MB | 2GB | 515MB | 43.75 | Cpu (5) |
| | | | | | | | | | | | | | | | | direct path read temp (1) |
| | | | | | | | | | | | | | | | | direct path write temp (1) |
| 2 | TABLE ACCESS FULL | M1 | 1M | 90822 | 6 | +0 | 1 | 1M | | | | | . | . | 12.50 | Cpu (2) |
| 3 | TABLE ACCESS FULL | M2 | 1M | 90574 | 24 | +5 | 1 | 1M | 2618 | 3GB | | | . | . | 18.75 | Cpu (2) |
| | | | | | | | | | | | | | | | | direct path read (1) |
=====================================================================================================================================================================================

おまけ
1つのSQLの実行で利用されるPGAの作業領域は1つだけではないということも確認しておきましょうかね。(知ってる方はスルーしてよいですよ:)
以下の例では、MERGE JOINさせていますが、表M1と表M2それぞれのSort作業領域は、Merge Join終了時まで保持されることになるため、最大3GBのSort作業領域が利用されています。(SQLモニターの結果ではわかりにくいのですが。。。)
なお、手動PGA管理、自動PGA管理に関係なく、実行される操作により複数の作業領域が同時に確保されることがあります。
(ちなみに、自動PGA管理で利用されるPGA_AGGREGATE_LIMITがPGA_AGGREGATE_TARGETの2倍とされるのも、このような動きが考慮された結果だと知っていると、納得感はあるかもしれません。)

前回使った図で朱色で示したSQL Work Areaが複数ありますが、Hash/Sort/Bitmap系など複数のタイプおよび同一タイプの作業領域が同時に確保されることも意図した図になっているのは、これが理由なんです。


Structure_of_pga

ORCL@SCOTT> @manual_sortwk2gb2_optimal
1* alter session set workarea_size_policy = manual

Session altered.

1* alter session set sort_area_size = 2147483647

Session altered.

1 SELECT
2 /*+
3 MONITOR
4 USE_MERGE(m1 m2)
5 */
6 *
7 FROM
8 m1
9 INNER JOIN m2
10 ON
11 m1.id = m2.id
12 AND m1.rev# = m2.rev#
13 WHERE
14* m1.id <= 'C075000'
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 |
===========================================================================
| 17 | 10 | 4.75 | 2.19 | 50001 | 667K | 5251 | 5GB |
===========================================================================

SQL Plan Monitoring Details (Plan Hash Value=1391069689)
========================================================================================================================================================
| 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 | | | | 23 | +10 | 1 | 750K | | | . | 14.29 | Cpu (2) |
| 1 | MERGE JOIN | | 752K | 849K | 23 | +10 | 1 | 750K | | | . | | |
| 2 | SORT JOIN | | 752K | 439K | 32 | +1 | 1 | 750K | | | 2GB | 21.43 | Cpu (3) |
| 3 | TABLE ACCESS FULL | M1 | 752K | 90828 | 8 | +2 | 1 | 750K | 2633 | 3GB | . | 35.71 | Cpu (3) |
| | | | | | | | | | | | | | direct path read (2) |
| 4 | SORT JOIN | | 759K | 410K | 23 | +10 | 750K | 750K | | | 1GB | 14.29 | Cpu (2) |
| 5 | TABLE ACCESS FULL | M2 | 759K | 90581 | 5 | +10 | 1 | 750K | 2618 | 3GB | . | 14.29 | Cpu (2) |
========================================================================================================================================================


SQLモニターの結果ではわかりにくいわけですが、v$sesstatをから眺めれば状況がよくわかります!
2つのソート作業領域が同時に確保されたことで、2GB + 1GB = 3GB程度のサイズにまで達していることが確認できます。

ORCL@SYSTEM> r
1 select
2 vss.value/1024/1024/1024 "GB"
3 ,vsn.name
4 ,vss.sid
5 from
6 v$sesstat vss
7 inner join v$statname vsn
8 on
9 vss.statistic# = vsn.statistic#
10 and vss.con_id = vsn.con_id
11 and vsn.name like '%pga%'
12 where
13 sid IN (select sid from v$session where username='SCOTT')
14 order by
15* sid,name

GB NAME SID
---------- ---------------------------------------------------------------- ----------
3.04611414 session pga memory 203
3.04611414 session pga memory max 203

では、次回はやっとw、真打、自動PGA管理下での確認。(引っ張り過ぎかもしれないw)
つづく。


Temp落ち #1 - "Temp落ち" って?
Temp落ち #2 - PGA (Program Global Area)
Temp落ち #3 - 手動PGA管理で作業領域として指定可能な最大サイズ

| | コメント (0) | トラックバック (0)

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)

| | コメント (0) | トラックバック (0)

2018年2月18日 (日)

Temp落ち #2 - PGA (Program Global Area)

Previously on Mac De Oracle
”Temp落ち”ってなに? 
という話と、それを確認できるツールをいくつか紹介したところまででした。


余談
このネタを書きながら、"Temp落ち"とは異なる理由で今回ネタにするメモリー領域の事を調べていた事を思い出しました。(懐かしい)
当時、自動PGA管理に関する情報があまりなく、新・ソートに関する検証 その1 ペンネーム グリーンペペを隅から隅まで読み試していました:)。その何年か後に、グリーンペペさんが、だれなのかを知ることに。。業界狭いですw
むか〜し、Windows(32bit)環境のOracleで、」ORA-12518が頻発していたトラブルの原因は今回のネタで取り上げるメモリー領域だった!なんてこともあるので、構造等、知ってて損はないですよ:)

では、今回のお話。

"Hash JoinやSortなどの処理をできる限りメモリー上で行う"という、そのメモリー領域とは???
PGA (Program Global Area)と呼ばれるメモリー領域のSQL Work Areaに確保されます。
また、それらのサイズを制御する初期化パラメータがあります。(後述)
PGAは、おおよそ以下のような構造で、サーバープロセスやバックグランドプロセス毎に確保されます。”ここ大切"
Pga

Pgas_processes2_2

Structure_of_pga




参考)
PGAのメモリーサイズを制御するパラメータは、以下の通り(隠しパラメータもありますが、とりあえずデフォルト前提で:)

以下の4パラメータは、手動PGA管理で利用されるパラメータで、自動PGA管理が主流となった今ではあまり利用されることはありません。(たま〜〜に使いたくなるときはありますが、結局使わなかったり、それは別の機会にでも書くことにします)

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

Oracle database 9.2.0以降、自動PGA管理が登場し、上記4パラメータで個別にサイズを管理する必要がなくなりました。
PGA_AGGREGATE_TARGET/PGA_AGGREATE_LIMITの2つのパラメータで自動PGA管理で行う方法に慣れておいた方が良いと思います。

余談
ちなみに、10gR1〜11gR2までは、PGA_AGGREGATE_TARGETパラメータだけだったのですが、その頃、自動PGA管理で割り当てられるサイズを検証していたネタは以下:) pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #8 http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2010/09/pga_aggregate-7.html

ところで、ひさびさに以下マニュアルを読んで見たのですが、何度見てもモヤモヤは消えない感じのままなのがつらいです。もう少しなんとかならんかなー。
と思わなくもないです...w 作業領域の割り当てルールを明記しないのは、何故なんだろうと、ズーーーーっと考えてる。書いてくれてれば楽なのにな〜と。

Oracle® Databaseパフォーマンス・チューニング・ガイド 12cリリース2 (12.2) E72901-03
16 プログラム・グローバル領域のチューニング
https://docs.oracle.com/cd/E82638_01/TGDBA/tuning-program-global-area.htm


Oracle® Databaseリファレンス 12cリリース2 (12.2) E72905-03
PGA_AGGREGATE_LIMIT
https://docs.oracle.com/cd/E82638_01/REFRN/PGA_AGGREGATE_LIMIT.htm

12.2からResource Managerを利用して特定のコンシューマ・グループ内の各セッションに割り当てられるようになったようで、 PGAメモリー使用量に絶対制限を設定し、超過した場合にはORA-10260エラーとさせることができるようになったようですね。(まだ試したことはないのですが)

16.3.2 リソース・マネージャを使用したプログラム・グローバル領域のサイズ設定
https://docs.oracle.com/cd/E82638_01/TGDBA/tuning-program-global-area.htm

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


12cの自動PGA管理について軽く確認した感触だと、12cR2でPDB毎の制御ができるようになった事以外、大きな変化ないんじゃないかと思っているのですが、そういえばちゃんと確認したことないな。。。
次回は、昔試した方法で、12cでもPGA割り当てルールに変化はないのか確認しておきますか。。。18cはどうなるんだろ:)


続く。


Temp落ち #1 - "Temp落ち" って?

| | コメント (0) | トラックバック (0)

2018年2月15日 (木)

Temp落ち #1 - "Temp落ち" って?

ぐるぐる系に並び、一部で人気?w のある俗称、

Temp_ochi


この、”Temp落ち”とはなんなのでしょうか?(ご存知の方も多いと思いますが)

こんなイメージです。
Optimal

Multipass
ざっくり、言ってしまうと、
Hash JoinやSort処理などを”メモリー上”で行おうとします。その際、利用可能なメモリー不足が発生すると一時表領域(HDDやSSDなど)を作業域としてして処理を行います。
この一時表領域も利用しながらHash JoinやSort処理などを行う動きが、"Temp落ち" の正体です。


なぜ可能な限りメモリー上で完結させようとするのか? 
たとえば、HDDを利用したソートとメモリー内のみで同量のソート行ったらどちらが早く終わると思いますか?
ということを考えれば、理由はわかりますよね。

ただ、最近はメモリーも安くなり、大容量のメモリーのサーバーもあり、ぜーんぶオンメモリーでできるじゃん?
と思わなくも無いのですが、

無限にメモリーを利用できるわけでもないく(仕様などにもよる)、データ量の爆発とともに、"Temp落ち”と戦わなければならない場面も多くなってきているような気はします。
また、"Temp落ち"の辛さが認識されていない場合は、大人の事情も絡まって予想以上にめんどくさい状況になることもしばしば。。。


そういえば、某性能問題専門チームへ本格的に参画しはじめたころの最初の戦いが、
”性能試験でTemp落ちして試験にならないというプロジェクトをなんとかする”というやつでしたw (もう7年ぐらい前ですがw)

その時期に調べていた内容をまとめていたのがエントリーが以下のシリーズでした。
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #8
http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2010/09/pga_aggregate-7.html


以下、津島博士のパフォーマンス講座も読んでおくとよいと思います。:)
参考)
津島博士のパフォーマンス講座 - 第36回 遅くなるSQLについて
http://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-36-2184118-ja.html

津島博士のパフォーマンス講座 - 第45回 Temp領域について

http://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-45-2491038-ja.html


ちなみに、AWRレポートやstatpackレポートなど、一時表領域が利用された"Temp落ち"の有無を確認する方法はいろいろあるのですが、代表的なものをいくつか載せておきますね。

ここで紹介する機能で利用したdatabase 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

12cR2のAWRレポートのTop 10 Foreground Eventsセクションより
インスタンス全体の状態を確認できます。
上位の待機イベントから、一時表領域が多く利用されていたことを知ることができます。
direct path write temp / direct path read tempという待機イベントが上位にきています。この2つの待機イベントは一時表領域への書き込みと読み込みを示す待機イベントです。
一時表領域に書き込まれたデータは、”どのようなタイプのデータ”であるか、この情報からでは判断できませんが、一時表領域を利用する操作が全体の6割近くを占めていることは確認できます。

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Total Wait Avg % DB Wait
Event Waits Time (sec) Wait time Class
------------------------------ ----------- ---------- --------- ------ --------
direct path write temp 8,688 36.9 4.24ms 50.3 User I/O
DB CPU 26 35.4
direct path read temp 8,403 6.2 738.19us 8.5 User I/O
direct path read 400 4.1 10.36ms 5.6 User I/O
db file sequential read 1,082 1.8 1.66ms 2.4 User I/O
control file sequential read 456 1.3 2.80ms 1.7 System I
SQL*Net message to client 66,674 .2 2.30us .2 Network
PGA memory operation 4,944 .1 25.30us .2 Other
log file sync 8 .1 10.55ms .1 Commit
db file scattered read 33 .1 1.69ms .1 User I/O

おなじく、12cR2のAWRレポートのPGA Aggr Target Histogramセクションより
インスタンス全体の状態を確認できます。1-Pass/M-Passの部分で一時表領域の利用した操作があったことが確認できます。
GB単位の1-Pass操作(一時表領域を利用した操作)があったことが確認できます。

PGA Aggr Target Histogram             DB/Inst: ORCL12C/orcl12c  Snaps: 214-216
-> Optimal Executions are purely in-memory operations

Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- -------------- ------------ ------------
2K 4K 879 879 0 0
64K 128K 12 12 0 0
128K 256K 14 14 0 0
256K 512K 8 8 0 0
512K 1024K 60 60 0 0
1M 2M 40 40 0 0
2M 4M 1 1 0 0
32M 64M 1 1 0 0
1G 2G 1 0 1 0
------------------------------------------------------

おなくじ、12cR2のAWRレポートのTop SQL with Top Row Sourcesより
以下の例では、SQLID=g95385r59bm47というSQL文の実行時間の内、50%以上がHash join操作でのdirect path write temp待機イベント(一時表領域への書き込み)であることがレポートされています。
このSQL文のHash Joinでメモリー内では処理しきれないほどの結合操作が行われたことがわかります。

Top SQL with Top Row Sources          DB/Inst: ORCL12C/orcl12c  Snaps: 214-216
-> Top SQL statements by DB Time along with the top row sources by DB Time
for those SQLs.
-> % Activity is the percentage of DB Time due to the SQL.
-> % Row Source is the percentage of DB Time spent on the row source by
that SQL.
-> % Event is the percentage of DB Time spent on the event by the
SQL executing the row source.
-> Executions is the number of executions of the SQL that were sampled in ASH.

SQL ID Plan Hash Executions % Activity
----------------------- -------------------- -------------------- --------------
% Row
Row Source Source Top Event % Event
---------------------------------------- ------- ----------------------- -------
Container Name
-------------------------------------------
g95385r59bm47 1822065247 1 100.00
HASH JOIN 66.67 direct path write temp 50.00
SELECT /*+ MONITOR LEADING(m1 m2) USE_HASH(m1 m2)
*/ * FROM m1 INNER JOIN m2 ON m1.id = m2.id AN
D m1.rev# = m2.rev#

AWRレポート以外では、
v$tempseg_usageを問い合わせることで一時表領域の利用状況をセッション、SQL毎に確認することもできます。
この例では、ブロックサイズは、8KBとしているので、Hash一時セグメントで、3GB程の一時表領域が利用されたことがわかります。

orcl12c@SYS> select con_id,session_num,username,sql_id,segtype,contents,blocks from v$Tempseg_usage order by session_num;

CON_ID SESSION_NUM USERNAME SQL_ID SEGTYPE CONTENTS BLOCKS
---------- ----------- ------------------------------ ------------- --------- --------- ----------
3 26123 SCOTT g95385r59bm47 HASH TEMPORARY 427520

SQLモニター - REPORT_SQL_MONITORファンクション
SQLモニターのMem(Max)/Temp(Max)列でHash joinやSort処理で利用された最大メモリーサイズと最大一時表領域のサイズを確認することができます。
この例ではId=1のHash JoinでTemp(Max) = 3GB より、一時表領域がHash Join操作の作業領域として3GB利用されたことを確認できます。
また、Activity Details列で、direct path write temp / direct path read temp待機イベントの割合も高いため一時表領域へのI/O量をどうするかが性能改善へのポイントであることも確認できます。

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 | | | | 86 | +2 | 1 | 1M | | | | | . | . | 9.52 | Cpu (6) |
| 1 | HASH JOIN | | 1M | 382K | 86 | +2 | 1 | 1M | 13302 | 3GB | 13302 | 3GB | 1GB | 3GB | 76.19 | Cpu (10) |
| | | | | | | | | | | | | | | | | direct path read temp (4) |
| | | | | | | | | | | | | | | | | direct path write temp (34) |
| 2 | TABLE ACCESS FULL | M1 | 1M | 90575 | 45 | +2 | 1 | 1M | 2619 | 3GB | | | . | . | 6.35 | Cpu (3) |
| | | | | | | | | | | | | | | | | direct path read (1) |
| 3 | TABLE ACCESS FULL | M2 | 1M | 90574 | 24 | +46 | 1 | 1M | 2619 | 3GB | | | . | . | 7.94 | Cpu (1) |
| | | | | | | | | | | | | | | | | direct path read (4) |
=======================================================================================================================================================================================

最後に、SQLモニターはTuning pack と Diagnostic packの両方が必要なのですが、追加パックなしで利用できる機能として、DBMS_XPLAN.DISPLAY_CURSORでActual Planを取得して確認する方法もあります。
DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL ALLSTATS LAST')でActual Planで確認することもできます。
Used-Mem/Used-Tmpから利用されたメモリーサイズと一時表領域のサイズが確認できます。
ただ、User_Tmpのサイズ単位は、KじゃなくてMなんじゃないか?(バグ? 誰か調べてw)
前述したv$tempseg_usageとSQLモニターで実行したSQL文なのですが....ほかにも単位が変なところがあるみたい。。。まあいいか。(いいわけ無い!w)

Plan hash value: 1822065247

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 382K(100)| | 1000K|00:00:56.65 | 727K| 1079K| 412K| | | | |
|* 1 | HASH JOIN | | 1 | 1000K| 4038M| 2126M| 382K (1)| 00:00:15 | 1000K|00:00:56.65 | 727K| 1079K| 412K| 2047M| 28M| 1049M (1)| 3340K|
| 2 | TABLE ACCESS FULL| M1 | 1 | 1000K| 2115M| | 90575 (1)| 00:00:04 | 1000K|00:00:06.79 | 374K| 333K| 0 | | | | |
| 3 | TABLE ACCESS FULL| M2 | 1 | 1000K| 1923M| | 90574 (1)| 00:00:04 | 1000K|00:00:01.68 | 352K| 333K| 0 | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQLモニター関連と、Actual Planを取得するサンプルスクリプト(私がよく利用してるやっつけスクリプト)
どちらを利用するかは、必要としている情報次第なのですが、利用できる環境であれば(主にライセンス)、SQLモニターとActual Planを併用することが多いです。(経験上)
SQLモニターは5秒以上の処理時間やパラレル処理を自動的にモニターできるので、利用可能な環境であれば、本番環境で今まさに終わらないSQLとなっているようなSQL文を分析するには便利です。
SQLIDは判明していても、終わらないSQL文の場合は、DBMS_XPLAN.DISPLAY_CORSORでActual Planを取得することは難しい(Actual Planの場合、SQLが正常終了しないと実行統計が取得できない)
ツール毎に、取得できる内容に多少違いがあるので、複数のツールを組み合わせて使うことが多い(ここ重要)


なお、SQLモニターは、対象とするSQL文に MONITORヒント付加することで強制的にモニタリングすることを前提にしてあります。
自動的にSQLモニターの対象となる条件は、5秒以上実行されているか、パラレル実行されている場合のみ

MONITORヒント付加を前提としており、パラメータを空で渡した場合は、直前に実行されたSQLモニター対象のSQLのモニター結果をテキスト形式でレポートします。(sql_idをパラメータとして渡した場合には対象のSQL文がSQLモニターの対象となっている必要があります)
SQLモニタースクリプト

[oracle@localhost ˜]$ cat show_realmon.sql
set linesize 1000
set long 1000000
set longchunksize 1000000
select dbms_sqltune.report_sql_monitor(sql_id=>'&1', type=>'text') from dual;

SQLモニタースクリプトの実行例

ORCL@SCOTT> select /*+ monitor */ * from dual;

D
-
X

Elapsed: 00:00:00.00
ORCL@SCOTT> @show_realmon ''
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

SQL Text
------------------------------
select /*+ monitor */ * from dual

Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SCOTT (123:34510)
SQL ID : 2w4nk70tv7w1d
SQL Execution ID : 16777216

・・・中略・・・

Fetch Calls : 1

Global Stats
=======================================
| Elapsed | Other | Fetch | Buffer |
| Time(s) | Waits(s) | Calls | Gets |
=======================================
| 0.00 | 0.00 | 1 | 3 |
=======================================

SQL Plan Monitoring Details (Plan Hash Value=272002086)
=========================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
=========================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | TABLE ACCESS FULL | DUAL | 1 | 2 | 1 | +0 | 1 | 1 | | |
=========================================================================================================================

Actual Plan取得スクリプト


ORCL@SCOTT> !cat show_actualplan.sql
set linesize 1000
set long 1000000
set longchunksize 1000000
select * from table(dbms_xplan.display_cursor(format=>'ALL ALLSTATS LAST'));

Actual Plan取得スクリプトの実行例

ORCL@SCOTT> alter session set statistics_level=all;

Session altered.

Elapsed: 00:00:00.01
ORCL@SCOTT> select * from dual;

D
-
X

Elapsed: 00:00:00.01
ORCL@SCOTT> @show_actualplan

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 1 | 2 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / DUAL@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "DUAL"."DUMMY"[VARCHAR2,1]


23 rows selected.

では、つづく。(どういうながれにするかまだ、考えてないけど、ブログ書かないとw)

| | コメント (0) | トラックバック (0)

2018年2月 8日 (木)

あなたの知らない世界? : Result Cacheが使われてるんです

Previously Mac De Oracle
なんて書こうとおもったら、もう一ヶ月以上空いてましたw

ブログの記事の書き込みも年度末です。
いろいろ急がないとw
(謎w

という大人の事情はさておき、


一昨年の今頃、 

CDBとPDBの間で迷子になりそう PART3 - containers clause - その2
なんてことを書いていました。


単に、Container clauseのことを書いていただけだったのですが、
その中で以下のようなことを書いていました。

"result cacheも使うようなので、そのあたりの待機イベントも多少気にかける必要もでてくるかもしれない。 と、とーくを見ながら..."

と気になった点も書いておきました。。。。。

実はこのあと、
こいつの洗礼を受けたので、忘れないうちにメモがわりに書いておきます。
(12cの機能の一部として実装されているので、いまのところ12cを利用している場合でないと遭遇することはないと思います。)

Container Clauseを利用した場合にかぎらず、以下で取り上げられているようなケースでは

https://blog.dbi-services.com/oracle-12c-cdb-metadata-a-object-links-internals/
https://blog.dbi-services.com/multitenant-internals-object-links-on-fixed-tables/


以下のようなRESULT_CACHEヒントが内部的に付加されて実行されているケースが多々見られます。(内部的に付加されます)

/*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */

このヒント、見ての通り結果キャッシュに強制的にキャッシュさせるためのヒントです。(見慣れない生存期間が指定されていますが、マニュアルには書かれていません)
https://blog.dbi-services.com/resultcache-hint-expiration-options/


注意しなければいけない点は、

あなたが意図していなくても、

結果キャッシュが利用されてしまう!


というところ。


参考)
15.2.1 サーバー結果キャッシュの構成
https://docs.oracle.com/cd/E49329_01/server.121/b71276/tune_result_cache.htm#CDEJCBII

なお、前述のヒント以外にも内部的に、RESULT_CACHEヒントを利用している機能があることが知られています。(12c)

マニュアルにも記載されていますが、結果キャッシュは共有プール内に確保されます。
”そこ”を”みんなで”共有するわけです。

共有するわけなので、みんなが結果をキャッシュ!!!! となると
結果キャッシュの管理作業も忙しくなっちゃうこともあるよね、と。

その結果は、待機イベントや処理遅いーーーという形で現れてくることになります。(結果キャッシュのサイズが小さすぎたりする影響などもあり)

ただ、今回紹介しているのは、”わたしたち”が意識的に利用しているものではないところが、ポイント。

/*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */

というヒント、
わたしたちが意図的に付加して、結果をキャッシュしようとしているわけではないので、
そもそも、結果キャッシュのことなんて、Out of 眼中 なのではないでしょうか。


でも、負荷試験してみたり、Oracle Databaseをいじめ倒していると、思わぬところで、以下の待機イベント高すぎて、

latch free (Event class : Others)
enq: RC - Result Cache: Contention (Event class : Application)

びっくり!!
いうことも、なくはないだろうなぁ。と思います。私もそうでしたw

(どちらかというと、Dynamic samplingがらみで発生している場合が多いようです。)

私が出会った

/*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */


2018/2/10追記
12.1.0.2では以下のヒントが付与されていました。
/*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */

12.2.0.1では、以下のヒントに変更されたようです。NO_STATEMENT_QUEUINGヒントでパラレル実行時のステートメントキューイング対象にならないようにしているようです。また、RESULT_CACHEヒントのオプションからSHELFFILEが除外されています。いずれにしても結果キャッシュは利用されるので、該当する待機イベントを多くみるようであればこの辺りのSQL文の実行が多くなっていないか観察してみるとよいかもしれません。

/*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) */


付きの内部SQLが多すぎて辛いって、ケースは少ないかもしれません。


大切なことは、あなたの意図しないところで、”RESULT CACHE"が使われている!ということを頭の片隅に置いておくこと!

です。


参考)↓↓↓↓↓

https://community.oracle.com/thread/3937748?start=15&tstart=0


High "Latch Free" Waits with Contention on 'Result Cache: RC Latch' when RESULT_CACHE_MODE = MANUAL on Oracle 12c (Doc ID 2002089.1)

| | コメント (0) | トラックバック (0)