概要

soracleの実行計画を取得する方法のメモ。

sql plusのインストール方法（mac）

sql plusのダウンロード

https://www.oracle.com/database/technologies/instant-client/macos-intel-x86-downloads.html から以下をダウンロードし、解凍する。解凍したソフトを任意の場所（今回は ~/oracle/instantclient_19_8）に移動する。

Basic Package (ZIP)
- https://download.oracle.com/otn_software/mac/instantclient/198000/instantclient-basic-macos.x64-19.8.0.0.0dbru.zip
Sql*Plus Package (ZIP)
- https://download.oracle.com/otn_software/mac/instantclient/198000/instantclient-sqlplus-macos.x64-19.8.0.0.0dbru.zip

環境変数の設定

echo export PATH=~/oracle/instantclient_19_8:$PATH >> ~/.bash_profile
source ~/.bash_profile

sql plusの実行

sqlplus [user_name]/[password]@[host]:[port]/[service_name]

tnsnames.oraを設定する場合

~/oracle/instantclient_19_8/network/admin/ ディレクトが存在するので、そちらにtnsnames.oraを作成する

~/oracle/instantclient_19_8/network/admin/tnsnames.ora

oracle =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = [host])(PORT = [port]))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = [service_name])
    )
  )

sql plusの実行（tnsnames.oraの設定を利用）

sqlplus [user_name]/[password]@oracle

テストデータの作成

実行計画取得用にテストデータを作成する

create table table_1 (
  no number(10),
  st char(10)
);

create table table_2 (
  no number(10),
  st char(10),
  table_2_no number(10)
);

begin
  for i in 1..100000 loop
    insert into table_1 values(i, dbms_random.string('x', 10));
    insert into table_2 values(i, dbms_random.string('x', 10), dbms_random.value(1, 100000));
  end loop;
commit;
end;

oracle 実行計画の取得

実行計画取得のための設定を行う。特に、linesizeと、tab off を設定することで、コンソールから実行計画等をコピペしてもレイアウトが崩れなくなる

set linesize 200; -- コンソールの一行の長さを指定する
set tab off; -- 空白をスペースで表示するよう設定する
set timing on; --  SQL実行時間を表示する

参考

https://www.shift-the-oracle.com/sqlplus/system-variable/linesize.html

実行計画の取得

SQL実行と同時に実行計画と、統計情報を出力する AUTOTRACE の設定を行う。dbの設定によっては、統計情報を取得するための権限等がない場合もあるので、適宜コマンドを変更する or 権限を設定する。

set autotrace on; -- 実行結果と実行計画、統計情報を表示する

EXPLAIN PLAN 文による実行計画の取得とは異なり DML の処理とフェッチ、データ転送処理も行なわれる(※)ので大量の件数を取得する検索文の場合には注意する。

参考

https://www.shift-the-oracle.com/sqlplus/tutorial/autotrace.html

glogin.sqlとlogin.sql

上記の設定は設定ファイルに記述することで、ログイン時に自動で実行してくれるよう設定が可能。今回は既に存在していた~/oracle/instantclient_19_8/glogin.sqlに追記する形で設定。

~/oracle/instantclient_19_8/glogin.sql

--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--

set linesize 200
set tab off
set timing on
set autotrace on

参考

実行計画を見てみる

SQLを実行してみる

select
  count(*) 
from 
  table_1 t1
inner join 
  table_2 t2 on t1.no = t2.table_1_no
;

実行結果の取得と共に、実行計画および、

-- 実行結果

  COUNT(*)
----------
    100000

-- 実行時間
Elapsed: 00:00:01.38

-- 実行計画
Execution Plan
----------------------------------------------------------
Plan hash value: 3048039173 -- 実行計画のハッシュ値


---------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |    26 |       |   434   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE     |         |     1 |    26 |       |            |          |
|*  2 |   HASH JOIN         |         |   102K|  2597K|  2200K|   434   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TABLE_1 | 89810 |  1140K|       |   102   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TABLE_2 |   102K|  1298K|       |   103   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("T1"."NO"="T2"."TABLE_1_NO")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


-- 統計情報
Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        692  consistent gets
          0  physical reads
          0  redo size
        359  bytes sent via SQL*Net to client
        407  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed