今回はPL/SQLで用意されているファンクションを利用して実行計画を取得してみます。
前回と同様にindexの有るTBL1、indexの無いTBL2でパフォーマンスを比較します。

[oracle@db01 ~]$ sqlplus / as sysdba

SQL> alter system FLUSH BUFFER_CACHE;

システムが変更されました。

SQL> connect user1/password
接続されました。
SQL> set timing on
SQL> select count(*) from TBL1;

  COUNT(*)
----------
   1000000

経過: 00:00:00.53

indexが有る表でcount(*)を実行した結果

SQL> connect / as sysdba
接続されました。
SQL> alter system FLUSH BUFFER_CACHE;

システムが変更されました。

SQL> connect user1/password
接続されました。
SQL> set timing on
SQL> select count(*) from TBL2;

  COUNT(*)
----------
   1000000

経過: 00:00:02.68

indexが無い表でcount(*)を実行した結果
明らかにパフォーマンスに違いがあります。

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION

経過: 00:00:00.83

実行計画を取得するためにdbms_xplan.display_cursor()というファンクションを利用しますが、権限の設定が追加で必要です。

SQL> connect / as sysdba
接続されました。
SQL> select SYNONYM_NAME, TABLE_NAME
       from DBA_SYNONYMS
  3        where SYNONYM_NAME = 'V$SESSION';

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
V$SESSION                      V_$SESSION

経過: 00:00:00.39

V$SESSIONはV_$SESSIONのシノニムです。
そのため権限を付与するのはV_$SESSIONになります。

grant select on V_$SESSION to user1;
grant select on V_$SQL_PLAN to user1;
grant select on V_$SQL to user1;
grant select on V_$SQL_PLAN_STATISTICS_ALL to user1;

上記のように権限を付与します。

SQL> connect user1/password
接続されました。
SQL> select count(*) from TBL1;

  COUNT(*)
----------
   1000000

経過: 00:00:00.04
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  843hhudrk4cwr, child number 0
-------------------------------------
select count(*) from TBL1

Plan hash value: 2904669057

------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |       |   573 (100)|          |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   2 |   INDEX FAST FULL SCAN| PK_TBL1_COL1 |  1000K|   573   (2)| 00:00:07 |
------------------------------------------------------------------------------


14行が選択されました。

経過: 00:00:00.31

count(*)のSQLを実行した後、dbms_xplan.display_cursor()で実行計画を取得します。
dbms_xplan.display_cursor()はデフォルトでは直前に実行したSQLの実行計画を取得します。

SQL> select count(*) from TBL2;

  COUNT(*)
----------
   1000000

経過: 00:00:02.35
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  61z00xm8cw770, child number 0
-------------------------------------
select count(*) from TBL2

Plan hash value: 2093157378

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |  4225 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   2 |   TABLE ACCESS FULL| TBL2 |  1000K|  4225   (1)| 00:00:51 |
-------------------------------------------------------------------


14行が選択されました。

経過: 00:00:00.01

同じようにTBL2をcount(*)した時の実行計画を取得します。
索引の無いTBL2をcount(*)した時は表全体をスキャンしたのに対して、索引の有るTBL1をcount(*)した時は索引全体をスキャンします。
表よりも索引の方がサイズは小さいので、その違いがcount(*)のパフォーマンスの違いです。

select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS
        from USER_SEGMENTS
       where SEGMENT_NAME in ('PK_TBL1_COL1','TBL2');

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME                    BLOCKS
------------------------------ ------------------ ------------------------------ ----------
PK_TBL1_COL1                   INDEX              TBS_BIG                              2176
TBL2                           TABLE              TBS_BIG                             16384

実際のブロックサイズも確認してみました。