SAK 図書館

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

SQL チューニング編2 - SQL 実行計画解析、EXPLAIN、表走査、索引走査

Oracle 固有の部分があるかもしれません。SQL-Server、MDB では注意のこと。

■実行計画解析

  ・SQL の実行計画がどのようになっているか調査することができる。
    まず、調査スキーマに plan_table を作成する。
    これは、調査スキーマで一度だけ実行すればよい。
    (データベース最適化、データベースチューニング、テーブル最適化)

@j:\orawin95\rdbms80\admin\utlxplan.sql

  ・utlxplan.sql では、次の plan_table が作成される。

create table plan_table (
  statement_id     varchar2(30),
  timestamp        date,
  remarks          varchar2(80),
  operation        varchar2(30),
  options          varchar2(30),
  object_node      varchar2(128),
  object_owner     varchar2(30),
  object_name      varchar2(30),
  object_instance  numeric,
  object_type      varchar2(30),
  optimizer        varchar2(255),
  search_columns   number,
  id               numeric,
  parent_id        numeric,
  position         numeric,
  cost             numeric,
  cardinality      numeric,
  bytes            numeric,
  other_tag        varchar2(255),
  partition_start  varchar2(255),
  partition_stop   varchar2(255),
  partition_id     numeric,
  other            long
);

  ・plan_table を削除するには、次のようにする。

drop table plan_table;

  ・実行計画を採取するには、次のように目的の SQL 文を発行する。

truncate table plan_table;
explain plan for
  select * from sak.受注m where 受注番号 like '00%' order by 受注番号
;

  ・実行計画を問い合わせ表示するには、次のようにする。

select operation || ' options= ' || options || ' object= '  || object_name
|| ' position= ' || position || ' cost= ' || cost from plan_table;

  ・結果は、次のように表示される。

【SELECT STATEMENT options=  object=  position=  cost=
【TABLE ACCESS options= BY INDEX ROWID object= 受注M position= 1 cost=
【INDEX options= RANGE SCAN object= SYS_C00866 position= 1 cost=

  ・例えば、次の SQL を発行したとする。
    where が中間一致になっているため、
      INDEX options= FULL SCAN
    となる。
    これでは索引の意味がない。

truncate table plan_table;
explain plan for
  select * from sak.受注m where 受注番号 like '_0%' order by 受注番号
;
select operation || ' options= ' || options || ' object= '  || object_name
|| ' position= ' || position || ' cost= ' || cost from plan_table;

【SELECT STATEMENT options=  object=  position=  cost=
【TABLE ACCESS options= BY INDEX ROWID object= 受注M position= 1 cost=
【INDEX options= FULL SCAN object= SYS_C00866 position= 1 cost=

  ・例えば、次の SQL を発行したとする。
    受注sv には索引がないため、
      TABLE ACCESS options= FULL
    になる。
    受注m に対して、同じ SQL を発行したときは、索引があるので、
      TABLE ACCESS options= BY INDEX ROWID
      INDEX options= RANGE SCAN
    となっていた。

truncate table plan_table;
explain plan for
  select * from sak.受注sv where 受注番号 like '0%' order by 受注番号
;
select operation || ' options= ' || options || ' object= '  || object_name
|| ' position= ' || position || ' cost= ' || cost from plan_table;

【SELECT STATEMENT options=  object=  position=  cost=
【SORT options= ORDER BY object=  position= 1 cost=
【TABLE ACCESS options= FULL object= 受注SV position= 1 cost=

  ・例えば、次の SQL を発行したとする。
    where が索引の一致になっているため、
      INDEX options= UNIQUE SCAN
    となる。

truncate table plan_table;
explain plan for
  select * from sak.受注m where 受注番号 = '000001-001'
;
select operation || ' options= ' || options || ' object= '  || object_name
|| ' position= ' || position || ' cost= ' || cost from plan_table;

【SELECT STATEMENT options=  object=  position=  cost=
【TABLE ACCESS options= BY INDEX ROWID object= 受注M position= 1 cost=
【INDEX options= UNIQUE SCAN object= SYS_C00866 position= 1 cost=

  ・例えば、次の SQL を発行したとする。
    where で索引を加工してしまっているので、
      TABLE ACCESS options= FULL
    になり、索引がまったく使用されていない。

truncate table plan_table;
explain plan for
  select * from sak.受注m where substr(受注番号, 1, 6) = '000001' order by 受注番号
;
select operation || ' options= ' || options || ' object= '  || object_name
|| ' position= ' || position || ' cost= ' || cost from plan_table;

【SELECT STATEMENT options=  object=  position=  cost=
【TABLE ACCESS options= FULL object= 受注M position= 1 cost=

  ・上と同じ結果を得るのであれば、次の SQL を発行した方が良い。
    where で like の前方一致を索引に対して使用しているので、
      INDEX options= RANGE SCAN
    となる。

truncate table plan_table;
explain plan for
  select * from sak.受注m where 受注番号 like '000001%' order by 受注番号
;
select operation || ' options= ' || options || ' object= '  || object_name
|| ' position= ' || position || ' cost= ' || cost from plan_table;

【SELECT STATEMENT options=  object=  position=  cost=
【TABLE ACCESS options= BY INDEX ROWID object= 受注M position= 1 cost=
【INDEX options= RANGE SCAN object= SYS_C00866 position= 1 cost=

  ・not や is null の where 句も全表走査になりやすい。
    not is null は > '' で代替え可能な場合もある。
    少なくとも RANGE SCAN になるように工夫した方が良い。

■options の種類

  ・TABLE ACCESS options= BY USER ROWID
    - rowid により走査している

  ・TABLE ACCESS options= FULL
    - 全表走査している

  ・INDEX options= UNIQUE SCAN
    - 索引に対して等価検索している

  ・INDEX options= RANGE SCAN
    - 索引に対して範囲検索している

■SQL チューニング編資料

■SQL 基礎実地編資料

■SQL 基礎編資料

■Oracle PL/SQL 編資料

■PostgreSQL 編、JAVA Servlet、JSP 編資料

■MySQL 編資料
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |
SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索



Copyright (C) 1999-2018 Y.SAK