備忘録

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

ダイナミックサンプリング

ダイナミックサンプリングとは統計情報がない時、実行時に動的に統計情報を取得する機能。
統計情報がないから、INDEXが使われずに遅いって事がなくなります。

・動的に取得するのでそれなりのオーバーヘッドがある。どれ位かは知らない。
・Oracle11gではデフォルトで有効になっている
・統計情報がとられている場合は、それが有効になってしまうので、ダイナミックサンプリングはされない。
 そのため、global temporary table などワークテーブルでいったん統計情報が取られてしまうとそれが有効になってしまう。
 global temporaryなんかは統計情報とっても必ず0件だから必ずダイナミックサンプリングになってくれればいいと思う。
・たくさんデータを入れて統計情報をとった後truncateして検索 →インデックスアクセス
 ダイナミックサンプリング →FULLアクセス(こっちのほうがいい)
DYNAMIC_SAMPLINGヒント は統計情報が取られているとそっちを使ってしまう。ダイナミックサンプリング固定にしたい場合は、手動統計を使用する場合 にあるとおり、統計情報を消したあとロックする必要がある。
exec DBMS_STATS.DELETE_TABLE_STATS('TEST','TMP01');
exec DBMS_STATS.LOCK_TABLE_STATS('TEST','TMP01');
-- ロック状態で削除しようとすると次のようになる。
-- なおロック状態は、Oracle再起動しても保持される。
-- スキーマ単位の統計情報取得 DBMS_STATS.GATHER_SCHEMA_STATS ( OWNNAME =>'TEST'); はエラーは起きない。
SQL> exec DBMS_STATS.DELETE_TABLE_STATS('TEST','TMP01');
BEGIN DBMS_STATS.DELETE_TABLE_STATS('TEST','TMP01'); END;

*
行1でエラーが発生しました。:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: "SYS.DBMS_STATS", 行2205
ORA-06512: "SYS.DBMS_STATS", 行3003
ORA-06512: "SYS.DBMS_STATS", 行9690
ORA-06512: 行1
-- ロック解除は次のとおり
SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('TEST','TMP01');

PL/SQLプロシージャが正常に完了しました。


■試したSQL
dynamic sampling used for this statement と出たらダイナミックサンプリング

drop table tmp01;
create table tmp01 (
n1 number(10)
,v1 varchar2(1000)
);

CREATE INDEX IDX_TMP01_01 ON TMP01 (N1);

begin
for i in 1..100000 loop
insert into tmp01 values(i, i);
end loop;
end;
/
commit;
set autotrace traceonly
select * from tmp01 where n1 >= 1;

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 49M| 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TMP01 | 101K| 49M| 69 (2)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("N1">=1)

Note
-----
- dynamic sampling used for this statement (level=2)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
13673 consistent gets
0 physical reads
0 redo size
2444892 bytes sent via SQL*Net to client
73746 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed

select * from tmp01 where n1 = 1;

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 515 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TMP01 | 1 | 515 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TMP01_01 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------


exec DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'TEST', tabname => 'TMP01');

select * from tmp01 where n1 >= 1;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 1074K| 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TMP01 | 100K| 1074K| 69 (2)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("N1">=1)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
13673 consistent gets
0 physical reads
0 redo size
2444892 bytes sent via SQL*Net to client
73746 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed

truncate table tmp01;
select * from tmp01 where n1 >= 1;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 1074K| 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TMP01 | 100K| 1074K| 69 (2)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select * from tmp01 where n1 = 1;

レコードが選択されませんでした。

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TMP01 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TMP01_01 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

exec DBMS_STATS.DELETE_TABLE_STATS('TEST','TMP01');

SQL> select * from tmp01 where n1 =1;

レコードが選択されませんでした。

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 515 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TMP01 | 1 | 515 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------


■試していて気付いたこと
・temporaryテーブルとそうでないテーブルで静的なサンプリングの実行計画が異なる。
 0件の時、temporaryテーブルの場合、INDEXアクセスするが通常のテーブルの場合FULL ACCESS(こっちのほうが速い)になる。
・DBMS_STATでとった時とダイナミックサンプリングの実行計画の結果が異なる。
 サンプリングのレベルが違うんだと思う。
ダイナミックサンプリング
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101K| 49M| 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TMP01 | 101K| 49M| 69 (2)| 00:00:01 |
---------------------------------------------------------------------------
DBMS_STAT
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 1074K| 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TMP01 | 100K| 1074K| 69 (2)| 00:00:01 |
---------------------------------------------------------------------------

コメント

ダイナミックサンプリング

ダイナミックサンプリングにはsqlが終わらなくなるバグ(OSプロセスをkillするしかない状態になる)があって、積極的に使うべきじゃないよ。

  • 2016/11/18(金) 22:08:13 |
  • URL |
  • ビル #-
  • [ 編集 ]

Re: ダイナミックサンプリング

> ダイナミックサンプリングにはsqlが終わらなくなるバグ(OSプロセスをkillするしかない状態になる)があって
そのバグを踏んだことはないですね。PSRが古いのですかね?
ただ、明示的にダイナミックサンプリングを使うことはなく、ダイナミックサンプリングでたまたま出てきた良い実行計画で固定化するのがメインです。

  • 2016/11/19(土) 18:32:26 |
  • URL |
  • ncdkayak #-
  • [ 編集 ]

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://ncdkayak.blog.fc2.com/tb.php/15-ec3e8d4e
この記事にトラックバックする(FC2ブログユーザー)

FC2Ad

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。