2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Day 1

Oracle Cloud Infrastructure Advent Calendar 2025

@jri_narita(成田 裕一郎)

ヒストグラム有無の統計情報収集時間への影響⭕

Last updated at Posted at 2025-12-29

はじめに

オプティマイザが生成する実行計画の精度を高める効果が期待できるヒストグラムですが、通常の統計情報収集に＋αとなるため、オーバーヘッドが気になるところです。
今回は、ヒストグラムの収集対象カラムを増やしながら、ヒストグラムの有無による統計情報収集時間の変化を確認します。
環境は、OCI のコンピュート・インスタンスに導入した OracleDB 19c を使用しています。

手順

下記の手順を 3セット実施します。

  • サンプルスキーマのOE.PRODUCT_INFORMATIONをベースにレコード数を増幅 (約100万件) ＆ CATEGORY_IDカラムを増幅 (CID_CP01～CID_CP15)
  • インデックス作成 (作成対象はCID_CP01→CID_CP01～CID_CP05→CID_CP01～CID_CP10→CID_CP01～CID_CP15と変化)
  • 統計情報収集 (ヒストグラム無しと有りそれぞれで実施)
  • テーブル削除

手順サンプル

箇条書きの手順ではわかりづらいと思うので、少し長いですがターミナルログを抜粋します。

インデックス×10 作成のケース (CID_CP01～CID_CP10)
SQL> -------------------------------テーブル作成
SQL> CREATE TABLE oe.prod_inf_cp AS
  2  SELECT
  3  product_id,
  4  product_name,
  5  product_description,
  6  category_id,
  7  weight_class,
  8  warranty_period,
  9  supplier_id,
 10  product_status,
 11  list_price,
 12  min_price,
 13  catalog_url,
 14  category_id + 1  AS cid_cp01,
 15  category_id + 2  AS cid_cp02,
 16  category_id + 3  AS cid_cp03,
 17  category_id + 4  AS cid_cp04,
 18  category_id + 5  AS cid_cp05,
 19  category_id + 6  AS cid_cp06,
 20  category_id + 7  AS cid_cp07,
 21  category_id + 8  AS cid_cp08,
 22  category_id + 9  AS cid_cp09,
 23  category_id + 10 AS cid_cp10,
 24  category_id + 11 AS cid_cp11,
 25  category_id + 12 AS cid_cp12,
 26  category_id + 13 AS cid_cp13,
 27  category_id + 14 AS cid_cp14,
 28  category_id + 15 AS cid_cp15
 29  FROM   oe.product_information;

Table created.

Elapsed: 00:00:00.05
SQL> 
SQL> -------------------------------レコード数増幅
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

288 rows created.

Elapsed: 00:00:00.00
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

576 rows created.

Elapsed: 00:00:00.00
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

1152 rows created.

Elapsed: 00:00:00.00
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

2304 rows created.

Elapsed: 00:00:00.01
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

4608 rows created.

Elapsed: 00:00:00.01
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);
insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

9216 rows created.

Elapsed: 00:00:00.01
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);
insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

18432 rows created.

Elapsed: 00:00:00.05
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);
insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);
insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);
commit;


36864 rows created.

Elapsed: 00:00:00.08
SQL> 
73728 rows created.

Elapsed: 00:00:00.20
SQL> 
147456 rows created.

Elapsed: 00:00:00.36
SQL> 
294912 rows created.

Elapsed: 00:00:01.41
SQL> 
589824 rows created.

Elapsed: 00:00:03.65
SQL> 
Commit complete.

Elapsed: 00:00:00.04
SQL>
SQL> -------------------------------レコード数確認
SQL> select count(1) from oe.prod_inf_cp;

  COUNT(1)
----------
   1179648

Elapsed: 00:00:00.07
SQL> -------------------------------インデックス作成
SQL> create index oe.cid_cp01_ix on oe.prod_inf_cp(cid_cp01);
create index oe.cid_cp02_ix on oe.prod_inf_cp(cid_cp02);
create index oe.cid_cp03_ix on oe.prod_inf_cp(cid_cp03);
create index oe.cid_cp04_ix on oe.prod_inf_cp(cid_cp04);
create index oe.cid_cp05_ix on oe.prod_inf_cp(cid_cp05);
create index oe.cid_cp06_ix on oe.prod_inf_cp(cid_cp06);
create index oe.cid_cp07_ix on oe.prod_inf_cp(cid_cp07);
create index oe.cid_cp08_ix on oe.prod_inf_cp(cid_cp08);
create index oe.cid_cp09_ix on oe.prod_inf_cp(cid_cp09);
create index oe.cid_cp10_ix on oe.prod_inf_cp(cid_cp10);

Index created.

Elapsed: 00:00:00.87
SQL> 
Index created.

Elapsed: 00:00:00.89
SQL> 
Index created.

Elapsed: 00:00:00.88
SQL> 
Index created.

Elapsed: 00:00:00.90
SQL> 
Index created.

Elapsed: 00:00:00.89
SQL> 
Index created.

Elapsed: 00:00:00.89
SQL> 
Index created.

Elapsed: 00:00:00.89
SQL> 
Index created.

Elapsed: 00:00:00.90
SQL> 
Index created.

Elapsed: 00:00:00.90
SQL> 
Index created.

Elapsed: 00:00:00.89
SQL> -------------------------------念のためメモリフラッシュ
SQL> alter system flush shared_pool;
alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.12
SQL> exec dbms_result_cache.flush;


System altered.

Elapsed: 00:00:05.05
SQL> 
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SQL> SQL> 
SQL> 
SQL> 
SQL> -------------------------------統計情報収集 (ヒストグラム無し)
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'OE',tabname=>'PROD_INF_CP',cascade=>TRUE,no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.43 ★確認対象
SQL> -------------------------------ヒストグラム収集状況確認 (ここでは 0件が期待)
SQL> select column_name,histogram from dba_tab_col_statistics where table_name = 'PROD_INF_CP' and column_name like 'CID%' and histogram = 'FREQUENCY' order by column_name;

no rows selected

Elapsed: 00:00:00.16
SQL> -------------------------------テーブル削除
SQL> drop table oe.prod_inf_cp;

Table dropped.

Elapsed: 00:00:00.18
SQL> -------------------------------テーブル作成
SQL> CREATE TABLE oe.prod_inf_cp AS
  2  SELECT
  3  product_id,
  4  product_name,
  5  product_description,
  6  category_id,
  7  weight_class,
  8  warranty_period,
  9  supplier_id,
 10  product_status,
 11  list_price,
 12  min_price,
 13  catalog_url,
 14  category_id + 1  AS cid_cp01,
 15  category_id + 2  AS cid_cp02,
 16  category_id + 3  AS cid_cp03,
 17  category_id + 4  AS cid_cp04,
 18  category_id + 5  AS cid_cp05,
 19  category_id + 6  AS cid_cp06,
 20  category_id + 7  AS cid_cp07,
 21  category_id + 8  AS cid_cp08,
 22  category_id + 9  AS cid_cp09,
 23  category_id + 10 AS cid_cp10,
 24  category_id + 11 AS cid_cp11,
 25  category_id + 12 AS cid_cp12,
 26  category_id + 13 AS cid_cp13,
 27  category_id + 14 AS cid_cp14,
 28  category_id + 15 AS cid_cp15
 29  FROM   oe.product_information;

Table created.

Elapsed: 00:00:00.05
SQL> -------------------------------レコード数増幅
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

288 rows created.

Elapsed: 00:00:00.00
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

576 rows created.

Elapsed: 00:00:00.00
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

1152 rows created.

Elapsed: 00:00:00.00
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

2304 rows created.

Elapsed: 00:00:00.00
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

4608 rows created.

Elapsed: 00:00:00.01
SQL> insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);
insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);
insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);
insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);
insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);
insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);
insert into oe.prod_inf_cp (select * from oe.prod_inf_cp);

9216 rows created.

Elapsed: 00:00:00.10
SQL> commit;

18432 rows created.

Elapsed: 00:00:00.03
SQL> 
36864 rows created.

Elapsed: 00:00:00.09
SQL> 
73728 rows created.

Elapsed: 00:00:00.19
SQL> 
147456 rows created.

Elapsed: 00:00:00.37
SQL> 
294912 rows created.

Elapsed: 00:00:01.31
SQL> 
589824 rows created.

Elapsed: 00:00:03.67
SQL> 
Commit complete.

Elapsed: 00:00:00.05
SQL> -------------------------------レコード数確認
SQL> select count(1) from oe.prod_inf_cp;

  COUNT(1)
----------
   1179648

Elapsed: 00:00:00.07
SQL> -------------------------------インデックス作成
SQL> create index oe.cid_cp01_ix on oe.prod_inf_cp(cid_cp01);
create index oe.cid_cp02_ix on oe.prod_inf_cp(cid_cp02);
create index oe.cid_cp03_ix on oe.prod_inf_cp(cid_cp03);
create index oe.cid_cp04_ix on oe.prod_inf_cp(cid_cp04);
create index oe.cid_cp05_ix on oe.prod_inf_cp(cid_cp05);
create index oe.cid_cp06_ix on oe.prod_inf_cp(cid_cp06);
create index oe.cid_cp07_ix on oe.prod_inf_cp(cid_cp07);
create index oe.cid_cp08_ix on oe.prod_inf_cp(cid_cp08);
create index oe.cid_cp09_ix on oe.prod_inf_cp(cid_cp09);
create index oe.cid_cp10_ix on oe.prod_inf_cp(cid_cp10);

Index created.

Elapsed: 00:00:00.90
SQL> 
Index created.

Elapsed: 00:00:00.90
SQL> 
Index created.

Elapsed: 00:00:00.92
SQL> 
Index created.

Elapsed: 00:00:00.91
SQL> 
Index created.

Elapsed: 00:00:00.90
SQL> 
Index created.

Elapsed: 00:00:00.88
SQL> 
Index created.

Elapsed: 00:00:00.91
SQL> 
Index created.

Elapsed: 00:00:00.89
SQL> 
Index created.

Elapsed: 00:00:00.91
SQL> 
Index created.

Elapsed: 00:00:00.93
SQL> -------------------------------念のためメモリフラッシュ
SQL> alter system flush shared_pool;
alter system flush buffer_cache;
exec dbms_result_cache.flush;


System altered.

Elapsed: 00:00:00.12
SQL> 
System altered.

Elapsed: 00:00:04.84
SQL> 
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SQL> -------------------------------統計情報収集 (ヒストグラム有り)
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'OE',tabname=>'PROD_INF_CP',method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',cascade=>TRUE,no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.49 ★確認対象
SQL> -------------------------------ヒストグラム収集状況確認 (ここでは 10件が期待)
SQL> select column_name,histogram from dba_tab_col_statistics where table_name = 'PROD_INF_CP' and column_name like 'CID%' and histogram = 'FREQUENCY' order by column_name;

COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
HISTOGRAM
---------------
CID_CP01
FREQUENCY

CID_CP02
FREQUENCY

CID_CP03
FREQUENCY

CID_CP04
FREQUENCY

CID_CP05
FREQUENCY

CID_CP06
FREQUENCY

CID_CP07
FREQUENCY

CID_CP08
FREQUENCY

CID_CP09
FREQUENCY

CID_CP10
FREQUENCY


10 rows selected.

Elapsed: 00:00:00.16
SQL> -------------------------------テーブル削除
SQL> drop table oe.prod_inf_cp;

Table dropped.

Elapsed: 00:00:00.17

結果

確認結果は下表の通りとなりました。

セット ヒストグラム収集対象カラム数 ヒストグラム有無 統計情報収集所要時間
1 1 無し 00:00:01.60
有り 00:00:01.34
5 無し 00:00:03.27
有り 00:00:03.35
10 無し 00:00:04.43
有り 00:00:05.49
15 無し 00:00:06.52
有り 00:00:07.21
2 1 無し 00:00:01.68
有り 00:00:01.29
5 無し 00:00:03.09
有り 00:00:03.07
10 無し 00:00:04.75
有り 00:00:05.23
15 無し 00:00:06.91
有り 00:00:07.14
3 1 無し 00:00:01.61
有り 00:00:01.30
5 無し 00:00:03.05
有り 00:00:05.17
10 無し 00:00:04.44
有り 00:00:05.24
15 無し 00:00:06.48
有り 00:00:07.15

セット数で平均してグラフにすると下記の通り。

image.png

インデックス (ヒストグラム対象) が 1 の時に、ヒストグラム有りの方が時間が短くなっているのは謎ですが、残りのケースでは 10～20%程度長くなっていました。
想像よりも小さい影響だったので、ヒストグラムは積極的に収集して良いかもしれません :smiley:

2
1
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
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?