概要
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)
- Sql*Plus Package (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
参考
- https://ameblo.jp/archive-redo-blog/entry-10035622265.html
- https://www.teamxeppet.com/glogin-loginsql/
実行計画を見てみる
- 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