2018年11月 4日 (日)

RDS Oracle 雑多なメモ#16 - 再び:) / FAQ

再び忘れがちなので、備忘録。

RDS Oracleでマスターユーザー以外で、SQL*PLusの Auto trace そして、DBMS_XPLAN.DISPLAY や DBMS_XPLAN.DISPLAY_CURSOR を使おうとすると以下のようなエラーに遭遇! 
なにも準備してないと。(explain plan for文だけは準備していなくても可能なのでが)


TEST> set autot trace exp stat
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

とか

...略...
TEST> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION

なんてことに、

RDS Oracle、マスターユーザーでは可能なのですが、PLUSTRACEロールも作成されていない、かつ、 plustrce.sql がない.

AWSUSER> select role from dba_roles where role = 'PLUSTRACE';

no rows selected


ということで、いちいち調べるのも面倒なFAQとなっているので、備忘録として書いておきました。

マスターユーザー以外のユーザーに alter session システム権限を付与しておきます。
該当システム権限が付与されていないと、セッションレベルで statistics_level パラメータを変更できません。
このパラメータは、dbms_xplan.display_cursor でactual planを取得する際に必要になるのですが、最悪付与されていない場合には、SQL文に以下のヒントを追加することで代替可能ではありますが、いちいちSQL文に以下のヒントを追加しなければならないので面倒。
とはいえ、alter sessionは付与したくないということもなくはなく、そんな時は以下のヒントで頑張っください。

ex.

SELECT
/*+ gather_plan_statistics */
*
FROM
hoge
WHERE
id = 1;


次に必要なのは、 v$sessionなどを含むいくつかのパフォーマンスビューへのSELECTオブジェクト権限、グローバルな一時表として定義されているplan_tableへの全オブジェクト権限です。
管理面を考えてロールを作成し関連権限をロールに付与、作成したロールを対象ユーザーに付与するようにすると便利です。


alter sessionsシステム権限を含む最低限必要なオブジェクト権限とそれを付与するロール作成スクリプトの例は以下の通り。

foobar$ cat create_dev_role.sql

-- create developer role
create role dev_role;

-- for show parameter
grant alter session to dev_role;
-- for dbms_xplan.display_cursor and auto trace
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SESSION', 'DEV_ROLE', 'SELECT');

-- for dbms_xplan.display_cursor
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL_PLAN_STATISTICS_ALL', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$PARAMETER', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL_PLAN', 'DEV_ROLE', 'SELECT');

-- for auto trace
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$STATNAME', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$MYSTAT', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SESSTAT', 'DEV_ROLE', 'SELECT');

-- for auto trace (plan_table - temporary table)
exec rdsadmin.rdsadmin_util.grant_sys_object('PLAN_TABLE$', 'DEV_ROLE', 'ALL');

参考
Oracle DB インスタンスの一般的な DBA タスク


ということで、動作確認を兼ねたサンプルは以下のとおり。

まずは、RDS Oracleのマスターユーザーで.
マスターユーザー以外のユーザーの作成と権限とロールの付与(チューニングが必要とされる開発者等)を想定

foobar$ sqlplus awsuser/xxxxxxx@xxxx.xxxxxxxx.rds.amazonaws.com:1521/HOGE

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 2 22:05:14 2018

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

Last Successful login time: Fri Nov 02 2018 22:01:24 +09:00

...中略...

AWSUSER> l
1 create user test identified by xxxxxx
2 default tablespace users
3 temporary tablespace temp
4* quota unlimited on users
AWSUSER> /

User created.

AWSUSER> grant connect, resource to test;

Grant succeeded.

AWSUSER> @create_dev_role

Role created.

Grant succeeded.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

AWSUSER> grant dev_role to test;

Grant succeeded.

AWSUSER> exit


作成したユーザーで接続して、各方法で実行計画を取得できるか確認!
クエリーを実行するため適当な表を作成してデータを登録しておく。

foobar$ sqlplus test/xxxxxxx@xxxx.xxxxxxxx.rds.amazonaws.com:1521/HOGE

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 2 22:15:23 2018

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

...中略...
TEST>
TEST> l
1 create table hoge (
2 id number not null primary key
3 ,foobar varchar2(20)
4* ) nologging
TEST> /

Table created.

TEST>
TEST> l
1 begin
2 for i in 1..10000 loop
3 insert into
4 hoge (
5 id
6 ,foobar
7 )
8 values (
9 i
10 ,to_char(i)
11 );
12 end loop;
13 commit;
14* end;
TEST> /

PL/SQL procedure successfully completed.


SQL*Plusのauto traceが行えるか確認!

TEST> set autot trace exp stat
TEST> select * from hoge where id = 1;

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 2757398040

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005687 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
342 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

参考
文のトレースについて


explain plan for文は準備なしで問題ないのですが、念のための確認! DBMS_XPLAN.DISPLAYプロシージャを利用して実行計画を取得

TEST> 
TEST> explain plan for
2 select * from hoge where id = 1;

explained.

TEST> @show_explain

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2757398040

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005687 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

14 rows selected.

Elapsed: 00:00:00.05

スクリプト例は以下の通り。$ORACLE_HOME/rdbms/admin/utlxpls.sql や、utlxplp.sqlが利用できれば楽なんでが、それら中身は、DBMS_XPLAN.DISPLAYなので大差ない内容。

$ cat show_explain.sql

set linesize 200
set long 100000
set longchunk 200
set tab off

SELECT
*
FROM
TABLE(
DBMS_XPLAN.DISPLAY(
format => 'ALL -PROJECTION -ALIAS'
)
)
;


Actual planをDBMS_XPLAN.DISPLAY_CURSORプロシージャで取得〜

TEST> 
TEST> @show_realplan

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL

Session altered.

*** SQL that you want to get an actual plan ***
1* select * from hoge where id = 1
***********************************************

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID 6f67zkz43kr76, child number 1
-------------------------------------
select * from hoge where id = 1

Plan hash value: 2757398040

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005687 | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

19 rows selected.

Elapsed: 00:00:00.11

Session altered.


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL

Actual planを取得するスクリプト例
SQL*Plusのコマンドを駆使してはいますが、ポイントはset statistics_level = allにすることと、DBMS_XPLAN.DISPLAY_CURSORをコールする際のformatパラメータです
set termout off/onでSQL文の結果を表示しないようにしています。この設定はSQLファイルからSQL文を実行した場合にだけ有効です。(ちょっとしたTips :)

$ cat show_realplan.sql

show parameter statistics_level
alter session set statistics_level = all;

set linesize 200
set long 100000
set longchunk 200
set tab off

PROMPT *** SQL that you want to get an actual plan ***

select * from hoge where id = 1
.
l

PROMPT ***********************************************

set termout off
r
set termout on


-- get the actual plan
set timi on
SELECT
*
FROM
TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
format => 'ALLSTATS LAST'
)
)
;
set timi off
alter session set statistics_level = typical;
show parameter statistics_level

参考
DBMS_XPLANサブプログラムの要約


Apple Store Ginzaで、息子が ”iPadでムービーを作ろう” に参加するので、その合間に、パタパタブログを書き、その足で英会話に向かう日曜日の午後w



Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ
RDS Oracle 雑多なメモ#7 / FAQ
RDS Oracle 雑多なメモ#8 / FAQ
RDS Oracle 雑多なメモ#9 / FAQ
RDS Oracle 雑多なメモ#10 / FAQ
RDS Oracle 雑多なメモ#11 / FAQ
RDS Oracle 雑多なメモ#12 / FAQ
RDS Oracle 雑多なメモ#13 / FAQ
RDS Oracle 雑多なメモ#14 - おまけ / FAQ
RDS Oracle 雑多なメモ#15 - おまけのおまけ / FAQ

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

2018年5月 2日 (水)

Oracle Database Connect 2018 エキスパートはどう考えるか? 体感!パフォーマンスチューニング Ⅱ (番外編=没ネタ)

Oracle Database Connect 2018 エキスパートはどう考えるか?体感!パフォーマンスチューニング Ⅱ
~Autonomous Databaseの到来において必要となるチューニングとは~

これ、パフォーマンスチューニングネタをまとめ上げるまで、みなさんスケジュール調整し、
オンライン/オフラインミーティングを繰り返してネタを詰めていくという、かなーり面倒なことをやっています。
私なんて、ネタの候補検討だけで16時間ぐらい使ってますからねw(自分の余暇を使って、半分楽しみながら、締め切りがあるので半分苦しみながらw)


余談はこれくらいにして、今日の本題です。:)

Oracle Database Connect 2018 エキスパートはどう考えるか? 体感!パフォーマンスチューニング Ⅱ で没にしたネタがあるのですが、
そのまま捨てるのも勿体ないので、多少取得情報を追加した状態で公開しちゃおうと思います。


お時間のありますときに、頭の体操、AWRレポートの解析方法(最近はADDM、ASHレポートまで含まれています)のトレーニングにどうぞ。

遅延原因の想定と、想定原因の特定情報は、このエントリでは公開しません。おそらくGW開けの12日あたりか、その一週間後の19日ごろには原因を公開しようかと:)の後半に追記してあります:)

なお、本ページに公開した情報以外に、追加で見たい情報がある場合は、コメント欄やtwのmentionで具体的にリクエストいただければ、取得されている情報の範囲内で追加公開します。取得されてない情報はその旨を追記していきます。:)
まずは、エスパー力全開で、原因を想定してみてください。>
(かなり難易度高めだとは思いますが、過去この状況に遭遇した経験をお持ちのかたは、ここに公開した程度の情報から勘で言い当てたりしちゃうんですよね。実は、昨年末に、何年か振りでこの症状を目にしたのでネタにしよう思いました。)

以下取得された情報は以下のとおり。以下の情報を元に遅延原因を想定してみてください。



なーんでだ?!


状況説明
試験時には問題のなかった処理(30分以内に終了する状態が正常な状態です。)をリリースしました。
ところが、30分以上経過しても終了しません。遅延原因を想定、特定してください。
なお、本番環境のメンテナンス時間中に試験を行ったため、データベースインスタンス内で試験を行いリリースしています。
ちなみに、試験時の処理時間は、約21分でしたが、リリース時は約56分と大幅に遅延。


他のトランザクションが走行している状況をミックスするとより
本物っぽくなり、特定に苦労するので面白いのですが、
遅延している処理だけが走行している状況にしてあります。


資料1 試験時(正常時)AWRレポート
資料2 リリース時(遅延時)AWRレポート
資料3 試験時(正常時)とリリース時(遅延時)のAWR DIFFレポート

資料4 試験時(正常時)CPU利用率グラフ(調査による多少のノイズあり)

1

資料5 リリース時(遅延時)CPU利用率グラグ(スパイク部分調査による多少のノイズあり)

2

GWも後半スタート、天気は少々荒れそうですが、みなさん、よいGWを!



Twitterでのやり取り....
20180506_82405
20180506_82353

ここまでくるとなにかが見えてきたかな。。。原因を特定するまであと一歩な感じ。

追加資料1(遅延時)
top

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
13058 oracle 20 0 10.434g 365704 359548 R 100.0 1.4 1:05.70 oracle_13058_or
5973 oracle 20 0 1955632 253648 73812 S 4.7 1.0 1:42.62 gnome-shell
4657 oracle -2 0 10.417g 58792 55660 S 2.0 0.2 0:12.15 ora_vktm_orcl12
1285 root 20 0 483544 76000 21372 S 1.7 0.3 0:55.11 Xorg
4862 oracle 20 0 10.417g 68960 65868 S 1.3 0.3 0:01.23 ora_lg00_orcl12
223 root 20 0 0 0 0 S 0.7 0.0 0:00.49 kworker/u24:3
13059 oracle 20 0 157976 4564 3544 R 0.7 0.0 0:00.29 top
...略...

追加資料2(遅延時)
sar -P ALL

   ...略...
02:47:29 AM CPU %user %nice %system %iowait %steal %idle
02:47:34 AM all 4.78 0.00 2.36 0.07 0.00 92.80
02:47:34 AM 0 1.06 0.00 1.06 0.85 0.00 97.03
02:47:34 AM 1 0.20 0.00 0.40 0.00 0.00 99.40
02:47:34 AM 2 0.00 0.00 0.00 0.00 0.00 100.00
02:47:34 AM 3 0.00 0.00 0.00 0.00 0.00 100.00
02:47:34 AM 4 0.00 0.00 0.20 0.00 0.00 99.80
02:47:34 AM 5 0.00 0.00 0.00 0.00 0.00 100.00
02:47:34 AM 6 0.00 0.00 0.00 0.00 0.00 100.00
02:47:34 AM 7 68.08 0.00 31.92 0.00 0.00 0.00
02:47:34 AM 8 0.00 0.00 0.00 0.00 0.00 100.00
02:47:34 AM 9 0.00 0.00 0.00 0.00 0.00 100.00
02:47:34 AM 10 0.00 0.00 0.00 0.00 0.00 100.00
02:47:34 AM 11 0.00 0.00 0.20 0.20 0.00 99.60
...略...

追加資料3(遅延時)
perf top -C 7

Samples: 107K of event 'cpu-clock', Event count (approx.): 29550214790                                                              
Overhead Shared Object Symbol
4.36% libc-2.17.so [.] vfprintf
2.77% oracle [.] lxoCpStr
2.00% [kernel] [k] __radix_tree_lookup
1.94% oracle [.] dbgfcsIlcsGetNextDef
1.58% oracle [.] skgovprint
1.48% [kernel] [k] __do_softirq
1.44% [kernel] [k] selinux_file_permission
1.43% oracle [.] dbgaFmtAttrCb_int
1.36% [kernel] [k] system_call_after_swapgs
1.33% oracle [.] dbgaAttrFmtProcArg
1.26% oracle [.] dbgtfmWriteMetadata
1.13% oracle [.] dbgtfdFileWrite
1.02% libc-2.17.so [.] _IO_default_xsputn
...略...



その後のやりとり (Twitter)

20180513_80620

そして核心に迫る追加資料が...あったんです。(しばちょうさん風にはしづらかったw)



正常時(試験時)の追加情報はないようです。リリース後は常に遅いらしい。

ふむふむ。

以下、遅延時のstrace -c -pの情報を取得してもらいました。
straceで眺めるとsystem call write()がダントツで上位にきています。その次が lseek()

追加資料4
strace -c -p

[oracle@localhost ˜]$ sudo strace -c -p 13058
Process 13058 attached
^CProcess 13058 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
77.24 47.883538 1 35085592 write
19.22 11.913638 1 17542850 lseek
3.53 2.191099 1 3414889 getrusage
0.01 0.006377 12 524 semop
0.00 0.000403 7 62 close
0.00 0.000210 4 54 chown
0.00 0.000126 2 63 open
0.00 0.000099 2 54 lstat
0.00 0.000050 1 54 chmod
0.00 0.000029 1 54 stat
0.00 0.000028 3 10 semtimedop
0.00 0.000015 0 54 fcntl
0.00 0.000010 1 8 read
0.00 0.000007 1 8 select
0.00 0.000000 0 3 mmap
0.00 0.000000 0 6 rt_sigprocmask
0.00 0.000000 0 1 rt_sigreturn
0.00 0.000000 0 1 readlink
------ ----------- ----------- --------- --------- ----------------
100.00 61.995629 56044287 total

そろそろ突き止めた感が。

なにかを、どこかに、書き出してますよね。

lsofで書き込みに絞って取得してもった結果は以下のとおり。

[root@localhost ˜]# lsof -p 13058 | grep -E '[0-9]+w'
oracle_13 13058 oracle 1w CHR 1,3 0t0 1038 /dev/null
oracle_13 13058 oracle 2w CHR 1,3 0t0 1038 /dev/null
oracle_13 13058 oracle 7w REG 8,17 2351882843 71176413 /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_13058.trc
oracle_13 13058 oracle 8w REG 8,17 358606101 71176414 /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_13058.trm

ほう。 .trcファイルへ大量の書き込みがあります。

このようになるときは、 なにが起っていると思いますか?

.trcファイルに大量の書き込みのあるケースとして、SQLトレースが有効になっている場合です。(意図したSQLトレースなら問題はないですが...意図していない場合はちょいと問題ですよね。)

ということで、確定診断に移りましょう。

SQL*Plus: Release 12.2.0.1.0 Production on Wed Apr 25 01:23:13 2018

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

Connected.
ORCL@SYSTEM>
ORCL@SYSTEM> select username,status,event,sql_trace from v$session where paddr = (select addr from v$process where spid = 13058)

USERNAME STATUS EVENT SQL_TRAC
------------------------------ -------- ---------------------------------------------------------------- --------
SCOTT ACTIVE log file switch completion ENABLED

見ての通り、
SQL_TRACE列がdisabled(デフォルト)からenabledに変わっていることから、.trcファイルへの大量の書き出しは、セッションレベルでSQLトレースが有効化されたため、と言えますよね。


今回の新規リリースバッチ処理遅延の原因は、

SQLトレースを有効化したままリリースしたしまったことにより、オーバーヘッドが発生し、グルグル系バッチ処理のスループットが低下したため! 

ということでした。SQLトレースを利用したあとの無効化をお忘れなく! :)


SQLトレースは便利な機能の一つですが、インスタンスレベルやグルグル系バッチ処理での利用時は大量のトレース情報の書き出し等によるオーバーヘッドが発生します。
便利なツールではありますが、スループットの低下等による影響を考慮して利用するタイプのツールでもあります。
また、利用した後は、必ず、無効化することもお忘れなく...

ミイラ取りがミイラになってしまってはいみないですから.....ね :)

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

2018年3月29日 (木)

Oracle JavaScript Extention Toolkit(JET) de ブラウザの性能比較

なんだか余裕ないので、ちょいと、息抜きw

Oracle JavaScript Extention Toolkit(JET)
Oracle JET

さあ、あなたのIE11とそれ以外のモダンブラウザで 10,000 nodesを描画してPerformanceを比較してみましょう!。
Oracle JET > Visualizations > Diagram > Performance

以下、Safari 11.3 (macOS High Sierra/2 x 2.4 GHz 6-Core Intel Xeon/Mac Pro Mid(2012))
20180329_225621


こちらも、どうぞ。
Edgeも出たし、JetStreamも出たので久々にブラウザのベンチマーク

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

2018年3月18日 (日)

Temp落ち #9 - 自動PGA管理で_pga_max_sizeと戯れたPGAサイズって本当に使えるのか? 12.2.0.1版

Previously on Mac De Oracle

自動PGA管理下で、_pga_max_size隠しパラメータとpga_aggregate_targetを最大値に設定するした場合、global memory boundは
最大で、約839GBまで増加することがわかりました。

ただ、これは、内部的なパラメータ上の話。


今日は、実際にソートやハッシュ結合を行なわせ、PGAのSQL Work Areaサイズがどこまで利用できるものなのか確認してみることにします。

さて、どういう結末になりますやら(w

随分前から同じようなことを試している方はいますし....:)

なぜ今そこをdiggingしちゃってるのか? って?

メモリーたっぷりあるのに、何故Temp落ち? が話題になったからに決まってるじゃないですかw

まず、デフォルト設定では最大サイズだった 1GB を超えられるか? の確認

注意)
事前にpga_aggregate_target および _pga_max_size をそれぞれ 4TB - 1に設定し、パラメータの上では、global memory boundが約839GBにしてあります。

なお、Temp落ちの確認データやスクリプトはTemp落ち #4 - 手動PGA管理で作業領域として指定可能な最大サイズ de Temp落ちの確認のエントリーを参照のこと。

ソートやハッシュ結合は2GB程度になるように調整しています。
2GBのソート

ORCL@SCOTT> @auto_sortwk2gb_optimal.sql

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 4398046511103

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 4398046511103

NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ---------------- ------------ ----------
global memory bound 879609302016 bytes 0

1 SELECT
2 /*+
3 MONITOR
4 */
5 *
6 FROM
7 m1
8 WHERE
9 id <= 'C750000'
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

・・・中略・・・

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 | | | | 34 | +2 | 1 | 1M | | | . | 5.26 | Cpu (1) |
| 1 | SORT ORDER BY | | 1M | 553K | 34 | +2 | 1 | 1M | | | 2GB | 26.32 | Cpu (5) |
| 2 | TABLE ACCESS FULL | M1 | 1M | 90829 | 17 | +1 | 1 | 1M | 2637 | 3GB | . | 68.42 | Cpu (3) |
| | | | | | | | | | | | | | direct path read (10) |
========================================================================================================================================================


2GBのハッシュ結合

ORCL@SCOTT> @auto_hashwk2gb_optimal.sql

・・・中略・・・

NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ------------- ------------ ----------
global memory bound 879609302016 bytes 0

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 <= 'C084000'
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 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 | | | | 37 | +2 | 1 | 840K | | | . | 4.35 | Cpu (1) |
| 1 | HASH JOIN | | 843K | 351K | 37 | +2 | 1 | 840K | | | 2GB | 26.09 | Cpu (5) |
| | | | | | | | | | | | | | PGA memory operation (1) |
| 2 | TABLE ACCESS FULL | M1 | 843K | 90828 | 15 | +1 | 1 | 840K | 2633 | 3GB | . | 52.17 | Cpu (1) |
| | | | | | | | | | | | | | direct path read (11) |
| 3 | TABLE ACCESS FULL | M2 | 846K | 90581 | 23 | +16 | 1 | 840K | 2619 | 3GB | . | 17.39 | Cpu (2) |
| | | | | | | | | | | | | | direct path read (2) |
===========================================================================================================================================================


ソートやハッシュ結合は4GB程度になるように調整しています。
見ての通り、ソートは4GBのメモリー内ソートですが、ハッシュ結合は、2GBまで使ったところで Temp落ち! (こんなもんなんですよ。実は!)
4GBのソート

ORCL@SCOTT> @auto_sortwk4gb_optimal.sql

・・・中略・・・

NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ------------- ------------ ----------
global memory bound 879609302016 bytes 0

1 SELECT
2 /*+
3 MONITOR
4 */
5 *
6 FROM
7 m1
8 WHERE
9 id <= 'C085000'
10 UNION ALL
11 SELECT *
12 FROM
13 m1 m12
14 WHERE
15 id <= 'C085000'
16 ORDER BY
17* 1, 2
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 Plan Monitoring Details (Plan Hash Value=2686238998)
============================================================================================================================================================
| 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 | | | | 40 | +2 | 1 | 2M | | | . | 27.78 | Cpu (5) |
| 1 | SORT ORDER BY | | 2M | 182K | 41 | +1 | 1 | 2M | | | 4GB | 38.89 | Cpu (6) |
| | | | | | | | | | | | | | PGA memory operation (1) |
| 2 | UNION-ALL | | | | 12 | +2 | 1 | 2M | | | . | | |
| 3 | TABLE ACCESS FULL | M1 | 855K | 90828 | 6 | +2 | 1 | 850K | 2633 | 3GB | . | 22.22 | Cpu (2) |
| | | | | | | | | | | | | | direct path read (2) |
| 4 | TABLE ACCESS FULL | M1 | 855K | 90828 | 6 | +8 | 1 | 850K | 2633 | 3GB | . | 11.11 | Cpu (2) |
============================================================================================================================================================


4GBのハッシュ結合
最大3GBのPGAが消費されていますが、Temp落ちしないサイズ、つまり、optimalで処理する場合には、最大2GBが最大サイズとなっています。以下を3GBのハッシュ結合にすると、2GBまでPGAを消費し、一時表領域が3GB利用されます。

ORCL@SCOTT> @auto_hashwk4gb_optimal.sql
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 Plan Monitoring Details (Plan Hash Value=3532417599)
=========================================================================================================================================================================================
| 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 | | | | 216 | +2 | 1 | 3M | | | | | . | . | 4.49 | Cpu (7) |
| 1 | HASH JOIN | | 2M | 670K | 217 | +1 | 1 | 3M | 18091 | 4GB | 18091 | 4GB | 3GB | 4GB | 79.49 | Cpu (87) |
| | | | | | | | | | | | | | | | | direct path read temp (19) |
| | | | | | | | | | | | | | | | | direct path write temp (18) |
| 2 | VIEW | | 2M | 182K | 9 | +2 | 1 | 2M | | | | | . | . | | |
| 3 | UNION-ALL | | | | 9 | +2 | 1 | 2M | | | | | . | . | | |
| 4 | TABLE ACCESS FULL | M1 | 752K | 90828 | 5 | +2 | 1 | 750K | 2633 | 3GB | | | . | . | 1.92 | Cpu (3) |
| 5 | TABLE ACCESS FULL | M1 | 752K | 90828 | 4 | +7 | 1 | 750K | 2633 | 3GB | | | . | . | 1.92 | Cpu (3) |
| 6 | VIEW | | 2M | 181K | 78 | +99 | 1 | 2M | | | | | . | . | 0.64 | Cpu (1) |
| 7 | UNION-ALL | | | | 78 | +99 | 1 | 2M | | | | | . | . | | |
| 8 | TABLE ACCESS FULL | M2 | 759K | 90581 | 50 | +92 | 1 | 750K | 2618 | 3GB | | | . | . | 9.62 | Cpu (2) |
| | | | | | | | | | | | | | | | | direct path read (13) |
| 9 | TABLE ACCESS FULL | M2 | 759K | 90581 | 36 | +141 | 1 | 750K | 2618 | 3GB | | | . | . | 1.92 | Cpu (2) |
| | | | | | | | | | | | | | | | | direct path read (1) |
=========================================================================================================================================================================================


では、最後に
ソートやハッシュ結合のサイズが8GB程度ならどうでしょうか?
結果は、ソートは4GBを使い切ったのち、Temp落ち、ハッシュ結合は、やはり、3GBまで利用したのちTemp落ちでした。なんと!(ちょっとわざとらしいリアクションしてすみませんw)

8GBのソート

ORCL@SCOTT> @auto_sortwk8gb_optimal.sql

・・・中略・・・

NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ------------- ------------ ----------
global memory bound 879609302016 bytes 0

1 SELECT
2 /*+
3 MONITOR
4 */
5 *
6 FROM
7 m1
8 WHERE
9 id <= 'C090000'
10 UNION ALL
11 SELECT *
12 FROM
13 m1 m12
14 WHERE
15 id <= 'C090000'
16 UNION ALL
17 SELECT *
18 FROM
19 m1 m13
20 WHERE
21 id <= 'C090000'
22 UNION ALL
23 SELECT *
24 FROM
25 m1 m14
26 WHERE
27 id <= 'C090000'
28 ORDER BY
29* 1, 2
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 Plan Monitoring Details (Plan Hash Value=946172832)
=========================================================================================================================================================================================
| 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 | | | | 279 | +2 | 1 | 4M | | | | | . | . | 5.79 | Cpu (6) |
| | | | | | | | | | | | | | | | | PGA memory operation (1) |
| | | | | | | | | | | | | | | | | local write wait (7) |
| 1 | SORT ORDER BY | | 4M | 363K | 279 | +2 | 1 | 4M | 38603 | 8GB | 32072 | 8GB | 4GB | 8GB | 84.30 | Cpu (49) |
| | | | | | | | | | | | | | | | | PGA memory operation (1) |
| | | | | | | | | | | | | | | | | direct path read temp (28) |
| | | | | | | | | | | | | | | | | direct path write temp (126) |
| 2 | UNION-ALL | | | | 176 | +2 | 1 | 4M | | | | | . | . | | |
| 3 | TABLE ACCESS FULL | M1 | 907K | 90828 | 16 | +1 | 1 | 900K | 2633 | 3GB | | | . | . | 4.55 | Cpu (2) |
| | | | | | | | | | | | | | | | | direct path read (9) |
| 4 | TABLE ACCESS FULL | M1 | 907K | 90828 | 8 | +17 | 1 | 900K | 2633 | 3GB | | | . | . | 2.48 | Cpu (5) |
| | | | | | | | | | | | | | | | | direct path read (1) |
| 5 | TABLE ACCESS FULL | M1 | 907K | 90828 | 79 | +26 | 1 | 900K | 2633 | 3GB | | | . | . | 2.48 | Cpu (2) |
| | | | | | | | | | | | | | | | | direct path read (4) |
| 6 | TABLE ACCESS FULL | M1 | 907K | 90828 | 72 | +106 | 1 | 900K | 2633 | 3GB | | | . | . | 0.41 | Cpu (1) |
=========================================================================================================================================================================================

8GBのハッシュ結合

ORCL@SCOTT> @auto_hashwk8gb_optimal.sql

・・・中略・・・

NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ------------- ------------ ----------
global memory bound 879609302016 bytes 0

1 SELECT
2 /*+
3 MONITOR
4 LEADING(m1 m2)
5 USE_HASH(m1 m2)
6 */
7 *
8 FROM
9 (
10 SELECT * FROM m1 m11
11 UNION ALL
12 SELECT * FROM m1 m12
13 UNION ALL
14 SELECT * FROM m1 m13
15 ) m1
16 INNER JOIN
17 (
18 SELECT * FROM m2 m21
19 UNION ALL
20 SELECT * FROM m2 m22
21 UNION ALL
22 SELECT * FROM m2 m23
23 ) m2
24 ON
25 m1.id = m2.id
26 AND m1.rev# = m2.rev#
27 WHERE
28* 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

・・・中略・・・

SQL Plan Monitoring Details (Plan Hash Value=2506347387)
=========================================================================================================================================================================================
| 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 | | | | 350 | +2 | 1 | 7M | | | | | . | . | 9.18 | Cpu (18) |
| | | | | | | | | | | | | | | | | PGA memory operation (1) |
| 1 | HASH JOIN | | 5M | 1M | 350 | +2 | 1 | 7M | 31032 | 7GB | 31032 | 7GB | 3GB | 8GB | 67.15 | Cpu (81) |
| | | | | | | | | | | | | | | | | direct path read temp (36) |
| | | | | | | | | | | | | | | | | direct path write temp (22) |
| 2 | VIEW | | 2M | 272K | 27 | +2 | 1 | 2M | | | | | . | . | | |
| 3 | UNION-ALL | | | | 27 | +2 | 1 | 2M | | | | | . | . | 0.97 | Cpu (2) |
| 4 | TABLE ACCESS FULL | M1 | 752K | 90828 | 16 | +1 | 1 | 750K | 2633 | 3GB | | | . | . | 7.25 | Cpu (3) |
| | | | | | | | | | | | | | | | | direct path read (12) |
| 5 | TABLE ACCESS FULL | M1 | 752K | 90828 | 5 | +18 | 1 | 750K | 2633 | 3GB | | | . | . | 1.45 | Cpu (2) |
| | | | | | | | | | | | | | | | | direct path read (1) |
| 6 | TABLE ACCESS FULL | M1 | 752K | 90828 | 7 | +23 | 1 | 750K | 2633 | 3GB | | | . | . | 1.93 | Cpu (2) |
| | | | | | | | | | | | | | | | | direct path read (2) |
| 7 | VIEW | | 2M | 272K | 130 | +97 | 1 | 2M | | | | | . | . | | |
| 8 | UNION-ALL | | | | 130 | +97 | 1 | 2M | | | | | . | . | 0.48 | Cpu (1) |
| 9 | TABLE ACCESS FULL | M2 | 759K | 90581 | 47 | +88 | 1 | 750K | 2618 | 3GB | | | . | . | 4.83 | Cpu (3) |
| | | | | | | | | | | | | | | | | direct path read (7) |
| 10 | TABLE ACCESS FULL | M2 | 759K | 90581 | 41 | +134 | 1 | 750K | 2618 | 3GB | | | . | . | 4.35 | Cpu (5) |
| | | | | | | | | | | | | | | | | direct path read (4) |
| 11 | TABLE ACCESS FULL | M2 | 759K | 90581 | 53 | +174 | 1 | 750K | 2618 | 3GB | | | . | . | 2.42 | Cpu (1) |
| | | | | | | | | | | | | | | | | direct path read (4) |
=========================================================================================================================================================================================


これ、Linuxのカーネルパラメータのvm.max_map_count

/proc/sys/vm/max_map_count

65530

が絡んでるよねという話と、隠しパラメータの realfree_heap関連のパラメータでも調整できそうだよね。という話はあるんですが...それはPL/SQLのはなし...で


_realfree_heap_pagesize 65536 TRUE
_use_realfree_heap TRUE TRUE

とりあえず、
vm/max_map_countを196608

にして
_realfree_heap_pagesize=65536 や
_realfree_heap_pagesize=256K

などとして戯れてみましたが

ソートやハッシュ結合で利用可能なサイズは、それらで制御できるものでもありません。いまのところ。

ORCL@SCOTT> @auto_sortwk8gb_optimal.sql

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 4398046511103

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 4398046511103

NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ------------- ------------ ----------
global memory bound 879609302016 bytes 0

/proc/sys/vm/max_map_count
196608

1 SELECT
2 /*+
3 MONITOR
4 */
5 *
6 FROM
7 m1
8 WHERE
9 id <= 'C090000'
10 UNION ALL
11 SELECT *
12 FROM
13 m1 m12
14 WHERE
15 id <= 'C090000'
16 UNION ALL
17 SELECT *
18 FROM
19 m1 m13
20 WHERE
21 id <= 'C090000'
22 UNION ALL
23 SELECT *
24 FROM
25 m1 m14
26 WHERE
27 id <= 'C090000'
28 ORDER BY
29* 1, 2
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 Plan Monitoring Details (Plan Hash Value=946172832)
=========================================================================================================================================================================================
| 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 | | | | 295 | +2 | 1 | 4M | | | | | . | . | 3.38 | Cpu (8) |
| 1 | SORT ORDER BY | | 4M | 363K | 297 | +1 | 1 | 4M | 38603 | 8GB | 38600 | 8GB | 4GB | 8GB | 91.56 | Cpu (43) |
| | | | | | | | | | | | | | | | | direct path read temp (12) |
| | | | | | | | | | | | | | | | | direct path write temp (162) |
| 2 | UNION-ALL | | | | 205 | +2 | 1 | 4M | | | | | . | . | 0.84 | Cpu (2) |
| 3 | TABLE ACCESS FULL | M1 | 907K | 90828 | 6 | +2 | 1 | 900K | 2633 | 3GB | | | . | . | 2.11 | Cpu (1) |
| | | | | | | | | | | | | | | | | direct path read (4) |
| 4 | TABLE ACCESS FULL | M1 | 907K | 90828 | 7 | +8 | 1 | 900K | 2633 | 3GB | | | . | . | 0.42 | Cpu (1) |
| 5 | TABLE ACCESS FULL | M1 | 907K | 90828 | 100 | +14 | 1 | 900K | 2633 | 3GB | | | . | . | 0.84 | Cpu (2) |
| 6 | TABLE ACCESS FULL | M1 | 907K | 90828 | 94 | +113 | 1 | 900K | 2633 | 3GB | | | . | . | 0.84 | Cpu (2) |
=========================================================================================================================================================================================


ちなみに、PL/SQLで巨大なPGAメモリーを利用する場合は、しっかり9GBとか...やばい状態になったら、pga_aggregate_limitで抑えてくれると思いますが.....

[oracle@localhost ˜]$ ulimit -v -m
virtual memory (kbytes, -v) unlimited
max memory size (kbytes, -m) unlimited

orcl12c@SYS> @show_param

KSPPINM KSPPSTVL KSPPSTDF
---------------------------------------------- ------------------------------ ------------------------------
_pga_max_size 4398046511103 FALSE
_realfree_heap_pagesize 65536 TRUE
_use_realfree_heap TRUE TRUE
pga_aggregate_limit 0 FALSE
pga_aggregate_target 4398046511103 FALSE


/proc/sys/vm/max_map_count
65530

PGAを大量に消費するスクリプトは以下のブログを参考に
Max PGA Research: Check that a process can allocate a large volume of memory / Yury's Blog


ORCL@SCOTT> @plsql_pga8gb 8192
old 2: c_count number := 1024*&1;
new 2: c_count number := 1024*8192;

PL/SQL procedure successfully completed.

実際に確保されたPGAサイズなど。。。

ORCL@SCOTT> 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%' or vsn.name like '%uga%')
12 where
13 sid IN (select sid from v$session where username='SCOTT')
14 order by
15* sid,name

GB NAME SID
---------- ---------------------------------------------------------------- ----------
9.49698084 session pga memory 321
9.49698084 session pga memory max 321
1.48221001 session uga memory 321
3.99991362 session uga memory max 321

[oracle@localhost ˜]$ free -h
total used free shared buff/cache available
Mem: 22G 1.0G 9.6G 11G 11G 10G
Swap: 4.0G 8.7M 4.0G

・・・中略・・・

[oracle@localhost ˜]$ free -h
total used free shared buff/cache available
Mem: 22G 10G 128M 11G 11G 705M
Swap: 4.0G 8.8M 4.0G


ここまでくればおわかりだと思いますが、global memory bound (約839GB)からは巨大なPGAサイズになりそうに思えますが、Temp落ちせずにソート可能なサイズは、隠しパラメータを調整した場合でも最大4GB、ハッシュ結合は、2GBが上限、となっています。いまのところ。。。。

ということは、2014年の以下のプレゼンでも話題になっていましたよね。:)
Overcome Oracle PGA Memory Limits Mar.5.2014 Alex Fatkulin / Enkatic

だったら、どのようにして、Temp落ち に立ち向かえばいいんだろう.....次回へ続く :)




10gR2(64bit)のころのままなので、以下のシリーズも合わせて読んでおくといいですよ:)

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


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版

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

2018年3月12日 (月)

Temp落ち #8 - 自動PGA管理でパラメータ上設定可能な最大サイズは?(実際に利用可能なサイズとは限りませんが。意味深) 12.2.0.1版

Previously on Mac De Oracle

自動PGA管理下では、SQL Work Areaサイズは、pga_aggregate_targetのサイズに応じて変化し、最大1GBまで調整され、それのサイズ以上のソートやハッシュ結合は、もれなく、"Temp落ち" する。
手動PGA管理下では、最大2GBまで指定できました。。自動だと1GBまでなのか。。。と残念がる声が、昔は海だった方面から聞こえた気がしましたが、たぶん、気のせいw

というところまででした。


今日は、自動PGA管理下ではそれが最大なのか? のか? 自己責任で試してみることにしますw

まずはこれまでの復習。

自動PGA管理で、SQL Work Areaサイズの算出に関わるパラメータは以下の通り

pga_aggregate_target = 10MB 〜 4TB - 1
_pga_max_size = 200MB 〜 2GB
Oracleが動的に値を調整している_pga_max_sizeパラメータへユーザーが値を設定してしまうとOracleの自動調整が無効化され、設定した値で固定されてしまうので注意が必要です。

私の観測範囲だとおおよそ以下のように変化します。最近は大量のメモリーを搭載したサーバーが多いので、pga_aggregate_targetが10GB以上という状況も普通になってきたので、自動PGA管理下のSQL Work Areaサイズは最大1GBとざっくり丸暗記しても困ることは思いますw(ちょっと乱暴かw)

pga_aggregate_target = 10MB〜10GB - 1 :
_pga_max_size = 200MB 〜 2047MB
GREATEST(pga_aggregate_target*0.2 ,200MB)

pga_aggregate_target = 10GB以上〜4TB-1 :
_pga_max_size = 2GB
LEAST(pga_aggregate_target*0.2 ,2GB)


_pga_max_sizeはpga_aggregate_targetの値に応じて動的に変化し、それらを元に _smm_max_size が算出される
LEAST(pga_aggregate_target * 0.2, _pga_max_size * 0.5)

pga_aggregate_target = 10MB〜10GB-1
_pga_max_size = 200MB〜2047MB
_smm_max_size = 2MB〜1023MB


pga_aggregate_target = 10GB〜4TB-1
_pga_max_size = 2048MB
_smm_max_size = 1024MB
_smm_max_sizeは、v$pgastatのglobal memory boundだろうということは確からしいということまでは確認しました。

20180307_143308
20180307_143330
いままでの結果をまとめると
PGAのSQL Work Areaサイズの最大サイズを示す global memory boundは、pga_aggregate_targetが10MB〜10GB-1までは2MB〜1023MBで調整され、pga_aggregate_targetが10GB〜4TB-1では、1024MB (1GB) で固定されている、というのが、Oracle 10GR2〜12cR2まで動きであることは間違いなさそう:)

自動PGA管理下でSQL Work Areaを1GBを超えるサイズにするにはどのパラメータをどのような値に設定すればよいか。。。
その鍵を握るパラメータは

_pga_max_size

(いまいちピンとこないという方は、過去のエントリーを参照していただけるとスッキリすると思います。

ただ、冒頭にも書きましたが(昔それでハマったw)、(Oracleが動的に値を調整している_pga_max_sizeパラメータへユーザーが値を設定してしまうとOracleの自動調整が無効化され、設定した値で固定されてしまうので注意が必要です。
という点はお忘れなく。また、今後も同仕様のままである保証はなく、世界中のOracle使いの方達が調べ上げた結果、現状はこんな感じ。
という状況だと認識しておいたほうが無難だと思っています。

とはいえ、実際どこまで設定可能なのか?  1GBを以上使えるのか? 知りたいですよね...

長い前置きはこれぐらいにして、
実際にSQL Work Areaサイズに対応したメモリーが実際にどの程度割り当てられるのかということの確認は後回しですが、 パラメータの上では、どこまで設定できるのかというところを確認しました。


なお、
以下のエラーメッセージから、_pga_max_sizeに設定可能な値は、pga_aggregate_targetと同様(Big Integer)、10MB〜4TB-1までであると思われます。

orcl12c@SYS> show parameter _pga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 2G

orcl12c@SYS> alter system set "_pga_max_size" = 4T scope=memory;
alter system set "_pga_max_size" = 4T scope=memory
*
行1でエラーが発生しました。:
ORA-02097: 指定した値が無効なので、パラメータを変更できません。 ORA-00093:
_pga_max_sizeは、10Mから4096G-1の間に設定する必要があります。

ということで、
_pga_max_sizeを1GB/10GB/100GB/1TB/4T-1のそれぞれに設定したうえで、Temp落ち #6 - pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? 12.2.0.1版 で利用したスクリプトを利用し、global memory boundやその他パラメータの変化を確認しました。

ログの例

orcl12c@SYS> alter system set "_pga_max_size" = 10m scope=both;

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

orcl12c@SYS> show parameter _pga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 10m


orcl12c@SYS> @dotest_auto_workarea
<< 10m >>
接続されました。
1* alter system set pga_aggregate_target=&1 scope=spfile
旧 1: alter system set pga_aggregate_target=&1 scope=spfile
新 1: alter system set pga_aggregate_target=10m scope=spfile

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

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

Total System Global Area 2147483648 bytes
Fixed Size 8794848 bytes
Variable Size 603983136 bytes
Database Buffers 1526726656 bytes
Redo Buffers 7979008 bytes
データベースがマウントされました。
データベースがオープンされました。
+++ initial parameters +++

KSPPINM KSPPSTVL KSPPSTDF
---------------------------------------------- ------------------------------ ------------------------------
__pga_aggregate_target 16777216 FALSE

・・・中略・・・

pga_aggregate_limit 0 FALSE
pga_aggregate_target 10485760 FALSE

50行が選択されました。

/proc/sys/vm/max_map_count

65530

+++ v$pgastat +++

NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ---------- ------------ ----------
aggregate PGA target parameter 10 MB 0
aggregate PGA auto target 4 MB 0
global memory bound 2 MB 0

・・・中略・・・


結果は以下のグラフと表にまとめたとおり。
_pga_max_size=4TB-1、pga_aggregate_target=4TB-1の時のglobal memory bound (v$pgastat) = _smm_max_size が最大となり、約839GBとなることがわかりました。
パラメータ上は839GB程度まで増加しますが、ほんとうにPGAがそんなサイズまで利用可能なのでしょうか?....(怪しいです。答え、知ってるんですけどねw。。

1g
10g
100g
1t
4t1
Globalmemorybound

ということで、

次回は実際に、そんなに使えんのかよ! という実験をしてみたいと思います :)

つづく。




10gR2(64bit)のころのままなので、以下のシリーズも合わせて読んでおくといいですよ:)
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


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版


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

2018年3月 8日 (木)

Temp落ち #7 - 自動PGA管理で到達可能な最大サイズ de Temp落ちの確認 12.2.0.1版

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落ちしない程度のデータ量にしています。)
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(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 <= '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) |
==================================================================================================================================================


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

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(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 <= '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) | トラックバック (0)

2018年3月 7日 (水)

Temp落ち #6 - pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? 12.2.0.1版

Previously on Mac De Oracle
pga_aggregate_target scope=memory or bothでそれまでとはことなる動きとエラーは発生するところの話でした。


きょうは、自動PGA管理下ではどうなるか確認しておきます。
以前、簡単に確認した範囲結果から、これまでの仕様と大きな違いはないとみています。(変わっていないと思っていたのでブログでも書いていなかったのですが、"Temp落ち"ネタの準備運動も兼ね現状を確認しながら進めてみたいと思います)


まず環境情報から、
確認に利用するインスタンスのPGA以外のパラメータは以下の通りです。
(隠しパラメータは必要がある場合には適宜変更します。また、いくつかのパラメータは確認の都合上物理メモリーサイズ以上に設定することがあります。それらの変更が必要な場合には事前に解説する予定です。)

OS等の情報は以前のエントリーを参照のこと。

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 12G
sga_min_size big integer 0
sga_target big integer 12G

今回の主役である自動PGA管理のパラメータの初期設定は以下の通り(隠しパラメータを除く)
pga_aggregate_limit = 0 に設定し、pga_aggregate_targetの上限値制限を仕様上のサイズ4096GB - 1まで利用できるようにしておきます。(以前と同様の手順で確認するために、pga_aggregate_limitによる制限を無効化しています。なお、pga_aggregate_limitを0以外に設定して行う場合には、pga_aggregate_targetを4TB - 1まで設定することを考慮すると、pga_aggregate_limitは、8TB以上に設定する必要があります。)

orcl12c@SYS> show parameter pga_aggregate

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 0
pga_aggregate_target big integer 10M

上記設定からスタートして、pga_aggregate_targetを10MB/50MB/100MB/500MB/1GB/5GB/10GB/50GB/100GB/500GB/1T/4TB - 1と増加させながら、pgaサイズおよび、関連する隠しパラメータ(_pga_max_sizeや_smm_*など)がどのように変化するか、pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #8の手順で確認します。

昔のように実行ログをペタペタ貼っていると長くなるので、確認結果を表とグラフにしました。
想定通り以前と変わっていませんでした:)

デフォルト設定のままであれば、pga_aggregate_targetを限界値まで大きく増やしたとしても、10GB以降、個々のソート操作やハッシュ結合操作で利用できるPGAのSQL work areaサイズはv$pgastatのglobal memory boundの示す通り、1GB(シリアル実行時)が最大であることが確認できます。 えーーー。手動PGA管理より最大サイズ小さいじゃんと、今更驚かないようにしましょうねw (以前からそうなのですからw)
つまり、自動PGA管理の場合、特定の隠しパラメータを変更しない限り、1GBを超えるソート処理やハッシュ結合操作は全て、"Temp落ち" する宿命にあるわけです。12cR2であっても。
え"〜〜〜〜〜っ! と一応、驚いておきましょうw。 メモリーを沢山積んでるマシンは最近多いわけですが、このあたりはなぜか変わってません。
20180307_143101

ちなみに、v$pgastatの示すglobal memory boundは、どこから? という確認がしたくて、 _smm_max_sizeの変化と比較しているグラグも作成しておきました。
_smm_max_sizeパラメータはpga_aggregate_targetのサイズ変化に伴い変化する隠しパラメータですが、このパラメータがglobal memory boundの元になっているのは確からしいですね。
20180307_143308

最後のグラフは、pga_aggregate_targetの値とv$pgastatのglobal memory boundの変化をまとめたグラフです。
global memory boundで示されるPGAのSQL Work Areaサイズの最大サイズには上限があることがわかります:)
20180307_143330

次回は、自動PGA管理では、1GBを超えるソートやハッシュ結合はもれなく"Temp落ち"するのか確認してみることにします。


参考:このエントリーで利用したスクリプト
(今後の確認も兼ねてw このエントリーでネタにしている以外で関連しそうなパラメータも取得して変化を確認できるようにしてあります。)

orcl12c@SYS> !cat show_param.sql
set linesize 200
col ksppinm for a46
col ksppstvl for a30
col ksppstdf for a30
select
a.ksppinm
,b.ksppstvl
,b.ksppstdf
from
x$ksppi a join x$ksppcv b
on a.indx = b.indx
where
a.ksppinm in (
'pga_aggregate_target'
,'pga_aggregate_limit'
,'_use_realfree_heap'
,'_realfree_heap_pagesize'
)
or a.ksppinm like '%\_smm%' escape '\'
or a.ksppinm like '%\_pga%' escape '\'
order by
a.ksppinm
/

!echo /proc/sys/vm/max_map_count
!more /proc/sys/vm/max_map_count

orcl12c@SYS> !cat show_pgstat.sql
select
name
,case
when unit='bytes' then
value/1024/1024
else value
end "VALUE"
,case
when unit='bytes' then
'MB'
else unit
end "UNIT"
,con_id
from
v$pgastat
where
name in (
'aggregate PGA target parameter'
,'aggregate PGA auto target'
,'global memory bound'
,'total PGA used for auto workareas'
,'maximum PGA used for auto workareas'
,'total PGA used for manual workareas'
,'maximum PGA used for manual workareas'
)
;
orcl12c@SYS> !cat auto_workarea.sql
conn sys/oracle@orcl12c as sysdba
alter system set pga_aggregate_target=&1 scope=spfile
.
r
shutdown immediate
startup

prompt +++ initial parameters +++
@show_param

prompt +++ v$pgastat +++
@show_pgstatå
orcl12c@SYS> !cat doTest_auto_workarea.sql
prompt << 10m >>
@auto_workarea 10m

prompt << 50m >>
@auto_workarea 50m

prompt << 100m >>
@auto_workarea 100m

prompt << 500m >>
@auto_workarea 500m

prompt << 1g >>
@auto_workarea 1g

prompt << 5g >>
@auto_workarea 5g

prompt << 10g >>
@auto_workarea 10g

prompt << 50g >>
@auto_workarea 50g

prompt << 100g >>
@auto_workarea 100g

prompt << 500g >>
@auto_workarea 500g

prompt << 1t >>
@auto_workarea 1t

prompt << 4t - 1 >>
@auto_workarea 4398046511103


実行例

orcl12c@SYS> @doTest_auto_workarea.sql
<< 10m >>
Connected.
1* alter system set pga_aggregate_target=&1 scope=spfile
old 1: alter system set pga_aggregate_target=&1 scope=spfile
new 1: alter system set pga_aggregate_target=10m scope=spfile

System altered.

Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size 8807168 bytes
Variable Size 1375735040 bytes
Database Buffers 5033164800 bytes
Redo Buffers 24743936 bytes
In-Memory Area 6442450944 bytes
Database mounted.
Database opened.
+++ initial parameters +++

KSPPINM KSPPSTVL KSPPSTDF
---------------------------------------------- ------------------------------ ------------------------------
__pga_aggregate_target 33554432 FALSE

・・・略・・・

pga_aggregate_limit 0 FALSE
pga_aggregate_target 10485760 FALSE

50 rows selected.

/proc/sys/vm/max_map_count

65530

+++ v$pgastat +++

NAME VALUE UNIT CON_ID
---------------------------------------------------------------- ---------- ------------ ----------
aggregate PGA target parameter 10 MB 0
aggregate PGA auto target 4 MB 0
global memory bound 2 MB 0
total PGA used for auto workareas .106445313 MB 0
maximum PGA used for auto workareas .106445313 MB 0
total PGA used for manual workareas 0 MB 0
maximum PGA used for manual workareas 0 MB 0

7 rows selected.

・・・略・・・


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版 (その前に少し脱線)

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

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)

2016年9月22日 (木)

メモ:SIMDあれこれ

SIMDあれこれ自分メモ

primitive: blog / introdunction to SIMD programming
http://i-saint.hatenablog.com/entry/2015/05/26/212441

wikipedia / Streaming SIMD Extensions
https://ja.wikipedia.org/wiki/Streaming_SIMD_Extensions

IA Software User Society / コンパイラー最適化入門: 第1回 SIMD 命令とプロセッサーの関係
http://www.isus.jp/products/c-compilers/compiler_part1/

IA Software User Society / コンパイラー最適化入門: 第2回 SIMD 命令と伝統的な IA 命令
http://www.isus.jp/products/c-compilers/compiler_part2/

IA Software User Society / コンパイラー最適化入門: 第3回 インテル® コンパイラーのベクトル化レポートを活用する
http://www.isus.jp/products/c-compilers/compiler_part3/

IA Software User Society / コンパイラー最適化入門: 第4回 自動ベクトル化はどんな時に行われるか
http://www.isus.jp/products/c-compilers/compiler_part4/

IA Software User Society / コンパイラー最適化入門: 第5回 明示的にベクトル化されたコードを記述する
http://www.isus.jp/products/c-compilers/compiler_part5/

IA Software User Society / コンパイラー最適化入門: 第6回 ベクトル化の裏技集
http://www.isus.jp/products/c-compilers/compiler_part6/

Oracle In-Memory Column Store Internals – Part 1 – Which SIMD extensions are getting used?
http://blog.tanelpoder.com/2014/10/05/oracle-in-memory-column-store-internals-part-1-which-simd-extensions-are-getting-used/

SIMD-Scan: Ultra Fast in-Memory Table Scan using on- Chip Vector Processing Units
http://www.vldb.org/pvldb/2/vldb09-327.pdf

Rethinking SIMD Vectorization for In-Memory Databases
http://www.cs.columbia.edu/~orestis/sigmod15.pdf

Oracle … as usual / SIMD Extensions in and out Oracle 12.1.0.2
https://laurent-leturgez.com/2015/04/22/simd-extensions-in-and-out-oracle-12-1-0-2/

Laurent Leturgez, / Ukoug15 SIMD outside and inside Oracle 12c (12.1.0.2)
http://www.slideshare.net/lolo115/ukoug15-simd-outside-and-inside-oracle-12c-12102

Cellプログラミングチュートリアル Version 0.2c
http://cell.fixstars.com/ps3linux/tutorial/index.html

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

2016年7月19日 (火)

DB Tech Showcase 2016 Tokyo - E35 - SQLチューニング総合診療所的予防医学のセッション資料

DB Tech Showcase 2016 Tokyo - E35 - SQLチューニング総合診療所的予防医学のセッション資料を公開しました。

ぼくとつと、性能試験データの質などのことを言い続ける感じになっていたでしょうか?w

ところで、次回のJPOUG主催のイベントの開催が決まりました。セッション内容は現在準備中ですが、確定次第随時更新します。
お申し込みは以下のイベントベージよりお願いします。

JPOUG in 15 minutes #1

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

2015年5月17日 (日)

Meetup! JPOUG @ Oracle CloudWorld 2015

意外に心?w 時間? に余裕がないのですが、途切れ途切れでも更新していこうかなと50%いや、60%
ぐらい思ってます。

久々の投稿ですが、一ヶ月前のネタです m(_ _)m

Meetup! JPOUG 開催報告

ショートセッション

スペシャル企画 「な〜んでだ?」


ショートセッションのSQLチューニング総合診療Oracle CloudWorld出張所は、db tech showcase 2014 Tokyoの続編のようなw (ような、じゃなくて、続編ですね、キッパリ

いろいろな性能病は無駄な物理I/Oや論理I/Oを削減していくことが、治療の第一歩!。

な〜んでだ? #3は、時間が押してしまい残り5分程度で駆け足で終わらせてしまったので、「な〜んでだ?」は?な雰囲気にできなかったのが心残りでした (笑

AWRを眺めていると、あるような、ないような時間が見えたりしてモヤモヤすることもあると思うんですよね。
そんなときの参考にならないかな? と思ったネタでした。


Oracle Databaseが雲の向こうに行っちゃっても、人工知能型オプティマイザでも登場してこないかぎり、まだ、まだ、人間のサポートが必要だと思うんだよねw いつまで必要なのかは、わからないけど。:)

賢い人工知能型オプティマイザがうまれたとして、各インスタンスごとに性格の違うオプティマイザが成長しちゃったら面白い世界になるかもしれない。
と妄想していると、眠れなくなるので、今日はこのへんで。

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

2015年1月 2日 (金)

机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!! (実解答編)

JPOUG Advent Calendar 2014 17日目のエントリーで公開したクイズを実解答編です。
実際にOracle Database 12c 12.1.0.2.0を使ってどの表を駆動表(外部表)にするか確認してみます。


予想はあたるか、外れるか...どのような結果が待っているのか..ワクワクします。 :-)
Oracle® Database SQLチューニング・ガイド 12cリリース1(12.1) B71277-02 - 7 結合


前回の予想も参考に....机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!! (予想解答編)

問題1
次に示すSQL文の駆動表(外部表)はどれでしょうか? 

表と索引
create table a1 (
id number primary key
, data varchar2(1000)
) nologging
/
create table a2 (
id number primary key
, data varchar2(1000)
) nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
A1 SYS_C0010377 10000 10000 295
A2 SYS_C0010378 2000 2000 59

SELECT
/* SQL01 */
/*+
MONITOR
USE_NL(a1 a2)
*/
*
FROM
a1
INNER JOIN a2
ON
a1.id = a2.id
WHERE
a1.id BETWEEN 1 AND 100
/


私の答え(予想)
Nested Loop結合かつ、INNER JOINですから駆動表(外部表)はデータセットの小さい方。
(Nested Loop結合でOUTER JOINだと結合順は固定されるので駆動表は見つけやすいですが...INNER JOINの場合はそうはいかないですよね。ちなみに、最近のOracleだと、OUTER JOINでHash結合だと外部表を入れれかえて最適化するケースもあります。いずれどこかでそのネタをやると思います。)

ただ、結合条件キーはどちらも主キーですし、WHERE a1.id BETWEEN 1 AND 100は、a2表にも適用されることになるでしょうから、a1、a2どちらも最大で100行ヒットする可能性はあります。
どちらでも正解になる可能性はありますが...w (最初から引っ掛けかよw

細かい条件は提示していないので、提示した情報だけで机上で駆動表(外部表)を決めるとすれば、全体のサイズが小さい、a2でいいんじゃないでしょうか?(私ならそうします。机上ですから)
(当初、もう少し詳細な情報を提示しようとしていたのですが忘れてました...なのでa1だと思った方も可能性は五分五分ですね。前提条件不足でした。...ごめんなさい。ごめんなさい。)

Oracle Database 12c 12.1.0.2.0の動き
以下、SQLモニタリングレポートをみると、駆動表は、 a2表でした。 :)
ちなみに、a1を駆動表にした場合は、若干Buffer Getsが増加していました。誤差の範囲ですが...

SQL Monitoring Report

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

SQL Plan Monitoring Details (Plan Hash Value=3695212685)
=====================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
=====================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 20 | | |
| 1 | NESTED LOOPS | | 20 | 23 | 1 | +0 | 1 | 20 | | |
| 2 | NESTED LOOPS | | 20 | 23 | 1 | +0 | 1 | 20 | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | A2 | 20 | 3 | 1 | +0 | 1 | 20 | | |
| 4 | INDEX RANGE SCAN | SYS_C0010378 | 20 | 2 | 1 | +0 | 1 | 20 | | |
| 5 | INDEX UNIQUE SCAN | SYS_C0010377 | 1 | | 1 | +0 | 20 | 20 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | A1 | 1 | 1 | 1 | +0 | 20 | 20 | | |
=====================================================================================================================================================

問題2
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題1と同じです。)

SELECT
/* SQL02 */
/*+
MONITOR
USE_HASH(a1 a2)
*/
*
FROM
a1
INNER JOIN a2
ON
a1.id = a2.id
/

私の答え(予想)
Hash結合でINNER JOINかつ、WHERE句はないのでこの問題は簡単ですよね! (これは迷わないはず!!!)

Hash結合も外部表はデータセットの小さい方ですから...この場合だと、a2が外部表になるはず。

Oracle Database 12c 12.1.0.2.0の動き
以下、SQLモニタリングレポートから....外部表は、予想通りのa2

SQL Monitoring Report

Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.01 | 0.01 | 0.00 | 135 | 497 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=1713954154)
==================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
==================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 2000 | | | |
| 1 | HASH JOIN | | 2000 | 121 | 1 | +0 | 1 | 2000 | 1M | | |
| 2 | TABLE ACCESS FULL | A2 | 2000 | 19 | 1 | +0 | 1 | 2000 | | | |
| 3 | TABLE ACCESS FULL | A1 | 10000 | 102 | 1 | +0 | 1 | 10000 | | | |
==================================================================================================================================


問題3
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
なお、D1とD2の多重度は、D1:D2 = 1:100
(個人的にUMLの多重度表記のほうが好きなので、UML表記の多重度で記述します。

表と索引
create table d1 (
id number
, data varchar2(1000)
) nologging
/
alter table d1 add constraint pk_d1 primary key (id) using index nologging
/
create table d2 (
id number not null
, seq# number not null
, data varchar2(1000)
) nologging
/
alter table d2 add constraint pk_d2 primary key (id, seq#) using index nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
D1 PK_D1 200 200 9
D2 PK_D2 20000 20000 870

SELECT
/* SQL03 */
/*+
MONITOR
*/
*
FROM
d2
WHERE
EXISTS (
SELECT
1
FROM
d1
WHERE
d1.id = d2.id
AND d1.id IN (1,5)
)
/


私の答え(予想)
これはちょっと意地悪な問題ですが、わかる人ならわかるはず。だと思って(信じて)作った問題です。 :)

最近のオプティマイザは、相関副問合せを可能であれば結合に書き換える(unnest)傾向が強いのをご存知でしょうか? 
となれば、方向はだいだい見えてきます。

d1.id IN (1,5)から最大で2件ヒットすると予想できますよね。
さらに、Nested Loop結合に書き換え、d2を内部表として結合できれば無駄がない。
つまり、駆動表は d1が理想的なはず。

d2を外部表にしてしまうと、WHERE条件がないので結合に置き換えてしまうとHash結合または、d2を全表走査して相関副問合せを都度実行のいづれかになってしまうでしょうね。(unnestの傾向が強いのでHash結合になる可能性が高そう)
ヒットするデータ件数から考えると、どちらも無駄なデータアクセスが多くなる可能性は高いのではないでしょうか。


Oracle Database 12c 12.1.0.2.0の動き
おおお〜。予想通り、相関副問合せは、unnestされて結合に書き換えられています。 さらに 12cの新機能であるAdaptive Joinになっています。興味深い。

で本題である、外部表はどうなっているかというと....pk_d1をindex only scanしてNested Loop結合になっています。つまり、駆動表は、予想通り、d1になっています。Yahoo!
id=1でHash結合がありますが、試行はしたようですが、実際にはNested Loop結合だったようです。

SQL Monitoring Report

Global Stats
=======================================
| Elapsed | Other | Fetch | Buffer |
| Time(s) | Waits(s) | Calls | Gets |
=======================================
| 0.01 | 0.01 | 15 | 44 |
=======================================

SQL Plan Monitoring Details (Plan Hash Value=804853015)
==============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
==============================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 0 | | |
| 1 | HASH JOIN | | 200 | 5 | 1 | +0 | 1 | 0 | | |
| 2 | NESTED LOOPS | | 200 | 5 | 1 | +0 | 1 | 200 | | |
| 3 | NESTED LOOPS | | 200 | 5 | 1 | +0 | 1 | 200 | | |
| 4 | STATISTICS COLLECTOR | | | | 1 | +0 | 1 | 1 | | |
| 5 | INLIST ITERATOR | | | | 1 | +0 | 1 | 2 | | |
| 6 | INDEX UNIQUE SCAN | PK_D1 | 2 | 1 | 1 | +0 | 2 | 2 | | |
| 7 | INDEX RANGE SCAN | PK_D2 | 1 | 1 | 1 | +0 | 2 | 200 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | D2 | 100 | 2 | 1 | +0 | 200 | 200 | | |
| 9 | INLIST ITERATOR | | | | | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED | D2 | 100 | 2 | | | | | | |
| 11 | INDEX RANGE SCAN | PK_D2 | 1 | 1 | | | | | | |
==============================================================================================================================================


問題4
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題3と同じです。)

SELECT
/* SQL04 */
/*+
MONITOR
*/
*
FROM
d1
INNER JOIN d2
ON
d1.id > d2.id
AND d2.id BETWEEN 1 AND 5
AND d2.seq# BETWEEN 2 AND 4
/

私の答え(予想)
結合条件が ">" であることに気付きましたか? ;)


これは、Nested Loop結合にも、Hash結合にもなりません。 Merge(sort/merge)結合が選択されます。

内部表は索引が利用できないのでソートが発生します。(ソートは避けられない)
外部表では索引を利用してソート処理が回避可能であれば、回避する。

つまりソート処理の影響を最小にしようとするはず、なので...

d1:d2=1:100という比率からd2のソート処理を重いと判断し回避するために外部表にするだろうなぁ〜〜。
私は、d2が外部表になるだろうと予想しています。
(直積じゃないMerge結合なんて最近お目にかかったことないのですが...)

余談)
Hash結合のない時代のOracleで、Merge結合の実行計画を見せられて、なんでソートしてるんですかね〜と、某ベンダーの方に質問されて一瞬固まったことを思い出したw


Oracle Database 12c 12.1.0.2.0の動き
良かったw。 予想どおりソートを回避したようで、 外部表は、 d2ですね。

SQL Monitoring Report

Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.02 | 0.01 | 0.01 | 198 | 48 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=705618498)
=============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
=============================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 2955 | | | |
| 1 | MERGE JOIN | | 3966 | 10 | 1 | +0 | 1 | 2955 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | D2 | 20 | 4 | 1 | +0 | 1 | 15 | | | |
| 3 | INDEX RANGE SCAN | PK_D2 | 20 | 3 | 1 | +0 | 1 | 15 | | | |
| 4 | SORT JOIN | | 199 | 6 | 1 | +0 | 15 | 2955 | 145K | | |
| 5 | TABLE ACCESS FULL | D1 | 199 | 5 | 1 | +0 | 1 | 199 | | | |
=============================================================================================================================================


問題5
次に示すSQL文の駆動表(外部表)はどれでしょうか? 

表と索引定義
create table b1 (
id number
, data varchar2(1000)
) nologging
/
alter table b1 add constraint pk_b1 primary key(id) using index nologging
/
create table b3 (
id number
, seq# number
, data varchar2(1000)
) nologging
/
alter table b3 add constraint pk_b3 primary key (id, seq#) using index nologging
/
create table b2 (
id number
, seq# number
, subseq# number
, data varchar2(1000)
) nologging
/
alter table b2 add constraint pk_b2 primary key (id ,seq#, subseq#) using index nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
B1 PK_B1 20000 20000 870
B2 PK_B2 5000 5000 228
B3 PK_B3 500 500 23

ERDと多重度
b1 : b3 = 1 : 0..2
b3 : b2 = 1 : 0..10
(UML表記の多重度で記述しています。
20141221_120733

SELECT
/* SQL05 */
/*+
MONITOR
USE_HASH(b1 b3 b2)
*/
*
FROM
b1
INNER JOIN b3
ON
b1.id = b3.id
INNER JOIN b2
ON
b3.id = b2.id
AND b3.seq# = b2.seq#
/

私の答え(予想)
INNER JOINでHash結合かつ、WHERE句もないので簡単な問題ですよね。

外部表は、b3

ついでに、続けると、 b3とb2を結合すると最大で5000件、b3とb1を結合した場合、最大で500件なので、 結合順として理想的なのは、 b3, b1, b2 ですよね。


Oracle Database 12c 12.1.0.2.0の動き
結果は、予想通りでした〜。これは簡単だから!。

SQL Monitoring Report

Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.06 | 0.04 | 0.02 | 335 | 1476 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=3711653655)
===================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
===================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 5000 | | | |
| 1 | HASH JOIN | | 5000 | 315 | 1 | +0 | 1 | 5000 | 1M | | |
| 2 | HASH JOIN | | 500 | 247 | 1 | +0 | 1 | 500 | 1M | | |
| 3 | TABLE ACCESS FULL | B3 | 500 | 9 | 1 | +0 | 1 | 500 | | | |
| 4 | TABLE ACCESS FULL | B1 | 20000 | 238 | 1 | +0 | 1 | 20000 | | | |
| 5 | TABLE ACCESS FULL | B2 | 5000 | 68 | 1 | +0 | 1 | 5000 | | | |
===================================================================================================================================


問題6
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題5と同じです。)

SELECT
/* SQL06 */
/*+
MONITOR
USE_HASH(b1 b3 b2)
*/
*
FROM
b1
LEFT OUTER JOIN b3
ON
b1.id = b3.id
LEFT OUTER JOIN b2
ON
b3.id = b2.id
AND b3.seq# = b2.seq#
/

私の答え(予想)
OUTER JOINなのですが、Hash結合なので、多分、予想通りにはならないだろうな〜〜と。

最近のオプティマイザは外部結合かつHash結合の場合、外部表と内部表をデータセットサイズに応じて入れ替える傾向が強いんですよね。PGAの使用サイズが小さくなるように....

とは言っても、机上の話なので、入れ替えないとしたらどうなるだろう..と、外部結合なので、内部表は、そのまま内部表として結合したほうがわかりやすいといえばわかりやすい。
(統計情報に乖離がある場合には入れ替えないほうが良い結果だったりすることもあります。)

注)()内を先に結合するものとします。
入れ替えなかった場合は、記述したままなので、(外部表、内部表)=外部表、内部表ということで、 (b1, b3), b2
データセットの小さい順に従えば、外部表と、内部表をいれかえて、b3, b1この結果セットの最大件数は元の外部表がb1ですから20,250件、b2が5,000件なので、また外部表と内部表を入れ替えて、結果として、b2, (b3, b1)ってことに...
(内部的に結合順が入れ替えられ、Right Joinに置き換えられることが多いんですよね〜いつ頃からだろう...10gあたりか。)

で、机上ならどちらを取るかということですが、机上では、ひとまず、SQL文の通りとしておくケースが多いです。あくまで私の場合ですよ。(難しいんですよ、これ。外れる可能性大w)
オプティマイザが内部的に外部表と内部表を入れ替えて問題がなければよし、問題があれば、内部的な入れ替えをヒントで止めるというのが私の基本方針なので、この辺りは、チューナーさんのさじ加減次第じゃないかなぁ、と思っています。

あはは、むずかし過ぎたか... 机上だと orz.

Oracle Database 12c 12.1.0.2.0の動き
結果を見てみると〜〜〜〜〜っ。恐れていたw 外部表と内部表の置き換え操作(LEFT OUTERがRIGHT OUTER)になってますね〜っ。あはは、見事に、外れました....orz.

SQL Monitoring Report

Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.34 | 0.21 | 0.13 | 1651 | 2648 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=1453650298)
======================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
======================================================================================================================================
| 0 | SELECT STATEMENT | | | | 4 | +0 | 1 | 24750 | | | |
| 1 | HASH JOIN RIGHT OUTER | | 203K | 316 | 4 | +0 | 1 | 24750 | 3M | | |
| 2 | TABLE ACCESS FULL | B2 | 5000 | 68 | 1 | +0 | 1 | 5000 | | | |
| 3 | HASH JOIN RIGHT OUTER | | 20250 | 247 | 4 | +0 | 1 | 20250 | 1M | | |
| 4 | TABLE ACCESS FULL | B3 | 500 | 9 | 1 | +0 | 1 | 500 | | | |
| 5 | TABLE ACCESS FULL | B1 | 20000 | 238 | 4 | +0 | 1 | 20000 | | | |
======================================================================================================================================

問題ごとの解説をおまけで何回かに分けて、書いた方が良さそうだなw

ということで、おまけエントリーを書くかも...し、れ、な、い。


机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!!
机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!! (予想解答編)

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

2015年1月 1日 (木)

机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!! (予想解答編)

JPOUG Advent Calendar 2014 17日目のエントリーで公開したクイズの予想解答編です。
机上ですし、限られた情報しか提示していないので、実際にやってみたら違う...ということも十分ありえます。 (^^;;;;


外部表はどれがいいか予想できると、いろいろな制限のあるチューニング現場では役立つこともあるんです。LEADINGヒント等で結合順を考える場合にも役立ちますyo!
机上の予想なので間違うこともあります。オプティマイザも統計情報から予想しているわけで(Adaptiveな機能を除く)ハズすことも多いですから... (^^

Oracle® Database SQLチューニング・ガイド 12cリリース1(12.1) B71277-02 - 7 結合


問題1
次に示すSQL文の駆動表(外部表)はどれでしょうか? 

表と索引
create table a1 (
id number primary key
, data varchar2(1000)
) nologging
/
create table a2 (
id number primary key
, data varchar2(1000)
) nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
A1 SYS_C0010377 10000 10000 295
A2 SYS_C0010378 2000 2000 59

SELECT
/* SQL01 */
/*+
MONITOR
USE_NL(a1 a2)
*/
*
FROM
a1
INNER JOIN a2
ON
a1.id = a2.id
WHERE
a1.id BETWEEN 1 AND 100
/

私の答え(予想)
Nested Loop結合かつ、INNER JOINですから駆動表(外部表)はデータセットの小さい方。
(Nested Loop結合でOUTER JOINだと結合順は固定されるので駆動表は見つけやすいですが...INNER JOINの場合はそうはいかないですよね)

ただ、結合条件キーはどちらも主キーですし、WHERE a1.id BETWEEN 1 AND 100は、a2表にも適用されることになるでしょうから、a1、a2どちらも最大で100行ヒットする可能性はあります。
どちらでも正解になる可能性はありますが...w (最初から引っ掛けかよw

細かい条件は提示していないので、提示した情報だけで机上で駆動表(外部表)を決めるとすれば、全体のサイズが小さい、a2でいいんじゃないでしょうか?(私ならそうします。机上ですから)
(当初、もう少し詳細な情報を提示しようとしていたのですが忘れてました...なのでa1だと思った方も可能性は五分五分ですね。前提条件不足でした。...ごめんなさい。ごめんなさい。)


問題2
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題1と同じです。)

SELECT
/* SQL02 */
/*+
MONITOR
USE_HASH(a1 a2)
*/
*
FROM
a1
INNER JOIN a2
ON
a1.id = a2.id
/

私の答え(予想)
Hash結合でINNER JOINかつ、WHERE句はないのでこの問題は簡単ですよね! (これは迷わないはず!!!)

Hash結合も外部表はデータセットの小さい方ですから...この場合だと、a2が外部表になるはず。

問題3
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
なお、D1とD2の多重度は、D1:D2 = 1:100
(個人的にUMLの多重度表記のほうが好きなので、UML表記の多重度で記述します。

表と索引
create table d1 (
id number
, data varchar2(1000)
) nologging
/
alter table d1 add constraint pk_d1 primary key (id) using index nologging
/
create table d2 (
id number not null
, seq# number not null
, data varchar2(1000)
) nologging
/
alter table d2 add constraint pk_d2 primary key (id, seq#) using index nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
D1 PK_D1 200 200 9
D2 PK_D2 20000 20000 870

SELECT
/* SQL03 */
/*+
MONITOR
*/
*
FROM
d2
WHERE
EXISTS (
SELECT
1
FROM
d1
WHERE
d1.id = d2.id
AND d1.id IN (1,5)
)
/


私の答え(予想)
これはちょっと意地悪な問題ですが、わかる人ならわかるはず。だと思って(信じて)作った問題です。 :)

最近のオプティマイザは、相関副問合せを可能であれば結合に書き換える(unnest)傾向が強いのをご存知でしょうか? 
となれば、方向はだいだい見えてきます。

d1.id IN (1,5)から最大で2件ヒットすると予想できますよね。
さらに、Nested Loop結合に書き換え、d2を内部表として結合できれば無駄がない。
つまり、駆動表は d1が理想的なはず。

d2を外部表にしてしまうと、WHERE条件がないので結合に置き換えてしまうとHash結合または、d2を全表走査して相関副問合せを都度実行のいづれかになってしまうでしょうね。(unnestの傾向が強いのでHash結合になる可能性が高そう)
ヒットするデータ件数から考えると、どちらも無駄なデータアクセスが多くなる可能性は高いのではないでしょうか。

問題4
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題3と同じです。)

SELECT
/* SQL04 */
/*+
MONITOR
*/
*
FROM
d1
INNER JOIN d2
ON
d1.id > d2.id
AND d2.id BETWEEN 1 AND 5
AND d2.seq# BETWEEN 2 AND 4
/

私の答え(予想)
結合条件が ">" であることに気付きましたか? ;)


これは、Nested Loop結合にも、Hash結合にもなりません。 Merge(sort/merge)結合が選択されます。

内部表は索引が利用できないのでソートが発生します。(ソートは避けられない)
外部表では索引を利用してソート処理が回避可能であれば、回避する。

つまりソート処理の影響を最小にしようとするはず、なので...

d1:d2=1:100という比率からd2のソート処理を重いと判断し回避するために外部表にするだろうなぁ〜〜。
私は、d2が外部表になるだろうと予想しています。
(直積じゃないMerge結合なんて最近お目にかかったことないのですが...)

余談)
Hash結合のない時代のOracleで、Merge結合の実行計画を見せられて、なんでソートしてるんですかね〜と、某ベンダーの方に質問されて一瞬固まったことを思い出したw


問題5
次に示すSQL文の駆動表(外部表)はどれでしょうか? 

表と索引定義
create table b1 (
id number
, data varchar2(1000)
) nologging
/
alter table b1 add constraint pk_b1 primary key(id) using index nologging
/
create table b3 (
id number
, seq# number
, data varchar2(1000)
) nologging
/
alter table b3 add constraint pk_b3 primary key (id, seq#) using index nologging
/
create table b2 (
id number
, seq# number
, subseq# number
, data varchar2(1000)
) nologging
/
alter table b2 add constraint pk_b2 primary key (id ,seq#, subseq#) using index nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
B1 PK_B1 20000 20000 870
B2 PK_B2 5000 5000 228
B3 PK_B3 500 500 23

ERDと多重度
b1 : b3 = 1 : 0..2
b3 : b2 = 1 : 0..10
(UML表記の多重度で記述しています。
20141221_120733

SELECT
/* SQL05 */
/*+
MONITOR
USE_HASH(b1 b3 b2)
*/
*
FROM
b1
INNER JOIN b3
ON
b1.id = b3.id
INNER JOIN b2
ON
b3.id = b2.id
AND b3.seq# = b2.seq#
/

私の答え(予想)
INNER JOINでHash結合かつ、WHERE句もないので簡単な問題ですよね。

外部表は、b3

ついでに、続けると、 b3とb2を結合すると最大で5000件、b3とb1を結合した場合、最大で500件なので、 結合順として理想的なのは、 b3, b1, b2 ですよね。


問題6
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題5と同じです。)

SELECT
/* SQL06 */
/*+
MONITOR
USE_HASH(b1 b3 b2)
*/
*
FROM
b1
LEFT OUTER JOIN b3
ON
b1.id = b3.id
LEFT OUTER JOIN b2
ON
b3.id = b2.id
AND b3.seq# = b2.seq#
/

私の答え(予想)
OUTER JOINなのですが、Hash結合なので、多分、予想通りにはならないだろうな〜〜と。

最近のオプティマイザは外部結合かつHash結合の場合、外部表と内部表をデータセットサイズに応じて入れ替える傾向が強いんですよね。PGAの使用サイズが小さくなるように....

とは言っても、机上の話なので、入れ替えないとしたらどうなるだろう..と、外部結合なので、内部表は、そのまま内部表として結合したほうがわかりやすいといえばわかりやすい。
(統計情報に乖離がある場合には入れ替えないほうが良い結果だったりすることもあります。)


注)()内を先に結合するものとします。
入れ替えなかった場合は、記述したままなので、(外部表、内部表)=外部表、内部表ということで、 (b1, b3), b2
データセットの小さい順に従えば、外部表と、内部表をいれかえて、b3, b1。
この結果セットの最大件数は元の外部表がb1ですから20,250件、b2が5,000件なので、また外部表と内部表を入れ替えて、結果として、b2, (b3, b1)ってことに...
(内部的に結合順が入れ替えられ、Right Joinに置き換えられることが多いんですよね〜いつ頃からだろう...10gあたりか。)

で、机上ならどちらを取るかということですが、机上では、ひとまず、SQL文の通りとしておくケースが多いです。あくまで私の場合ですよ。(難しいんですよ、これ。外れる可能性大w)
オプティマイザが内部的に外部表と内部表を入れ替えて問題がなければよし、問題があれば、内部的な入れ替えをヒントで止めるというのが私の基本方針なので、この辺りは、チューナーさんのさじ加減次第じゃないかなぁ、と思っています。

あはは、むずかし過ぎたか... 机上だと orz.

結合順がほぼ想定できる場合、状況次第で変わりそうな結合順、いろいろありますよね。
オプティマイザの気持ちになって考えてみると、いろいろ気づくことも多いんじゃないかなぁ。と思います。

オプティマイザってソートが嫌いなんだ〜、とか、PGA少ない方が好きなんだ〜とか....


役に立ったか、どうなのか不明なオチになりましたが.....

本年もよろしくお願いいたします。 m(_ _)m


あ、そうそう、一つ忘れてました。

次回は、Oracle Database 12c 12.1.0.2.0を使って試したオプティマイザが選択した駆動表(外部表)がどれだったかのか公開する予定です。


机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!!

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

2014年8月23日 (土)

IO Resource Manager 関係のメモ

黒い画面の好きなyskwkzhrさんも気に入るよね、きっと :)


PART 1 of 4 "Test interdatabase consolidation with Oracle Exadata IORM"

PART 2 of 4 "Test interdatabase consolidation with Oracle Exadata IORM"

PART 3 of 4 "Test intradatabase consolidation with Oracle Exadata IORM"

PART 4 of 4 "Test intradatabase consolidation with Oracle Exadata IORM"


ここで使ってるモニターリングツールはSwingbenchでも有名なDominic Gilesさんのサイトね。ステキです:)
Database Time Monitor
CPU Monitor
Monitor DB

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

2014年8月19日 (火)

pivotとSQL*PlusとSETコマンドと #2

昨日の続きですw

v$sys_time_modelの列データを行データへpivotで変換し、かつシェルで定期的に取得してみたものの、出力形式は今ひとつ。 iostatやvmstatのように出力したい。

前回のエントリーの出力結果は以下の通りでした。

[oracle ˜]$ ./sample.sh

SQL*Plus: Release 12.1.0.2.0 Production on 日 8月 17 21:10:47 2014

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


Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
に接続されました。
21:10:48 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66172667 7714783 127809578 27114874

経過: 00:00:00.02
21:10:48 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66179178 7720782 128085853 27160868

経過: 00:00:00.01
21:10:57 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66180864 7723782 128249102 27197862

経過: 00:00:00.00

じゃまな出力は、以下の通り

  • SQL*Plusのプロンプト
  • ヘッダー行
  • 経過時間
  • 出力の状態から見て、余分な改行

そして、不足している出力はメトリックのログ取得時のタイムスタンプ


以下のSQL*Plusシステム変数を調整追加すればなんとかなりそうな感じ。。。。

  • SQL*Plusのプロンプトは、 set sqlp "" で抑止。
  • ヘッダー行は、 set head off で抑止
  • 経過時間は、 set timi off で抑止
  • 余分な改行は、 たぶん、 set newp none で抑止
  • 直接関係ないけど、Excelにコピペするときにじゃまになるので set tab offでタブの混入抑止


不足しているログ取得時のタイムスタンプは、シェルのdateコマンドで取得した日時をSQL文に埋め込むことでなんとかなりそうな気がします。

と頭に浮かんだら忘れないうちに試してみますよ〜

★横に長くてごめんなさい。時間取れたらSyntaxHighlighterとか入れます詐欺 m(_ _)m

#!/bin/bash
#
(
echo "conn scott/tiger"
echo "set timi off time off tab off sqlp \"\" head off newp none"
echo "col db_time for 999999999999999999999999999999"
echo "col db_cpu for 999999999999999999999999999999"
echo "col bg_time for 999999999999999999999999999999"
echo "col bg_cpu for 999999999999999999999999999999"
while [ 1 ]
do
echo "SELECT db_time,db_cpu,bg_time,bg_cpu FROM (SELECT stat_name,value FROM v\$sys_time_model) PIVOT (MAX(value) FOR stat_name IN ('DB time' AS db_time,'DB CPU' AS db_cpu,'background elapsed time' AS bg_time,'background cpu time' AS bg_cpu));"
sleep 10
done
) | sqlplus /nolog


ん〜〜〜〜〜、なんか、惜しい!!!!  いい感じにななったのに。... しばし考える。。。。

[oracle ˜]$ ./sample.sh

SQL*Plus: Release 12.1.0.2.0 Production on 日 8月 17 21:45:43 2014

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

21:45:43 > 接続されました。
21:45:44 SCOTT> 67126005 8476651 182158980 37408311

67129138 8479651 182310474 37445305


きた〜〜〜、神が降りてきたのでちょっと書き換えた

#!/bin/bash
#
(
echo "conn scott/tiger"
echo "set timi off time off sqlp \"\""
echo "col db_time for 999999999999999999999999999999"
echo "col db_cpu for 999999999999999999999999999999"
echo "col bg_time for 999999999999999999999999999999"
echo "col bg_cpu for 999999999999999999999999999999"
while [ 1 ]
do
t=`date +'%DT%T'`
echo "SELECT SUBSTR('${t}',1,INSTR('${t}','T')-1) as logged_date,SUBSTR('${t}',INSTR('${t}','T')+1) as logged_time,db_time,db_cpu,bg_time,bg_cpu FROM (SELECT stat_name,value FROM v\$sys_time_model) PIVOT (MAX(value) FOR stat_name IN ('DB time' AS db_time,'DB CPU' AS db_cpu,'background elapsed time' AS bg_time,'background cpu time' AS bg_cpu));"
echo "set head off newp none"
sleep 10
done
) | sqlplus /nolog


ん〜〜〜、まだ余計な改行というか空行がある。。。なんだこれ。。。。再び、考え中........ あ、あれだ! 出力行数を返すやつ!

SQL*Plus: Release 12.1.0.2.0 Production on 日 8月 17 21:56:21 2014

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

21:56:21 > 接続されました。
21:56:22 SCOTT>
LOGGED_D LOGGED_T DB_TIME DB_CPU BG_TIME BG_CPU
-------- -------- ------------------------------- ------------------------------- ------------------------------- -------------------------------
08/17/14 21:56:21 67688748 8937570 198751230 40650823

08/17/14 21:56:31 67695983 8945569 199012673 40699815


で、できたのがこれ。

#!/bin/bash
#
(
echo "conn scott/tiger"
echo "set timi off time off sqlp \"\" feed off"
echo "col db_time for 999999999999999999999999999999"
echo "col db_cpu for 999999999999999999999999999999"
echo "col bg_time for 999999999999999999999999999999"
echo "col bg_cpu for 999999999999999999999999999999"
while [ 1 ]
do
t=`date +'%DT%T'`
echo "SELECT SUBSTR('${t}',1,INSTR('${t}','T')-1) as logged_date,SUBSTR('${t}',INSTR('${t}','T')+1) as logged_time,db_time,db_cpu,bg_time,bg_cpu FROM (SELECT stat_name,value FROM v\$sys_time_model) PIVOT (MAX(value) FOR stat_name IN ('DB time' AS db_time,'DB CPU' AS db_cpu,'background elapsed time' AS bg_time,'background cpu time' AS bg_cpu));"
echo "set head off newp none"
sleep 10
done

) | sqlplus /nolog

出力結果は以下のようになり、 iostatやvmstat風にヘッダー行は一度だけ、その後、定期的に取得されるメトリックが出力されていくイメージに! :)

[oracle ˜]$ ./sample.sh

SQL*Plus: Release 12.1.0.2.0 Production on 日 8月 17 22:02:59 2014

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

22:02:59 > 接続されました。
22:02:59 SCOTT>
LOGGED_D LOGGED_T DB_TIME DB_CPU BG_TIME BG_CPU
-------- -------- ------------------------------- ------------------------------- ------------------------------- -------------------------------
08/17/14 22:02:59 68026469 9252517 211057107 42641516
08/17/14 22:03:09 68034074 9261515 211217278 42686507
08/17/14 22:03:19 68039874 9267514 211414489 42730501
08/17/14 22:03:29 68045221 9272514 211576132 42769498
08/17/14 22:03:39 68051396 9276513 211844118 42862480
^C
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsとの接続が切断されました。


Enjoy!


pivotとSQL*PlusとSETコマンドと

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

2014年8月18日 (月)

pivotとSQL*PlusとSETコマンドと

Oracle Databaseの性能試験で以下のようなメトリックを定期取得して、分析やビジュアライズに利用している方も多いと思います。(思ってます。)
でも、ですねぇ。
以下のv$sys_time_model動的パフォーマンスビューも典型例なのですが、列持ちのメトリックが多いので集計にはかなり苦労します。というか、してます。

v$sys_time_model動的パフォーマンスビューの出力例)

SCOTT> SELECT stat_name, value from v$sys_time_model;

STAT_NAME VALUE
---------------------------------------------------------------- ----------
DB time 64073868
DB CPU 6549986
background elapsed time 42988476
background cpu time 11080311

・・・以下略・・・

列持ちなんですよね、 列持ち!(しつこいw

行持ちにしたいですよね。 どう料理しましょう。 まさか、手作業ではやってないですよね。

SQL文でやってますよね! 私もそうです。
ちなみに、UNION連打はしてませんからね!(キリっ!

昔はほかに手がなかったのですが、Oracle11gから便利で比較的読みやすい構文がサポートされています。

列持ちを行持ちにするといえば....そうです、あれです。 pivot

ということで、

pivotを使って、v$sys_time_modelを例にオレオレv$sys_time_modelを作り出してみます。
(これができれば、数あるオラクルの動的パフォーマンスビューをもっと好きになれるんじゃないかなぁ。と思います。)

では、早速

v$sys_time_modelの stat_name列の列値が、'DB time'、'DB CPU'、 'background elapsed time'、'background cpu time'の4つのメトリックを列持ちから行持ちに変え、オレオレv$sys_time_model作り出すSQL文です。
ビューは作りませんけど (^^;;;

SELECT 
db_time
,db_cpu
,bg_time
,bg_cpu
FROM
(
SELECT
stat_name
,value
FROM
v$sys_time_model
)
PIVOT
(
MAX(value)
FOR stat_name IN
(
'DB time' AS db_time
,'DB CPU' AS db_cpu
,'background elapsed time' AS bg_time
,'background cpu time' AS bg_cpu
)
)
;


これを実行すると以下のような結果になります。本来4行なのですが、1行にできるんです! 便利ですね。 pivot (pivotの逆の操作をする unpivotもあります)

   DB_TIME     DB_CPU    BG_TIME     BG_CPU
---------- ---------- ---------- ----------
63895360 6364016 32965031 8718671

いい感じになってきました。


しかし、まだ物足りないですよね。 そう!
取得時のタイムスタンプとか、例えば iostat や vmstatのように定期的に取得したくなってきます!!!!

「門外不出のOracle現場ワザ」 第5章 DBアクセスの空白地帯 コネクションプーリングを極めるの定期的にSQLを発行するシェルを作成するには? でも解説されているのでこの方法で取得されている方も多いと思います。:)

ただ、そのまんまだと以下のような出力になってしまいます。 iostatやvmstatの出力をイメージしちゃうと余分な表示が多いわけです。

[oracle ˜]$ ./sample.sh

SQL*Plus: Release 12.1.0.2.0 Production on 日 8月 17 21:10:47 2014

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


Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
に接続されました。
21:10:48 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66172667 7714783 127809578 27114874

経過: 00:00:00.02
21:10:48 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66179178 7720782 128085853 27160868

経過: 00:00:00.01
21:10:57 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66180864 7723782 128249102 27197862

経過: 00:00:00.00

前述の出力は以下のコードで取得したのですが、実はそんなに手を加えなくても vmstatやiostatのような出力形式で、みなさんの大好きなExcelで集計しやすくすることができるんですよ。
どこを変更すればよいか分かった人、手を挙げて〜〜〜〜っ!

注)scottユーザにselect any dictionaryシステム権限付けてます。

#!/bin/bash
#
(
echo "conn scott/tiger"
echo "set timi on time on"
echo "col db_time for 999999999999999999999999999999"
echo "col db_cpu for 999999999999999999999999999999"
echo "col bg_time for 999999999999999999999999999999"
echo "col bg_cpu for 999999999999999999999999999999"
while [ 1 ]
do
echo "SELECT db_time,db_cpu,bg_time,bg_cpu FROM (SELECT stat_name,value FROM v\$sys_time_model) PIVOT (MAX(value) FOR stat_name IN ('DB time' AS db_time,'DB CPU' AS db_cpu,'background elapsed time' AS bg_time,'background cpu time' AS bg_cpu));"
sleep 10
done
) | sqlplus /nolog


つづきは、次のエントリーで :)

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

2014年7月26日 (土)

TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #3

つづきです。
バッファキャッシュにヒットした場合はどうかというと、物理I/Oは発生しないので、そのまんまの結果ですよね。(^^;;;

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 195 0.11 0.18 0 6287 0 2907
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 197 0.11 0.18 0 6287 0 2907

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110 (SCOTT)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2907 2907 2907 NESTED LOOPS (cr=6287 pr=0 pw=0 time=206651 us)
2907 2907 2907 NESTED LOOPS (cr=3380 pr=0 pw=0 time=116870 us cost=5362 size=1744812 card=2851)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID BATCHED HIGH_CLUSTERING_FACTOR (cr=3108 pr=0 pw=0 time=42395 us cost=2861 size=872406 card=2851)
2907 2907 2907 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=201 pr=0 pw=0 time=12292 us cost=8 size=0 card=2851)(object id 93727)
2907 2907 2907 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=272 pr=0 pw=0 time=36902 us cost=0 size=0 card=1)(object id 93725)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2907 pr=0 pw=0 time=37280 us cost=1 size=306 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2907 NESTED LOOPS
2907 NESTED LOOPS
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID BATCHED) OF 'HIGH_CLUSTERING_FACTOR' (TABLE)
2907 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_HIGH_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_LOW_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'LOW_CLUSTERING_FACTOR' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 195 0.00 0.00
SQL*Net message from client 195 0.00 0.15
SQL*Net more data to client 193 0.00 0.01
********************************************************************************

12cで、optimizer_features_enable='11.2.0.1'にしてバッファキャッシュをクリアすれば、11gと同じ実行計画(TABLE ACCESS BY INDEX ROWID)になって、db file parallel readになるよね!

という確認もしておいた!

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.42 1.59 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 195 18.34 61.02 2450 6287 0 2907
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 197 18.77 62.63 2450 6287 0 2907

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110 (SCOTT)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2907 2907 2907 NESTED LOOPS (cr=6287 pr=2450 pw=0 time=92881090 us)
2907 2907 2907 NESTED LOOPS (cr=3380 pr=2232 pw=0 time=59488238 us cost=5362 size=1744812 card=2851)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=3108 pr=2221 pw=0 time=31142410 us cost=2861 size=872406 card=2851)
2907 2907 2907 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=201 pr=7 pw=0 time=3493710 us cost=8 size=0 card=2851)(object id 93727)
2907 2907 2907 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=272 pr=11 pw=0 time=10377721 us cost=0 size=0 card=1)(object id 93725)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2907 pr=218 pw=0 time=13380036 us cost=1 size=306 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2907 NESTED LOOPS
2907 NESTED LOOPS
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID BATCHED) OF 'HIGH_CLUSTERING_FACTOR' (TABLE)
2907 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_HIGH_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_LOW_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'LOW_CLUSTERING_FACTOR' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 195 0.00 0.21
Disk file operations I/O 1 0.00 0.00
db file sequential read 452 0.21 1.54
SQL*Net message from client 195 15.35 16.71
SQL*Net more data to client 193 0.00 0.47
db file parallel read 190 0.05 5.91
********************************************************************************


整形前SQLトレースより抜粋 (db file parallel readが索引で現れている

WAIT #140513423347200: nam='db file parallel read' ela= 16615 files=1 blocks=13 requests=13 obj#=93726 tim=6258017207

straceより抜粋...io_submit()、だよね〜。:)

io_submit(140513465466880, 13, ...中略... ) = 13


最後に、11g R2 11.2.0.1で同じことを確認(確認するまでもないんだけどね)

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 169 0.58 6.88 2148 5439 0 2509
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 171 0.58 6.88 2148 5439 0 2509

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84 (SCOTT)

Rows Row Source Operation
------- ---------------------------------------------------
2509 NESTED LOOPS (cr=5439 pr=2148 pw=0 time=19292592 us)
2509 NESTED LOOPS (cr=2930 pr=1931 pw=0 time=24524478 us cost=5012 size=1530612 card=2501)
2509 TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=2683 pr=1922 pw=0 time=24392808 us cost=2510 size=765612 card=2502)
2509 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=174 pr=5 pw=0 time=11616 us cost=8 size=0 card=2502)(object id 113222)
2509 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=247 pr=9 pw=0 time=0 us cost=0 size=0 card=1)(object id 113220)
2509 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2509 pr=217 pw=0 time=0 us cost=1 size=306 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2509 NESTED LOOPS
2509 NESTED LOOPS
2509 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'HIGH_CLUSTERING_FACTOR' (TABLE)
2509 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_HIGH_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2509 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_LOW_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2509 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'LOW_CLUSTERING_FACTOR' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 169 0.00 0.00
SQL*Net message from client 169 24.57 25.54
Disk file operations I/O 1 0.00 0.00
db file parallel read 167 0.09 5.66
db file sequential read 357 0.01 0.92
SQL*Net more data to client 167 0.00 0.01
********************************************************************************

11gまでは、実行計画上には現れず実行時に内部的に行っていたI/O最適化の動きが、12cからは実行計画上でも確認できるようになったというのは分かり易くていい。
時間あったら、v$sesstatも載せるかも。 :)

さて、12c 12.1.0.2.0のダウンロードも終わったので、別の楽しみが増えましたよね、みなさん!

ダウンロード! しましたか〜〜〜〜〜っ! :)

みなさんの、12c ネタ祭りを楽しみにしております。 (^^


TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #1
TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #2

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

2014年7月25日 (金)

TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #2

Oracle Database 12c R1 12.1.0.2.0 EEが公開されましたが、12.1.0.1.0で試してあります。

注)バッファキャッシュをクリア後にSQL文を実行してあります。

SELECT
/*+
leading(t2 t1)
use_nl(t2 t1)
index(t2 pk_high_clustering_factor)
*/
t2.id
,t2.name
,t1.name
FROM
low_clustering_factor t1
INNER JOIN high_clustering_factor t2
ON
t1.id = t2.id
WHERE
t2.id BETWEEN 30001 AND 35000

上記のSQL文でTABLE ACCESS BY INDEX ROWID BATCHED操作を行わせてみた。

以下、SQLトレースの結果です。予想通り db file parallel readが発生しています。
高いクラスタリングファクタを持つ索引をアクセス、不連続なROWDをかき集めかき集めたROWIDをある程度まとめて1度のI/Oリクエストでバッファキャッシュへ読み込む動作ですよね!

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.07 6 6 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 195 0.48 4.92 2288 6338 0 2907
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 197 0.50 4.99 2294 6344 0 2907

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110 (SCOTT)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2907 2907 2907 NESTED LOOPS (cr=6338 pr=2288 pw=0 time=2150320 us)
2907 2907 2907 NESTED LOOPS (cr=3431 pr=2087 pw=0 time=6036546 us cost=3368 size=1159620 card=3514)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID BATCHED HIGH_CLUSTERING_FACTOR (cr=3107 pr=2082 pw=0 time=5948676 us cost=1012 size=579810 card=3514)
2907 2907 2907 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=201 pr=0 pw=0 time=15435 us cost=11 size=0 card=3514)(object id 93727)
2907 2907 2907 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=324 pr=5 pw=0 time=56981 us cost=0 size=0 card=1)(object id 93725)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2907 pr=201 pw=0 time=312848 us cost=1 size=165 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2907 NESTED LOOPS
2907 NESTED LOOPS
2907 TABLE ACCESS (BY INDEX ROWID BATCHED) OF 'HIGH_CLUSTERING_FACTOR' (TABLE)
2907 INDEX (RANGE SCAN) OF 'PK_HIGH_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 INDEX (UNIQUE SCAN) OF 'PK_LOW_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 TABLE ACCESS (BY INDEX ROWID) OF 'LOW_CLUSTERING_FACTOR' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 195 0.00 0.00
db file sequential read 209 0.03 0.32
SQL*Net message from client 195 19.95 21.21
db file parallel read 194 0.09 4.33
SQL*Net more data to client 193 0.00 0.01
********************************************************************************

SQLトレース(整形前)のトレースログより : db file parallel read (この時は、最小 3blocks〜15blocksの範囲で行われていた。

WAIT #139657829206792: nam='db file parallel read' ela= 64710 files=1 blocks=12 requests=12 obj#=93726 tim=4037078687

ちなみにstraceでみるとio_submitが呼び出されていて11gの頃と変わりはなかった :)

つまり、実行計画上、db file parallel readやるからね!! 物理I/Oを行うときは! という意思が明確に表示されるようになった。 
分かり易くなっていい!!!!
これだと物理I/Oがあれば、db file parallel readやってるな〜と実行計画を見ただけで想像できますな:)




TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #1

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

2014年7月23日 (水)

TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #1

Oracle® Database SQLチューニング・ガイド 12cリリース1(12.1) - ROWIDによる表アクセス: 例にも記載され、昨年試していたときにも現れていた、table access by index rowid batched という操作って、実際のところdb file parallel readをやるよ! と実行計画に表すようになったということだよね! それだけだよね。

ということを確認してみた。(TODOと書いてから1年経過してしまった orz )

詳細はあした以降に書きまする。 :)

20140723_220538

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

2014年6月11日 (水)

html5j パフォーマンス部 @ DeNA へ参加してきた

html5j パフォーマンス部 第二回勉強会へ参加してきた。

勉強会2連チャンでした :)

html5jとか勉強会(そんな名称になる前から参加してました :))には何度か参加していますが、パフォーマンスという単語に脊髄反応して当日のセッション内容を確認もぜず参加してしまったw のは内緒。


データベースが遅い! と呼ばれ、調査すると本当に遅いのはブラウザの上、なんてことも意外に多いんですよね。

10301204_712086918857590_3502558762

講義を受けてるみたいで脳に汗かいた。 

1.品質管理と統計学「IT業界へどうつなぐ?」 - 成蹊大学 / 中西寛子
2.ソフトウェア工学の基礎を学ぶ - 大槻肇


ところで、
今回で2回目のようですが、16回先までテーマが決まっているようでおどろきました。
他の勉強会を運営している方がってどうしてるんだろ?。  

そして、DeNAさんのセミナー会場いいですよね〜〜〜。
都合がつけば次回も参加すると思います。 :)

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