Oracle-SQL-チューニング-オラクルの潜在能力を引き出す実践的チューニングガイド.pdfの
実習用スキーマ、データの用意と実習

show pdbs

alter pluggable database ORCLPDB open;
alter pluggable database ORCLPDB save state;

alter session set container=cdb$root;
alter session set container=ORCLPDB;

set pages 50000

select username from dba_users order by 1;

alter user test12 identified by test12;

alter user hr account unlock;
alter user hr identified by hr;

conn test12/test12@ORCLPDB
conn hr/hr@ORCLPDB

select table_name from user_tables;

REGIONS
COUNTRIES
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY

select count(*) from REGIONS;
select count(*) from COUNTRIES;
select count(*) from LOCATIONS;
select count(*) from DEPARTMENTS;
select count(*) from JOBS;
select count(*) from EMPLOYEES;
select count(*) from JOB_HISTORY;

select index_name from user_indexes;

— オートトレース取得環境をつくる
alter session set container=ORCLPDB;
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
grant plustrace to hr;

conn hr/hr@ORCLPDB
set autotrace on

####################################################################################
# — ダミーデータ作成
# set mark csv on
# select * from EMPLOYEES;
#
# HR.EMP_EMP_ID_PK
#
# set serveroutput on
#
# DECLARE
# n_cnt NUMBER(5) := 0;
# n_num NUMBER(10) :=1009;
#
# BEGIN
# FOR n_cnt in 1..10000 LOOP
# n_num := n_num + 1;
#
# insert into EMPLOYEES
# values(
# n_num,
# ‘Dummy-Firsr’,
# ‘Dummy-Last’,
# ‘E-Mail-‘||TO_CHAR(n_num),
# ‘515.123.8181’,
# TO_DATE(‘20240125′,’yyyymmdd’),
# ‘AC_ACCOUNT’,
# 8300,
# 0.0,
# 205,
# 110
# );
#
# END LOOP;
# END;
# /
#
# commit;
#
# set mark csv on
# select * from EMPLOYEES;
#
# SQL> desc EMPLOYEES
# 名前 NULL? 型
# —————————————– ——– —————————-
# EMPLOYEE_ID NOT NULL NUMBER(6)
# FIRST_NAME VARCHAR2(20)
# LAST_NAME NOT NULL VARCHAR2(25)
# EMAIL NOT NULL VARCHAR2(25)
# PHONE_NUMBER VARCHAR2(20)
# HIRE_DATE NOT NULL DATE
# JOB_ID NOT NULL VARCHAR2(10)
# SALARY NUMBER(8,2)
# COMMISSION_PCT NUMBER(2,2)
# MANAGER_ID NUMBER(6)
# DEPARTMENT_ID NUMBER(4)
#
#
# insert into EMPLOYEES
# values(
# 10800,
# ‘Dummy-Firsr’,
# ‘Dummy-Last’,
# ‘E-Mail-10800’,
# ‘515.123.8181’,
# TO_DATE(‘20240115′,’yyyymmdd’),
# ‘AC_ACCOUNT’,
# 8400,
# 0.0,
# 205,
# 110
# );
#
#
# select * from EMPLOYEES where EMPLOYEE_ID=100;
####################################################################################

set autotrace on
set autotrace on explain
set autotrace on statistics

set autotrace traceonly

drop table smpl_sales;

create table smpl_sales(
sales_id number(10),
parson_id number(5),
sales_date date,
sales_update date,
sales_create date
);

alter table smpl_sales add constraint smpl_sales_pkey primary key (sales_id);

alter table smpl_sales add kbn1 number(1);

create index smpl_sales_idx_1 on smpl_sales(sales_id,parson_id);
create index smpl_sales_idx_2 on smpl_sales(sales_date,sales_update);

create index smpl_sales_idx_3 on smpl_sales(kbn1);
create index smpl_sales_idx_4 on smpl_sales(sales_create);

set lines 200
col Operation for a30
col name for a30

select * from smpl_sales where sales_id = 1 and parson_id=1;
———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| SMPL_SALES | 1 | 32 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SMPL_SALES_PKEY | 1 | | 2 (0)| 00:00:01 |
———————————————————————————————–

select * from smpl_sales where sales_id is not null;
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1000K| 30M| 1409 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| SMPL_SALES | 1000K| 30M| 1409 (1)| 00:00:01 |
——————————————————————————–

select * from smpl_sales where parson_id =1 and sales_id = 1;
※インデックス内の列順が逆でもインデックスが選択される。
———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| SMPL_SALES | 1 | 32 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SMPL_SALES_PKEY | 1 | | 2 (0)| 00:00:01 |
———————————————————————————————–

select * from smpl_sales where parson_id=1;
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 32 | 1408 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SMPL_SALES | 1 | 32 | 1408 (1)| 00:00:01 |
——————————————————————————–

select * from smpl_sales where sales_date = sysdate and sales_update=sysdate;
——————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 32 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SMPL_SALES | 1 | 32 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SMPL_SALES_IDX_2 | 1 | | 3 (0)| 00:00:01 |
——————————————————————————————————–

select * from smpl_sales where sales_update=sysdate;
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 32 | 1417 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SMPL_SALES | 1 | 32 | 1417 (2)| 00:00:01 |
——————————————————————————–

select * from smpl_sales where kbn1=1 and sales_create = sysdate;
——————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SMPL_SALES | 1 | 32 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SMPL_SALES_IDX_3 | 1 | | 1 (0)| 00:00:01 |
——————————————————————————————————–

select * from smpl_sales where sales_create = sysdate and kbn1=1;
——————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SMPL_SALES | 1 | 32 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SMPL_SALES_IDX_3 | 1 | | 1 (0)| 00:00:01 |
——————————————————————————————————–

select * from smpl_sales where parson_id = 1 and sales_update=sysdate;
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 32 | 1409 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SMPL_SALES | 1 | 32 | 1409 (1)| 00:00:01 |
——————————————————————————–

set serveroutput on

DECLARE
n_cnt NUMBER(5) := 0;
n_id NUMBER(3) := 0;

BEGIN
FOR n_cnt in 1..1000000 LOOP
n_id := 100;

insert into smpl_sales
values(
n_cnt,
n_id,
sysdate,
sysdate,
sysdate
);
END LOOP;

END;
/

commit;

insert into smpl_sales values(3001,200,sysdate,sysdate,sysdate);

exec dbms_stats.gather_table_stats(‘hr’,’smpl_sales’);

select * from smpl_sales;

SALES_ID,PARSON_ID

select * from smpl_sales where SALES_ID=1001;
select * from smpl_sales where SALES_ID!=1001;

select * from smpl_sales where
SALES_ID=1001;
“———————————————————————————————–”
“| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |”
“———————————————————————————————–”
“| 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 |”
“| 1 | TABLE ACCESS BY INDEX ROWID| SMPL_SALES | 1 | 32 | 3 (0)| 00:00:01 |”
“|* 2 | INDEX UNIQUE SCAN | SMPL_SALES_PKEY | 1 | | 2 (0)| 00:00:01 |”
“———————————————————————————————–”

select * from smpl_sales where
SALES_ID=1001
AND SALES_ID=1002;
“————————————————————————————————”
“| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |”
“————————————————————————————————”
“| 0 | SELECT STATEMENT | | 1 | 32 | 0 (0)| |”
“|* 1 | FILTER | | | | | |”
“| 2 | TABLE ACCESS BY INDEX ROWID| SMPL_SALES | 1 | 32 | 3 (0)| 00:00:01 |”
“|* 3 | INDEX UNIQUE SCAN | SMPL_SALES_PKEY | 1 | | 2 (0)| 00:00:01 |”
“————————————————————————————————”

select * from smpl_sales where
SALES_ID=1001
OR SALES_ID=1002;
“————————————————————————————————”
“| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |”
“————————————————————————————————”
“| 0 | SELECT STATEMENT | | 2 | 64 | 5 (0)| 00:00:01 |”
“| 1 | INLIST ITERATOR | | | | | |”
“| 2 | TABLE ACCESS BY INDEX ROWID| SMPL_SALES | 2 | 64 | 5 (0)| 00:00:01 |”
“|* 3 | INDEX UNIQUE SCAN | SMPL_SALES_PKEY | 2 | | 4 (0)| 00:00:01 |”
“————————————————————————————————”

select * from smpl_sales where
SALES_ID!=1001
OR SALES_ID=1002;
“——————————————————————————–”
“| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |”
“——————————————————————————–”
“| 0 | SELECT STATEMENT | | 999K| 30M| 1410 (1)| 00:00:01 |”
“|* 1 | TABLE ACCESS FULL| SMPL_SALES | 999K| 30M| 1410 (1)| 00:00:01 |”
“——————————————————————————–”

7.2.2 ネストテッドループ
USE_NLヒントを用いてネストされたループを使うようにオプティマイザに指不できる。ここで、
USE NLヒントを用いて特定されたテーブルは結合中の参照テーブル、つまり結合順序で2番目のテーブ
ル、であることを思い出してほしい。ネストされたループの場合、このテーブルは結合を可能にするイ
ンデックスを含んでいることが多い。逆に、インデックスがない場合には、駆動テーブルを1行処理する
たびに、参照テーブルを全走査しなければならないので、パフォーマンスが悪くなる。次の例は、以下
の結果をもたらすヒントと実行計画を示している。

select /*+ oRDERED USE_NL(e) */
count ( * )
from customers c,
employees e
where c . sales_rep_id=e . employee_id;

7.2.3 ソートマージ結合に適している状況では、ハッシュ結合はたいていソートマージ結合以上のパフオー
マンスを発揮する。ハッシュ結合はソートを必要としないので、テーブルが大きすぎてソートのための
メモリが足りないような場合、目を見張るようなパフォーマンスの向上が見込めるだろう。ハッシュ結
合をソートマージの代わりにオプティマイザに選択させるには、初期パラで
HASH_JOIN_ENABLED=TRUEと設定する。

7.2.4 結合のパフォーマンスの比較
2つのテーブルのすべての行を結合する作業ではネストされたループのパフォーマンスが一番劣る。
ソートマージ結合のパフォーマンスの方が優れており、ハッシュ結合のパフォーマンスはさらに良い。

テーブルの中のすべてあるいはほとんどの行を結合する場合は、ネストされたループ結合を使わずに、
ソートマージ結合かハッシュ結合を使うと良いだろう。

確かに、ネストされたループ結合は、複数のテーブルのすべての行を結合させるのには不向きである。
インデックスを読み込むオーバーヘッドがあるためだ(すべての行を読み込むならインデックスは必要が
ない)。しかし、参照テーブルの部分集合が小さいときには向いている。たとえば、次の例で営業の情報を
取り出すために顧客データと従業員データを結合させる。