LoginSignup
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@plusultra(Noriyoshi Shinoda)

統計情報を取得する SELECT 文を確認する（Oracle Database 19c）

Last updated at Posted at 2023-03-25

オプティマイザ統計

SQL文が実行されるためには実行計画の作成が必要です。実行計画はデータへのアクセス方法（テーブルを直接参照、インデックス経由で参照）、結合方法（Nested Loop, Merge, Hash）、結合順序、パラレル・クエリーの使用等を SQL 文毎に作成された情報です。この実行計画を作成するために「オプティマイザ統計」が使われます。オプティマイザ統計はテーブルやインデックスのメタデータであり、レコード数、列の最大値や最小値、データの分散、ブロック数等の情報になります。

オプティマイザ統計の取得

　Oracle Databaseはデフォルト状態では１日１回オプティマイザ統計を取得するジョブが自動実行され、オプティマイザ統計が存在しないテーブルや、一定以上変更があったテーブルを取得に取得対象とします。オプティマイザ統計を取得するためには DBMS_STATS パッケージの GATHER_(*)_STATS プロシージャを実行します（(*) の部分は TABLE / SCHMA / SYSTEM / INDEX / FIXED / SYSTEM など）。このプロシージャは最終的には対象テーブルに対して SELECT 文を実行し、その結果からオプティマイザ統計を生成します。
　本記事では DBMS_STATS パッケージの GATHER_TABLE_STATS プロシージャを実行し、実際に実行される SELECT 文を調査しています。

調査に使用するテーブル DATA1 は以下の仕様で作成しています。テーブル作成後にセッションのトレース情報を取得（DBMS_MONITOR.SESSION_TRACE_ENABLE）し、出力されたトレース・ファイルを tkprof ユーティリティで整形して掲載しています。

SQL> CREATE TABLE data1(c1 NUMBER, c2 VARCHAR2(10));

表が作成されました。

SQL> INSERT INTO data1 SELECT * FROM data;

16000000行が作成されました。

SQL> COMMIT;

コミットが完了しました。

SQL>  EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE();

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

デフォルト設定による統計情報取得

　統計情報を取得する設定はデフォルト値です。

SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT', 'SCOTT', 'DATA1') FROM DUAL;

DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT','SCOTT','DATA1')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT', NULL, NULL) FROM DUAL;

DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT',NULL,NULL)
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

SQL> SELECT DBMS_STATS.GET_PREFS('DEGREE', NULL, NULL) FROM DUAL;

DBMS_STATS.GET_PREFS('DEGREE',NULL,NULL)
--------------------------------------------------------------------------------
NULL

SQL> SELECT DBMS_STATS.GET_PREFS('NO_INVALIDATE', NULL, NULL) FROM DUAL;

DBMS_STATS.GET_PREFS('NO_INVALIDATE',NULL,NULL)
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

まずスキーマ名とテーブル名のみを指定して統計情報を取得します。

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SCOTT','DATA1');

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

実行された SQL 文を下記に示します。全件検索を行う full ヒントや、パラレルクエリーを使わない no_parallel, no_parallel_index ヒント等が使われています。また列 c1, c2 に対して個数（COUNT）、最小値（MIN）、最大値（MAX）等が取得されています。古いバージョンでは自動サンプリングサイズ（DBMS_STATS.AUTO_SAMPLE_SIZE）は一部のデータのみのサンプリングを行っていましたが、最近のバージョンでは全レコードの取得を行うようです。この辺のアルゴリズムは公開されていません。

ヒント 説明
full(t) 全件検索 TABLE FULL SCAN
no_parallel(t) パラレルクエリーを使わない
dbms_stats 統計情報取得用のSQL
cursor_sharing_exact リテラルのバインド変数化を行わない
use_weak_name_resl 不明
dynamic_samplint(0) ダイナミック・サンプリングを使わない
no_monitoring モニタリングを行わない
xmlindex_sel_idx_tbl 不明
opt_param('optimizer_inmmemory_aware' 'false') インメモリ属性を使わない
no_substrb_pad SUBSTRB関数の動作変更 
select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false')
  no_substrb_pad  */to_char(count("C1")),substrb(dump(min("C1"),16,0,64),1,
  240),substrb(dump(max("C1"),16,0,64),1,240),to_char(count("C2")),
  substrb(dump(min("C2"),16,0,64),1,240),substrb(dump(max("C2"),16,0,64),1,
  240)
from
 "SCOTT"."DATA1" t  /* NDV,NIL,NIL,NDV,NIL,NIL*/

統計情報取得割合を指定

統計情報の取得割合を 10% に指定（estimate_percent=>10）して実行します。

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SCOTT','DATA1', NULL, 10);

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

実行された SQL 文は以下の通りです。テーブルに対して SAMPLE (10.0000000000) 句が指定され、サンプリングが行われていることが分かります。

select /*+  no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false')
  no_substrb_pad  */count(*), count("C1"), count(distinct "C1"),
  sum(sys_op_opnsize("C1")), substrb(dump(min("C1"),16,0,64),1,240),
  substrb(dump(max("C1"),16,0,64),1,240), count("C2"), count(distinct "C2"),
  sum(sys_op_opnsize("C2")), substrb(dump(min("C2"),16,0,64),1,240),
  substrb(dump(max("C2"),16,0,64),1,240)
from
 "SCOTT"."DATA1" sample ( 10.0000000000)  t

ブロック・サンプリングを指定

情報取得にサンプリング割合とブロック・サンプリングを指定（block_sample=>TRUE）します。

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'DATA1', estimate_percent=>10, block_sample=>TRUE);

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

実行された SQL 文は以下の通りです。テーブルに対して SAMPLE BLOCK (10.0000000000, 3) 句が指定され、ブロック・サンプリングが行われていることが分かります。２つ目の数字「３」が何を示すかは不明です。SQLリファレンス・マニュアルにも記載がありません。

select /*+  no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false')
  no_substrb_pad  */count(*), count("C1"), count(distinct "C1"),
  sum(sys_op_opnsize("C1")), substrb(dump(min("C1"),16,0,64),1,240),
  substrb(dump(max("C1"),16,0,64),1,240), count("C2"), count(distinct "C2"),
  sum(sys_op_opnsize("C2")), substrb(dump(min("C2"),16,0,64),1,240),
  substrb(dump(max("C2"),16,0,64),1,240)
from
 "SCOTT"."DATA1" sample block ( 10.0000000000,3)  t

並列度を指定

情報取得に並列度を４に指定（degree=>4）します。

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'DATA1', degree=>4);

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

実行された SQL 文は以下の通りです。SELECT 文に対して並列度ヒント（parallel(t, 4)）と、インデックスに対する並列度ヒント（parallel_index(t, 4)）が指定されていることがわかります。

select /*+  full(t)    parallel(t,4) parallel_index(t,4) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false')
  no_substrb_pad  */to_char(count("C1")),substrb(dump(min("C1"),16,0,64),1,
  240),substrb(dump(max("C1"),16,0,64),1,240),to_char(count("C2")),
  substrb(dump(min("C2"),16,0,64),1,240),substrb(dump(max("C2"),16,0,64),1,
  240)
from
 "SCOTT"."DATA1" t  /* NDV,NIL,NIL,NDV,NIL,NIL*/

NO_INVALIDATEを指定

オプション no_invalidateの設定を変化させて SELECT 文のトレースを取得しました。
以下は no_invalidate=>TRUE を指定した時の SELECT 文です。

select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false')
  no_substrb_pad  */to_char(count("C1")),substrb(dump(min("C1"),16,0,64),1,
  240),substrb(dump(max("C1"),16,0,64),1,240),to_char(count("C2")),
  substrb(dump(min("C2"),16,0,64),1,240),substrb(dump(max("C2"),16,0,64),1,
  240)
from
 "SCOTT"."DATA1" t  /* NDV,NIL,NIL,NDV,NIL,NIL*/

以下は no_invalidate=>FALSE を指定した時の SELECT 文です。

select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false')
  no_substrb_pad  */to_char(count("C1")),substrb(dump(min("C1"),16,0,64),1,
  240),substrb(dump(max("C1"),16,0,64),1,240),to_char(count("C2")),
  substrb(dump(min("C2"),16,0,64),1,240),substrb(dump(max("C2"),16,0,64),1,
  240)
from
 "SCOTT"."DATA1" t  /* NDV,NIL,NIL,NDV,NIL,NIL*/

NO_INVALIDATEオプションについては SELECT 文のレベルでは違いがありませんでした。

インデックスの統計情報

テーブルにインデックスを作成して統計情報を取得してみます。以下のインデックスを作成しました。デフォルト設定で統計情報を取得します。

SQL> CREATE INDEX idx1_data1 ON data1(c1);

索引が作成されました。

SQL> CREATE INDEX idx2_data1 ON data1(c2);

索引が作成されました。

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'DATA1');

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

実行された SELECT 文は以下の通りです。

select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false')
  no_substrb_pad  */to_char(count("C1")),substrb(dump(min("C1"),16,0,64),1,
  240),substrb(dump(max("C1"),16,0,64),1,240),to_char(count("C2")),
  substrb(dump(min("C2"),16,0,64),1,240),substrb(dump(max("C2"),16,0,64),1,
  240)
from
 "SCOTT"."DATA1" t  /* NDV,NIL,NIL,NDV,NIL,NIL*/

select /*+ opt_param('_optimizer_use_auto_indexes' 'on')
  no_parallel_index(t, "IDX1_DATA1")  dbms_stats cursor_sharing_exact
  use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl
  opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  no_expand
  index_ffs(t,"IDX1_DATA1") */ count(*) as nrw,
  approx_count_distinct(sys_op_lbid(83811,'L',t.rowid)) as nlb,null as ndk,
  sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from
 "SCOTT"."DATA1" t where "C1" is not null

select /*+ opt_param('_optimizer_use_auto_indexes' 'on')
  no_parallel_index(t, "IDX2_DATA1")  dbms_stats cursor_sharing_exact
  use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl
  opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  no_expand
  index_ffs(t,"IDX2_DATA1") */ count(*) as nrw,
  approx_count_distinct(sys_op_lbid(83812,'L',t.rowid)) as nlb,null as ndk,
  sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from
 "SCOTT"."DATA1" t where "C2" is not null

テーブルに対する検索だけでなく、作成した２つのインデックスに対してそれぞれ INDEX FAST FULL SCAN になるようにヒントを指定してインデックスの検索を行っています。

Author: Noriyoshi Shinoda / Date: March 25, 2023

1
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?