ねら~ITエンジニア雑記

やきうのお兄ちゃんが綴るOracle Databaseメインのブログ

ADW(Autonomous Data Warehouse) に OCI の Object Storage にアップロードした dmp を インポート(impdp)してみる。

ADW(Autonomous Data Warehouse) は ORACLE が提供する
Full Managed なデータベースの Cloud Service なんやで彡(゚)(゚)

Autonomous Data Warehouse
https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/

今回はこの ADW に OCI(Oracle Cloud Infrastructure) の Object Storage に
アップロードした dmp をインポート(impdp)してみます。

1. データのエクスポート(expdp)

まずはデータをエクスポート(expdp)します。オプションは下記参照彡(゚)(゚)

Export Your Existing Oracle Database to Import into Autonomous Data Warehouse
https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/load-data.html#GUID-7068E963-C464-4ABB-AC60-BA56DFC98650

実際に流したコマンドは下記です。index は exclude してません。
Object Storage に置けるファイルは 1ファイルあたり5GB までという制限があるので、
FILESIZEパラメータでdmpファイルのサイズを制限します。

expdp xxxxxxxx/yyyyyyyy@zzzzzzzz \
exclude=cluster,indextype,materialized_view,materialized_view_log,materialized_zonemap,db_link \
data_options=group_partition_table_data \
parallel=4 \
schemas=soe \
directory=DATA_PUMP_DIR \
LOGFILE=exp_soe.log \
DUMPFILE=exp_soe%U.dmp \
FILESIZE=4900MB \
REUSE_DUMPFILES=YES \
VERSION=12.2.0

expdpの実行結果は下記の通りです。

Export: Release 18.0.0.0.0 - Production on Mon Jan 21 16:25:09 2019
Version 18.2.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "PDBADMIN"."SYS_EXPORT_SCHEMA_01":  PDBADMIN/********@10.0.2.3:1521/ayspdb2.sub12070931432.vcnayshibat.oraclevcn.com exclude=cluster,indextype,materialized_view,materialized_view_log,materialized_zonemap,db_link data_options=group_partition_table_data parallel=4 schemas=soe directory=DATA_PUMP_DIR LOGFILE=exp_soe.log DUMPFILE=exp_soe%U.dmp FILESIZE=4900MB REUSE_DUMPFILES=YES VERSION=12.2.0
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
:
:
  /u01/app/oracle/product/18.2/dbhome_1/rdbms/log/7C6D751E07A94BB2E0530302000AFD1F/exp_soe01.dmp
  /u01/app/oracle/product/18.2/dbhome_1/rdbms/log/7C6D751E07A94BB2E0530302000AFD1F/exp_soe02.dmp
  /u01/app/oracle/product/18.2/dbhome_1/rdbms/log/7C6D751E07A94BB2E0530302000AFD1F/exp_soe03.dmp
  /u01/app/oracle/product/18.2/dbhome_1/rdbms/log/7C6D751E07A94BB2E0530302000AFD1F/exp_soe04.dmp
Job "xxxxxxxx"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jan 21 16:27:24 2019 elapsed 0 00:02:13

2. dmpファイルの Object Storage へのアップロード

dmpファイルを Object Storage にアップロードします。ブラウザ画面から
アップロードすることも可能なんですが、今回は OCI CLI
コマンド(oci os object bulk-upload ...)で Object Storage にアップロードしてしまいました。

oci os object bulk-upload -ns xxxxxxxx -bn yyyyyyyy-objs001 --src-dir /u01/app/oracle/product/18.2/dbhome_1/rdbms/log/7C6D751E07A94BB2E0530302000AFD1F/
Uploaded exp_soe02.dmp  [####################################]  100%
{
  "skipped-objects": [],
  "upload-failures": {},
  "uploaded-objects": {
    "exp_soe01.dmp": {
      "etag": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
      "last-modified": "Mon, 21 Jan 2019 16:35:00 GMT",
      "opc-multipart-md5": "yyyyyyyyyyyyyyyyyyyyyyyyyyy"
:
}

成功するとアップロードされたファイルの情報がJSON形式で返ってきます。

OCI CLIのセットアップ方法は下記のブログ群が参考になります。

コマンドライン(CLI)でOCIを操作する - Oracle Cloud Infrastructureアドバンスド
https://community.oracle.com/docs/DOC-1019624#jive_content_id_3_CLI

Oracle Cloud Infrastructure CLIの導入 (後編:環境設定)
https://cloudii.atomitech.jp/entry/2018/08/01/104038

[Oracle Cloud] OCI DatabaseにCLIを設定してみた
http://itedge.stars.ne.jp/http-itedge-stars-ne-jp-oracle-cloud-oci-database-cli/

3. Object Storage にアップロードしたdmpファイルの Pre-authenticated URL生成

下記ドキュメント(※要アカウント)の記載の通り、Object Storage に
アップロードした dmpファイル の Pre-authenticated URL を生成します。

ATP : Import to ATP From Object Store Fails with
'ORA-17500: ODM Err:ODM HTTP Unauthorized' (ドキュメントID 2446550.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2446550.1

これもブラウザ画面から生成可能なんですが、今回は OCI CLI
コマンド(oci os preauth-request create ...)で生成してしまいました。

oci os preauth-request create -ns xxxxxxxx -bn yyyyyyyy-objs001 --access-type ObjectRead --name prauth_exp_soe01 --time-expires 2099-12-31 -on exp_soe01.dmp
oci os preauth-request create -ns xxxxxxxx -bn yyyyyyyy-objs001 --access-type ObjectRead --name prauth_exp_soe02 --time-expires 2099-12-31 -on exp_soe02.dmp
oci os preauth-request create -ns xxxxxxxx -bn yyyyyyyy-objs001 --access-type ObjectRead --name prauth_exp_soe03 --time-expires 2099-12-31 -on exp_soe03.dmp
oci os preauth-request create -ns xxxxxxxx -bn yyyyyyyy-objs001 --access-type ObjectRead --name prauth_exp_soe04 --time-expires 2099-12-31 -on exp_soe04.dmp

下記が実行ログです。成功すると生成された Pre-authenticated URL が
JSON形式で出力されます。後からは参照不可なので、テキストファイル等に保全しておきます。

oci os preauth-request create -ns xxxxxxxx -bn yyyyyyyy-objs001 --access-type ObjectRead --name prauth_exp_soe01 --time-expires 2099-12-31 -on exp_soe01.dmp
{
  "data": {
    "access-type": "ObjectRead",
    "access-uri": "/p/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/n/xxxxxxx/b/yyyyyyyy-objs001/o/exp_soe01.dmp",
    "id": "...exp_soe01.dmp",
    "name": "prauth_exp_soe01",
    "object-name": "exp_soe01.dmp",
    "time-created": "2019-01-21T16:38:20.118000+00:00",
    "time-expires": "2099-12-31T00:00:00+00:00"
  }
}
:
}

4. OCIユーザーの作成とGROUP設定、Auth Token生成

Menu => Identity => Users => Create User で OCIユーザーを作成します。

f:id:gonsuke777:20190122160735j:plain
ユーザー作成

作成したOCIユーザーをクリック => Groups で Administrator を付与します。

f:id:gonsuke777:20190122160847j:plain
グループ設定

作成したOCIユーザーをクリック => Auth Tokens で Auth Token を生成します。
後から参照する事はできないので、テキストファイル等に保全しておきます。

f:id:gonsuke777:20190122160914j:plain
Auth Token生成

5. DBMS_CLOUDパッケージで ADW に Credential を作成

下記マニュアルの手順を元に ADW に Credantial を作成します。

Import Data Using Oracle Data Pump Version 18.3 or Later
https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/load-data.html#GUID-297FE3E6-A823-4F98-AD50-959ED96E6969

ADW に接続して DBMS_CLOUD.CREATE_CREDENTIALプロシージャを実行します。

credential_name には任意の文字列、username には上記 4. で作成したOCIユーザー名、
passwordには上記 4. で生成した Auth Token をセットします。

CONNECT ADMIN/xxxxxxxxxxxxx@yyyyyy_high

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_CLI_AYS_ADW', -- 任意の文字列
    username => 'aysapi',                 -- OCIユーザー名
    password => 'xxxxxxxxxxxxxxxxxxxx'    -- 生成したAuth Token
  );
END;
/

PL/SQL procedure successfully completed.

6. インポート(impdp)の実行

impdpを実行してADWにデータをロードします。dumpfileパラメータには
3. で生成した Pre-authenticated URL を ,(カンマ)区切りで指定します。

impdp ADMIN/xxxxxxxxxxxxx@yyyyyy_high \
  credential=OCI_CLI_AYS_ADW \
  directory=data_pump_dir \
  dumpfile=https://objectstorage.us-ashburn-1.oraclecloud.com/p/zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz/n/xxxxxxxx/b/yyyyyyyy-objs001/o/exp_soe01.dmp,\
  https://objectstorage.us-ashburn-1.oraclecloud.com/p/zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz/n/xxxxxxxx/b/yyyyyyyy-objs001/o/exp_soe02.dmp,\
  https://objectstorage.us-ashburn-1.oraclecloud.com/p/zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz/n/xxxxxxxx/b/yyyyyyyy-objs001/o/exp_soe03.dmp,\
  https://objectstorage.us-ashburn-1.oraclecloud.com/p/zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz/n/xxxxxxxx/b/yyyyyyyy-objs001/o/exp_soe04.dmp \
  parallel=2 \
  partition_options=merge \
  transform=segment_attributes:n \
  exclude=cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link \
  logfile=impdp_schema_SOE.log

impdpの実行結果は以下の通りです。

Import: Release 18.0.0.0.0 - Production on Tue Jan 22 06:24:47 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "ADMIN"."SYS_IMPORT_FULL_03" successfully loaded/unloaded
:
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
:
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SOE"."ORDERS"                              1.289 GB 14298586 rows
. . imported "SOE"."ORDER_ITEMS"                         2.236 GB 42511747 rows
:

Job "ADMIN"."SYS_IMPORT_FULL_03" completed with 1 error(s) at Tue Jan 22 06:42:27 2019 elapsed 0 00:17:25

データを確認してみると。。。

sqlplus /nolog
connect SOE/yyyyyyyy@xxxxxx_high

SELECT COUNT(*) FROM ORDERS;

  COUNT(*)
----------
  14298586

データがセットされてるで!彡(^)(^)

7. まとめ

上記3. の Pre-authenticated URL生成 がハマりポイントかも。つかワイはハマった彡(-)(-)
だがコレでexpdp/impdpでデータ移行して、ADWをバリバリ(死語)使うんや!彡(゚)(゚)

まとめ:Oracle Database or GoldenGate Advent Calendar 2018 を振り返る!【Day 25】

このエントリは Oracle Database or GoldenGate Advent Calendar 2018 の Day 25 の
記事となります。最後も一日遅れてしまいました。本当にスミマセン……:(;゙゚'ω゚'):

Oracle Database or GoldenGate Advent Calendar 2018
https://adventar.org/calendars/3525

執筆して頂いた皆さん、ありがとうございました!彡(^)(^)
最終日は各記事を振り返りつつ、一言コメントをしていきますやで。彡(゚)(゚)

Day 1. SQL性能計測1

Oracle DatabaseでSQLの性能計測1(SET AUTOTRACE TRACEONLY と SET TIMING ON編)
https://gonsuke777.hatenablog.com/entry/2018/12/01/210651

Day 1 はワイのSET AUTOTRACE TRACEONLY と SET TIMING ON による SQL性能計測記事や!

記事にも書いてますが、SET TIMING と AUTOTRACE は sqlplus だけで
操作が完結するんで、SQLチューニングで超高速PDCAを廻してる
タイミングでは重宝するんやで彡(゚)(゚)

Day 2. Data Guard 奮闘記

Data Guard 奮闘記
[Oracle Database or GoldenGate Advent Calendar 2018] Oracle on Hyper-V 2018
http://charade.seesaa.net/article/462984231.html

Day 2 は charade_oo4oさん の Data Guard記事となります。12cR2(12.2) の Data Gurad の
主に PDB にフォーカスして、便利な使い方やハマりポイントを解説してくれています。
PDBフラッシュバック中は REDO適用 が止まるの、知らんかったやで…彡(-)(-)

Day 3. MOSナレッジをWeb検索

[Oracle] My Oracle Supportのナレッジ記事がWeb検索可能になった話
http://itedge.stars.ne.jp/tips_my-oracle-support/

Day 3 は itedgeblogさん のMOSナレッジがWeb検索できるようになった話です。
Google検索とかで My Oracle Support のナレッジが引っかかるようになったのはエエですね彡(^)(^)
公開状況はナレッジにより様々ですが、どんどん公開していって欲C。

Day 4. GoldenGate布教活動①

GoldenGate布教活動① ~GoldenGateの基本のキ~ - Qiita
https://qiita.com/ch0c0bana0/items/0567de420c2a40128e94

Day 4 は Unoki Yoshifumiさん の GoldenGate記事となります。
ORACLEレプリケーション製品である Oracle GoldenGate について、
基本的なところを語ってくれてます。Capture! Datapump! Replicat!彡(゚)(゚)

Day 5. VirtualBox で DB 18c環境を楽々構築

OTN の VirtualBoxイメージ で Oracle DB 18c環境 を 楽々構築
https://gonsuke777.hatenablog.com/entry/2018/12/05/231350

Day 5 はワイの VirtualBox による Oracle DB 18c環境の構築記事。
ovaイメージをダウンロードしてきてポチポチするだけなんで、めっちゃ楽彡(^)(^)

なお VirtualBox の NAT(ポートフォワード)は知らなかった、と云う声も頂きました。
Terminalソフトによるssh接続は馴染みが有るんで、ワイ好きなんすよね彡(゚)(゚)

Day 6. GoldenGate布教活動②

GoldenGate布教活動② ~GoldenGateのインストール~
https://qiita.com/ch0c0bana0/items/a57debf29a8d907e9feb

Day 6 は Unoki Yoshifumiさん の GoldenGate記事で、Day 4 の続き。
GoldenGate のインストールについて書いてくれています。
zip展開してrunInstaller実行するだけなんで、めっちゃ楽。

まあ昔はzip展開だけだったんですけどね……パッチとか色々事情は有りまして(゚ε゚ )

Day 7. GoldenGate布教活動③

GoldenGate布教活動③ ~GoldenGateを使ってみよう 事前準備編~
https://qiita.com/ch0c0bana0/items/2486c8481305ecf5de6f

Day 7 は引き続き Unoki Yoshifumiさん の GoldenGate記事。
サプリメンタルロギング設定、Capture/Datapump/Replicatの各プロセス構成など、
レプリケーションへの準備は着々や!彡(゚)(゚)

Day 8. SQL性能計測2

Oracle DatabaseでSQLの性能計測2(DBMS_XPLAN&DBMS_SQLTUNE編)
https://gonsuke777.hatenablog.com/entry/2018/12/08/215428

Day 8. はワイの DBMS_XPLAN と DBMS_SQLTUNE による SQL性能計測記事や!

特に DBMS_SQLTUNE は最高や~~。コイツが有ると無いとでSQLチューニングの
効率が全く違う。要オプションなんやけど、有る所ではどんどん使おうな彡(゚)(゚)
過去講演(DDD 2013)の資料もよろしくや~~彡(^)(^)

Day 9. GoldenGate布教活動④

GoldenGate布教活動④ ~GoldenGateプロセスの構成~
https://qiita.com/ch0c0bana0/items/651e8378a74ba9c0059e

Day 9 は Day 7 から続く Unoki Yoshifumiさん の GoldenGate記事。
ついにレプリケーション(データ伝播)が現実のものに!彡(゚)(゚)

GoldenGateは比較的シンプルな製品で、レプリケーションの構成も
堅牢かつ柔軟に組み立てられるので、ガンガン使ってやで彡(^)(^)

Cloud の Data Integration Platform Cloud(DIPC) でもお手軽に使えますよ!

Day 10. GoldenGate布教活動⑤

GoldenGate布教活動⑤ ~GoldenGateスターターキット~
https://qiita.com/jimaz/items/f6b45d1400306b4dfc79

Day 10 は jimaz26さん のエントリで、GoldenGate関連のマニュアル、
セミナー資料、ホワイトペーパーのリンクをまとめてくれてますやで彡(^)(^)

Day 11. SDW(SQL Developer Web) on DBCS

SDW(SQL Developer Web) on DBCS をお試ししてみる。
https://gonsuke777.hatenablog.com/entry/2018/12/11/230110

Day 11 はワイが DBCS上で動作する SDW(SQL Developer Web) について、
セットアップ方法のサンプルや一部画面のお試しをしています。彡(゚)(゚)

まだまだ未完成なところも有りますが、SDW の今後に期待や!

Day 12. GoldenGate用Datapump(expdp)新機能 インスタンス化SCN

インスタンス化SCNを使ってお手軽初期移行
https://qiita.com/kurouuuron/items/118afdc6b6d15d46e7dd

Day 12 は kurouuuron1さんがDatapump(expdp)のGoldenGate用新機能となる
インスタンス化SCN(instantiation scn)について、解説&サンプル提示してくれてます。

こいつが有ればexpdp時のflashback_scn指定やそのORA-1555から開放されて、
Replicat起動時のaftercsn指定も不要になって、いいこと尽くめや!彡(^)(^)

Day 13. Python で Statspack を見える(グラフ)化

PythonでStatspackのTOP5待機イベントを見える化する
https://qiita.com/kngsym2018/items/feee8c43db8399821365

Day 13 は kngsym2018さんが Statspack の Top5待機イベント を
Python で見える(グラフ)化する方法を書いてくれています。

こいつが有れば Standard Edition でも闘えるんや!彡(゚)(゚)

Day 14. CDBフリート管理

CDBフリート管理
http://cosol.jp/techdb/2018/12/cdbfreet.html

Day 14 は moritaxp_oironさん が Oracle Database 18c 新機能 の
CDBフリート管理について検証記事を書いてくれています。

フリートとは艦隊の意味なので、CDB艦隊の意味……
複数CDBを1つの艦隊として捉えて、その中をPDBがグルグルするイメージですやね彡(゚)(゚)

Day 15. Oracle text の Create index を LogMiner

ログマイナーを活用した内部処理の検証・出力方法~Oracle text のcreate indexを例に~
http://ectogre.hatenablog.com/entry/2018/12/15/000000

Day 15 は SPIRYTUS_alc96さん が Oracle Text の create index時の
ORA-1031エラーを、ログマイナー(LogMiner)によって深堀りしてくれています。

LogMinerはワイも過去何回か使った事が有りますが、初めて使った時には
「ま、まさか…!!」「こんなことが…!!」「物理的に可能なのか」(マッスルドッキング並感
てな感じの衝撃を受けた記憶が彡(゚)(゚)

Day 16. Oracle Database 18c XE on Docker

Oracle Database 18c XE をDockerで動かしてみる。
http://knopp.hatenablog.jp/entry/2018/12/16/162002

Day 16 はあらゆるプラットフォーム(AWS, Azure, GCP, Docker, etc...)で
RAC を動作させる事で有名な、RACウィザード の s4r_agent(KNOPP)さん が
Oracle Database 18c XE を Docker上で動作させるサンプルを書いてくれてます。

ワイも Docker はやらないとなぁ… VirtualBox や Multitenant(MTA, PDB) で
代替できてしまうので、つい後回しになっちゃうんよね彡(-)(-)

Day 17. OCI VM DB の バックアップ構成

OCI Virtual Machine DBシステムのバックアップ構成
http://cosol.jp/techdb/2018/12/oci-virtual-machine-db-system-backup-config.html

Day 17 は wrcsus4さん が OCI(Oracle Cloud Infrastructure)の
VM Database の バックアップ構成を紐解いてくれています。

OCI上の DB では Object Storage が テープデバイス(sbt_tape)として
構成されていて、その Object Storage にバックアップが取られるんですやね。

Object Storage は確かにテープデバイスと似た、巨大ファイルの
Sequential Access が得意という特性が有るので、納得の構成ですやね彡(゚)(゚)

Day 18. GoldenGate布教活動⑥

GoldenGate布教活動⑥ ~GoldenGate管理コマンド編1~
https://qiita.com/jimaz/items/5a0271255b021a9af50d

Day 18 は jimaz26さん が GoldenGate の 管理コマンド、
INFOコマンドやSTATSコマンド等について解説してくれています。

twitterでも呟いたんやけど、GoldenGateの管理コマンドと云えば
LAGは外せないでしょ。Capture の LAG! Datapump の LAG! Replicat の LAG! 彡(゚)(゚)

来たる GoldenGate管理コマンド編2 に期待ですやね彡(^)(^)

Day 19. Oracle Database to EnterprisePostgres

Oracle Database Advent Calendar2018 Day 19
https://ameblo.jp/shinogi-gogo/entry-12428728474.html

Day 19 は Naotaka Shinogiさん がOracle Database から EnterprisePostgres への
移行についての エントリを書かれています。移行しちゃいやん……彡(゚)(゚)

Day 20. SQL性能計測3

Oracle DatabaseでSQLの性能計測3(SQLトレース編)
https://gonsuke777.hatenablog.com/entry/2018/12/21/190057

Day 20. はワイの SQLトレースによる SQL性能計測記事や!

DBMS_SQLTUNEが無くたって、SQLトレースが有れば同等レベルの分析は可能なんや!
こいつが有れば Standard Edition でも闘える!彡(゚)(゚)

あとは TRCSESSユーティリティとか V$DIAG_TRACE_FILE や
V$DIAG_TRACE_FILE_CONTENTS からのトレース採取など、
ネタはまだまだ有るやね。

Day 21. RAC One Nodeアラートログ

Oracle】押さえておきたいRAC One Nodeのアラートログの仕組み | アシスト
https://www.ashisuto.co.jp/db_blog/article/201812-rac-one-alert.html

Day 21 は satoayu_twさん が RAC One Node の
アラートログの仕組みについて解説してくれています。

記事を読むとよく解るんですが、なるほどちょっとクセが有る……彡(゚)(゚)

Day 22. キャンディちゃんが語る投票ディスク

キャンディちゃんに叱られる!~どうして投票ディスクは奇数個がいいの?~ -
https://qiita.com/HIROKAZU_MIRUMIRU/items/1162fbbeccd7fa273538

Day 22 は HIROKAZU YATSUNAMIさん が RAC の投票ディスクは
なぜ奇数個が良いのか、可愛く説明してくれてます。

可愛さに嫉妬……!彡(゚)(゚)

Day 23. Oracle DB 18c マニュアルのマニュアル

Oracle Database 18c マニュアルのマニュアル(※よく使うマニュアルのリンク集)
https://gonsuke777.hatenablog.com/entry/2018/12/24/232621

Day 23 はワイが Oracle Database 18c のマニュアルで、
頻繁に見るマニュアルのリンクを簡単に解説しつつ、貼っていってます彡(゚)(゚)

記事にも書いたんですが、書くのが意外と大変だった割りに地味wwwww

Day 24. Oracle Databaseバージョン考察2018師走

もし師走に『Oracle Databaseバージョン選択における考察’18』を創るなら
https://wmo6hash.hatenablog.jp/entry/2018/12/24/000000

Day 24 は wmo6hashさん が Oracle Databaseバージョンの選択について、
2018年師走版の考察を書いてくれています。

LTS(Long Term Support) の DB 19c か、取り敢えず DB 18c を採用するか……彡(゚)(゚)

Day 25. Oracle DB or OGG Advent Calendar 2018 振り返り

まとめ:Oracle Database or GoldenGate Advent Calendar 2018 を振り返る!
https://gonsuke777.hatenablog.com/entry/2018/12/26/195137

この記事や!いったん投稿して、URLを確定させないとURLを書けない罠……:(;゙゚'ω゚'):

お隣 Advent Calendar

Oracle Cloud Advent Calendar 2018
https://adventar.org/calendars/3077


Oracle Cloud その2 Advent Calendar 2018
https://adventar.org/calendars/3522


(全部俺) Oracle Cloud Infrastructure Advent Calendar 2018
※一人Advent Calendar
https://adventar.org/calendars/3088


Oracle OpenStack R5 への道。 Advent Calendar 2018
※一人Advent Calendar
https://adventar.org/calendars/3716


Oracle絡みの Advent Calendar です。どれも必見や!彡(゚)(゚)
一人Advent Calendar は侠気(狂喜)に満ち溢れているので、オススメですwwwww

過去の JPOUG Advent Calendar

JPOUG Advent Calendar 2017
https://jpoug.doorkeeper.jp/events/67051


JPOUG Advent Calendar 2016
https://jpoug.doorkeeper.jp/events/53797


JPOUG Advent Calendar 2015
https://jpoug.doorkeeper.jp/events/33345


JPOUG Advent Calendar 2014
https://jpoug.doorkeeper.jp/events/17313


※JPOUG Advent Calendar 2013 と JPOUG Advent Calendar 2012 は消えちゃってますね(´・ω・`)

まとめ&お礼

繰り返しになりますが、執筆して頂いた皆さん、
本当にありがとうございました!彡(^)(^)

スタートダッシュ時の状況から、埋まらないかな~~?と感じてましたが、
皆様のご協力のお陰で埋まりました。感謝感激雨霰(`・ω・)ゞ

それでは皆様、良いお年を!

Oracle Database 18c マニュアルのマニュアル(※よく使うマニュアルのリンク集)【Oracle Database or GoldenGate Advent Calendar 2018 Day 23】

このエントリは Oracle Database or GoldenGate Advent Calendar 2018 の Day 23 の
記事となります。またまた一日遅れてしまいました。ほんとスミマセンスミマセン……:(;゙゚'ω゚'):

Oracle Database or GoldenGate Advent Calendar 2018
https://adventar.org/calendars/3525

Oracle Database のマニュアルは、ワイ的には良くできてると思うんですが、
いかんせん数と種類が多過ぎる……彡(-)(-)

そこで今日は「Oracle Database 18c マニュアルのマニュアル」と称して、
ワイが頻繁に見る Oracle Database のマニュアルのリンクを張りつつ、
簡単な解説をしていくやで! 彡(゚)(゚)
※下記リンクは2018年12月時点のものです。リンク切れの際はご容赦下さい。

1. ブック(全マニュアル)のリンク

Oracle Database 18c ブック
https://docs.oracle.com/cd/E96517_01/books.html

まずはココから!全マニュアルへのリンクがここに有るんやで。ココをブックマークしとくんや!彡(゚)(゚)


Oracle Database Cloud Service ブック
https://docs.oracle.com/cd/E83857_01/paas/database-dbaas-cloud/books.html

こちらは DB の PaaS(DBaaS) のマニュアルなんやで。現在は Cloud の時代、ここも要チェックやで。

2. 頻繁に見るマニュアル

Oracle Databaseデータベース・リファレンス 18c
https://docs.oracle.com/cd/E96517_01/refrn/index.html

初期化パラメータ、データディクショナリ(DBA_~)、動的ビュー(V$~)、データベースの制限事項(最大サイズとか)、待機イベント説明、各種統計(V$SYSSTATのレコード)の説明、など重要情報が満載や!
彡(゚)(゚)


Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 18c
https://docs.oracle.com/cd/E96517_01/arpls/index.html

DBMSほにゃららパッケージ系のご説明はこちら。DBMS_STATS、DBMS_XPLAN、DBMS_SQLTUNEなんかを良く見るかなぁ彡(゚)(゚)

3. 言語開発(SQL, PL/SQL, 等)系のマニュアル

Oracle Database SQL言語リファレンス, 18cバージョン18.1
https://docs.oracle.com/cd/E96517_01/sqlrf/index.html

各種SQLコマンド(SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, DROP, etc..)の構文図やオプション、標準ファンクション(TO_CHAR、SYSDATEとか)、ヒントの構文など彡(゚)(゚)


Oracle DatabaseデータベースPL/SQL言語リファレンス 18c
https://docs.oracle.com/cd/E96517_01/lnpls/index.html

PL/SQLの制御文(IF文, CASE文)、繰り返し(LOOP文, FOR文, WHILE文)、コレクションの使い方、BULK処理(FORALL処理)、ストアド系、トリガー、テーブル・ファンクションの作り方、etc...
うん、書き切れないなwwwww


Oracle Database Databaseグローバリゼーション・サポート・ガイド 18c
https://docs.oracle.com/cd/E96517_01/nlspg/index.html

文字コード系、NLS_~ほにゃらら、タイムゾーンサマータイム、年号元号とかはここ彡(゚)(゚)


Oracle Database JSON開発者ガイド 18c
https://docs.oracle.com/cd/E96517_01/adjsn/index.html

実はそこまで馴染みは無いんですが、JSONは避けては通れない道なんで彡(゚)(゚)
12cR1あたりから滅茶苦茶強化されてます。

4. ユーティリティ、ツール系のマニュアル

Oracle Databaseデータベース・ユーティリティ 18c
https://docs.oracle.com/cd/E96517_01/sutil/index.html

Datapump(expdp, impdp), SQL*Loader(sqlldr), 外部表(EXTERNAL TABLE), LogMiner, ADRCI, 従来型エクスポート/インポート(exp, imp) などがこちら彡(゚)(゚)


SQL*Plusユーザーズ・ガイドおよびリファレンス 18c
https://docs.oracle.com/cd/E96517_01/sqpug/index.html

SQL*Plus(sqlplus)の使い方、SETコマンド、AUTOTRACEのオプションなど彡(゚)(゚)

5. 性能系(チューニング系)のマニュアル

Oracle Databaseデータベース・パフォーマンス・チューニング・ガイド 18c
https://docs.oracle.com/cd/E96517_01/tgdba/index.html

全体最適化、インスタンス・チューニング系のマニュアル。パフォーマンスの計画の話に始まり、Time Model、待機イベント、AWR分析、CPU/Memory/IO分析、メモリーチューニング、etc...
良く出来てます。オススメや!彡(^)(^)


Oracle Database SQLチューニング・ガイド 18c
https://docs.oracle.com/cd/E96517_01/tgsql/index.html

こちらは単体のSQLチューニングに特化した内容。SQLの解析(Parse)、実行計画、オプティマイザ統計、アクセスパスの解説、アドバイザ&SQLプロファイル、SPM、etc...SQLトレースやtkporfの使い方もココ。
こちらも非常に良く出来ていて、オススメ!彡(^)(^)


Oracle Database VLDBおよびパーティショニング・ガイド 18c
https://docs.oracle.com/cd/E96517_01/vldbg/index.html

VLDB ⇒ Very Large Database の略、パーティション(RANGE, LIST, HASH, COMPOSITE, etc...) と パラレル・クエリ のマニュアルと覚えときゃ大体おk(雑

6. SQL*Net系(tnsnames.oraとか sqlnet.oraとか 簡易接続とか)のマニュアル

Oracle Database Net Servicesリファレンス 18c
https://docs.oracle.com/cd/E96517_01/netrf/index.html

tnsnames.ora, sqlnet.ora, listener.ora のパラメータの意味などです。辞書っぽく使う感じ。


Oracle Database Net Services管理者ガイド 18c
https://docs.oracle.com/cd/E96517_01/netag/index.html

前者のマニュアルがパラメータ系のリファレンスなのに対し、こちらはNet Serviceのアーキテクチャ解説など。
でも一番良く読んだのは、簡易接続(EZCONNECT)とローカル・ネーミング・メソッドの接続記述子の書き方か……彡(゚)(゚)

7. DBA系のマニュアル

Oracle Databaseデータベース概要 18c
https://docs.oracle.com/cd/E96517_01/cncpt/index.html
cncpt233.gif

Oracle Database に詳しくなくても、この画像は見たこと有る人も多いかも。RDBMSの成り立ちから始まり、トランザクションの説明、データベースとインスタンスの関係、プロセス構造、メモリ構造、記憶域構造、読取一貫性、etc...
アーキテクチャのお勉強や!彡(゚)(゚)


Oracle Database 2日でデータベース管理者 18c
https://docs.oracle.com/cd/E96517_01/admqs/index.html

DBA の一連の作業を広く網羅的に。インストールから始まり、DBCAによるDB作成、ネットワーク構成(リスナー、tnsnames.oraなど)、インスタンスの起動/停止、表領域やREDO&UNDOの管理、バックアップ・リカバリ、監視、チューニング、パッチ適用


Oracle Database管理者ガイド, 18c
https://docs.oracle.com/cd/E96517_01/admin/index.html

DBA の一連の作業の深めなところ。でもワイがこれで一番読んだのって、dbca の サイレント・モード の コマンド・リファレンスかなぁ…彡(゚)(゚)

8. バックアップ・リカバリ, RMAN系のマニュアル

Oracle Databaseバックアップおよびリカバリ・リファレンス 18c
https://docs.oracle.com/cd/E96517_01/rcmrf/index.html

こちらは RMAN のコマンド・リファレンスとなります。BACKUP, CONFIGURE, RESTORE, RECOVER, DELETE, DUPLICATE あたりをよく見るですやろうか彡(゚)(゚)


Oracle Databaseバックアップおよびリカバリ・ユーザーズ・ガイド, 18c
https://docs.oracle.com/cd/E96517_01/bradv/index.html

こちらは RMAN によるバックアップ&リカバリアーキテクチャ、戦略等が解説されてます。フラッシュバックについても。RMANを使わない、ユーザー管理のバックアップ話も有るよ彡(゚)(゚)

9. RAC, Clusterware, ASM のリファレンス・マニュアル

Oracle Real Application Clusters管理およびデプロイメント・ガイド 18c
https://docs.oracle.com/cd/E96517_01/racad/index.html

RAC(Real Application Cluster) の コマンド系のマニュアルです。srvctlコマンドのリファレンスと覚えておけばおk(雑


Oracle Clusterware管理およびデプロイメント・ガイド 18c
https://docs.oracle.com/cd/E96517_01/cwadd/index.html

こちらは Oracle Clusterware のコマンド系のマニュアルです。crsctlコマンドのリファレンス、たまにoifcfgのリファレンスと覚えておけばおk(雑2


Oracle Automatic Storage Management管理者ガイド 18c
https://docs.oracle.com/cd/E96517_01/ostmg/index.html

ASM(Automatic Storage Management)による Storage や ディスク・グループ の設計指針、管理など。asmcmdコマンドやacfsutilのリファレンスも有るよ!彡(゚)(゚)

10. Data Guard系のマニュアル

Oracle Data Guard概念および管理 18c
https://docs.oracle.com/cd/E96517_01/sbydb/index.html

Data Guard と言えばまずこれ。Data Guard のアーキテクチャ、スタンバイの作成、REDO転送と適用、ロール変換、Data Guard系の各種パラメータご説明も彡(゚)(゚)


Oracle Data Guard Broker 18c
https://docs.oracle.com/cd/E96517_01/dgbkr/index.html

Data Guard構成(Primary-Standby)を一つの論理的な構成として扱う Data Guard Broker。Broker と言えば自動Failover(ファスト・スタート・フェイルオーバー)を
思い出されるかもしれませんが、ワンコマンドのスイッチオーバー、MRP自動起動、ロール・ベース・サービスなど、その他のメリットも一杯!彡(^)(^)

11. インストール系のマニュアル

Oracle Databaseインストレーション・ガイド, 18c for Linux
https://docs.oracle.com/cd/E96517_01/ladbi/index.html

まずは Single構成 のインストールから。近年のバージョンのインストールは Oracle Preinstallation RPM のお陰でラクになった…彡(-)(-)


Oracle Grid Infrastructureインストレーションおよびアップグレード・ガイド 18c for Linux
https://docs.oracle.com/cd/E96517_01/cwlin/index.html

Single構成の次は GI(Grid Infrastructure) のインストール。昔よりはラクになったんですが、GIインストールの難易度は今も高め……彡(゚)(゚)


Oracle Real Application Clustersインストレーション・ガイド 18c for Linux and UNIX
https://docs.oracle.com/cd/E96517_01/rilin/index.html

最後に RAC(Real Application Clusters)のインストール。GIがインストールできてれば、こっちのインストールは楽勝なんかと彡(゚)(゚)

12. セキュリティ系のマニュアル

Oracle Databaseセキュリティ・ガイド 18c
https://docs.oracle.com/cd/E96517_01/dbseg/index.html

Oracle Database のセキュリティ一般の話。認証、認可(権限付与)、アクセス制御、暗号化、監査の話など彡(゚)(゚)


Oracle Database Vault管理者ガイド 18c
https://docs.oracle.com/cd/E96517_01/dvadm/index.html

Database Vaultは管理者同士の権限分掌と相互監視によって、特定管理者のセキュリティ違反を防止/追跡する機能。レルムじゃレルムじゃ~~彡(゚)(゚)


Oracle Database Advanced Securityガイド, 18c
https://docs.oracle.com/cd/E96517_01/asoag/index.html

主に暗号化に関するマニュアル。いつの間にかこんなのが彡(゚)(゚) Oracle Cloudでは暗号化がデフォルトなんで、新しく作られたのかね彡(゚)(゚)

13. 新機能&アップグレード系のマニュアル

Oracle Database新機能ガイド 18c
https://docs.oracle.com/cd/E96517_01/newft/index.html

新機能と言えばまずはこのガイド。つらつらと眺めていると、意外な発見が有りますやね彡(^)(^)


Oracle Databaseアップグレード・ガイド 18c
https://docs.oracle.com/cd/E96517_01/upgrd/index.html

既存の Oracle Database を直接アップグレードするためのガイド。でもこのやり方のアップグレードは、昔よりは少なくなったなぁ……彡(-)(-)

14. その他のマニュアル

Oracle Databaseライセンス情報ユーザー・マニュアル 18c
https://docs.oracle.com/cd/E96517_01/dblic/index.html

各種Editionやオプション、Cloudのサービスで使用可能な機能を
俯瞰できるマニュアル。何気に重要な情報が満載彡(゚)(゚)


Oracle Databaseエラー・メッセージ 18c
https://docs.oracle.com/cd/E96517_01/errmg/index.html

ORA-xxxxxやRMAN-xxxxx等のエラー番号/メッセージを記載したマニュアル。エラーと言えばORA-600やORA-7445!
ORA-600やORA-7445と聞くと顔が曇る人も多いですが、エラー無しでクラッシュするより何百倍もマシなんやで彡(゚)(゚)

15. まとめ

このエントリ、書くのが意外と大変だった割りに地味やなぁwwwww
さりながら、めくるめくOracle Databaseの世界を巡る手助けになれば、本望ですやで!彡(^)(^)

Oracle DatabaseでSQLの性能計測3(SQLトレース編)【Oracle Database or GoldenGate Advent Calendar 2018 Day 20】

このエントリは 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ホーム・ディレクトリの下に配置されるファイルは、次のとおりです。

・トレース・ファイル - <adr-home>/traceサブディレクトリに配置されます。
:

今回の環境は 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       213.2313.7366698926          0261

対象の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の待機イベントで時間が掛かっている。

このようなケースでは以下のようなチューニングを試すのがセオリーとなります。※前回のコピペ

  • オプティマイザ統計の採取
  • ヒストグラム、拡張統計(複数列統計, 式統計)の取得
  • SQLチューニング・アドバイザの実行 と SQLプロファイルの適用(※)
     ※SQLプロファイルが提案された場合

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インスタンスで端末へのX Window ssh転送が出来るように設定する。

OCI(Oracle Cloud Infrastructure)のComputeインスタンスで端末にX Windowssh転送が出来るように設定してみるやで。
環境は OCI で OEL 7.6(Oracle Enterprise Linux 7.6) の仮想マシンで試してみます。

1. ターミナルソフト(teraterm等)のX転送設定

まずターミナルソフト(teraterm等)のX転送設定を有効にします。
下記のような記事をご参照下さい。

TeratermX Windowサーバでsshx11フォワーディング機能を利用する 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. 端末へのXサーバー(Xming等)導入と起動

端末に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

上記設定については↓に情報があります。

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

f:id:gonsuke777:20181214201315j:plain
xeyes起動画面

見事に起動!(`・ω・)Ъ

6. AddressFamily inet の設定について

まずsshd_configに追記する「AddressFamily inet」の動作ですが、
これはsshdの接続でIPv4を強制することを意味します。

sshdで「IPv4」「IPv6」のどちらかのプロトコルのみ接続させる方法
https://www.server-memo.net/server-setting/ssh/addressfamily.html

そして bug のコメントでは、システム全体で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 any as mentioned in the description):
AddressFamily inet

bug の中では、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が無効化されている場合は同様なんかと?彡(゚)(゚)

SDW(SQL Developer Web) on DBCS をお試ししてみる。【Oracle Database or GoldenGate Advent Calendar 2018 Day 11】【Oracle Cloud その2 Day 11】

このエントリは 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スキーマにアクセスします。

https://xxx.xxx.xxx.xxx/ords/PDB4/sdwtest/_sdw

f:id:gonsuke777:20181211230411j:plain
SDWのログイン画面

f:id:gonsuke777:20181211223210j:plain
ログイン後のDashboard

画面左上のボタンからSQL Developer Webに遷移します。

f:id:gonsuke777:20181211223305j:plain
SQL Developer Webへの遷移

f:id:gonsuke777:20181211223456j:plain
SQL Developer WebのHome画面

3. Worksheetタブをお試し。

Worksheetタブをお試ししてみます。
SQL Developer といえば、まずはコイツですやね彡(゚)(゚)

f:id:gonsuke777:20181211223647j:plain
Worksheetタブ

v$sessionをSELECTしてみると。。。

f:id:gonsuke777:20181211223742j:plain
v$sessionビューのSELECT結果

結果が取れてきてますやね彡(^)(^)

さらにSQL Developer でも良く使うFormat(整形)機能もあります。

f:id:gonsuke777:20181211223921j:plain
Format機能

f:id:gonsuke777:20181211224112j:plain
Format後のSQL

きっちり整形されてますやね彡(^)(^)

4. DBAタブのお試し

DBAタブをお試ししてみます。幾つか選択肢は有りますが、まずはAlertsを…彡(゚)(゚)

f:id:gonsuke777:20181211224319j:plain
Alertsのサンプル

アラートログ相当の内容を確認できます。

次はSessionsを見てみると…彡(゚)(゚)

f:id:gonsuke777:20181211224433j:plain
Sessionsのサンプル

v$session相当の情報が確認できますやね彡(^)(^)

5. OSタブのお試し

最後にOSタブをお試ししてみます。CPUを見てみます。

f:id:gonsuke777:20181211224827j:plain
OSタブのCPUのサンプル

mpstat -P all相当の情報が確認できますやね。彡(゚)(゚)

6. まとめ

SQL Developer Web は Oracle APEX で作られているのですが、APEXの実力が垣間見えます。
Jeff Smithさんがリーダとして作っていますが、彼のブログもチェックしてやで彡(゚)(゚)

www.thatjeffsmith.com

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 に gather_plan_statisticsヒント を付与する。
  • STATISTICS_LEVELパラメータをALLにセットした状態で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-FD23E9999C6A

Oracle 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 の実行時間が5秒以上
  • SQL がパラレル・クエリとして実行
  • 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 の性能計測により確認できます。
このようなケースでは以下のようなチューニングを試すのがセオリーとなります。

  • オプティマイザ統計の採取
  • ヒストグラム、拡張統計(複数列統計, 式統計)の取得
  • SQLチューニング・アドバイザの実行 と SQLプロファイルの適用(※)
    SQLプロファイルが提案された場合

どのチューニングも 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

Alt text