« Oracle VM VirtualBox 6.0 でも治ってなかった、ドラッグするとDesktopが取り残される問題 / FAQ | トップページ

2019年3月25日 (月)

Temp落ち #10 - パーティションワイズジョインはどうよ

Temp落ちシリーズ、久々ですが、今回は、そもそもメモリーに乗り切らないなら、一時表領域に落ちる量をなんとかできないのか。。。というおはなし

pgaとして利用できるメモリーサイズには限界もありますし、その範囲のデータサイズで、ずーーーーーつと収まっていてくれていればいいですが、そうじゃない状況のほうが圧倒的に多いのではないでしょうか。
であれば、オンメモリーで処理させようなんて考えずに、ある程度落ちることは仕方ないと諦め、一時表領域の利用サイズを減らせないか。。。その方法の一つがパーティションワイズジョイン
ただし、パーティションを利用するので、Enterprise Editionでしか使えません。それ以外の場合は。。。別の手を考えるとか。。。方法は少ないですけど。。(それは別の機会にでも。。)


では、非パーティション表とパーティション表のパーティシィンワイズジョインのPGAの使い方の差異を確認してみましょう。
思いつきで作ったにしてもよくない例ではりますが(やっつけ感満載wで、セグメントサイズを稼いでぐための表定義となっています)PGAの利用サイズの変化は見ることができると思います。

非パーティション表の定義と統計情報取得、それに登録した各表のセグメントサイズは以下のとおり。

create table m
(
id number(10) not null
,foo varchar2(4000)
,bar varchar2(4000)
,constraint pk_m primary key(id) using index
);


create table d
(
id number(10) not null
,rev# number(3) not null
,foo varchar2(4000)
,bar varchar2(4000)
,constraint pk_d primary key(id, rev#) using index
);

SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'M',cascade=>true,no_invalidate=>false);
SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'D',cascade=>true,no_invalidate=>false);

SCOTT> select segment_name,bytes/1024/1024 "MB" from user_segments where segment_name in ('M','D')

SEGMENT_NAME MB
------------------------------ ----------
M 192
D 3766

1つの操作で利用可能なPGAサイズは約80MBにしてあります。この状態で上記の2表を結合すれば、いい感じで一時表領域へ落ちるはずです。

SCOTT> select * from v$pgastat;

NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ---------- ------------ ----------
・・・略・・・
global memory bound 83886080 bytes 0
・・・略・・・

2表をハッシュ結合するようにヒントで強制しています。
想定通り、PGAを使いきっても足りずに、580MBほど一時表猟奇が利用されています。

SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ MONITOR LEADING(m d) USE_HASH(m d) */ * FROM m INNER JOIN d ON m.id = d.id

Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SCOTT (302:21504)
SQL ID : 44pfxjgnt7bcq
SQL Execution ID : 16777216
Execution Started : 03/24/2019 09:40:48
First Refresh Time : 03/24/2019 09:40:48
Last Refresh Time : 03/24/2019 09:41:07
Duration : 19s
Module/Action : SQL*Plus/-
Service : orcl
Program : sqlplus@localhost.localdomain (TNS V1-V3)
Fetch Calls : 16001

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 |
===========================================================================================
| 11 | 4.41 | 5.53 | 1.28 | 16001 | 777K | 217K | 6GB | 2292 | 555MB |
===========================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1876521935)
=====================================================================================================================================================================================
| 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 | | | | 19 | +1 | 1 | 240K | | | | | . | . | 10.00 | Cpu (1) |
| 1 | HASH JOIN | | 240K | 232K | 19 | +1 | 1 | 240K | 2292 | 555MB | 2292 | 555MB | 84MB | 579MB | 60.00 | Cpu (1) |
| | | | | | | | | | | | | | | | | direct path write temp (5) |
| 2 | TABLE ACCESS FULL | M | 12000 | 6609 | 1 | +1 | 1 | 12000 | 4344 | 219MB | | | . | . | | |
| 3 | TABLE ACCESS FULL | D | 240K | 130K | 16 | +1 | 1 | 240K | 210K | 5GB | | | . | . | 30.00 | Cpu (1) |
| | | | | | | | | | | | | | | | | db file sequential read (2) |
=====================================================================================================================================================================================

パーティションワーズジョインが行われ、かつ、PGAにおさまりそうなサイズ以下になるようにハッシュパーティション化した表を作成し、同じデータ量を登録しました。
この例では8パーティションにしています。

表定義と時計情報取得及

create table m
(
id number(10) not null
,foo varchar2(4000)
,bar varchar2(4000)
,constraint pk_m primary key(id) using index
)
PARTITION BY HASH (id)
(
PARTITION m_p1
,PARTITION m_p2
,PARTITION m_p3
,PARTITION m_p4
,PARTITION m_p5
,PARTITION m_p6
,PARTITION m_p7
,PARTITION m_P8
);


create table d
(
id number(10) not null
,rev# number(3) not null
,foo varchar2(4000)
,bar varchar2(4000)
,constraint pk_d primary key(id, rev#) using index
)
PARTITION BY HASH (id)
(
PARTITION d_p1
,PARTITION d_p2
,PARTITION d_p3
,PARTITION d_p4
,PARTITION d_p5
,PARTITION d_p6
,PARTITION d_p7
,PARTITION d_P8
);

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'M',cascade=>true,no_invalidate=>false,granularity=>'ALL',degree=>4);
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'D',cascade=>true,no_invalidate=>false,granularity=>'ALL',degree=>4);


8分割のハッシュパーティション化したのでパーティション単位のセグメントサイズも随分小さくなりました。

SCOTT> select segment_name,partition_name,bytes/1024/1024 "MB" from user_segments where segment_name in ('M','D') order by 1,2;

SEGMENT_NAME PARTITION_NAME MB
------------------------------ ------------------------------ ----------
D D_P1 490
D D_P2 440
D D_P3 480
D D_P4 440
D D_P5 440
D D_P6 512
D D_P7 496
D D_P8 512
M M_P1 32
M M_P2 24
M M_P3 24
M M_P4 24
M M_P5 24
M M_P6 32
M M_P7 32
M M_P8 32


実行しているSQL文は同じですが、表はハッシュパーティションでぞれぞれ 1/8程度のサイズです。
結果を見ると、最大PGAサイズは、14MB、一時表領域はまだ利用されていますが、13MBと大きく削減できたことがわかると思います。
(パーティションワイズジョインの実行計画は独特なのでこの形を想えておいてください。NOTE等でパーティションワイズジョインが行われていることを示すコメント表示されません)

SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ MONITOR LEADING(m d) USE_HASH(m d) */ * FROM m INNER JOIN d ON m.id = d.id

Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SCOTT (302:21504)
SQL ID : 44pfxjgnt7bcq
SQL Execution ID : 16777217
Execution Started : 03/24/2019 09:49:55
First Refresh Time : 03/24/2019 09:49:55
Last Refresh Time : 03/24/2019 09:50:08
Duration : 13s
Module/Action : SQL*Plus/-
Service : orcl
Program : sqlplus@localhost.localdomain (TNS V1-V3)
Fetch Calls : 16001

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 |
============================================================================================
| 4.90 | 1.62 | 1.56 | 1.72 | 16001 | 774K | 12322 | 4GB | 278 | 33MB |
============================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1935477735)
======================================================================================================================================================================================
| 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 | | | | 14 | +0 | 1 | 240K | | | | | . | . | 25.00 | Cpu (1) |
| 1 | PARTITION HASH ALL | | 240K | 235K | 14 | +0 | 1 | 240K | | | | | . | . | | |
| 2 | HASH JOIN | | 240K | 235K | 14 | +0 | 8 | 240K | 278 | 33MB | 278 | 33MB | 14MB | 13MB | 25.00 | direct path write temp (1) |
| 3 | TABLE ACCESS FULL | M | 12000 | 7647 | 13 | +0 | 8 | 12000 | 384 | 189MB | | | . | . | | |
| 4 | TABLE ACCESS FULL | D | 240K | 131K | 14 | +0 | 8 | 240K | 11660 | 4GB | | | . | . | 50.00 | Cpu (1) |
| | | | | | | | | | | | | | | | | direct path read (1) |
======================================================================================================================================================================================


SQLモニターの結果からは明らかですが、v$sesstatからpga/ugaサイズが最大との程度利用されたか確認しておきます。(違いはパーティション化してパーティションワーズジョインしたかしないかという差です)
パーティションなし

       SID STATNAME                                                              VALUE
---------- ---------------------------------------------------------------- ----------
182 session uga memory 16561304
182 session uga memory max 90435464
182 session pga memory 26904760
182 session pga memory max 95717560


パーティション化 (8ハッシュパーティションにほぼ均一分割)

       SID STATNAME                                                              VALUE
---------- ---------------------------------------------------------------- ----------
182 session uga memory 17128696
182 session uga memory max 17250296
182 session pga memory 19564728
182 session pga memory max 19630264

temp落ちが辛い結合にであったら、ワーティション化とパーティションワイズジョインが利用できないか検証してみるのもありではないでしょうか?(EEに限りますが)


桜が咲き始めた、目黒川をランチタイムに散歩するか。。。

では、また。



Mac De Oracle なんですが、Windows(32bit)でのOracleな話
Mac De Oracle なんですが、Windows(32bit)でのOracleな話 #2
Mac De Oracle なんですが、Windows(32bit)でのOracleな話 #3
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #1
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #2
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #3
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #4
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #5
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #6
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #7
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #8
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #9
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #10
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #11
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #12
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #13
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #14
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #15
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #16
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #17
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #18
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #19
_pga_max_sizeってOracle11gではどうなったっけ? という確認。
_pga_max_sizeってOracle11gではどうなったっけ? という確認。シーズン2
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #1
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #2
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #3
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #4
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #5
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #6
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #7
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #8
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #8

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版
Temp落ち #7 - 自動PGA管理で到達可能な最大サイズ de Temp落ちの確認 12.2.0.1版
Temp落ち #8 - 自動PGA管理でパラメータ上設定可能な最大サイズは?(実際に利用可能なサイズとは限りませんが。意味深) 12.2.0.1版
Temp落ち #9 - 自動PGA管理で_pga_max_sizeと戯れたPGAサイズって本当に使えるのか? 12.2.0.1版

|

コメント

コメントを書く