Oracle DatabaseでSQLの性能計測3(SQLトレース編)【Oracle Database or GoldenGate Advent Calendar 2018 Day 20】
- 1. SQLトレースとは？
- 2. SQLトレースの出力先と出力可能サイズの確認
- 3. DBMS_SESSIONパッケージでセッション単位でSQLトレースを取得
- 4. 出力されたSQLトレースの確認と整形(tkprof)
- 5. 整形(tkprof)済みトレースの見方
- 6. 整形(tkprof)前の生トレース確認
- 7. まとめ
このエントリは Oracle Database or GoldenGate Advent Calendar 2018 の Day 20 の
記事となります。一日遅れてしまいました。スミマセン彡(-)(-)
Oracle Database or GoldenGate Advent Calendar 2018
https://adventar.org/calendars/3525
今日は Oracle Database で SQLトレースを取得して、
SQLの実行時間を計測してみるやで彡(ﾟ)(ﾟ) Day 8 の続きになります。
Oracle DatabaseでSQLの性能計測2(DBMS_XPLAN＆DBMS_SQLTUNE編)
【Oracle Database or GoldenGate Advent Calendar 2018 Day 8】
https://gonsuke777.hatenablog.com/entry/2018/12/08/215428
1. SQLトレースとは？
セッション内で実行される個々のSQLのパフォーマンス情報を取得する機能です。
この記事でも幾つか記述しますが、下記のような情報を採取する事が可能です。
Oracle Database SQLチューニング・ガイド 18c
23.1.3.1 SQLトレース機能の概要
https://docs.oracle.com/cd/E96517_01/tgsql/performing-application-tracing.html#GUID-374ABE48-C63F-4A7B-B80E-906A51F9246C
SQLトレースは、文ごとに次の統計を生成します。
・解析、実行、フェッチのカウント
・CPU時間および経過時間
・物理読取りおよび論理読取り
・処理された行数
：
2. SQLトレースの出力先と出力可能サイズの確認
下記マニュアルの手順通り、まずSQLトレースの出力先と出力可能サイズを確認します。
Oracle Database SQLチューニング・ガイド 18c
23.4.1 手順1: トレース・ファイル管理用の初期化パラメータの設定
https://docs.oracle.com/cd/E96517_01/tgsql/performing-application-tracing.html#GUID-A4FD1913-D35A-4EE6-9CE8-361718433E91
まず DIAGNOSTIC_DESTパラメータ のパスを確認します。
SHOW PARAMETER DIAG NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ diagnostic_dest string /u01/app/oracle
上記の結果より、自動診断リポジトリ(ADR)ホーム の場所を確定します。
Oracle Database データベース・リファレンス 18c
1.100 DIAGNOSTIC_DEST
https://docs.oracle.com/cd/E96517_01/refrn/DIAGNOSTIC_DEST.html#GUID-BFE9648A-24D4-4717-8C33-21B0868401EE
:
DIAGNOSTIC_DESTによって指定されるディレクトリの構造は、次のとおりです。<diagnostic_dest>/diag/rdbms/<dbname>/<instname>
:ADRホーム・ディレクトリの下に配置されるファイルは、次のとおりです。
今回の環境は Single(非RAC)環境 で DB名, インスタンス名, いずれも orclcdb となります。
トレースファイルは ADRホームのtrace配下に出力されるため、今回は下記のパスに出力されます。
# トレースファイルの出力先 /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace
MAX_DUMP_FILE_SIZEパラメータも確認します。今回の環境は unlmited にセットされています。
SHOW PARAMETER MAX_DUMP NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_dump_file_size string unlimited
3. DBMS_SESSIONパッケージでセッション単位でSQLトレースを取得
SQLトレースを取得するには幾つかの方法が有りますが、
今回は DBMS_SESSIONパッケージのSESSION_TRACE_ENABLEプロシージャで、
セッション単位でSQLトレースを取得します。10046？知らんなぁ？彡(ﾟ)(ﾟ)
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 18c
146 DBMS_SESSION
SESSION_TRACE_ENABLEプロシージャ
https://docs.oracle.com/cd/E96517_01/arpls/DBMS_SESSION.html#GUID-0EF8429F-B552-40D0-885C-3C560A66152D
トレースファイルを識別し易くするために、TRACEFILE_IDENTIFIERパラメータもセットします。
Oracle Databaseデータベース・リファレンス, 18c
1.336 TRACEFILE_IDENTIFIER
https://docs.oracle.com/cd/E96517_01/refrn/TRACEFILE_IDENTIFIER.html#GUID-976352B6-1189-4B73-835B-9D5441D11115
下記がSQLトレース取得の実行サンプルになります。TRACEFILE_IDENTIFIERパラメータを
セットしつつ、待機イベント／バインド変数／行ソース統計を全て取得します。
それぞれのパラメータ(waits, binds, plan_stat)の意味は、生トレースの章で解説します。
-- TRACEFILE_IDENTIFIERパラメータをセット ALTER SESSION SET TRACEFILE_IDENTIFIER = "AYSHIBAT"; -- SQLトレースを有効化 EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE, plan_stat => 'ALL_EXECUTIONS'); -- サンプルSQL SELECT /*+ MONITOR */ B.ITEM_NAME , TRUNC(A.ORDER_DATE, 'DD') AS ORDER_DAILY , COUNT(*) FROM ORDER_TBL A , ITEM_TBL B WHERE A.ITEM_NO = B.ITEM_NO -- AND B.ITEM_NO BETWEEN 1 AND 100 AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801' GROUP BY B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD') ORDER BY 1; -- SQLトレースを無効化 EXEC DBMS_SESSION.SESSION_TRACE_DISABLE; Session altered. PL/SQL procedure successfully completed. 261 rows selected. PL/SQL procedure successfully completed.
4. 出力されたSQLトレースの確認と整形(tkprof)
2.で確認したディレクトリに移動して、SQLトレースファイルを確認します。
cd /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace ls -ltra *AYSHIBAT* [oracle@localhost trace]$ [oracle@localhost trace]$ cd /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace [oracle@localhost trace]$ ls -ltra *AYSHIBAT* -rw-r-----. 1 oracle oinstall 123298 Dec 21 01:03 orclcdb_ora_26747_AYSHIBAT.trm -rw-r-----. 1 oracle oinstall 1842101 Dec 21 01:03 orclcdb_ora_26747_AYSHIBAT.trc ★出力されたトレース [oracle@localhost trace]$
出力されたトレースファイルをtkprofコマンドで整形します。
tkprofにも幾つかオプション(sys=no, aggregate=no, sortなど)が有るのですが、
今回はシンプルに出力ファイル名のみ指定しますやで彡(ﾟ)(ﾟ)
tkprof orclcdb_ora_26747_AYSHIBAT.trc orclcdb_ora_26747_AYSHIBAT.trc.txt ls -ltra *AYSHIBAT* TKPROF: Release 18.0.0.0.0 - Development on Fri Dec 21 01:29:27 2018 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. -rw-r-----. 1 oracle oinstall 123298 Dec 21 01:03 orclcdb_ora_26747_AYSHIBAT.trm -rw-r-----. 1 oracle oinstall 1842101 Dec 21 01:03 orclcdb_ora_26747_AYSHIBAT.trc -rw-r--r--. 1 oracle oinstall 41833 Dec 21 01:29 orclcdb_ora_26747_AYSHIBAT.trc.txt ★整形されたトレース
5. 整形(tkprof)済みトレースの見方
5-1. 整形(tkprof)済みトレースの抜粋
整形(tkprof)済みトレースから、対象SQLの結果を抜粋します。
******************************************************************************** : SELECT /*+ MONITOR */ B.ITEM_NAME , TRUNC(A.ORDER_DATE, 'DD') AS ORDER_DAILY , COUNT(*) FROM ORDER_TBL A , ITEM_TBL B WHERE A.ITEM_NO = B.ITEM_NO -- AND B.ITEM_NO BETWEEN 1 AND 100 AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801' GROUP BY B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD') ORDER BY 1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.02 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 19 3.21 13.70 6669 8926 0 261 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 21 3.23 13.73 6669 8926 0 261 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 129 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 261 261 261 SORT GROUP BY (cr=8926 pr=6669 pw=6669 time=13704807 us starts=1 cost=30 size=208 card=8) 26000 26000 26000 HASH JOIN (cr=8926 pr=6669 pw=6669 time=13880000 us starts=1 cost=29 size=676 card=26) 2600000 2600000 2600000 TABLE ACCESS FULL ORDER_TBL (cr=8835 pr=0 pw=0 time=378587 us starts=1 cost=3 size=286 card=26) 10 10 10 TABLE ACCESS FULL ITEM_TBL (cr=91 pr=0 pw=0 time=856 us starts=1 cost=26 size=4500 card=300) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 19 0.00 0.00 direct path write temp 6669 0.00 11.31 direct path read temp 6669 0.00 0.06 SQL*Net message from client 19 3.38 3.48 ******************************************************************************** :
5-2. 整形(tkprof)済みトレースの表形式統計の確認
下記の部分(表形式の統計)に着目します。
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.02 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 19 3.21 13.70 6669 8926 0 261 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 21 ★3.23 ★13.73 ★6669 ★8926 0 ★261
対象のSQLについて、cpu時間で3.23秒、経過時間が13.73秒、diskアクセスが6669回、
一貫性読取で8926ブロック、フェッチ行数は261行であることが確認できます。
それぞれの統計の意味はマニュアルも参照して下さい。
Oracle Database SQLチューニング・ガイド 18c
23.6.2.5.2 TKPROFの表形式の統計
https://docs.oracle.com/cd/E96517_01/tgsql/performing-application-tracing.html#GUID-B6EE4109-5946-415D-9B75-EC1B73D16B87
整形(tkprof)済みSQLトレースの冒頭にも載ってますやね！彡(ﾟ)(ﾟ)
: ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** :
5-3. 整形(tkprof)済みトレースの行ソース統計の確認
次は下記の部分(行ソース統計)に着目します。
Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 261 261 261 SORT GROUP BY (cr=8926 pr=6669 pw=6669 time=13704807 us starts=1 cost=30 size=208 card=8) 26000 26000 26000 HASH JOIN (cr=8926 pr=6669 pw=6669 time=13880000 us starts=1 cost=29 size=676 card=26) 2600000 2600000 2600000 TABLE ACCESS FULL ORDER_TBL (cr=8835 pr=0 pw=0 time=378587 us starts=1 cost=3 size=286 card=26) 10 10 10 TABLE ACCESS FULL ITEM_TBL (cr=91 pr=0 pw=0 time=856 us starts=1 cost=26 size=4500 card=300)
まずは time値 に着目します。実行計画の各ステップで、
どの程度時間が掛かったかを表します。μ秒(1/1,000,000秒)表記です。
Row Source Operation --------------------------------------------------- SORT GROUP BY time=13704807 us ★ここまでで13.70秒掛かっている。 HASH JOIN time=13880000 us ★ここまでで13.88秒掛かっている。 TABLE ACCESS FULL ORDER_TBL time=378587 us TABLE ACCESS FULL ITEM_TBL time=856 us
上記例だと HASH JOIN までのステップで 13880000us(13.88秒)、
SORT GROUP BY までのステップで 13704807us(約13.7秒)掛かっています。
※矛盾が有りますが、傾向を掴む事が重要なので多少の誤差は(ﾟεﾟ )ｷﾆｼﾅｲ!
次に cr値、pr値、pw値に着目。cr値は一貫性読込のブロック数、
pr値は物理読込の回数、pw値は物理書込の回数を表します。
Row Source Operation --------------------------------------------------- SORT GROUP BY cr=8926 pr=6669 pw=6669 HASH JOIN cr=8926 pr=6669 pw=6669 ★ここで6669回の物理読込／物理書込が発生 TABLE ACCESS FULL ORDER_TBL cr=8835 pr=0 pw=0 ★ここまでで8835ブロックの一貫性読込が発生 TABLE ACCESS FULL ITEM_TBL cr=91 pr=0 pw=0
上記例だと TABLE ACCESS FULL ORDER_TBL のステップで 8835ブロックの一貫性読込が、
HASH JOIN のステップで 6669回の物理読込／物理書込が発生している事が確認できます。
その次はオプティマイザの予測値と実測値の確認。
cost がオプティマイザの予測コスト値、size が予測バイト数、card が予測行数になりますが、
その中の予測行数(card)と実測行数(Rows)を比較します。
DBMS_XPLAN や DBMS_SQLTUNE の時と同じ流れですやね彡(ﾟ)(ﾟ)
Rows (1st) Row Source Operation ---------- --------------------------------------------------- 261 SORT GROUP BY card=8 26000 HASH JOIN card=26 2600000 TABLE ACCESS FULL ORDER_TBL card=26 ★実測値(2,600,000行)と予測値(26行)が乖離 10 TABLE ACCESS FULL ITEM_TBL card=300
上記例だと ORDER_TBL の実測行数(2,600,000行)と予測行数(26行)が乖離しているのが確認できます。
各行ソース統計の値の意味については、マニュアルも参照して下さい。
Oracle Database SQLチューニング・ガイド 18c
23.6.2.5.4 TKPROFの行ソースの操作
https://docs.oracle.com/cd/E96517_01/tgsql/performing-application-tracing.html#GUID-77906451-193D-4D73-BA49-95A64445C673
下記のドキュメントも確認ですやで彡(ﾟ)(ﾟ) ※要My Oracle Supportアカウント
TKProf Interpretation (9i and above) (ドキュメントID 760786.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=760786.1
5-4. 整形(tkprof)済みトレースの待機イベントの確認
最後に下記の部分(待機イベント部分)に着目します。
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ------------------------------ Waited ---------- ------------ SQL*Net message to client 19 0.00 0.00 direct path write temp 6669 0.00 11.31 ★direct path write tempで時間が掛かっている。 direct path read temp 6669 0.00 0.06 SQL*Net message from client 19 3.38 3.48
以上の結果より、下記の事実が確認できます。
- ORDER_TBL表の Rows(実測値) と card(予測値)が乖離している。
- HASH JOINのステップで時間が掛かっている。
- direct path write tempの待機イベントで時間が掛かっている。
このようなケースでは以下のようなチューニングを試すのがセオリーとなります。※前回のコピペ
6. 整形(tkprof)前の生トレース確認
6-1. 整形(tkprof)前の生トレースで確認できる情報
整形前の生トレースを見ると、下記の情報も確認できます。
- 待機イベントの(WAIT行)
- バインド変数(BINDSセクション)
- 行ソース統計(STAT行)
- etc...
下記に生トレースに関する先端技様の良い記事もあります。是非ご参照を彡(^)(^)
整形する前の SQL トレースを見ることが有益なケース
http://www.intellilink.co.jp/article/column/ora-report20150507.html
6-2. WAIT行(待機イベント)を生トレースで確認
DBMS_SESSION.SESSION_TRACE_ENABLE の waits => TRUE は、
SQLトレースにWAIT行を出力するオプションとなります。
下記は生トレースのWAIT行サンプルです。
-- 生トレースのWAIT行サンプル ： WAIT #140512706274008: nam='SQL*Net message to client' ela= 11 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=14331548081 WAIT #140512706274008: nam='direct path write temp' ela= 4310 file number=203 first dba=391 block cnt=1 obj#=-1 tim=14331638606 WAIT #140512706274008: nam='direct path write temp' ela= 1176 file number=203 first dba=392 block cnt=1 obj#=-1 tim=14331640040 WAIT #140512706274008: nam='direct path write temp' ela= 844 file number=203 first dba=393 block cnt=1 obj#=-1 tim=14331641358 ： FETCH #140512706274008:c=158,e=157,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=44130803,tim=14345356617 WAIT #140512706274008: nam='SQL*Net message from client' ela= 3721 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=14345360474 WAIT #140512706274008: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=14345360605 FETCH #140512706274008:c=0,e=87,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=44130803,tim=14345360673 WAIT #140512706274008: nam='SQL*Net message from client' ela= 4547 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=14345365303 WAIT #140512706274008: nam='SQL*Net message to client' ela= 286 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=14345365981 FETCH #140512706274008:c=635,e=633,p=0,cr=0,cu=0,mis=0,r=5,dep=0,og=1,plh=44130803,tim=14345366311 ：
FETCH ⇒ SQL*Net message from client ⇒ SQL*Net message to client の流れが確認できますね～彡(ﾟ)(ﾟ)
6-3. BINDSセクション(バインド変数)を生トレースで確認
DBMS_SESSION.SESSION_TRACE_ENABLE の binds => TRUE は、
SQLトレースにBINDSセクションを出力するオプションとなります。
下記は生トレースのBINDSサンプルです。
-- 生トレースのBINDSサンプル : BINDS #140512706270952: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0 kxsbbbfp=7fcba3a6d150 bln=22 avl=03 flg=05 value=715 Bind#1 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24 kxsbbbfp=7fcba3a6d168 bln=22 avl=02 flg=01 value=5 :
バインド変数の取得＆参照は完全な代替機能が無く、
生トレースの中では最もよく参照される箇所ではないでしょうか彡(ﾟ)(ﾟ)
6-4. STAT行(行ソース統計)を生トレースで確認
DBMS_SESSION.SESSION_TRACE_ENABLE の plan_stat => 'ALL_EXECUTIONS' は、
SQLトレースにSTAT行(行ソース統計)の出力を毎回強制するオプションとなります。
下記は生トレースのSTAT行サンプルです。
： STAT #140512706274008 id=1 cnt=261 pid=0 pos=1 obj=0 op='SORT GROUP BY (cr=8926 pr=6669 pw=6669 str=1 time=13704807 us cost=30 size=208 card=8)' STAT #140512706274008 id=2 cnt=26000 pid=1 pos=1 obj=0 op='HASH JOIN (cr=8926 pr=6669 pw=6669 str=1 time=13880000 us cost=29 size=676 card=26)' STAT #140512706274008 id=3 cnt=2600000 pid=2 pos=1 obj=79972 op='TABLE ACCESS FULL ORDER_TBL (cr=8835 pr=0 pw=0 str=1 time=378587 us cost=3 size=286 card=26)' STAT #140512706274008 id=4 cnt=10 pid=2 pos=2 obj=79975 op='TABLE ACCESS FULL ITEM_TBL (cr=91 pr=0 pw=0 str=1 time=856 us cost=26 size=4500 card=300)' ：
デフォルト値ではSQLの初回実行のみSTAT行(行ソース統計)が出力されますが、
plan_stat => 'ALL_EXECUTIONS' で毎回出力できるようにするんですやね彡(ﾟ)(ﾟ)
7. まとめ
Standard Edition な環境でも、SQLトレースで闘える！彡(^)(^)
でも Tuning Pack(DBMS_SQLTUNE) は便利なんで、こちらもよろしくです。
SQLトレースはまだまだネタが多いですやね。TRCSESSユーティリティとか、
V$DIAG_TRACE_FILE や V$DIAG_TRACE_FILE_CONTENTS による動的ビューからのトレース採取とか。
こちらはまたの機会に。彡(ﾟ)(ﾟ)
OCI(Oracle Cloud Infrastructure)のComputeインスタンスで端末(PC等)へのX Window ssh転送が出来るように設定する。
OCI(Oracle Cloud Infrastructure)のComputeインスタンスで端末(PC等)にX Windowがssh転送が出来るように設定してみるやで。
環境は OCI で OEL 7.6(Oracle Enterprise Linux 7.6) の仮想マシンで試してみます。
1. ターミナルソフト(teraterm等)のX転送設定
まずターミナルソフト(teraterm等)のX転送設定を有効にします。
下記のような記事をご参照下さい。
TeratermとX Windowサーバでsshのx11フォワーディング機能を利用する https://orebibou.com/2015/02/teraterm%E3%81%A7ssh%E3%81%AEx11%E3%83%95%E3%82%A9%E3%83%AF%E3%83%BC%E3%83%87%E3%82%A3%E3%83%B3%E3%82%B0%E6%A9%9F%E8%83%BD%E3%82%92%E5%88%A9%E7%94%A8%E3%81%99%E3%82%8B/
2. 端末(PC等)へのXサーバー(Xming等)導入と起動
端末(PC等)にXサーバー(Xming等)を導入して、起動しておきます。
下記のような記事をご参照下さい。
Xming環境構築
https://cell-innovation.nig.ac.jp/surfers/R_Xming.html
3. sshd_config の設定確認／変更／再起動
opcユーザーでログインして、sshd_config を編集します。
sudo vi /etc/ssh/sshd_config
「X11Forwarding yes」がセットされているのを確認します。
X11Forwarding yes
「AddressFamily inet」を追記します。
AddressFamily inet
上記設定については↓のbugzillaに情報があります。
ssh -X shows shows error: X11 forwarding request failed on channel 0
https://bugzilla.redhat.com/show_bug.cgi?id=1436097
sshdサービスを再起動します。
sudo service sshd restart
4. ライブラリ(xauth)とテスト用アプリ(xeyes)のインストール
ライブラリ(xauth)とテスト用アプリ(xeyes)をyumでインストールします。
sudo yum install xorg-x11-xauth sudo yum install xeyes
5. DISPLAY環境変数の確認とxeyesの起動
一度ターミナルを落としてログインし直します。
その後DIAPLAY環境変数を確認です。上手く行っていれば、自動でセットされているはず。
env | grep -i display DISPLAY=localhost:10.0
xeyesを起動してみます。
xeyes
見事に起動！(｀･ω･)Ъ
6. AddressFamily inet の設定について
まずsshd_configに追記する「AddressFamily inet」の動作ですが、
これはsshdの接続でIPv4を強制することを意味します。
sshdで「IPv4」「IPv6」のどちらかのプロトコルのみ接続させる方法
https://www.server-memo.net/server-setting/ssh/addressfamily.html
そして前述の bugzilla のコメントでは、システム全体でIPv6が無効化されているのにも関わらず、
getaddrinfo()システムコールでIPv6のアドレス解決が失敗していると言及されています。
To my understanding, it looks like a problem that
getaddrinfo()is returning AF_INET6 addresses
even though it is disabled "systemwide" (at least on Fedora as I tested).
解決策として sshd_config に「AddressFamily inet」追記して、
明示的にIPv4を選択することがすることが提示されています。
You can resolve the problem by explicitly selecting ipv4 (not
anyas mentioned in the description):
AddressFamily inet
bugzilla の中では、IPv6無効化時の getaddrinfo()システムコールの動作についての
意見も出されているようですが、特に対応はされていないようです。
ちなみに 2018年12月の時点では、OCI(Oracle Cloud Infrastructure) の
VCN(OCIの仮想ネットワーク)は IPv6 に対応されてない事が明記されています。
Oracle Cloud Infrastructure VCN - FAQ
https://cloud.oracle.com/ja_JP/bare-metal-network/vcn/faq
VCNはIPv6をサポートしていますか。
いいえ。現在、VCNはIPv6をサポートしていません。
7. まとめ
sshd_configへの「AddressFamily inet」追記がハマりポイントですかね。
今回はOEL 7.6環境で検証してますが、RedHat7系やCentOS7系も
IPv6が無効化されている場合は同様なんかと？彡(ﾟ)(ﾟ)
8. (追記) 踏み台経由で xeyes を起動
下記のような構成の場合は……彡(ﾟ)(ﾟ)
PC端末 → Public Compute(踏み台) → Private Compute(xeyes起動)
踏み台 → Private Compute の SSH接続時に -Xオプションを追加します。
(↓踏み台へのSSH接続後に) ssh -X xxxx.xxxx.oraclevcn.com (↓Private Compute接続後に) xeyes
SDW(SQL Developer Web) on DBCS をお試ししてみる。【Oracle Database or GoldenGate Advent Calendar 2018 Day 11】【Oracle Cloud その2 Day 11】
- 1. SQL Developer Web の有効化
- 2. SQL Developer Web へのアクセス
- 3. Worksheetタブをお試し。
- 4. DBAタブのお試し
- 5. OSタブのお試し
- 6. まとめ
このエントリは Oracle Database or GoldenGate Advent Calendar 2018 の Day 11 の記事となります。
Oracle Database or GoldenGate Advent Calendar 2018
https://adventar.org/calendars/3525
Oracle Cloud その2 Day 11 の記事も兼ねてますやで彡(ﾟ)(ﾟ)
Oracle Cloud その2 Advent Calendar 2018
https://adventar.org/calendars/3522
このエントリでは DBCS で利用可能な SDW(SQL Developer Web)をお試ししてみるやで彡(ﾟ)(ﾟ)
1. SQL Developer Web の有効化
まず下記マニュアルの通り、SQL Developer Web を有効化してみます。
Enabling a Schema for SQL Developer Web
https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/use-sql-dev-web-this-service.html#GUID-FDEF7370-7809-4037-9770-AE548885B19C
まずはパスワードのファイルを用意します。
sudo -s touch /home/oracle/password.txt chmod 600 /home/oracle/password.txt # ログイン用のパスワードを記述する。 vi /home/oracle/password.txt
次にordsコマンドで SQL Developer Web を有効化します。
SDWのスキーマは"sdwtest"、プラガブルDBの PDB4 にセットアップします。
cd /var/opt/oracle/ocde/assistants/ords ./ords -ords_action="enable_schema_for_sdw" \ -ords_sdw_schema="sdwtest" \ -ords_sdw_schema_password="/home/oracle/password.txt" \ -ords_sdw_schema_container="pdb4" \ -ords_sdw_schema_enable_dba="TRUE"
下記は実行結果のサンプルとなります。
./ords -ords_action="enable_schema_for_sdw" \ > -ords_sdw_schema="sdwtest" \ > -ords_sdw_schema_password="/home/oracle/password.txt" \ > -ords_sdw_schema_container="pdb4" \ > -ords_sdw_schema_enable_dba="TRUE" : INFO: DBTools ORDS Standalone RPM installed : dbtools_ords_standalone-18.1.0.11.22.15-1.el6.x86_64 INFO: DBTools ORDS Standalone RPM "/var/opt/oracle/rpms/dbtools/dbtools_ords_standalone-18.1.0.11.22.15-1.el6.x86_64.rpm" MD5 : 480355ac3ce0f357d5741c2c2f688901 : : INFO: To access SQL Developer Web through DBaaS Landing Page, the schema "pdb4/sdwtest" needs to be provided... INFO: "SDWTEST" schema in the "pdb4" container for SQL Developer Web was enabled successfully...
2. SQL Developer Web へのアクセス
SQL Developer Web へのアクセス方法は幾つか有りますが、直接アクセスしてみます。
Accessing SQL Developer Web
https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/use-sql-dev-web-this-service.html#GUID-058B2B65-610A-4022-98A9-4BAA7D553DD5
下記のURLでアクセスします。
https://ip-address/ords/PDB名/スキーマ名/_sdw
今回はセットアップした下記のPDB、スキーマにアクセスします。
画面左上のボタンからSQL Developer Webに遷移します。
3. Worksheetタブをお試し。
Worksheetタブをお試ししてみます。
SQL Developer といえば、まずはコイツですやね彡(ﾟ)(ﾟ)
v$sessionをSELECTしてみると。。。
結果が取れてきてますやね彡(^)(^)
さらにSQL Developer でも良く使うFormat(整形)機能もあります。
きっちり整形されてますやね彡(^)(^)
4. DBAタブのお試し
DBAタブをお試ししてみます。幾つか選択肢は有りますが、まずはAlertsを…彡(ﾟ)(ﾟ)
アラートログ相当の内容を確認できます。
次はSessionsを見てみると…彡(ﾟ)(ﾟ)
v$session相当の情報が確認できますやね彡(^)(^)
5. OSタブのお試し
最後にOSタブをお試ししてみます。CPUを見てみます。
mpstat -P all相当の情報が確認できますやね。彡(ﾟ)(ﾟ)
6. まとめ
SQL Developer Web は Oracle APEX で作られているのですが、APEXの実力が垣間見えます。
Jeff Smithさんがリーダとして作っていますが、彼のブログもチェックしてやで彡(ﾟ)(ﾟ)
Oracle DatabaseでSQLの性能計測2(DBMS_XPLAN＆DBMS_SQLTUNE編)【Oracle Database or GoldenGate Advent Calendar 2018 Day 8】
このエントリは Oracle Database or GoldenGate Advent Calendar 2018 の Day 8 の記事となります。
Oracle Database or GoldenGate Advent Calendar 2018
https://adventar.org/calendars/3525
今日は Oracle Database の DBMS_XPLANパッケージ と DBMS_SQLTUNE で
SQLの実行時間を計測してみるやで彡(ﾟ)(ﾟ) Day 1 の続きになります。
Oracle DatabaseでSQLの性能計測1(SET AUTOTRACE TRACEONLY と SET TIMING ON編)
【Oracle Database or GoldenGate Advent Calendar 2018 Day 1】
https://gonsuke777.hatenablog.com/entry/2018/12/01/210651
1. DBMS_XPLANによるSQL実行時間の計測
1-1. DBMS_XPLAN とは？
Oracle Database の 標準パッケージ で、SQLの実行計画を出力する機能を持ちます。
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 18c
210 DBMS_XPLAN
https://docs.oracle.com/cd/E96517_01/arpls/DBMS_XPLAN.html#GUID-BAD480AA-351A-48FE-A8E7-F0D8EF643EBF
DBMS_XPLANパッケージには幾つかの機能が有りますが、今回は DISPLAY_CURSORファンクションの
ALLSTATS書式による実行時の統計出力でSQLの実行時間を計測しますやで彡(ﾟ)(ﾟ)
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 18c
210.4.4 DISPLAY_CURSORファンクション
https://docs.oracle.com/cd/E96517_01/arpls/DBMS_XPLAN.html#GUID-0EE333AF-E9AC-40A4-87D5-F6CF59D6C47B
1-2. ALLSTATS書式の前提条件
以下の「どちらか」の条件を満たして、SQLを実行する必要があります。
更に以下の条件を満たす必要があります。
- SQL の実行が終了していること(※Ctrl+Cによる強制終了でもOK)
1-3. DBMS_XPLAN.DISPLAY_CURSOR(ALLSTATS書式)の実行サンプル
下記が実行サンプルになります。STATISTICS_LEVELパラメータ を
セッション単位（ALTER SESSION ～）で ALL にセットしています。
-- パラメータをセット ALTER SESSION SET STATISTICS_LEVEL = ALL; -- サンプルSQL SELECT /*+ MONITOR */ B.ITEM_NAME , TRUNC(A.ORDER_DATE, 'DD') AS ORDER_DAILY , COUNT(*) FROM ORDER_TBL A , ITEM_TBL B WHERE A.ITEM_NO = B.ITEM_NO -- AND B.ITEM_NO BETWEEN 1 AND 100 AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801' GROUP BY B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD') ORDER BY 1; -- DBMS_XPLAN.DISPLAY_CURSORによる実行計画の出力 SET LINESIZE 300; SET PAGESIZE 1000; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f9r2y6xdz6t84', NULL, 'ALL ALLSTATS LAST ADAPTIVE'));
実行結果は以下の通りです。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f9r2y6xdz6t84', NULL, 'ALL ALLSTATS LAST ADAPTIVE')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID f9r2y6xdz6t84, child number 0 ------------------------------------- An uncaught error happened in prepare_sql_statement : ORA-01403: no data found Plan hash value: 44130803 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 30 (100)| | 261 |00:00:07.37 | 9068 | 4366 | 6795 | | | | | | 1 | SORT GROUP BY | | 1 | 8 | 208 | 30 (7)| 00:00:01 | 261 |00:00:07.37 | 9068 | 4366 | 6795 | 20480 | 20480 |18432 (0)| | |* 2 | HASH JOIN | | 1 | 26 | 676 | 29 (4)| 00:00:01 | 26000 |00:00:07.20 | 9068 | 4366 | 6795 | 142M| 9292K| 17M (1)| 58368 | | 3 | TABLE ACCESS FULL| ORDER_TBL | 1 | 26 | 286 | 3 (0)| 00:00:01 | 2600K|00:00:00.60 | 8836 | 0 | 0 | | | | | |* 4 | TABLE ACCESS FULL| ITEM_TBL | 1 | 300 | 4500 | 26 (4)| 00:00:01 | 10 |00:00:00.06 | 91 | 0 | 0 | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / A@SEL$1 4 - SEL$1 / B@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."ITEM_NO"="B"."ITEM_NO") 4 - filter(TO_CHAR(INTERNAL_FUNCTION("B"."REGIST_DATE"),'YYYYMMDD')='20120801') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=2) "B"."ITEM_NAME"[VARCHAR2,100], TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"),'fmdd')[8], COUNT(*)[22] 2 - (#keys=1) "A"."ORDER_DATE"[DATE,7], "B"."ITEM_NAME"[VARCHAR2,100] 3 - (rowset=256) "A"."ITEM_NO"[NUMBER,22], "A"."ORDER_DATE"[DATE,7] 4 - (rowset=256) "B"."ITEM_NO"[NUMBER,22], "B"."ITEM_NAME"[VARCHAR2,100]
1-4. 出力された統計の統計の見方(DBMS_XPLAN.DISPLAY_CURSORのALLSTATS書式)
まず A-Time を確認します。ここで実行計画の各ステップで
実際に掛かった実行時間を確認できます。今回の例では全体で7.37秒、
HASH JOINまでのステップで7.2秒ほど掛かっているのが確認できます。
： Plan hash value: 44130803 -----------------------------------------…--------------…- | Id | Operation | Name |…| A-Time |…| -----------------------------------------…--------------…- | 0 | SELECT STATEMENT | |…|00:00:07.37 |…| ★全体で7.37秒程度時間が掛かっている。 | 1 | SORT GROUP BY | |…|00:00:07.37 |…| |* 2 | HASH JOIN | |…|00:00:07.20 |…| ★HASH JOIN までのステップで7.2秒ほど掛かっている。 | 3 | TABLE ACCESS FULL| ORDER_TBL |…|00:00:00.60 |…| |* 4 | TABLE ACCESS FULL| ITEM_TBL |…|00:00:00.06 |…| -----------------------------------------…--------------…- ：
次に E-Rows と A-Rows を確認します。E-Rows はオプティマイザの予測行数、 A-Rows は実測値として処理行数が確認できます。今回の例では STEP 3 の ORDER_TBL表 の予測と実測が大きく乖離していることが確認できます。
Plan hash value: 44130803 -----------------------------------------…----------…----------…- | Id | Operation | Name |…| E-Rows |…| A-Rows |…| -----------------------------------------…----------…----------…- | 0 | SELECT STATEMENT | |…| |…| 261 |…| | 1 | SORT GROUP BY | |…| 8 |…| 261 |…| |* 2 | HASH JOIN | |…| 26 |…| 26000 |…| | 3 | TABLE ACCESS FULL| ORDER_TBL |…| 26 |…| 2600K|…| ★E-Rows(予測行数) と A-Rows(実測行数) が乖離している。 |* 4 | TABLE ACCESS FULL| ITEM_TBL |…| 300 |…| 10 |…| -----------------------------------------…----------…----------…-
2. DBMS_SQLTUNEによるSQL実行時間の計測
2-1. DBMS_SQLTUNE とは？
Oracle Database の パッケージ で、SQLチューニング関連の機能を持ちます。
Enterprise Edition の Tuning Packオプション の機能となります。
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 18c
160 DBMS_SQLTUNE
https://docs.oracle.com/cd/E96517_01/arpls/DBMS_SQLTUNE.html#GUID-821462BF-1695-41CF-AFF7-FD23E9999C6AOracle Database ライセンス情報ユーザー・マニュアル 18c
1.6 機能、オプションおよびManagement Packの使用状況の確認
https://docs.oracle.com/cd/E96517_01/dblic/Licensing-Information.html#GUID-C3042D9A-5596-41A3-A08A-4581FED7634F
：
Oracle Tuning Pack
：
Oracle Tuning Packの機能は、データベース・サーバーAPIおよびコマンドライン・インタフェースでもアクセスできます。
：
・DBMS_SQLTUNE (注意事項を参照)
DBMS_SQLTUNEパッケージには幾つかの機能が有りますが、今回は REPORT_SQL_MONITORファンクションで
SQL監視(SQLモニタリング)のレポートを出力してみますやで彡(ﾟ)(ﾟ)
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 18c
160.5.27 REPORT_SQL_MONITORファンクション
https://docs.oracle.com/cd/E96517_01/arpls/DBMS_SQLTUNE.html#GUID-CFA1F851-1FC1-44D6-BB5C-76C3ADE1A483
2-2. DBMS_SQLTUNE.REPORT_SQL_MONITORファンクションの前提条件
以下の「どれか」の条件が満たされると、SQLの監視情報が自動でモニタリングされて、
REPORT_SQL_MONITORファンクションで確認可能となります。
SQLが完了する必要は無く、実行中でも確認できます。
※(2018/12/16追記) SQLの実行計画のステップ数が300を超える場合は、
上記の条件を満たしていてもSQL監視の対象になりません。
隠しパラメータ"_sqlmon_max_planlines"を300よりも多くセットする必要があります。
-- データベース全体でセットする場合 ALTER SYSTEM SET "_sqlmon_max_planlines"=1000 SCOPE=BOTH SID='*'; -- セッション単位でセットする場合 ALTER SESSION SET "_sqlmon_max_planlines"=1000;
上記パラメータの詳細は下記ドキュメントをご参照下さい(｀･ω･)ゞ ※要My Oracle Supportアカウント
How to Monitor SQL Statements with Large Plans Using Real-Time SQL Monitoring? (ドキュメントID 1613163.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1613163.1
2-3. DBMS_SQLTUNE.REPORT_SQL_MONITORの実行サンプル
下記が実行サンプルになります。MONITORヒントを付与してSQLを実行しています。
ALTER SESSION SET STATISTICS_LEVEL = ALL; -- サンプルSQL SELECT /*+ MONITOR */ B.ITEM_NAME , TRUNC(A.ORDER_DATE, 'DD') AS ORDER_DAILY , COUNT(*) FROM ORDER_TBL A , ITEM_TBL B WHERE A.ITEM_NO = B.ITEM_NO -- AND B.ITEM_NO BETWEEN 1 AND 100 AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801' GROUP BY B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD') ORDER BY 1; -- DBMS_SQLTUNE.REPORT_SQL_MONITORによるレポート出力 SET LONG 1000000 SET LONGC 1000000 SET LINESIZE 300; SET PAGESIZE 1000; VAR c_rep CLOB; EXEC :c_rep := DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => 'f9r2y6xdz6t84', TYPE => 'TEXT'); PRINT c_rep;
実行結果は以下の通りです。
SQL> EXEC :c_rep := DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => 'f9r2y6xdz6t84', TYPE => 'TEXT'); PL/SQL procedure successfully completed. SQL> PRINT c_rep; C_REP ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL Monitoring Report SQL Text ------------------------------ SELECT /*+ MONITOR */ B.ITEM_NAME , TRUNC(A.ORDER_DATE, 'DD') AS ORDER_DAILY , COUNT(*) FROM ORDER_TBL A , ITEM_TBL B WHERE A.ITEM_NO = B.ITEM_NO -- AND B.ITEM_NO BETWEEN 1 AND 100 AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801' GROUP BY B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD') ORDER BY 1 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : AYSHIBAT (88:40832) SQL ID : f9r2y6xdz6t84 SQL Execution ID : 16777216 Execution Started : 12/08/2018 04:07:45 First Refresh Time : 12/08/2018 04:07:45 Last Refresh Time : 12/08/2018 04:07:53 Duration : 8s Module/Action : SQL*Plus/- Service : orcl Program : sqlplus@localhost.localdomain (TNS V1-V3) Fetch Calls : 19 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 | =========================================================================================== | 7.37 | 1.31 | 5.56 | 0.51 | 19 | 9068 | 292 | 34MB | 453 | 53MB | =========================================================================================== SQL Plan Monitoring Details (Plan Hash Value=44130803) ========================================================================================================================================================================================== | 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 | | | | 1 | +8 | 1 | 261 | | | | | . | . | 12.50 | local write wait (1) | | 1 | SORT GROUP BY | | 8 | 30 | 1 | +8 | 1 | 261 | | | | | 18432 | . | | | | 2 | HASH JOIN | | 26 | 29 | 7 | +2 | 1 | 26000 | 291 | 34MB | 453 | 53MB | 18MB | 57MB | 75.00 | direct path write temp (6) | | 3 | TABLE ACCESS FULL | ORDER_TBL | 26 | 3 | 5 | +2 | 1 | 3M | | | | | . | . | | | | 4 | TABLE ACCESS FULL | ITEM_TBL | 300 | 26 | 1 | +8 | 1 | 10 | | | | | . | . | | | ==========================================================================================================================================================================================
2-4. 出力された統計の統計の見方(DBMS_SQLTUNE.REPORT_SQL_MONITORによるレポート出力)
まず Global Information の Duration を確認します。SQL全体の実行時間を確認できます。
Global Information
------------------------------
：
Duration : 8s
：
今回のSQLでは該当しませんが、Global Stats の Elapsed Time(s) は
パラレル・クエリの子プロセス(スレーブ)のSQL実行時間も積算されるため、
その点を考慮して確認する必要があります。
： 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 | =========================================================================================== | 7.37 | 1.31 | 5.56 | 0.51 | 19 | 9068 | 292 | 34MB | 453 | 53MB | =========================================================================================== ^^^^^^^^^^^子プロセス(スレーブ)のSQL実行時間も含んだ積算値 ：
次に Activity(%) と Activity Detail(# samples) を確認します。
実行計画のどこで時間が掛かっているか、どんな待機イベントで時間が掛かっているかを
直感的に把握できるため、非常に有用な情報となります。
SQL Plan Monitoring Details (Plan Hash Value=44130803) ==========================================…========================================= | Id | Operation | Name |…| Activity | Activity Detail | | | | |…| (%) | (# samples) | ==========================================…========================================= | 0 | SELECT STATEMENT | |…| 12.50 | local write wait (1) | | 1 | SORT GROUP BY | |…| | | | 2 | HASH JOIN | |…| 75.00 | direct path write temp (6) | ★direct path write temp で全体の75%を占める。 | 3 | TABLE ACCESS FULL | ORDER_TBL |…| | | | 4 | TABLE ACCESS FULL | ITEM_TBL |…| | | ==========================================…=========================================
あとはDBMS_XPLANと同様にRows(Estim)の予測値とRows(Actual)の実測値を確認します。
SQL Plan Monitoring Details (Plan Hash Value=44130803) ====================================================…============…= | Id | Operation | Name | Rows |…| Rows |…| | | | | (Estim) |…| (Actual) |…| ====================================================…============…= | 0 | SELECT STATEMENT | | |…| 261 |…| | 1 | SORT GROUP BY | | 8 |…| 261 |…| | 2 | HASH JOIN | | 26 |…| 26000 |…| | 3 | TABLE ACCESS FULL | ORDER_TBL | 26 |…| 3M |…| ★ 26件(予測)と3M件(実測)で大きく乖離 | 4 | TABLE ACCESS FULL | ITEM_TBL | 300 |…| 10 |…| ====================================================…============…=
3. 今回のケースでのSQL性能改善(チューニング)
このエントリは SQL性能計測 をメインとしているため
チューニングの詳細は割愛しますが、今回のケースでは
- ORDER_TBL表の E-Rows(予測値) と A-Rows(実測値)が乖離している。
- HASH JOINのステップで時間が掛かっている。
事が、DBMS_XPLAN と DBMS_SQLTUNE の性能計測により確認できます。
このようなケースでは以下のようなチューニングを試すのがセオリーとなります。
どのチューニングも E-Rows(予測値) と A-Rows(実測値) の
乖離を埋めて、良い実行計画が生成される事を期待する手法となります。
4. まとめ
DBMS_XPLAN.DISPLAY_CURSOR と DBMS_SQLTUNE.REPORT_SQL_MONITOR による性能計測は、
SQLのボトルネック特定に非常に有効な手段となります。皆活用するんやで彡(^)(^)
もっと詳しく説明した資料も有るので、皆読んでクレメンス(ステマ
Oracle DBA & Developer Day 2013
https://www.oracle.com/technetwork/jp/ondemand/ddd-2013-2051348-ja.html【A-1】オラクル・コンサルが語る！ SQLチューニングに必要な考え方と最新テクニック
http://www.oracle.com/webfolder/technetwork/jp/ondemand/ddd2013/A-1.pdf
OTN の VirtualBoxイメージ で Oracle DB 18c環境 を 楽々構築【Oracle Database or GoldenGate Advent Calendar 2018 Day 5】
- 1. VirtualBox をインストール
- 2. Database Virtual Box Appliance / Virtual Machine の ovaイメージをダウンロード
- 3. ovaイメージのインポート
- 4. 仮想マシンの起動
- 5. 仮想マシンへのアクセス(ssh + sqlplus)
- 6. 仮想マシン上のDBへのアクセス(SQL Developer)
- 7. 仮想マシンのAPEX ADMIN環境にホストマシンのWebブラウザからアクセス(Oracle APEX)
- 8. まとめ
このエントリは Oracle Database or GoldenGate Advent Calendar 2018 の Day 5 の記事となります。
Oracle Database or GoldenGate Advent Calendar 2018
https://adventar.org/calendars/3525
当初の予定から変更！ OTNからダウンロードできる VirtualBoxイメージ(ovaファイル)で
Oracle Database 18c環境をサクっと構築してみるやで彡(ﾟ)(ﾟ)
1. VirtualBox をインストール
まずは VirtualBox をダウンロードしてインストール、 既に導入済みの人は不要ですやで彡(ﾟ)(ﾟ)
Oracle VM VirtualBox
https://www.oracle.com/technetwork/server-storage/virtualbox/downloads/index.html?ssSourceSiteId=otnjp
インストールで詰まることは無いと思いますが、色々な記事も有ります。
VirtualBoxのインストール方法
https://eng-entrance.com/virtualbox-install
VirtualBox を Windows にインストール
https://www.shookuro.com/entry/2018/01/28/162252
2. Database Virtual Box Appliance / Virtual Machine の ovaイメージをダウンロード
下記ページから ovaイメージ を ダウンロードします。
7.7GB位あるので、ダウンロードしたら少し待ちます彡(ﾟ)(ﾟ)
Oracle Technology Network
Database Virtual Box Appliance / Virtual Machine
https://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html
スグに次の手順(インポート)してもエエんですが、ダウンロード失敗の可能性も加味して、
MD5のハッシュ値を確認してダウンロード元のと比較するのをオススメします。
下記はWindowsのコマンドプロンプトでのMD5ハッシュ値確認例彡(ﾟ)(ﾟ)
certutil -hashfile C:\Users\AYSHIBAT\Downloads\DeveloperDaysVM2018-10-16_09.ova MD5 MD5 ハッシュ (ファイル C:\Users\AYSHIBAT\Downloads\DeveloperDaysVM2018-10-16_09.ova): bf 2b 8e cd 1c c2 1f 0b de c6 39 73 f7 2c ea 83 CertUtil: -hashfile コマンドは正常に完了しました。
3. ovaイメージのインポート
ダウンロードしたovaイメージをインポートします。基本ポチポチするだけ彡(ﾟ)(ﾟ)
4. 仮想マシンの起動
インポートが成功したら、仮想マシンを起動します彡(ﾟ)(ﾟ)
5. 仮想マシンへのアクセス(ssh + sqlplus)
インポートされた仮想マシンにはNATのポートフォワードが下記のように設定されています。
sshのポートフォワードは 2222(HOST)→22(GUEST) に設定されているので、
「localhost:2222」でsshログインが可能です。
ユーザ名とパスワードは元ページに書かれている通り oracle 彡(ﾟ)(ﾟ)
(Username and password is oracle.)
sshログイン後、環境変数ORACLE_HOMEとPATHを設定します。
tnsnames.ora にはCDBへの接続文字列ORCLCDBと、
PDBへの接続文字列ORCLがセットされています。
後は sqlplus でログインするだけや！PDBに接続してみます。彡(ﾟ)(ﾟ)
export ORACLE_HOME=/u01/app/oracle/product/version/db_1/
export PATH=${PATH}:${ORACLE_HOME}/bin
cd $ORACLE_HOME/network/admin
cat tnsnames.ora
sqlplus /nolog
CONNECT PDBADMIN/oracle@ORCL
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS CON_NAME FROM DUAL;
実行結果は下記の通り。DBにログイン成功や！彡(^)(^)
[oracle@localhost admin]$ export ORACLE_HOME=/u01/app/oracle/product/version/db_1/
[oracle@localhost admin]$ export PATH=${PATH}:${ORACLE_HOME}/bin
[oracle@localhost admin]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ cat tnsnames.ora
ORCLCDB=localhost:1521/orclcdb
ORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
[oracle@localhost admin]$ sqlplus /nolog
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Dec 5 09:02:47 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
SQL> CONNECT PDBADMIN/oracle@ORCL
Connected.
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS CON_NAME FROM DUAL;
CON_NAME
--------------------------------------------------------------------------------
ORCL
SQL>
6. 仮想マシン上のDBへのアクセス(SQL Developer)
お次は SQL Developer で仮想マシンのDBにログインしてみます彡(ﾟ)(ﾟ)
仮想マシンのOracleリスナーのポートは 1521(HOST)→1521(GUEST) に
フォワードされているので、localhost:1521 でDB接続できます。
サービス名はPDBのサービス(orcl)で、PDB管理者(PDBADMIN)でログインしてみます。
接続後、試しにコンテナ名(PDB名)をSELECTしてみます。
成功(｀･ω･)Ъ
7. 仮想マシンのAPEX ADMIN環境にホストマシンのWebブラウザからアクセス(Oracle APEX)
(2019/2/12追記)Oracle APEX UG Meetup 2019 #1開催の勢いに任せて、追記するやで彡(ﾟ)(ﾟ)
仮想マシンの8080ポート(HOST)は8080ポート(GUEST)にフォワードされていて、
ホストマシンのブラウザから仮想マシンのAPEX ADMIN環境にアクセスできます。
URL…………http://localhost:8080/apex
Workspace…INTERNAL
Username …ADMIN
Password …oracle
接続画面キャプチャはこちら
接続後のキャプチャはこちら
この接続情報元ネタは下記、publicなのは無いのかな…？
Installing and Configuring an Oracle Developer Day VirtualBox Image
https://mikesmithers.wordpress.com/2015/01/25/installing-and-configuring-an-oracle-developer-day-virtualbox-image/
このADMIN環境からワークスペースを作成すれば、
もりもりAPEXアプリを作れるやで。ワイの過去記事も見てね彡(^)(^)
Oracle APEX Meetup 第2回「2時間でできるAPEXハンズオン」(6/1・木) に行ってみたのでご報告
https://gonsuke777.hatenablog.com/entry/20170613/1497339652
8. まとめ
VirtualBoxでお手軽にOracle Database 18c環境を構築できたやで！どんどん活用してね。
彡(^)(^)
Oracle DatabaseでSQLの性能計測1(SET AUTOTRACE TRACEONLY と SET TIMING ON編)【Oracle Database or GoldenGate Advent Calendar 2018 Day 1】
このエントリは Oracle Database or GoldenGate Advent Calendar 2018 の Day 1 の記事となります。
Oracle Database or GoldenGate Advent Calendar 2018
https://adventar.org/calendars/3525
1日目はSQL*Plusの SET AUTOTRACE TRACEONLY と SET TIMING ON で、SQLの実行時間を計測してみるやで。
彡(ﾟ)(ﾟ)
1. AUTOTRACE とは？
Oracle Databaseユーティリティ の SQL*Plus(sqlplus)の機能で、
SQLの実行計画や実行統計の取得できます。
SQL*Plus ユーザーズ・ガイドおよびリファレンス 18c
12.41.6 SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
https://docs.oracle.com/cd/E96517_01/sqpug/SET-system-variable-summary.html#GUID-0791433C-CA4A-4C79-BFF6-B7976E1534BA
8.1 文のトレースについて
https://docs.oracle.com/cd/E96517_01/sqpug/tuning-SQL-Plus.html#GUID-1425180A-9917-429E-B908-B217C0CAC3DD
2. SET TIMING とは？
Oracle Databaseユーティリティ の SQL*Plus(sqlplus)の機能で、
SQL や PL/SQL を実行した際の経過時間を表示できます。
SQL*Plus ユーザーズ・ガイドおよびリファレンス 18c
12.41.64 SET TIMI[NG] {ON | OFF}
https://docs.oracle.com/cd/E96517_01/sqpug/SET-system-variable-summary.html#GUID-35276053-FA28-4CA3-94A8-8806682C9EA5
3. 実行サンプル
下記に実行サンプルを提示してみますやで彡(ﾟ)(ﾟ)
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy SET TIMING ON; -- TIMING を有効化しています。 SET AUTOTRACE TRACEONLY; -- AUTOTRACE を有効化しています。 -- サンプルSQL SELECT /*+ MONITOR */ B.ITEM_NAME , TRUNC(A.ORDER_DATE, 'DD') AS ORDER_DAILY , COUNT(*) FROM ORDER_TBL A , ITEM_TBL B WHERE A.ITEM_NO = B.ITEM_NO -- AND B.ITEM_NO BETWEEN 1 AND 100 AND TO_CHAR(B.REGIST_DATE, 'YYYYMMDD') = '20120801' GROUP BY B.ITEM_NAME, TRUNC(A.ORDER_DATE, 'DD') ORDER BY 1; Connected. 261 rows selected. Elapsed: 00:00:05.22 ★SET TIMING ON によって出力されたSQLの経過時間です。 Execution Plan ---------------------------------------------------------- Plan hash value: 44130803 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 208 | 30 (7)| 00:00:01 | | 1 | SORT GROUP BY | | 8 | 208 | 30 (7)| 00:00:01 | |* 2 | HASH JOIN | | 26 | 676 | 29 (4)| 00:00:01 | | 3 | TABLE ACCESS FULL| ORDER_TBL | 26 | 286 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| ITEM_TBL | 300 | 4500 | 26 (4)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."ITEM_NO"="B"."ITEM_NO") 4 - filter(TO_CHAR(INTERNAL_FUNCTION("B"."REGIST_DATE"),'YYYYMMDD')='2 0120801') Statistics ★SET AUTOTRACE TRACEONLY によって出力されたSQLの実効統計 ---------------------------------------------------------- 97 recursive calls 0 db block gets 9012 consistent gets 15579 physical reads 0 redo size 7401 bytes sent via SQL*Net to client 795 bytes received via SQL*Net from client 19 SQL*Net roundtrips to/from client 11 sorts (memory) 0 sorts (disk) 261 rows processed
4. 出力された統計の見方
SET TIMING ON の方はシンプル、経過時間そのものを確認します。
： Elapsed: 00:00:05.22 ←コレを確認する。 ：
SET AUTOTRACE TRACEONLY では様々な統計が出力されますが、
SQL性能観点だと「consistent gets」「physical reads」辺りをよく見ますかね彡(ﾟ)(ﾟ)
： Statistics ---------------------------------------------------------- 97 recursive calls 0 db block gets 9012 consistent gets ★SQLの読込ブロック数 ←これはよく見る 15579 physical reads ★SQLのDisk IOブロック数 ←これはよく見る 0 redo size 7401 bytes sent via SQL*Net to client 795 bytes received via SQL*Net from client 19 SQL*Net roundtrips to/from client 11 sorts (memory) 0 sorts (disk) 261 rows processed ：
8.1.3 統計
https://docs.oracle.com/cd/E96517_01/sqpug/tuning-SQL-Plus.html#GUID-32806E04-0E05-4579-B2DB-E2C757CEA448
統計は、文を実行したときにサーバーによって記録され、文の実行に必要とされた
システム・リソースを示します。結果には、次の統計が含まれます。
recursive calls
db block gets
：
SQLチューニング観点で言えば、SET TIMING ON で確認できる「Elapsed」や
AUTOTRACEの「consistent gets」「physical reads」を血眼になって減らすんやね彡(ﾟ)(ﾟ)
5. AUTOTRACE TRACEONLY の注意点
まずひとつ目、AUTOTRACE TRACEONLY はその字面に反して
SQLのトランザクションが実行されてしまいます。下記記事参照彡(ﾟ)(ﾟ)
sqlplus の SET AUTOTRACE TRACEONLY は トレースオンリーじゃなくてトランザクションが実行されてしまう。
https://gonsuke777.hatenablog.com/entry/20160120/1453266062
ふたつ目は、AUTOTRACE TRACEONLY で出てくる実行計画と
(共有プール上に格納された)実際の実行計画は異なるケースが有ります。下記記事参照
EXPLAIN PLAN FOR …(※AUTOTRACE TRACEONLY含む)で出てくる実行計画 と
SQL実行時の実行計画が異なるケースを作ってみる。
https://gonsuke777.hatenablog.com/entry/20150723/1437662167
これは下記マニュアルにも制限事項として記載が有ります。
6.2.5 EXPLAIN PLANの制限事項
https://docs.oracle.com/cd/E96517_01/tgsql/generating-and-displaying-execution-plans.html#GUID-E2463C7B-F71A-4F06-85D3-1AF3D4D71CE8
Oracle Databaseでは、日付バインド変数の暗黙的な型変換を実行する文でのEXPLAIN PLANをサポートしません。
一般にバインド変数では、EXPLAIN PLANが実際の実行計画を表していない場合があります。
過去あるいは後日記載する DBMS_XPLAN.DISPLAY_CURSOR や DBMS_SQLTUNE も併用や！彡(ﾟ)(ﾟ)
6. まとめ
上記のような制限事項が有るとはいえ、特に AUTOTRACE TRACEONLY は便利です。
sqlplus だけで操作が完結するんで、SQLチューニングの最盛期で
超高速PDCAを廻してるタイミングでは重宝しますやね彡(^)(^)
増分統計(INCREMENTALプリファレンス)を有効にした状態で統計を採取して、シノプシス(synopsis)が使われる様子をSQLトレースで確認する。
表題の通り、増分統計(INCREMENTALプリファレンス=TRUE)を採取してみて、 内部動作をSQLトレースで確認してみるやで彡(ﾟ)(ﾟ)
Oracle Database SQLチューニング・ガイド 18c
13.2.8.2 DBMS_STATSによるパーティション表のグローバル統計の導出方法
https://docs.oracle.com/cd/E96517_01/tgsql/gathering-optimizer-statistics.html#GUID-6ECF96F3-72DD-4B09-974D-70D3FABE6F47
増分統計メンテナンスが有効な場合、DBMS_STATSでは、変更されたパーティションのみの統計を収集し、シノプシスを作成します。また、データベースは、パーティション・レベルのシノプシスをグローバル・シノプシスに自動的にマージし、パーティション・レベルの統計およびグローバル・シノプシスからグローバル統計を導出します。
1. オブジェクト作成と増分統計の有効化
まずはオブジェクトの作成と増分統計の有効化を実行彡(ﾟ)(ﾟ)
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy -- テーブル作成 DROP TABLE TBL_A PURGE; CREATE TABLE TBL_A ( C1 NUMBER , C2 VARCHAR2(10) , C3 DATE ) PARTITION BY RANGE (C1) INTERVAL(10000) (PARTITION VALUES LESS THAN (10001)); -- 索引作成 CREATE INDEX TBL_A_I1 ON TBL_A(C1, C3) LOCAL; CREATE INDEX TBL_A_I2 ON TBL_A(C1) GLOBAL; -- INCREMENTALプリファレンス有効化と確認 COLUMN PREFERENCE FORMAT A30; EXEC DBMS_STATS.SET_TABLE_PREFS('AYSHIBAT', 'TBL_A', 'INCREMENTAL', 'TRUE'); SELECT DBMS_STATS.GET_PREFS('INCREMENTAL', 'AYSHIBAT', 'TBL_A') AS PREFERENCE FROM DUAL; Table dropped. Table created. Index created. Index created. PL/SQL procedure successfully completed. PREFERENCE ------------------------------ TRUE
2. テストデータの作成
次にテストデータを投入しますやで彡(ﾟ)(ﾟ)
-- テストデータ作成 INSERT INTO TBL_A SELECT LEVEL, 'C2_' || LEVEL, SYSDATE + (LEVEL/24/60) FROM DUAL CONNECT BY LEVEL <= 50000; COMMIT; SELECT COUNT(*) FROM TBL_A; 50000 rows created. Commit complete. COUNT(*) ---------- 50000
3. SQLトレースを取得しながら統計を採取
SQLトレースを取得しながらトレースを採取彡(ﾟ)(ﾟ)
-- 統計採取およびSQLトレース取得 CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy ALTER SESSION SET TRACEFILE_IDENTIFIER = 'AYSHIBAT'; EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE, plan_stat => 'ALL_EXECUTIONS'); EXEC DBMS_STATS.GATHER_TABLE_STATS('AYSHIBAT', 'TBL_A'); EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE; -- オプティマイザ統計確認 SET LINESIZE 300; SET PAGESIZE 100; COLUMN OWNER FORMAT A20; COLUMN TABLE_NAME FORMAT A20; COLUMN INDEX_NAME FORMAT A20; COLUMN PARTITION_NAME FORMAT A20; COLUMN SUBPARTITION_NAME FORMAT A20; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; Connected. Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Session altered. OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A 2018/11/28 02:09:12 50000 NO AYSHIBAT TBL_A SYS_P4443 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4444 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4445 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4446 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4447 2018/11/28 02:09:11 10000 NO OWNER TABLE_NAME INDEX_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A TBL_A_I1 2018/11/28 02:09:13 50000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4443 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4444 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4445 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4446 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4447 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I2 2018/11/28 02:09:13 50000 NO 7 rows selected.
4. SQLトレース(整形後)の確認
SQLトレースを確認しますやで。tkprofの整形後のトレースです彡(ﾟ)(ﾟ) シノプシス表(wri\$_optstat_synopsis_head\$)にデータをINSERTしてますね！
TKPROF: Release 12.2.0.1.0 - Development on Wed Nov 28 02:11:48 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Trace file: orcl_ora_4417_AYSHIBAT.trc Sort options: default ： ： SQL ID: 808a0gzdt26kc Plan Hash: 0 insert into sys.wri$_optstat_synopsis_head$ (bo#, group#, intcol#, spare1, spare2) values (:1, :2, :3, :4, :5) ★シノプシス表へのINSERT文 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 15 0.00 0.00 0 0 0 0 Execute 15 0.00 0.00 0 25 135 15 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 30 0.00 0.00 0 25 135 15 ：
5. 追加データ投入と統計失効確認
追加データを投入します。DBMS_STATS.FLUSH_DATABASE_MONITORING_INFOで 失効状態をフラッシュした後に、統計が失効したことを確認彡(ﾟ)(ﾟ)
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy -- 追加データ投入 INSERT INTO TBL_A SELECT LEVEL+50000, 'C2_' || (LEVEL+50000), SYSDATE + (LEVEL/24/60) FROM DUAL CONNECT BY LEVEL <= 10000; COMMIT; -- 統計情報の失効を即座に反映 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; -- オプティマイザ統計確認 SET LINESIZE 300; SET PAGESIZE 100; COLUMN OWNER FORMAT A20; COLUMN TABLE_NAME FORMAT A20; COLUMN INDEX_NAME FORMAT A20; COLUMN PARTITION_NAME FORMAT A20; COLUMN SUBPARTITION_NAME FORMAT A20; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; Connected. 10000 rows created. Commit complete. PL/SQL procedure successfully completed. Session altered. OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A 2018/11/28 02:09:12 50000 YES ★統計が失効 AYSHIBAT TBL_A SYS_P4443 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4444 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4445 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4446 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4447 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4448 7 rows selected. OWNER TABLE_NAME INDEX_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A TBL_A_I1 2018/11/28 02:09:13 50000 YES ★統計が失効 AYSHIBAT TBL_A TBL_A_I1 SYS_P4443 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4444 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4445 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4446 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4447 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4448 AYSHIBAT TBL_A TBL_A_I2 2018/11/28 02:09:13 50000 YES ★統計が失効 8 rows selected.
6. SQLトレースを取得しながら増分統計を採取
グローバル統計が失効した状態で、統計を採取します彡(ﾟ)(ﾟ)
-- 増分統計採取およびSQLトレース取得 CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy ALTER SESSION SET TRACEFILE_IDENTIFIER = 'AYSHIBAT'; EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE, plan_stat => 'ALL_EXECUTIONS'); EXEC DBMS_STATS.GATHER_TABLE_STATS('AYSHIBAT', 'TBL_A'); EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE; -- オプティマイザ統計確認 SET LINESIZE 300; SET PAGESIZE 100; COLUMN OWNER FORMAT A20; COLUMN TABLE_NAME FORMAT A20; COLUMN INDEX_NAME FORMAT A20; COLUMN PARTITION_NAME FORMAT A20; COLUMN SUBPARTITION_NAME FORMAT A20; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'AYSHIBAT' AND TABLE_NAME = 'TBL_A' ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME NULLS FIRST, SUBPARTITION_NAME NULLS FIRST; Connected. Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Session altered. OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A 2018/11/28 02:16:03 60000 NO AYSHIBAT TBL_A SYS_P4443 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4444 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4445 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4446 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4447 2018/11/28 02:09:11 10000 NO AYSHIBAT TBL_A SYS_P4448 2018/11/28 02:16:03 10000 NO 7 rows selected. OWNER TABLE_NAME INDEX_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED NUM_ROWS STA -------------------- -------------------- -------------------- -------------------- -------------------- ------------------- ---------- --- AYSHIBAT TBL_A TBL_A_I1 2018/11/28 02:16:04 60000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4443 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4444 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4445 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4446 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4447 2018/11/28 02:09:13 10000 NO AYSHIBAT TBL_A TBL_A_I1 SYS_P4448 2018/11/28 02:16:04 10000 NO AYSHIBAT TBL_A TBL_A_I2 2018/11/28 02:16:04 60000 NO 8 rows selected.
7. 増分統計採取時のSQLトレース(整形後)確認
増分統計によるSQLトレースは下記の通り。 でもこれだけだと増分統計の効果が判らないので…彡(ﾟ)(ﾟ)
TKPROF: Release 12.2.0.1.0 - Development on Wed Nov 28 02:17:18 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Trace file: orcl_ora_4496_AYSHIBAT.trc Sort options: default ： ： insert into sys.wri$_optstat_synopsis_head$ (bo#, group#, intcol#, spare1, spare2) values (:1, :2, :3, :4, :5) ： ： OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 143 0.01 0.01 0 0 0 0 Execute 455 0.04 0.04 3 136 292 50 Fetch 382 0.65 0.65 0 3009 0 318 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 980 0.71 0.71 3 3145 292 368 ：
8. 参考：増分統計を有効化しなかった場合のSQLトレース(※7.のトレースと同じ条件)
増分統計を有効化しなかった場合のSQLトレースも見てみます。 増分統計を有効化していない以外は7.のSQLトレースと同じ条件です。
TKPROF: Release 12.2.0.1.0 - Development on Wed Nov 28 02:36:53 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Trace file: orcl_ora_4648_AYSHIBAT.trc Sort options: default ： OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 199 0.02 0.02 0 0 0 0 Execute 833 0.10 0.11 0 273 631 117 Fetch 670 1.22 1.23 0 4419 0 638 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1702 1.35 1.37 0 4692 631 755 ：
増分統計の方が query, current, rows と云った負荷が 少ない事が解りますやね！彡(^)(^)
9. マニュアル
マニュアルも読んでおくんやで彡(ﾟ)(ﾟ)
13.2.8.3.2 SET_TABLE_PREFSを使用した増分統計の有効化
https://docs.oracle.com/cd/E96517_01/tgsql/gathering-optimizer-statistics.html#GUID-75AD8460-A5F6-43B1-ADE0-4AD77AF72274
変更されたパーティションのみをスキャンしてグローバル統計を増分的に更新するデータベースでは、次の条件を満たす必要があります。
・パーティション表のPUBLISH値がtrueです。
・パーティション表のINCREMENTAL値がtrueです。
・統計収集プロシージャでは、ESTIMATE_PERCENTにAUTO_SAMPLE_SIZE、GRANULARITYにAUTOを指定する必要があります。Oracle Database PL/SQL Packages and Types Reference 18c
162 DBMS_STATS
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_STATS.html#GUID-01FAB8ED-E4A3-4C3E-8FE2-88717DCDDA06
10. 追記：シノプシス(synopsis)を集計してるっぽい内部SQL
select /*+ parallel(1) OPT_PARAM('_parallel_syspls_obey_force' 'false') */ b.intcol#, b.nnv, b.nmin, b.nmax, b.minval, b.maxval, b.acl, nvl(n.ndv, 0) sndv from ( -- all basic stats except ndv select /*+ no_merge */ intcol#, greatest(0, :total_rows - sum(h.null_cnt)) nnv, sum(h.avgcln * t.rowcnt)/greatest(:total_rows, 1) acl, -- if total_rows is 0, avgcln is 0 min(nvl2(h.lowval, h.minimum, null)) nmin, -- normalized min max(nvl2(h.hival, h.maximum, null)) nmax, -- normalized max min(h.lowval) minval, -- raw value max(h.hival) maxval from sys.tabpart$ t, sys."_HIST_HEAD_DEC" h where t.bo# = :tab_num and t.obj# = h.obj# group by h.intcol# union all select intcol#, greatest(0, :total_rows - sum(h.null_cnt)) nnv, sum(h.avgcln * t.rowcnt)/greatest(:total_rows, 1) avgcln, min(nvl2(h.lowval, h.minimum, null)) nmin, -- normalized min max(nvl2(h.hival, h.maximum, null)) nmax, -- normalized max min(h.lowval) minval, -- raw value max(h.hival) maxval from sys.tabcompart$ t, sys."_HIST_HEAD_DEC" h where t.bo# = :tab_num and t.obj# = h.obj# group by h.intcol# ) b, ( select intcol#, to_approx_count_distinct(approx_count_distinct_agg(spare2)) ndv from wri$_optstat_synopsis_head$ where bo# = :tab_num group by intcol# ) n where b.intcol# = n.intcol#(+)
