DBMS_STATSパッケージ の estimate_percentパラメータ を AUTO_SAMPLE_SIZE と 100(100%) に設定した際の PGA使用量 を比較してみる。(Oracle Database)

表題の通り、DBMS_STATSパッケージ の estimate_percentパラメータ を変更した時の PGA使用量 を比較してみたやで。
彡(ﾟ)(ﾟ)

1. 検証を行った環境

今回は Autonomous Database の ATP-S の環境を使用しました。必ずしも Autonomous Database の
必要は無いんですが、IO性能が良いのと SSB(Star Schema Bench) のサンプルデータがプリセットされているので。
(ﾟεﾟ )

2. サンプルデータの作成(SSB.CUSTOMER表の複製)

Autonomous Database には SSB(Star Schema Bench) のサンプルスキーマがプリセットされているので、
そこの CUSTOMER表 を ADMINスキーマ に CTAS でコピーして、サンプルデータとします。

SET TIME ON;
SET TIMING ON;

DROP TABLE ADMIN.CUSTOMER;
CREATE TABLE ADMIN.CUSTOMER PARALLEL 12 AS SELECT /*+ PARALLEL(12) */ * FROM SSB.CUSTOMER;
ALTER TABLE ADMIN.CUSTOMER PARALLEL 1;

COLUMN SEGMENT_NAME FORMAT A30;
SELECT SEGMENT_NAME, BYTES FROM DBA_SEGMENTS WHERE OWNER = 'ADMIN' AND SEGMENT_NAME = 'CUSTOMER';

SELECT /*+ PARALLEL(12) */ COUNT(*) FROM ADMIN.CUSTOMER;

実行結果は下記の通り、3000万件のサンプルデータができました。

Table ADMIN.CUSTOMERが削除されました。

経過時間: 00:00:00.228

Table ADMIN.CUSTOMERは作成されました。

経過時間: 00:00:13.052

Table ADMIN.CUSTOMERが変更されました。

経過時間: 00:00:00.224

SEGMENT_NAME                        BYTES
------------------------------ ----------
CUSTOMER                       3965714432

経過時間: 00:00:00.215

  COUNT(*)
----------
  30000000

経過時間: 00:00:00.400

3. estimate_percentパラメータ に AUTO_SAMPLE_SIZE を セットした際の PGA使用量

estimate_percentパラメータ に AUTO_SAMPLE_SIZE をセットした時の PGA使用量 を確認してみます。

SET TIMING ON;
COLUMN VALUE FORMAT 999,999,999,999,999;

SELECT S.SID, N.NAME, S.VALUE
  FROM V$MYSTAT   S
     , V$STATNAME N
 WHERE S.STATISTIC# = N.STATISTIC#
   AND N.NAME LIKE '%pga%';

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'ADMIN', TABNAME => 'CUSTOMER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 1);

SELECT S.SID, N.NAME, S.VALUE
  FROM V$MYSTAT   S
     , V$STATNAME N
 WHERE S.STATISTIC# = N.STATISTIC#
   AND N.NAME LIKE '%pga%';

COLUMN OWNER FORMAT A10;
COLUMN TABLE_NAME FORMAT A20;

SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN
  FROM DBA_TAB_STATISTICS
 WHERE OWNER = 'ADMIN' AND TABLE_NAME = 'CUSTOMER';

結果は下記の通り、44MB程度の PGA を使用しています。

       SID NAME                                             VALUE
---------- --------------------------------- --------------------
     37237 session pga memory                          14,311,736
     37237 session pga memory max                      16,539,960 ★統計取得前は16MB程度

経過時間: 00:00:00.217

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

経過時間: 00:01:03.802

       SID NAME                                             VALUE
---------- --------------------------------- --------------------
     37237 session pga memory                          20,209,976
     37237 session pga memory max                      44,327,224 ★44MB程度を使用

経過時間: 00:00:00.203

OWNER      TABLE_NAME             NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- -------------------- ---------- ---------- -----------
ADMIN      CUSTOMER               30000000     484096         108

経過時間: 00:00:00.405

4. estimate_percentパラメータ に 100(100%) を セットした際の PGA使用量

SET TIMING ON;
COLUMN VALUE FORMAT 999,999,999,999,999;

SELECT S.SID, N.NAME, S.VALUE
  FROM V$MYSTAT   S
     , V$STATNAME N
 WHERE S.STATISTIC# = N.STATISTIC#
   AND N.NAME LIKE '%pga%';

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'ADMIN', TABNAME => 'CUSTOMER', estimate_percent => 100, degree => 1);

SELECT S.SID, N.NAME, S.VALUE
  FROM V$MYSTAT   S
     , V$STATNAME N
 WHERE S.STATISTIC# = N.STATISTIC#
   AND N.NAME LIKE '%pga%';

COLUMN OWNER FORMAT A10;
COLUMN TABLE_NAME FORMAT A20;

SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN
  FROM DBA_TAB_STATISTICS
 WHERE OWNER = 'ADMIN' AND TABLE_NAME = 'CUSTOMER';

結果は下記の通り、730MB程度の PGA を使用しています。

       SID NAME                                           VALUE
---------- ------------------------------- --------------------
     37237 session pga memory                        19,882,296
     37237 session pga memory max                    43,671,864 ★統計取得前は43MB程度

経過時間: 00:00:00.187

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

経過時間: 00:04:27.525

       SID NAME                                           VALUE
---------- ------------------------------- --------------------
     37237 session pga memory                        19,423,544
     37237 session pga memory max                   731,341,112 ★730MB程度を使用

経過時間: 00:00:00.205

OWNER      TABLE_NAME             NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- -------------------- ---------- ---------- -----------
ADMIN      CUSTOMER               30000000     484096         108

経過時間: 00:00:00.462

5. まとめ

AUTO_SAMPLE_SIZE最高や！下記ブログに記載が有りますが、approximate NDV algorithm というのが効いているみたいです。

How does AUTO_SAMPLE_SIZE work in Oracle Database 12c?
https://blogs.oracle.com/optimizer/how-does-auto_sample_size-work-in-oracle-database-12c

AUTO_SAMPLE_SIZE をどんどん使用して下さいね彡(^)(^)