今回はOracleで統計情報と共に取得されるヒストグラムの効果について確認します。
ヒストグラムとは簡単に言えば、データの分布状況です。
実行計画の策定に重要な役割を果たします。
SQL> create table TBL1 (COL1 number NOT NULL, COL2 char(100));
表が作成されました。
SQL> insert into TBL1 select LEVEL, 'hoge' || to_char(LEVEL) from DUAL connect by LEVEL <= 1000;
1000行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> create unique index IDB_TBL1_COL1 on TBL1(COL1);
索引が作成されました。
SQL> alter table TBL1 add primary key (COL1) using index;
表が変更されました。
1000行のデータが挿入されているTBL1を作成します。
COL1は1～1000の数字が均等に採番されています。
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'user1', -
> tabname => 'TBL1', -
> cascade => FALSE, -
> method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQLプロシージャが正常に完了しました。
統計情報を取得しました。
「method_opt => 'FOR ALL COLUMNS SIZE 1'」の部分でヒストグラムを取得しないように指定しています。
connect / as sysdba
alter system flush buffer_cache ;
alter system flush shared_pool ;
connect user1/password
SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from (select distinct(COL2) from TBL1 where COL1 <= 1000);
COUNT(*)
----------
1000
経過: 00:00:00.08
実行計画
----------------------------------------------------------
Plan hash value: 2225897066
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 (7)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 1000 | | 15 (7)| 00:00:01 |
| 3 | HASH UNIQUE | | 1000 | 102K| 15 (7)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TBL1 | 1000 | 102K| 14 (0)| 00:00:01 |
-----------------------------------------------------------------------------
・
・
・
1000件のデータから1000件のデータを抽出するSQLを実行しました。
COL1にはインデックスが付与されていますが、「 TABLE ACCESS FULL」スキャンがコストが低いと判断されています。
SQL> insert into TBL1 VALUES (1000000, 'ADD DATA');
1行が作成されました。
SQL> commit;
コミットが完了しました。
経過: 00:00:00.01
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'user1', -
> tabname => 'TBL1', -
> cascade => FALSE, -
method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQLプロシージャが正常に完了しました。
1件データを挿入します。
さらに統計情報も同じように取得します。
connect / as sysdba
alter system flush buffer_cache ;
alter system flush shared_pool ;
connect user1/password
SQL> select count(*) from (select distinct(COL2) from TBL1 where COL1 <= 1000);
COUNT(*)
----------
1000
経過: 00:00:00.10
SQL> set linesiz 200
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a8vtg7v21vzqt, child number 0
-------------------------------------
select count(*) from (select distinct(COL2) from TBL1 where COL1 <=
1000)
Plan hash value: 1367380336
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 2 | | 4 (25)| 00:00:01 |
| 3 | HASH UNIQUE | | 2 | 210 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TBL1 | 2 | 210 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDB_TBL1_COL1 | 2 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
・
・
・
再度、同様のSQLを実行しました。
今度はindexを利用する方がコストは低いと判断したようです。
1件データを挿入しただけなのに・・・
原因は挿入したデータの中身です。
1～1000のデータがある表に、いきなり1000000というかけ離れた数字がinsertされたので、
実行計画を判断するオプティマイザは1～1000000のデータが均等に存在すると判断し、indexスキャンの方がコストが低いと判断したのです。
これらの問題はヒストグラムを取得することで解決します。
データの分布状況がハッキリとするのです。
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'user1', -
tabname => 'TBL1', -
cascade => FALSE, -
method_opt => 'FOR COLUMNS COL1 SIZE AUTO');
統計情報をCOL1のヒストグラムと一緒に取得します。
これで、1000000がかけ離れた異常値だということがオプティマイザにも判断できるようになりました。
SQL> select count(*) from (select distinct(COL2) from TBL1 where COL1 <= 1000);
COUNT(*)
----------
1000
経過: 00:00:00.13
SQL> set linesize 200
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a8vtg7v21vzqt, child number 0
-------------------------------------
select count(*) from (select distinct(COL2) from TBL1 where COL1 <=
1000)
Plan hash value: 2225897066
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 997 | | 15 (7)| 00:00:01 |
| 3 | HASH UNIQUE | | 997 | 102K| 15 (7)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TBL1 | 997 | 102K| 14 (0)| 00:00:01 |
-----------------------------------------------------------------------------
再度SQLを実行すると・・・
今度は「 TABLE ACCESS FULL」スキャン（全表走査）が選択されました。
ヒストグラムとは簡単に言えば、データの分布状況です。
実行計画の策定に重要な役割を果たします。
SQL> create table TBL1 (COL1 number NOT NULL, COL2 char(100));
表が作成されました。
SQL> insert into TBL1 select LEVEL, 'hoge' || to_char(LEVEL) from DUAL connect by LEVEL <= 1000;
1000行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> create unique index IDB_TBL1_COL1 on TBL1(COL1);
索引が作成されました。
SQL> alter table TBL1 add primary key (COL1) using index;
表が変更されました。
1000行のデータが挿入されているTBL1を作成します。
COL1は1～1000の数字が均等に採番されています。
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'user1', -
> tabname => 'TBL1', -
> cascade => FALSE, -
> method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQLプロシージャが正常に完了しました。
統計情報を取得しました。
「method_opt => 'FOR ALL COLUMNS SIZE 1'」の部分でヒストグラムを取得しないように指定しています。
connect / as sysdba
alter system flush buffer_cache ;
alter system flush shared_pool ;
connect user1/password
SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from (select distinct(COL2) from TBL1 where COL1 <= 1000);
COUNT(*)
----------
1000
経過: 00:00:00.08
実行計画
----------------------------------------------------------
Plan hash value: 2225897066
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15 (7)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 1000 | | 15 (7)| 00:00:01 |
| 3 | HASH UNIQUE | | 1000 | 102K| 15 (7)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TBL1 | 1000 | 102K| 14 (0)| 00:00:01 |
-----------------------------------------------------------------------------
・
・
・
1000件のデータから1000件のデータを抽出するSQLを実行しました。
COL1にはインデックスが付与されていますが、「 TABLE ACCESS FULL」スキャンがコストが低いと判断されています。
SQL> insert into TBL1 VALUES (1000000, 'ADD DATA');
1行が作成されました。
SQL> commit;
コミットが完了しました。
経過: 00:00:00.01
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'user1', -
> tabname => 'TBL1', -
> cascade => FALSE, -
method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQLプロシージャが正常に完了しました。
1件データを挿入します。
さらに統計情報も同じように取得します。
connect / as sysdba
alter system flush buffer_cache ;
alter system flush shared_pool ;
connect user1/password
SQL> select count(*) from (select distinct(COL2) from TBL1 where COL1 <= 1000);
COUNT(*)
----------
1000
経過: 00:00:00.10
SQL> set linesiz 200
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a8vtg7v21vzqt, child number 0
-------------------------------------
select count(*) from (select distinct(COL2) from TBL1 where COL1 <=
1000)
Plan hash value: 1367380336
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 2 | | 4 (25)| 00:00:01 |
| 3 | HASH UNIQUE | | 2 | 210 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TBL1 | 2 | 210 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDB_TBL1_COL1 | 2 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
・
・
・
再度、同様のSQLを実行しました。
今度はindexを利用する方がコストは低いと判断したようです。
1件データを挿入しただけなのに・・・
原因は挿入したデータの中身です。
1～1000のデータがある表に、いきなり1000000というかけ離れた数字がinsertされたので、
実行計画を判断するオプティマイザは1～1000000のデータが均等に存在すると判断し、indexスキャンの方がコストが低いと判断したのです。
これらの問題はヒストグラムを取得することで解決します。
データの分布状況がハッキリとするのです。
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'user1', -
tabname => 'TBL1', -
cascade => FALSE, -
method_opt => 'FOR COLUMNS COL1 SIZE AUTO');
統計情報をCOL1のヒストグラムと一緒に取得します。
これで、1000000がかけ離れた異常値だということがオプティマイザにも判断できるようになりました。
SQL> select count(*) from (select distinct(COL2) from TBL1 where COL1 <= 1000);
COUNT(*)
----------
1000
経過: 00:00:00.13
SQL> set linesize 200
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a8vtg7v21vzqt, child number 0
-------------------------------------
select count(*) from (select distinct(COL2) from TBL1 where COL1 <=
1000)
Plan hash value: 2225897066
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 997 | | 15 (7)| 00:00:01 |
| 3 | HASH UNIQUE | | 997 | 102K| 15 (7)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TBL1 | 997 | 102K| 14 (0)| 00:00:01 |
-----------------------------------------------------------------------------
再度SQLを実行すると・・・
今度は「 TABLE ACCESS FULL」スキャン（全表走査）が選択されました。
コメント