GAGA LIFE.

DBAブログ

Docker上にOracle Database 18cを構築

概要

Oracle Database 18cのオンプレ版がリリースされました。
Docker(CentOS)にOracle Database EE 18.0.0.0(18.3)をインストールします。

環境情報

ホストOS:Windows 10
VirtualBox:Ver 5.2.16
ゲストOS:CentOS 7 64bit
Oracle:Oracle Database EE 18.0.0.0

手順概要

基本的に以下の公式の手順とスクリプトを使用して構築しています。
https://github.com/oracle/docker-images/blob/master/OracleDatabase/SingleInstance/README.md

 1.Dockerイメージの準備(配置)
 2.buildDockerImage.shの動作確認
 3.インストールモジュール準備
 4.データベース環境作成
 5.データベース作成
 6.インスタンスへの接続確認

また、以前に下記の記事でインストールした12.2.0.1と方法は大きく変わりません。
http://www.undercoverlog.com/entry/2018/05/05/120615

手順説明

1.Dockerイメージの準備

https://github.com/oracle/docker-images
[Clone or download]ボタンを押してダウンロードしました。
※「docker-images/OracleDatabase/SingleInstance/dockerfiles」配下がデータベース用

2.buildDockerImage.shの動作確認

今回は、「/home/oracle」配下でダウンロードした「docker-images-master.zip」を解凍して実行しました。
/home/oracle/docker-images-master/OracleDatabase/SingleInstance/dockerfiles配下に「buildDockerImage.sh」が展開されます。hオプション(help)で実行確認します。

3.インストールモジュールの準備

下記OTNサイトから(ライセンスに同意し)ダウンロード

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-linux-12201-3608234.html?ssSourceSiteId=otnjp
ダウンロードファイル名:LINUX.X64_180000_db_home

ダウンロードしたファイルは、解凍せず(zip形式のまま)に以下のディレクトリ配下へ格納しています。
/home/oracle/docker-images-master/OracleDatabase/SingleInstance/dockerfiles/18.3.0

4.データベース環境作成

ファイルの配置とパーミッション等が整備されていれば、シェルスクリプトを実行するのみです。
それぞれの引数の意味はhelpから確認可能です。
[oracle@Gretsch dockerfiles]$ ./buildDockerImage.sh -h

Usage: buildDockerImage.sh -v [version] [-e | -s | -x] [-i] [-o] [Docker build option]
Builds a Docker Image for Oracle Database.

Parameters:
-v: version to build
Choose one of: 11.2.0.2 12.1.0.2 12.2.0.1 18.3.0
-e: creates image based on 'Enterprise Edition'
-s: creates image based on 'Standard Edition 2'
-x: creates image based on 'Express Edition'
-i: ignores the MD5 checksums
-o: passes on Docker build option

* select one edition only: -e, -s, or -x


5.データベースの作成

以下の構成で作成しています。

・MTA(マルチテナントアーキテクチャ)
--> CDB(コンテナ)+1PDB(プラガブル)

・環境変数(コンテナ/プラガブル)
ORACLE_SID=orcl
ORACLE_PDB=pdb1

・ポート構成
DBへの接続ポート:1521
EMへの接続ポート:5500

・データのDockerホストへのマッピング先
/mnt/oradata

・管理者ユーザパスワード
起動(docker run実行)後、以下の文字列後に出力されます。
今回の例では、「ZCqw5kkI0YA」(手順内の赤字部分)となります。

ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN:

手順実行

■1.Dockerイメージの準備(配置)
[oracle@Gretsch dockerfiles]$ pwd
/home/oracle/docker-images-master/OracleDatabase/SingleInstance/dockerfiles
[oracle@Gretsch dockerfiles]$ ll
合計 20
drwxr-xr-x. 2 oracle docker 126 7月 24 07:21 11.2.0.2
drwxr-xr-x. 2 oracle docker 4096 7月 24 07:21 12.1.0.2
drwxr-xr-x. 2 oracle docker 4096 7月 24 07:21 12.2.0.1
drwxr-xr-x. 2 oracle docker 4096 7月 24 22:22 18.3.0
-rwxr-xr-x. 1 oracle docker 4171 7月 24 07:21 buildDockerImage.sh
■2.buildDockerImage.shの動作確認
[oracle@Gretsch dockerfiles]$ ./buildDockerImage.sh -h
Usage: buildDockerImage.sh -v [version] [-e | -s | -x] [-i] [-o] [Docker build option]
Builds a Docker Image for Oracle Database.

Parameters:
-v: version to build
Choose one of: 11.2.0.2 12.1.0.2 12.2.0.1 18.3.0
-e: creates image based on 'Enterprise Edition'
-s: creates image based on 'Standard Edition 2'
-x: creates image based on 'Express Edition'
-i: ignores the MD5 checksums
-o: passes on Docker build option
* select one edition only: -e, -s, or -x
■3.インストールモジュール準備
[oracle@Gretsch 18.3.0]$ pwd
/home/oracle/docker-images-master/OracleDatabase/SingleInstance/dockerfiles/18.3.0
[oracle@Gretsch 18.3.0]$ ll
合計 4457744
-rw-r--r--. 1 oracle docker 63 7月 24 07:21 Checksum.ee
-rw-r--r--. 1 oracle docker 63 7月 24 07:21 Checksum.se2
-rw-r--r--. 1 oracle docker 2606 7月 24 07:21 Dockerfile.ee
-rw-r--r--. 1 oracle docker 2612 7月 24 07:21 Dockerfile.se2
-rwxrwxrwx. 1 oracle docker 4564649047 7月 24 21:53 LINUX.X64_180000_db_home.zip
-rwxr-xr-x. 1 oracle docker 1148 7月 24 07:21 checkDBStatus.sh
-rwxr-xr-x. 1 oracle docker 905 7月 24 07:21 checkSpace.sh
-rwxr-xr-x. 1 oracle docker 2953 7月 24 07:21 createDB.sh
-rw-r--r--. 1 oracle docker 6878 7月 24 07:21 db_inst.rsp
-rw-r--r--. 1 oracle docker 9204 7月 24 07:21 dbca.rsp.tmpl
-rwxr-xr-x. 1 oracle docker 2220 7月 24 07:21 installDBBinaries.sh
-rwxr-xr-x. 1 oracle docker 6140 7月 24 07:21 runOracle.sh
-rwxr-xr-x. 1 oracle docker 1015 7月 24 07:21 runUserScripts.sh
-rwxr-xr-x. 1 oracle docker 758 7月 24 07:21 setPassword.sh
-rwxr-xr-x. 1 oracle docker 894 7月 24 07:21 setupLinuxEnv.sh
-rwxr-xr-x. 1 oracle docker 678 7月 24 07:21 startDB.sh
■4.データベース環境作成(事前にoracleユーザーをdockerグループに所属させる)
[oracle@Gretsch dockerfiles]$ sudo usermod -g docker oracle
[oracle@Gretsch dockerfiles]$ sudo /bin/systemctl restart docker.service
[oracle@Gretsch dockerfiles]$ chmod -R 777 18.3.0/
[oracle@Gretsch dockerfiles]$ ./buildDockerImage.sh -v 18.3.0 -e
Checking if required packages are present and valid...
LINUX.X64_180000_db_home.zip: 完了
==========================
DOCKER info:
Containers: 3
Running: 0
Paused: 0
Stopped: 3
Images: 50
Server Version: 18.03.1-ce
Storage Driver: overlay2
Backing Filesystem: xfs
Supports d_type: true
Native Overlay Diff: true
Logging Driver: json-file
Cgroup Driver: cgroupfs
Plugins:
Volume: local
Network: bridge host macvlan null overlay
Log: awslogs fluentd gcplogs gelf journald json-file logentries splunk syslog
Swarm: inactive
Runtimes: runc
Default Runtime: runc
Init Binary: docker-init
containerd version: 773c489c9c1b21a6d78b5c538cd395416ec50f88
runc version: 4fc53a81fb7c994640722ac585fa9ca548971871
init version: 949e6fa
Security Options:
seccomp
Profile: default
Kernel Version: 3.10.0-862.2.3.el7.x86_64
Operating System: CentOS Linux 7 (Core)
OSType: linux
Architecture: x86_64
CPUs: 4
Total Memory: 7.638GiB
Name: Gretsch
ID: R6PX:3ITF:FHHA:NRSS:2Q3J:XVBL:B2ER:VWQT:EDMN:C6CX:FLLB:UPWZ
Docker Root Dir: /var/lib/docker
Debug Mode (client): false
Debug Mode (server): false
Registry: https://index.docker.io/v1/
Labels:
Experimental: false
Insecure Registries:
127.0.0.0/8
Live Restore Enabled: false
==========================
Building image 'oracle/database:18.3.0-ee' ...
Sending build context to Docker daemon 4.565GB
Step 1/17 : FROM oraclelinux:7-slim
---> c94cc930790a
Step 2/17 : MAINTAINER Gerald Venzl <gerald.venzl@oracle.com>
---> Running in 76b7836e8c0e
Removing intermediate container 76b7836e8c0e
---> 2645dd189c95
~(略)~
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
openssl x86_64 1:1.0.2k-12.0.1.el7 ol7_latest 492 k
oracle-database-preinstall-18c
x86_64 1.0-1.el7 ol7_latest 18 k
tar x86_64 2:1.26-34.el7 ol7_latest 845 k
unzip x86_64 6.0-19.el7 ol7_latest 169 k
Installing for dependencies:
GeoIP x86_64 1.5.0-11.el7 ol7_latest 1.1 M
acl x86_64 2.2.51-14.el7 ol7_latest 81 k
bc x86_64 1.06.95-13.el7 ol7_latest 114 k
bind-libs x86_64 32:9.9.4-61.el7 ol7_latest 1.0 M
bind-license noarch 32:9.9.4-61.el7 ol7_latest 85 k
bind-utils x86_64 32:9.9.4-61.el7 ol7_latest 204 k
binutils x86_64 2.27-28.base.el7_5.1 ol7_latest 5.9 M
compat-libcap1 x86_64 1.10-7.el7 ol7_latest 17 k
compat-libstdc++-33 x86_64 3.2.3-72.el7 ol7_latest 190 k
~(略)~
qrencode-libs x86_64 3.4.1-3.el7 ol7_latest 49 k
quota x86_64 1:4.01-17.el7 ol7_latest 178 k
quota-nls noarch 1:4.01-17.el7 ol7_latest 90 k
rpcbind x86_64 0.2.0-44.el7 ol7_latest 59 k
smartmontools x86_64 1:6.5-1.el7 ol7_latest 460 k
sysstat x86_64 10.1.5-13.el7 ol7_latest 310 k
systemd x86_64 219-57.0.1.el7 ol7_latest 5.0 M
systemd-libs x86_64 219-57.0.1.el7 ol7_latest 402 k
systemd-sysv x86_64 219-57.0.1.el7 ol7_latest 79 k
sysvinit-tools x86_64 2.88-14.dsf.el7 ol7_latest 62 k
tcp_wrappers x86_64 7.6-77.el7 ol7_latest 78 k
tcp_wrappers-libs x86_64 7.6-77.el7 ol7_latest 65 k
util-linux x86_64 2.23.2-52.el7 ol7_latest 2.0 M
xorg-x11-utils x86_64 7.5-22.el7 ol7_latest 113 k
xorg-x11-xauth x86_64 1:1.0.9-1.el7 ol7_latest 29 k
xz x86_64 5.2.2-1.el7 ol7_latest 228 k
Updating for dependencies:
libcom_err x86_64 1.42.9-12.el7_5 ol7_latest 40 k
libstdc++ x86_64 4.8.5-28.0.1.el7_5.1 ol7_latest 303 k
Transaction Summary
================================================================================
Install 4 Packages (+108 Dependent packages)
Upgrade ( 2 Dependent packages)
Total download size: 46 M
Downloading packages:
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
--------------------------------------------------------------------------------
Total 10 MB/s | 46 MB 00:04
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Updating : libcom_err-1.42.9-12.el7_5.x86_64 1/116
Installing : tcp_wrappers-libs-7.6-77.el7.x86_64 2/116
Installing : fipscheck-1.4.1-6.el7.x86_64 3/116
Installing : fipscheck-lib-1.4.1-6.el7.x86_64 4/116
Installing : gzip-1.5-10.el7.x86_64 5/116
Installing : cracklib-2.9.0-11.el7.x86_64 6/116
Updating : libstdc++-4.8.5-28.0.1.el7_5.1.x86_64 7/116
Installing : cracklib-dicts-2.9.0-11.el7.x86_64 8/116
~(略)~
Verifying : cronie-1.4.11-19.el7.x86_64 108/116
Verifying : GeoIP-1.5.0-11.el7.x86_64 109/116
Verifying : 2:tar-1.26-34.el7.x86_64 110/116
Verifying : compat-libstdc++-33-3.2.3-72.el7.x86_64 111/116
Verifying : systemd-sysv-219-57.0.1.el7.x86_64 112/116
Verifying : libpwquality-1.2.3-5.el7.x86_64 113/116
Verifying : 1:dbus-libs-1.10.24-7.0.1.el7.x86_64 114/116
Verifying : libcom_err-1.42.9-11.0.1.el7.x86_64 115/116
Verifying : libstdc++-4.8.5-28.0.1.el7.x86_64 116/116
Installed:
openssl.x86_64 1:1.0.2k-12.0.1.el7
oracle-database-preinstall-18c.x86_64 0:1.0-1.el7
tar.x86_64 2:1.26-34.el7
unzip.x86_64 0:6.0-19.el7
Dependency Installed:
GeoIP.x86_64 0:1.5.0-11.el7
acl.x86_64 0:2.2.51-14.el7
bc.x86_64 0:1.06.95-13.el7
bind-libs.x86_64 32:9.9.4-61.el7
bind-license.noarch 32:9.9.4-61.el7
bind-utils.x86_64 32:9.9.4-61.el7
binutils.x86_64 0:2.27-28.base.el7_5.1
compat-libcap1.x86_64 0:1.10-7.el7
compat-libstdc++-33.x86_64 0:3.2.3-72.el7
cracklib.x86_64 0:2.9.0-11.el7
cracklib-dicts.x86_64 0:2.9.0-11.el7
cronie.x86_64 0:1.4.11-19.el7
cronie-anacron.x86_64 0:1.4.11-19.el7
crontabs.noarch 0:1.11-6.20121102git.el7
cryptsetup-libs.x86_64 0:1.7.4-4.el7
dbus.x86_64 1:1.10.24-7.0.1.el7
dbus-libs.x86_64 1:1.10.24-7.0.1.el7
device-mapper.x86_64 7:1.02.146-4.0.2.el7
device-mapper-libs.x86_64 7:1.02.146-4.0.2.el7
dracut.x86_64 0:033-535.0.2.el7
e2fsprogs-libs.x86_64 0:1.42.9-12.el7_5
elfutils-default-yama-scope.noarch 0:0.170-4.el7
elfutils-libs.x86_64 0:0.170-4.el7
ethtool.x86_64 2:4.8-7.el7
~(略)~
make.x86_64 1:3.82-23.el7
net-tools.x86_64 0:2.0-0.22.20131004git.el7
nfs-utils.x86_64 1:1.3.0-0.54.0.1.el7
openssh.x86_64 0:7.4p1-16.el7
openssh-clients.x86_64 0:7.4p1-16.el7
pam.x86_64 0:1.1.8-22.el7
pkgconfig.x86_64 1:0.27.1-4.el7
procps-ng.x86_64 0:3.3.10-17.el7_5.2
psmisc.x86_64 0:22.20-15.el7
qrencode-libs.x86_64 0:3.4.1-3.el7
quota.x86_64 1:4.01-17.el7
quota-nls.noarch 1:4.01-17.el7
rpcbind.x86_64 0:0.2.0-44.el7
smartmontools.x86_64 1:6.5-1.el7
sysstat.x86_64 0:10.1.5-13.el7
systemd.x86_64 0:219-57.0.1.el7
systemd-libs.x86_64 0:219-57.0.1.el7
systemd-sysv.x86_64 0:219-57.0.1.el7
sysvinit-tools.x86_64 0:2.88-14.dsf.el7
tcp_wrappers.x86_64 0:7.6-77.el7
tcp_wrappers-libs.x86_64 0:7.6-77.el7
util-linux.x86_64 0:2.23.2-52.el7
xorg-x11-utils.x86_64 0:7.5-22.el7
xorg-x11-xauth.x86_64 1:1.0.9-1.el7
xz.x86_64 0:5.2.2-1.el7
Dependency Updated:
libcom_err.x86_64 0:1.42.9-12.el7_5 libstdc++.x86_64 0:4.8.5-28.0.1.el7_5.1
Complete!
~(略)~
Step 12/17 : USER oracle
---> Running in 3f080487eeb5
Removing intermediate container 3f080487eeb5
---> 8933361333d7
Step 13/17 : WORKDIR /home/oracle
Removing intermediate container 2791d10eca55
---> 99e63389ce67
Step 14/17 : VOLUME ["$ORACLE_BASE/oradata"]
---> Running in 08cf5d17938e
Removing intermediate container 08cf5d17938e
---> 6e0ca709442b
Step 15/17 : EXPOSE 1521 5500
---> Running in bbc8d7a45364
Removing intermediate container bbc8d7a45364
---> df25e017a730
Step 16/17 : HEALTHCHECK --interval=1m --start-period=5m CMD "$ORACLE_BASE/$CHECK_DB_FILE" >/dev/null || exit 1
---> Running in 9f3bef559152
Removing intermediate container 9f3bef559152
---> 607b5b5a71a3
Step 17/17 : CMD exec $ORACLE_BASE/$RUN_FILE
---> Running in 0aabfb36585a
Removing intermediate container 0aabfb36585a
---> 085b58577b1f
Successfully built 085b58577b1f
Successfully tagged oracle/database:18.3.0-ee
Oracle Database Docker Image for 'ee' version 18.3.0 is ready to be extended:

--> oracle/database:18.3.0-ee
Build completed in 1621 seconds.
■5.データベース作成
[oracle@Gretsch dockerfiles]$ sudo mkdir -p /mnt/oradata
[sudo] oracle のパスワード:
[oracle@Gretsch dockerfiles]$ sudo chmod -R 777 /mnt/
$ docker run --name ora1830 -p 1521:1521 -p 5500:5500 -v /mnt/oradata:/opt/oracle/oradata -e ORACLE_SID=ora1830 -e ORACLE_PDB=pdb1 oracle/database:18.3.0-ee
ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: ZCqw5kkI0YA=1
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 24-JUL-2018 14:11:56
Copyright (c) 1991, 2018, Oracle. All rights reserved.
Starting /opt/oracle/product/18c/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/2b82d37c1875/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 24-JUL-2018 14:11:56
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/2b82d37c1875/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/ora1830.
Database Information:
Global Database Name:ora1830
System Identifier(SID):ora1830
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ora1830/ora1830.log" for further details.
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jul 24 14:26:40 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL>
System altered.
SQL>
Pluggable database altered.
SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
ls: cannot access /opt/oracle/oradata/ora1830/*/: No such file or directory
The Oracle base remains unchanged with value /opt/oracle
#####################################
########### E R R O R ###############
DATABASE SETUP WAS NOT SUCCESSFUL!
Please check output for further info!
########### E R R O R ###############
#####################################
The following output is now a tail of the alert.log:
Completed: alter pluggable database pdb1 open
2018-07-24T14:26:38.030755+00:00
PDB1(3):CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/opt/oracle/oradata/ORA1830/pdb1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
PDB1(3):Completed: CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/opt/oracle/oradata/ORA1830/pdb1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
PDB1(3):ALTER DATABASE DEFAULT TABLESPACE "USERS"
PDB1(3):Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
2018-07-24T14:26:40.444801+00:00
ALTER SYSTEM SET control_files='/opt/oracle/oradata/ora1830/control01.ctl' SCOPE=SPFILE;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE
Completed: ALTER PLUGGABLE DATABASE pdb1 SAVE STATE
2018-07-24T15:06:14.267626+00:00
Resize operation completed for file# 3, old size 522240K, new size 532480K
■6.インスタンスへの接続確認
[oracle@Gretsch ~]$ docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
2b82d37c1875 oracle/database:18.3.0-ee "/bin/sh -c 'exec $O…" 7 hours ago Up 7 hours (unhealthy) 0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp ora1830
3eb0624a21e9 oracle/database:12.1.0.2-ee "/bin/sh -c 'exec $O…" 2 months ago Exited (143) 2 months ago ora12102
b4eb3c10ed7d oracle/database:12.2.0.1-ee "/bin/sh -c 'exec $O…" 2 months ago Exited (143) 2 weeks ago ora12201
02c6716c60e9 hello-world "/hello" 2 months ago Exited (0) 2 months ago boring_heisenberg
[oracle@Gretsch ~]$ docker exec -it ora1830 /bin/bash
[oracle@2b82d37c1875 ~]$ sqlplus /nolog
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jul 24 21:08:33 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
----------------    ------------
ora1830              OPEN
SQL> show pdbs
 CON_ID CON_NAME               OPEN MODE RESTRICTED
---------- ------------------------------ ------------  ----------
             2 PDB$SEED                          READ         ONLY NO
             3 PDB1                                  READ         WRITE NO
SQL>

参考資料

https://github.com/oracle/docker-images
http://www.undercoverlog.com/entry/2018/05/05/120615

Oracle 問合せ変換(Query Transformation)

はじめに

オプティマイザは、多数の問合せ変換を使用して、意味的には同等の新しいSQL文を生成します。
数あるOracleの問合せ変換について主要なものを整理しました。

  • 以下でカテゴリ分けしています。
    • 単純変換:No-brainer Transformations
    • 集合と結合の変換:Set and Join Transformations
    • 集約に関する変換:Aggregation Transformations
    • サブクエリ変換:Subquery Transformations
    • その他の変換:Miscellaneous Transformations
    • スター型変換:Star Transformation

問合せ変換

名称 和名 ヒント句
No-brainer Transformations
Count Transformation カウント変換
Predicate Move-around 述語移動
Set and Join Transformations
Join Elimination 結合排除 eliminate_join/no_eliminate_join
Outer Join to Inner Join 外部結合の内部結合変換 outer_join_to_inner/no_outer_join_to_inner
Full Outer Join to Outer Join 外部結合の完全外部結合変換 full_outer_join_to_outer/no_full_outer_join_to_outer
Semi-Join to Inner Join セミ結合の内部結合変換 semi_to_inner/no_semi_to_inner
Subquery Unnesting サブクエリのネスト解除 unnest/no_unnest
Partial Joins 部分結合 partial_join/no_partial_join
Join Factorization 結合の因数分解 factorize_join/no_factorize_join
Set to Join セット操作の結合 set_to_join/no_set_to_join
Aggregation Transformations
Distinct Aggregation 個別集計 transform_distinct_agg/no_transform_distinct_agg
Distinct Placement Distinct配置の最適化 place_distinct/no_place_distinct
Group by Placement Group by配置の最適化 place_group_by/no_place_group_by
Group by Pushdown Group byのプッシュ parallel gby_pushdown/no_parallel gby_pushdown
Subquery Transformations
Simple View Merging ビューマージ merge/no_merge
Complex View Merging 複雑なビューマージ merge/no_merge
Factored Subquery Materialization 因数分解されたサブクエリの実体化 inline/materialize
Subquery Pushdown サブクエリのプッシュ push_subq/no_push_subq
Join Predicate Pushdown 結合述語のプッシュ push_pred/no_push_pred
Subquery Decorrelation サブクエリ相関解除 decorrelate/no_decorrelate
Subquery Coalescing サブクエリの結合 coalsesce_sq/no_coalsesce_sq
Miscellaneous Transformations
Or Expansion OR拡張 use_concat/no_expand
Materialized View Rewrite クエリーリライト rewrite/no_rewrite
Grouping Sets to Union Expansion UNION拡張のグループ化 expand_gset_to_union/no_expand_gset_to_union
Order by Elimination ORDER BYの排除 eliminate_oby/no_eliminate_oby
Table Expansion 表拡張 expand_table/no_expand_table
Star Transformation
The Distributed Join Filter Problem 分散結合フィルタ no_table_lookup_by_nl

※和名は、勝手に命名しているものもあります。

参考資料

Expert Oracle SQL
Oracle® Database SQLチューニング・ガイド 12cリリース2 (12.2)

Oracle SQL言語~なぜ非効率な実行計画が選択され、SQLが性能劣化するのか~

概要

統計情報の必要性について自分なりに整理してみました。
なぜ非効率な実行計画が選択され、SQL性能が悪くなるのかもあわせて考えてみました。
DBAをしているとアプリケーションのご担当者や上層部への報告でこの点に関する説明を求められる機会が多いためまとめておきます。

SQL言語の特徴

コンピュータのプログラミング言語には、手続き型言語と非手続き型に大別されます。
SQLはこの内の非手続き型言語にカテゴリされます。

非手続き型言語には、さらに関数型言語/論理型言語/問合わせ言語があり、SQLはこの内の問合わせ言語にカテゴライズされます。

カテゴリ 主な言語 説明
手続き型言語 C言語,Java,Python,Perl,Ruby 処理の方法を記述
非手続き型言語 Lisp,SQL 処理の内容を記述

~SQL実践入門(引用)~

権限移譲の功罪
この態度の違いは良いとか悪いとかいうものではなく、言語の設計思想の違いです。
C言語、JavaからRubyに至るまで、手続き型を基礎する言語においては、ユーザがデータアクセスのための手段(How)を責任を持って記述することが前提です。
他方、手続き型であるRDBは、その仕事をユーザからシステム側に移管しました。
その結果、ユーザのすることは対象(What)の記述だけに限定されたのです。

~SQLパフォーマンス詳解(引用)~

開発者はインデックスを知る必要がある

SQLのクエリは、データを要求する英語の文として読むことができます。
通常SQLを書く時には、データベースの内部動作やストレージシステム(ディスクやファイルなど)に関する知識は必要とされません。
データベースに対して、どの ファイルを開き、どのように要求する行を見つけるかを指示する必要はないわけです。
多くの開発者は、何年もSQLを触っているにもかかわらず、データベースの内部で行われている処理についてはほとんど知らないでしょう。

何が欲しいのかと、どのようにそれを取得するかという関連性を分離することは、SQLにおいては非常に有効に機能しています。
しかし、それでも完璧ではありません。
パフォーマンスの問題にたどり着く時、このような抽象化は逆効果になります。
前述の通り、SQL文を書く人は、どのようにデータベースが文を実行するかを知らないからです。
つまり、SQL文を書いた人はクエリの実行が遅くても責任を持てないのです。
しかし、経験的には逆のことが言えるでしょう。つまり、パフォーマンス上の問題を避けるためには、開発者はデータベースについて多少なりとも知っておく必要があるということです。

つまり、SQLは条件(What)のみを記述し、どのように(How)データを戻すかは記述しない、という特性があるということです。
多くの手続き型言語では、for文・while文・if文などの繰り返し処理や条件分岐をプログラマ自身が記述しますが、SQL言語ではそのようなアルゴリズムを記述する必要がない点が特徴として挙げられます。
SQLで記述しないHow(どのようにデータを抽出するかのアルゴリズム決定・制御)について、リレーショナルデータベースではクエリ評価エンジンと呼ばれる機能が担います。Oracleでは、この部分を「CBO(Cost Base Optimizer)」と呼ばれる機能が実施しています。
CBOがアルゴリズムを組み立てる際のインプットとなる情報には、「統計情報」やSQLテキストがあります。
CBOが決定したアルゴリズム(どのようにデータを取ってくるか)は「実行計画」と呼ばれます。
CBOが決定するHow部分(データ取得のためのアルゴリズム)は、常に最良のものが選択されるわけではなく、予測として決定されるため、非効率な実行計画が選択されてパフォーマンス劣化を招きます。
CBOの予測精度を高めるためにはインプット情報の精度を高める、特に統計情報の精度を高い状態に保つことが肝要です。
※それでも非効率な実行計画が選択されることはありますが。。

SQLの成り立ちや歴史については以下が参考になります

Pro Oracle SQL, Second Edition
CHAPTER 1: Core SQL
The SQL Language

参考資料

The Relational Model for Database Management: Version 2
データベース実践講義 ―エンジニアのためのリレーショナル理論
プログラマのためのSQL 第4版
SQLパフォーマンス詳解
アート・オブ・SQL ―パフォーマンスを引き出すSQLプログラミング手法
SQL and Relational Theory, 3rd Edition
SQL実践入門
達人に学ぶSQL徹底指南書
Troubleshooting Oracle Performance, Second Edition
Pro Oracle SQL, Second Edition 

Oracle EVENT 10053(CBOトレース)の出力内容について

概要

10053のトレースファイルの内容はあまり文書化されておらず、
各リリース毎に変更されています。
今回は、バージョン12.2の環境で取得したトレース内容を確認しています。

出力の概要

トレースには以下のようなセクションが表示されています。

セクション 説明
QUERY BLOCK 問合せに含まれる表の問合せブロックおよびオブジェクト識別子(DBA_OBJECTS.OBJECT_ID)
Predicate Move-Around (PM) 述語移動を使うか否かの詳細
Automatic degree of parallelism (ADOP) 自動並列度に関する記述(11.2~)
Current SQL Statement for this session (sql_id=XXXXXXX) 解析したSQL文の表示
Legend トレースの中で使用されている略語の説明。
「Predicate Move-Around (PM)」を確認する際に使用
PARAMETERS USED BY THE OPTIMIZER オプティマイザの動作に影響を与えるパラメータ(SQL解析時点)の値のリスト
・PARAMETERS WITH ALTERED VALUES:変更された値
・PARAMETERS WITH DEFAULT VALUES:デフォルト値
・Bug Fix Control Environment:
   _fix_controlパラメータで制御可能なBug Fixの状況
・PARAMETERS IN OPT_PARAM HINT:使用されたヒント
Column Usage Monitoring is クエリテーブルの列の使用情報
Query transformations (QT) オプティマイザによって考慮されたクエリ変換の情報
・Final query after transformations:問合せ変換された後のSQL文
Peeked values of the binds in SQL statement 実行計画を生成するために確認したバインド変数に関する情報
QUERY BLOCK TEXT 解析されたSQLテキスト
SYSTEM STATISTICS INFORMATION システム統計に関する情報
BASE STATISTICAL INFORMATION SQLで参照しているテーブル・カラム・インデックスの統計情報を出力
SINGLE TABLE ACCESS PATH SQLで参照している各表へのアクセス・パスとして選択可能なものをコスト計算し、最もコストの低いアクセス・パスを選択
OPTIMIZER STATISTICS AND COMPUTATIONS
GENERAL PLANS
結合順序を変更しながら、可能な結合操作ごとにそれぞれの結合順序を適用した時のコストを計算
・Outer table:外部表
・Inner table:内部表
・Final cost:最終的な最適化されたプランのコスト
・Best join order:選択された結合方法
Plan Table(Explain Plan Dump) 解析の結果として最終的に選択された実行計画とPredicate Information
Content of other_xml column クエリのアウトラインヒントの情報
Optimizer state dump 最後に再度パラメータ一覧を表示

参考資料

Cost-Based Oracle Fundamentals
Secrets of the Oracle Database
Expert Oracle Practices
Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition, Second Edition
Expert Oracle RAC 12c
http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%2010053%20Event.pdf

Oracle CBOトレース(10053)の取得

概要

CBOトレースの取得方法について記載します。
event 10053は、CBO(Cost Base Optimizer)の動作をトレースするイベントです。
CBOがどのような計算を行い、最終的にその実行計画を選択したのかを調査する際に有用です。
主に、CBOが選択した実行計画が最適なものでないと考えられる場合の調査で使用することが多いです。

取得方法

10053トレースの取得方法は、大きく以下の3点があります。

   1.SQL*Plusから調査対象のSQLを実行
   2.共有プールに解析済みのカーソル情報より Optimizer のトレース
      (event 10053 トレース相当)の情報を取得する方法(※11.2~)
   3.SQLTXPLAINの使用(※別途インストール要)

取得例

1.
connect ユーザー名/パスワード
SQL> alter session set max_dump_file_size=unlimited;
SQL> alter session set tracefile_identifier=’MY_10053_TRACE’;
SQL> alter session set events ‘10053 trace name context forever, level 1’;
  <調査対象のSQL実行>
SQL> alter session set events ‘10053 trace name context off’;
SQL> exit


tracefile_identifierは必須ではありません。
トレース・ファイルに独自の名前を付加することで出力したトレースファイルを容易に識別するために実行しています。

2.
<解析対象のSQL実行>
<共有プールから解析対象SQLのSQL_IDの確認>
DBMS_SQLDIAG.DUMP_TRACE の実行
SQL> BEGIN
            DBMS_SQLDIAG.DUMP_TRACE(
              P_SQL_ID => '&P_SQL_ID',
              P_CHILD_NUMBER => 0,
              P_COMPONENT => 'Compiler',
              P_FILE_ID => 'OPT_TRACE');
         END;
         /
  p_sql_idに値を入力してください: 0s4rmy40jw5dp ← 解析対象の SQL_ID を入力
  旧 3: P_SQL_ID => '&P_SQL_ID',
  新 3: P_SQL_ID => '0s4rmy40jw5dp',

上記実行後、<diagnostic_dest>/diag/rdbms/<db_unique_name>/<SID>/trace/配下に
「<SID>_ora_<PID>_<P_FILE_IDの識別文字列>.trc」という名称のトレースファイルが生成される

3.
標準機能ではないため今回は割愛します。

取得に際しての注意点

10053トレースは、SQLのハードパースが実行されたタイミングで出力される情報のためハードパースをしなければ有効な情報が出力されません。意図的にハードパースさせるために下記のいずれかの方法でハードパースが実行されるようにしてください。

  1.適当なコメントを追加し、別カーソルとする
  2.情報取得前に共有プールのフラッシュを実行
     コマンド:
     SQL> alter system flush shared_pool;
     但し、共有プールのフラッシュを行った場合、他のセッションにも影響を与えるので本番環境では実施すべきではないと考えます。

実行例(取得方法1)

SQL> conn hr/hr@pdb1;
Connected.
SQL>
SQL>
SQL> set line 2000
SQL> set pages 2000
SQL> select * from tab;
TNAME                    TABTYPE CLUSTERID
------------------------ ------- ----------
COUNTRIES                TABLE
DEPARTMENTS              TABLE
EMPLOYEES                TABLE
EMP_DETAILS_VIEW         VIEW
JOBS                     TABLE
JOB_HISTORY              TABLE
LOCATIONS                TABLE
REGIONS                  TABLE
8 rows selected.
SQL> alter session set max_dump_file_size=unlimited;
Session altered.
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL> select
  2      /*+ hard parse */
  3      first_name,
  4      last_name,
  5      hire_Date
  6  from
  7      employees
  8 where
  9      hire_date in(
  10         select
  11             hire_date
  12         from
  13             employees
  14         where
  15             department_id = 30
  16      )
  17   ;
FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
Den                  Raphaely                  07-DEC-02
Alexander            Khoo                      18-MAY-03
Shelli               Baida                     24-DEC-05
Sigal                Tobias                    24-JUL-05
Guy                  Himuro                    15-NOV-06
Karen                Colmenares                10-AUG-07
6 rows selected.
 
SQL> alter session set events '10053 trace name context off';
 
Session altered.
 
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
$ cd /opt/oracle/diag/rdbms/ora12201/ora12201/trace
$ ls -ltr
total 35436
-rw-r-----. 1 oracle oinstall 945 May 18 12:29 ora12201_vktm_446.trm
-rw-r-----. 1 oracle oinstall 1534 May 18 12:29 ora12201_vktm_446.trc
    :
  (中略)
    :
-rw-r-----. 1 oracle oinstall   1147 May 28 12:00 ora12201_m001_5225.trc
-rw-r-----. 1 oracle oinstall    925 May 28 13:00 ora12201_m001_7691.trm
-rw-r-----. 1 oracle oinstall   1147 May 28 13:00 ora12201_m001_7691.trc
-rw-r-----. 1 oracle oinstall  70814 May 28 13:43 ora12201_ora_9374.trm
-rw-r-----. 1 oracle oinstall 154784 May 28 13:43 ora12201_ora_9374.trc <--★出力したトレースファイル

参考資料

Cost-Based Oracle Fundamentals
Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition, Second Edition
http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%2010053%20Event.pdf

Oracle アクセスパス

概要

Oracleのアクセス・パスについてまとめた内容を記載します。
アクセス・パスは、データを取り出す方法のことです。

アクセス・パス一覧

分類 実行計画(Operation) 説明
Table TABLE ACCESS FULL 全表走査。HWM(High Water Mark)までの全てのブロックにアクセスし、全行を取り出す。
単一のI/O呼び出しで複数のブロックが要求される。単一のI/Oでの読込みブロック数は最大でdb_file_multiblock_read_countパラメータの値。
TABLE ACCESS BY BY ROWID RANGE 指定された範囲のROWIDを使用してテーブルから行を取得。
TABLE ACCESS BY USER ROWID (ROWID を条件に指定した場合)ROWID を使用して表からの行を取得する。
TABLE ACCESS BY INDEX ROWID 索引からのROWIDを使用して表から行を取り出す。※索引は非パーティション
TABLE ACCESS BY GLOBAL INDEX ROWID 非ローカル・パーティション索引からのROWIDを使用して表から行を取り出す。
TABLE ACCESS BY LOCAL INDEX ROWID ローカルにパーティション化された索引からのROWIDを使用して表から行を取り出す。
TABLE ACCESS BY INDEX ROWID BATCHED 索引から取得したROWIDを同じデータ・ブロックごとにまとめてアクセスする。
TABLE ACCESS CLUSTER インデックスクラスタキーに基づいてクラスタ化されたテーブルの行にアクセスする。
TABLE ACCESS HASH ハッシュされたクラスタキーに基づいてクラスタ化されたテーブルの行にアクセスする
TABLE ACCESS SAMPLE テーブル内のブロックのサンプルをスキャンする。
テーブルデータをランダムにサンプルスキャンする。
TABLE ACCESS SAMPLE BY ROWID RANGE 指定されたROWID範囲を使用して、テーブル内のブロックのサンプルのスキャンする。
TABLE ACCESS INMEMORY FULL (インメモリ―列ストアが有効になっている場合)メモリ上のテーブルを全表走査する。
Index INDEX FULL SCAN 索引から全ての ROWID取得。表データをフルスキャンするのではなく、ソート済みの索引をフルスキャンするため、ソート処理を回避できる。
INDEX FULL SCAN (MIN/MAX) インデックス内の最初または最後のエントリを返却する。
最小値または最大値に相当する値を発見したところでリーフのスキャンを停止する。
INDEX FULL SCAN DESCENDING 降順で全索引スキャンする。
INDEX RANGE SCAN リーフ・ブロックをスキャンして条件に該当する複数エントリを返却する。
索引が定義された列に対して範囲条件(BETWEEN,<,>)を指定した場合と非一意索引の場合の等価条件でも選択される。
索引には索引列がソートして格納されているため、索引を使用してORDER BY句を満たせる場合はソート処理を回避可能。
INDEX RANGE SCAN DESCENDING INDEX RANGE SCAN を索引値の降順にスキャンする。
INDEX RANGE SCAN (MIN/MAX) インデックス範囲の最初または最後のエントリを返します。
最小値または最大値に相当する値を発見したところでリーフのスキャンを停止する。
INDEX SKIP SCAN 複合索引の第1列目に対する条件指定がなく、2列目以降の列に対して条件があった場合に
複合索引を使用して条件に該当する行を選択するアクセス方法
INDEX SKIP SCAN DESCENDING INDEX SKIP SCAN を索引値の降順にスキャンする。
INDEX UNIQUE SCAN 条件に該当する1エントリを返す。主キーor一意索引の付いた列に対して等価条件を使用している場合のみ選択される可能性がある。
INDEX FAST FULL SCAN INDEX FULL SCANのマルチブロックリード版。索引ブロックを先頭エクステントから格納順にアクセスする方法のため、戻される結果は索引キーの順にソートされていない。
INDEX SAMPLE FAST FULL SCAN 行のサンプルに対してインデックス高速フルスキャンを実行する。
HASH JOIN(INDEX JOIN) 索引が結合されている表から必要な列のみが索引に存在する列である場合にのみ可能。
AND-EQUAL 複数の ROWID のセットを受け取り重複をなくし共通する ROWID を戻す。
BITMAP INDEX SINGLE VALUE 単一のキー値のビットマップ内の行を返す。
BITMAP INDEX RANGE SCAN 一連のキー値のビットマップ内の行を返す。
BITMAP INDEX FULL SCAN 可能なすべてのキー値のビットマップ内の行を返す。
BITMAP INDEX FAST FULL SCAN 可能なすべてのキー値のビットマップ内の行を返す。
BITMAP CONSTRUCTION ビットマップインデックスの作成時にビットマップを構築する。
BITMAP COMPACTION ビットマップインデックスの作成中にビットマップを圧縮する。
BITMAP CONVERSION FROM ROWIDS ROWIDのセットをビットマップのセットに変換する。
BITMAP CONVERSION TO ROWIDS ビットマップをキー値に変換し、ROWIDに変換する。
BITMAP AND WHERE句に複数の条件をANDで指定した場合、いくつかのビットマップ索引のビットマップ・エントリをマージする
BITMAP OR 複数の条件をORて指定した場合に実行される。
BITMAP MINUS 特定のビットマップからほかのビットマップで1が立っているビットを排除する処理
BITMAP MERGE BITMAP AND/OR/MINUSなどのオペレーションを実行する前に、RANGE SCANの結果得られた複数のビットマップをマージ
BITMAP CONVERSION COUNT 1つまたは複数のビットマップの行数のカウントを返します。
BITMAP KEY ITERATION ビットマップを反復処理します。スター・トランザクションで使用

参考資料

Expert Oracle SQL
Pro Oracle SQL, Second Edition
Troubleshooting Oracle Performance, Second Edition
Expert Oracle Indexing and Access Paths: Maximum Performance for Your Database
https://docs.oracle.com/cd/E82638_01/TGSQL/optimizer-access-paths.htm#GUID-00711237-35D3-4CFC-A234-59B3EC53DCD1
8 オプティマイザのアクセス・パス

Oracle 実行計画の確認方法(DBMS_XPLAN.DISPLAY_CURSOR)

概要

SQLパフォーマンス調査で使用するDBMS_XPLANについてのまとめを記載します。

DBMS_XPLAN.DISPLAY_CURSORについて:
過去にDatabaseで実行されたSQLの実行計画を確認することが可能です。

また、この機能を使用することでSQL文の実行計画とそのSQLを実行した際の実行統計の情報を併記して表示することが可能です。
そのため、この機能によって、CBO(コストベース・オプティマイザ)の見積もりミスの有無やメモリ不足等の事象で作業効率が悪くなっていないかを確認したりすることが可能になります。

特徴

- 過去に実行されたSQL文の共有カーソルの情報がライブラリ・キャッシュに存在している場合に該当SQLの実行計画を確認することが可能です。
  → ライブラリ・キャッシュからage-outされていた場合は確認することが出来ません。
- 既に実行されキャッシュされている情報を検索するため、実行計画を確認するためにSQL文を再度解析する必要がなく、低コストで実行できます。
- 実行計画と併せて実行統計も表示することが可能なため、Estimate(見積)とActual(実測)の乖離の確認が容易です。

使用の前提条件

DISPLAY_CURSOR機能を使用する場合、ユーザーに下記ビューに対するSELECTまたはREAD権限が必要です。
権限がない場合は、エラー・メッセージが表示されます。
※DBMS_XPLANは内部的にこれらのビューに対する検索を実行し、情報を表示しています。

  V$SQL_PLAN_STATISTICS_ALL
  V$SQL
  V$SQL_PLAN

パラメータ

DISPLAY_CURSORファンクションのパラメータは下記です。
   sql_id:カーソル・キャッシュ内のSQL文のSQL_IDを指定します。
              NULLの場合、セッションで最後に実行されたカーソルが表示されます。
   cursor_child_no:表示するカーソルの子番号を指定します
                              ※NULLの場合、指定のsql_idパラメータに一致するすべてのカーソルの実行計画を表示
   format:計画の詳細レベルを制御します。

format句の指定

BASIC:計画の最小限の情報として、操作ID、操作名および操作オプションを表示します。
TYPICAL:これがデフォルトです。計画に関する最も一般的な情報
                 (操作ID、操作名、操作オプション、行数、バイト数およびオプティマイザ・コスト)が表示されます。
SERIAL:TYPICALと同じですが、計画がパラレルで実行される場合でもパラレル情報は表示されません。
ALL:TYPICALレベルで表示される情報に加えて、
         追加情報(PROJECTION、ALIAS、および分散処理が行われる場合はREMOTE SQLに関する情報)が表示されます。

ADVANCEDパラメータについて

マニュアルには記載がありませんが、上記以外にformat句に「ADVANCED」を指定することが可能です。
このオプションによって、OUTLINE(ヒント句)の情報などの追加情報が表示できます。

formatパラメータで指定可能な修飾子
Value Description
adaptive サブプランの表示を制御します。この修飾子は、バージョン12.1以降でのみ使用できます。
alias クエリブロック名とオブジェクトエイリアスを含むセクションの表示を制御します。
bytes 実行計画テーブルのバイト列の表示を制御します。
cost 実行計画テーブルのCost列の表示を制御します。
note ノートを含むセクションの表示を制御します。
outline アウトラインを含むセクションの表示を制御します。
parallel 並列処理情報、具体的には実行計画表のTQ、IN-OUT、およびPQ Distrib列の表示を制御します。
partition パーティション化情報、特に実行計画テーブルのPstart列とPstop列の表示を制御します。
peeked_binds 表示されたバインド変数を含むセクションの表示を制御します。
predicate フィルター、アクセス、およびストレージの述部を含むセクションの表示を制御します。
projection 列投影情報を含むセクションの表示を制御します。
remote リモートで実行されるSQL文の表示を制御します。
report レポートモードのアクティブ化を制御します。
有効にすると、適応および再最適化された実行計画に関する追加情報が表示されます。
この修飾子は、バージョン12.1以降でのみ使用できます。
rows 実行計画テーブルの行列の表示を制御します。

   format句に指定可能なパラメータの詳細については以下のマニュアルを参考にしてください。
  https://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_xplan.htm#sthref14602
  Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 11g リリース2(11.2)
  https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_xplan.htm#i998364
  Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1 (12.1)
  https://docs.oracle.com/cd/E82638_01/ARPLS/DBMS_XPLAN.htm#GUID-BAD480AA-351A-48FE-A8E7-F0D8EF643EBF
  Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース2 (12.2)

  詳細については「$ORACLE_HOME/rdbms/admin/dbmsxpln.sql」でも確認できます。

また、すべてのレベルは、データを削除するマイナス記号または追加するオプションのプラス記号とともに、詳細な制御を使用してカスタマイズできます。
例) 「ADVANCED -COST」や「BASIC +PEEKED_BINDS」

実行統計を併記する方法

実行統計の収集するには、SQL文の実行時に実行統計を取得する必要がありますが、SQL実行統計を収集する場合は、以下の3つの条件のいずれかを満たしている必要があります。

  ・STATISTICS_LEVELパラメータをALLの状態でSQL文を実行する
  ・SQL文にGATHER_PLAN_STATISTICSヒントを指定して実行する
  ・SQLトレースを有効にしてSQL文を実行する

上記にて統計情報が収集されている場合には、DBMS_XPLAN.DISPLAY_CURSORにてformat句に以下のキーワードを指定します。

  IOSTATS : I/Oに関する実行統計を表示
  MEMSTATS : メモリに関する実行統計を表示
  ALLSTATS : IOSTATSとMEMSTATSの両方を指定
  LAST : このオプションが指定されていると特定のSQL文を最後に実行した際の1回分の統計を表示。
              指定しない場合は同一SQL文の全実行を集計表示。
              (注:パラレルクエリの場合にLASTを指定する場合は注意してください。Doc ID 1757657.1参照)

format句の書式レベルと制御
パラメータ alias bytes cost note predicate parallel partition projection outlines peeked_binds remote rows
Column/Section S C C S S C C S S S S C
BASIC                        
null              
TYPICAL        
SERIAL            
ALL  
ADVANCED

現時点で、DBMS_XPLAN.DISPLAY_CURSORから最大の表示を得るには、formatパラメータに 'ADVANCED ALLSTATS LAST ADAPTIVE'を指定する必要があります。

表示内容

表示される実行計画の各ステップには、次のような項目が併記されます。
※SQLの実行内容とformat句で指定したパラメータに応じて下記項目から必要な情報が表示されます。
なお、この情報は、V$SQL_PLAN_STATISTICS_ALLから取得しています。

▽実行計画を含むテーブルの列
Column Description
Basics(Always Available)
id 実行計画内の各操作(行)の識別子。番号の前にアスタリスクが付いている場合は、その行の述部情報が後で使用できることを意味します。
Operation 実行される操作。これは、行ソース操作とも呼ばれます。
Name 操作が実行されるオブジェクト。
Query Optimizer Estimations
Rows and E-Rows 操作によって戻された推定行数。
Bytes and E-Bytes 操作によって戻されたデータの見積もり量。
TempSpc and E-Temp 操作に必要な一時スペースの推定量(バイト単位)。
Cost (%CPU) 操作の推定コスト。 CPUコストの割合はカッコで示しています。この値は実行計画によって累積されます。
つまり、親オペレーションのコストには、子オペレーションのコストが含まれています。
Time and E-Time 操作を実行するのに必要な推定時間(HH:MM:SS)。
Partitioning
Pstart アクセスされる最初のパーティションの番号。その番号が解析時にわからない場合は、KEYまたはINVALIDのいずれかです。
KEYは、実行フェーズ中に最初のパーティションが決定されたときに使用されます。
Pstop アクセスされる最後のパーティションの番号。その番号が解析時にわからない場合は、KEYまたはINVALIDのいずれかです。
KEYは、実行フェーズ中に最後のパーティションが決定されたときに使用されます。
Parallel and Distributed Processing
Inst 分散処理の場合、操作で使用されるデータベース・リンクの名前。
TQ 並列処理の場合、スレーブプロセス間の通信に使用されるテーブルキュー。
IN-OUT 並列または分散操作の関係。
PQ Distrib 並列処理の場合、プロデューサがデータをコンシューマに送信するために使用されるディストリビューション。
Runtime Statistics*
Starts 特定の操作が実行された回数。特別な場合には、この統計情報は、特定のメモリ構造にアクセスした回数を示します
A-Rows 操作によって戻される実際の行数。
A-Time 操作の実行に費やされた実際の時間(HH:MM:SS.FF)。
I/O Statistics*
Buffers 実行中に実行された論理読み取りの数。
Reads 実行中に実行された物理読み込みの数。
Writes 実行中に実行された物理書き込みの数。
Memory Utilization Statistics
Omem 最適な実行に必要なメモリーの推定量(バイト単位)。
1Mem 1パス実行に必要なメモリーの推定量(バイト単位)。
O/1/M(*2) 実行が最適/ワンパス/マルチパスモードで実行された回数。
Used-Mem(*1) 最後の実行中に操作で使用されたメモリー量(バイト単位)。
Used-Tmp(*1) 最後の実行中に操作で使用された一時スペースの量(キロバイト単位)。他のメモリー使用率列と一致するように、この値に1,024を掛けなければなりません(たとえば、32Kは32MBを意味します)。
Max-Tmp(*2) 操作で使用される一時スペースの最大量(キロバイト単位)。この値は、他のメモリ使用率列と一致するように、1,024を掛けなければなりません(たとえば、32Kは32MBを意味します)。

  *実行統計が有効になっている場合に出力される項目

  赤字:Estimate  青字:Actual(実行統計)

  (*1)format句にLAST指定をした場合に表示される項目
  (*2)format句にLAST指定をしなかった場合に表示される項目

  (補足)
  Optimal:そのステップ(例えばSORT)のすべての作業をメモリ内で実行すること
  1-pass:中間データを一段階ディスクに退避してマージ作業を行うこと
  Multi-pass:中間データを二段階以上ディスクに退避してマージ作業を行うこと

▽セクションの表示内容(実行計画以外の部分)

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

  1 - SEL$1
  3 - SEL$1 / T2@SEL$1
  4 - SEL$1 / T2@SEL$1
  5 - SEL$1 / T1@SEL$1

このセクションでは、クエリブロック名とオブジェクトエイリアスを示します。
実行計画の各操作では、関連するクエリブロックと、オプションで実行されるオブジェクトが表示されます。
この情報は、SQL文が同じ表を複数回参照する場合には不可欠です。

2.
Outline Data
-------------

/*+
     BEGIN_OUTLINE_DATA
     IGNORE_OPTIM_EMBEDDED_HINTS
     OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
     DB_VERSION('11.2.0.4')
     ALL_ROWS
     OUTLINE_LEAF(@"SEL$1")
     INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."ID"))
     FULL(@"SEL$1" "T1"@"SEL$1")
     LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
     USE_HASH(@"SEL$1" "T1"@"SEL$1")
     END_OUTLINE_DATA
*/

その特定の実行計画を再現するのに十分な、アウトラインと呼ばれる一連のヒントを示します。
アウトラインには必ずしもすべての必要なヒントが含まれているとは限りません。

3.
Peeked Binds (identified by position):
--------------------------------------

   1 - :T1_ID (NUMBER): 6
   2 - :T2_ID_MIN (NUMBER): 6
   3 - :T2_ID_MAX (NUMBER): 19

問合せオプティマイザがバインド変数を利用する場合にのみ表示されます。
上記の通り、各バインド変数のデータ型と値が表示されます。

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

   1 - filter(:T2_ID_MIN<=:T2_ID_MAX)
   2 - access("T1"."N"="T2"."N")
   4 - access("T2"."ID">=:T2_ID_MIN AND "T2"."ID"<=:T2_ID_MAX)
   5 - filter("T1"."ID">:T1_ID)

どの述部が適用されるかを示します。
それぞれについて、(行)と方法(アクセス、フィルタまたはストレージ)が適用される場所が示されます。
アクセス述語は、効率的なアクセス構造を利用して行を特定するために使用されますが、フィルタ述語は行をすでに格納している構造体から抽出されています。

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

   1 - "T2"."N"[NUMBER,22], "T2"."ID"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000]
   2 - (#keys=1) "T2"."N"[NUMBER,22], "T2"."ID"[NUMBER,22],"T2"."PAD"[VARCHAR2,1000]
   3 - "T2"."ID"[NUMBER,22], "T2"."N"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000]
   4 - "T2".ROWID[ROWID,10], "T2"."ID"[NUMBER,22]
   5 - "T1"."N"[NUMBER,22]

各操作が実行されるときにどの列が出力として返されるかを示します。

6.
Note
-----
   - dynamic sampling used for this statement (level=2)

最後に、最適化フェーズ、環境、またはSQLステートメント自体に関する注釈と警告を提供するセクションがあります。
上記例では、問合せオプティマイザがオブジェクト統計を収集するために動的サンプリングを使用したことが通知されます。

実行例

SQL> set linesize 200
SQL> col sql_id for a15
SQL> select sql_id,sql_text from v$sqltext where sql_text like 'select * from emp%';
SQL_ID          SQL_TEXT
--------------- ----------------------------------------------------------------
8wqqr7cg11y7t   select * from emp e, dept d where e.deptno = d.deptno
SQL> set line 2000
SQL> set pages 2000
SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> select * from table(dbms_xplan.display_cursor('&SQLID',null,'advanced'));
Enter value for sqlid: 8wqqr7cg11y7t
old 1: select * from table(dbms_xplan.display_cursor('&SQLID',null,'advanced'))
new 1: select * from table(dbms_xplan.display_cursor('8wqqr7cg11y7t',null,'advanced'))
SQL_ID 8wqqr7cg11y7t, child number 1
-------------------------------------
select * from emp e, dept d where e.deptno = d.deptno
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time         |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |      |       |     6 (100)|              |
|* 1 | HASH JOIN        |      |   14 |  1638 |     6   (0)|     00:00:01 |
|  2 | TABLE ACCESS FULL| DEPT |    4 |   120 |     3   (0)|     00:00:01 |
|  3 | TABLE ACCESS FULL| EMP  |   14 |  1218 |     3   (0)|     00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / D@SEL$1
   3 - SEL$1 / E@SEL$1
Outline Data
-------------
  /*+
       BEGIN_OUTLINE_DATA
       IGNORE_OPTIM_EMBEDDED_HINTS
       OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
       DB_VERSION('12.2.0.1')
       ALL_ROWS
       OUTLINE_LEAF(@"SEL$1")
       FULL(@"SEL$1" "D"@"SEL$1")
       FULL(@"SEL$1" "E"@"SEL$1")
       LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
       USE_HASH(@"SEL$1" "E"@"SEL$1")
       END_OUTLINE_DATA
   */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],
       "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14],
       "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
   2 - (rowset=256) "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14],
       "D"."LOC"[VARCHAR2,13]
   3 - (rowset=256) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
Note
-----
    - dynamic statistics used: dynamic sampling (level=2)

124 rows selected.

※12cR2からtable句の省略可

(これまで) select * from table(dbms_xplan.display_cursor('&SQLID',null,'allstats last'));

(12.2以降) select * from (dbms_xplan.display_cursor('&SQLID',null,'allstats last'));

個人的希望

個人的には以下のサイトのQ&Aにもある通り、「-SQLTEXT」「-QUERY」のようなオプションを設けてPLANから確認が始められる(SQLテキスト部分を除外できる)ようにして欲しい。

https://sqlmaria.com/2017/08/08/using-dbms_xplan-display_cursor-to-examine-execution-plans/

参考資料

Expert Oracle SQL
Troubleshooting Oracle Performance, Second Edition
https://docs.oracle.com/cd/E82638_01/TGSQL/reading-execution-plans.htm#GUID-725CADF8-3803-416F-96EB-BF942F742663
Oracle® Database SQLチューニング・ガイド 12cリリース2 (12.2)
https://sqlmaria.com/2017/08/08/using-dbms_xplan-display_cursor-to-examine-execution-plans/