表題の通り、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 をどんどん使用して下さいね彡(^)(^)