近似値計算の性能

近似値を計算する関数で機能を確認しましたが、Oracle Database 18c の近似値計算関数について、性能やリソース使用量を少しだけ検証しました。生の実行時間を出すのは差しさわりがありそうなので、時間については相対値で書いています。

準備

検証する関数は APPROX_COUNT_DISTINCT と COUNT(DISTINCT) です。以下のテーブルを作成し、32,000,000 レコードを格納、c1 列は一意な値が 1,000,000 レコード格納されています。

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

Table created.

検証方法

以下の方法で検証を行いました。

SID の取得

カレントセッションのSIDを取得します。

SQL> SELECT SYS_CONTEXT('USERENV', 'SID') FROM DUAL;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
9

SQLトレースの取得

SQL トレースを有効にします。トレースを有効にする方法はいくつもありますが、最も簡単な ALTER SESSION 文で実行します。
接続ユーザーに ALTER SESSION システム権限が必要です。

SQL> ALTER SESSION SET SQL_TRACE=TRUE;

Session altered.

検証対象のSQL 文を実行します。

SQL> SELECT COUNT(DISTINCT c1) FROM data1;

COUNT(DISTINCTC1)
-----------------
          1000000

または

SQL> SELECT APPROX_COUNT_DISTINCT(c1) FROM data1;

APPROX_COUNT_DISTINCT(C1)
-------------------------
                   971092

PGA 使用量の取得

V$SESSTAT ビューを検索し、セッションが使用した PGA 量を確認します。先ほど取得した SID を指定しています。

SQL> SELECT name, value FROM V$SESSTAT s, V$STATNAME n
     WHERE  sid = 9
     AND n.statistic# = s.statistic#
     AND name LIKE '%pga%'
     ORDER BY value DESC ;

その後、セッションを終了します。

トレース・ファイルの整形

tkprof コマンドでトレース・ファイルを整形します。トレース・ファイルは ${ORACLE_BASE}/diag/rdbms/{DB_NAME}/{INSTANCE_NAME}/trace ディレクトリに作成されます。

$ tkprof count_distinct.trc count_distinct.txt explain=scott/password

TKPROF: Release 18.0.0.0.0 - Development on Wed Mar 6 15:36:38 2019

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

実行結果

CPU 時間

バッファ・プールからデータを取得する部分には違いが無いため、どちらの関数も時間はほぼ同じでした。
一方で計算に使用する CPU 時間は、APPROX_COUNT_DISTINCT 関数の方が約 50 % 削減されています。

COUNT(DISTINCT) 文の実行計画

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=70555 pr=0 pw=0 time=***** us starts=1)
   1000000    1000000    1000000   VIEW  VW_DAG_0 (cr=70555 pr=0 pw=0 time=***** us starts=1 cost=3487 size=6606080 card=508160)
   1000000    1000000    1000000    HASH GROUP BY (cr=70555 pr=0 pw=0 time=****** us starts=1 cost=3487 size=2540800 card=508160)
  32000000   32000000   32000000     TABLE ACCESS FULL DATA1 (cr=70555 pr=0 pw=0 time=***** us starts=1 cost=1404 size=5000000 card=1000000)

APPROX_COUNT_DISTINCT 関数の実行計画

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE APPROX (cr=70555 pr=0 pw=0 time=***** us starts=1)
  32000000   32000000   32000000   TABLE ACCESS FULL DATA1 (cr=70555 pr=0 pw=0 time=**** us starts=1 cost=1404 size=5000000 card=1000000)

PGA 量

メモリー使用量も取得してみました。PGA の最大使用量は、約 7% まで削減できています。

COUNT(DISTINCT) 文のメモリー使用量

NAME                                                             VALUE     
---------------------------------------------------------------- ----------
session pga memory max                                             54461688
session pga memory                                                  4130040

APPROX_COUNT_DISTINCT 関数のメモリー使用量

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory max                                              3867896
session pga memory                                                  3867896

これらの検証から、Oracle Database 18c の近似値計算関数は、プロセッサ使用時間やメモリー要求も小さくできることがわかります。

近似値計算関数の性能を検証する（Oracle Database 18c)