オラクる。

oracle専門ブログにしてみようかな~っと

2016年05月

共有プールは実行済みSQLの実行計画を格納します。
再度、同様のSQLが実行される場合、共有プール内の実行計画を再利用することで、実行計画策定の負荷を減らすことが出来ます。
今回はv$SQLビューで共有プール内を覗いてみます。

SQL> alter system FLUSH SHARED_POOL;

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


まずは、共有プールを綺麗にします。

SQL> select /* practiceSQL1 */ COL1 || ':' || COL2 "Recors" from TBL1 where ROWNUM <=3;

Recors
--------------------------------------------------------------------------------
199:199AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAA

200:200AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAA

201:201AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAA


適当にSQLを実行してみます。
これで共有プール内にSQL文の実行計画が格納されたはずです。

[oracle@db01 ~]$ sqlplus / as sysdba


SQL> set pagesize 100 linesize 120
SQL> col SQL_TEXT for a90
SQL> select SQL_ID, CHILD_NUMBER, SQL_TEXT
  2    from V$SQL
  3  where SQL_TEXT like 'select /* practiceSQL1 */ COL1 || '' : '' || %' ;

SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------------------------------------------------------------------
2vrywhbx1jxtb            0 select /* practiceSQL1 */ COL1 || ' : ' || COL2 "Record" from TBL2 where ROWNUM <=3


v$SQLに実行したSQLが格納されていました。
このSQLIDを頼りに色々と調べてみます。

set pagesize 100 linesize 120
col LAST_LOAD_TIME      for a20
col PARSING_SCHEMA_NAME for a4
col MODULE              for a8
select SQL_ID, CHILD_NUMBER, LAST_LOAD_TIME, EXECUTIONS, PARSING_SCHEMA_NAME, MODULE, CPU_TIME, ELAPSED_TIME
  from V$SQL
 where SQL_ID = '2vrywhbx1jxtb';

SQL_ID        CHILD_NUMBER LAST_LOAD_TIME       EXECUTIONS PARS MODULE     CPU_TIME ELAPSED_TIME
------------- ------------ -------------------- ---------- ---- -------- ---------- ------------
2vrywhbx1jxtb            0 2016-05-31/00:40:33           1 USER SQL*Plus       5999        17737
                                                           1


さらにv$SQLを調べてみると、共有プールに書き込まれた(ロードされた)時間、合計のCPU時間、経過時間がなどがわかります。

さらに、そんな裏で先程のSQLを5回ほど実行してみます。

select SQL_ID, EXECUTIONS, PARSE_CALLS,
       CPU_TIME, ELAPSED_TIME, ELAPSED_TIME - CPU_TIME,
       USER_IO_WAIT_TIME, DISK_READS,PHYSICAL_READ_BYTES
  from V$SQL
 where SQL_ID = '2vrywhbx1jxtb';

SQL_ID        EXECUTIONS PARSE_CALLS   CPU_TIME ELAPSED_TIME ELAPSED_TIME-CPU_TIME USER_IO_WAIT_TIME DISK_READS
------------- ---------- ----------- ---------- ------------ --------------------- ----------------- ----------
PHYSICAL_READ_BYTES
-------------------
2vrywhbx1jxtb          6           3       7999        39166                 31167             31218        168
            1376256


EXECUTIONSが6になっていることがわかります。
これが、ソフト(共有プール内にSQLが存在)、ハード(共有プール内にSQLが存在しない)の合計回数です。
またCPU時間、経過時間が単純に6倍になっていないので、共有プール内に存在しているSQLを読み込むことでCPU使用率が削減されていることがわかります。

今回は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


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

OracleのAutotrace機能を利用して、Index有る無しの実行計画の違いを確認してみます。
まず、Autotrace機能を利用する場合、実行ユーザーに必要なロールを付与する必要があります。

[oracle@db01 ~]$ sqlplus / as sysdba

SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;

ロールが削除されました。

SQL> create role plustrace;

ロールが作成されました。

SQL>
SQL> grant select on v_$sesstat to plustrace;

権限付与が成功しました。

SQL> grant select on v_$statname to plustrace;

権限付与が成功しました。

SQL> grant select on v_$mystat to plustrace;

権限付与が成功しました。

SQL> grant plustrace to dba with admin option;

権限付与が成功しました。

SQL>
SQL> set echo off

SQLスクリプトでPLUSTRACEというロールを作成します。

SQL> grant PLUSTRACE to user1;

権限付与が成功しました。

そのロールをautotraceを実行するユーザーに付与します。

SQL> set linesize 200
SQL> set autotrace on

SQL> select * from TBL1 where col1 = 10000;

      COL1 COL2
---------- ----------------------------------------------------------------------------------------------------
     10000 10000AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


実行計画
----------------------------------------------------------
Plan hash value: 653716635

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    65 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TBL1         |     1 |    65 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_TBL1_COL1 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL1"=10000)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          4  physical reads
          0  redo size
        580  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


さて、DBユーザーで接続し、まずはindex有りの表をSELECTした場合のトレースを確認してみます。
SQLステートメントの所からindexをスキャンしてrowidを取得し、そのrowidを読み込んでいることがわかります。
また読み込み量「physical reads」「consistent gets」は少ないことがわかります。

SQL> select * from TBL2 where col1 = 10000;

      COL1 COL2
---------- ----------------------------------------------------------------------------------------------------
     10000 10000AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


実行計画
----------------------------------------------------------
Plan hash value: 978686403

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   171 | 11115 |  4226   (1)| 00:00:51 |
|*  1 |  TABLE ACCESS FULL| TBL2 |   171 | 11115 |  4226   (1)| 00:00:51 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"=10000)

Note
-----
   - dynamic sampling used for this statement (level=2)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      15390  consistent gets
      15386  physical reads
          0  redo size
        712  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

一方、Index無しの表をSELECTした場合のトレース結果です。
SQLステートメントから表をフルスキャンした後に結果を取得していることがわかります。
そのため、読み込み量「physical reads」「consistent gets」が膨大な量になっていることがわかります。

これがindex有り無しの読み込み時間の違いの原因です。

索引の有る無しによるパフォーマンス、実行計画の違いを確認してみます。

SQL> create table TBL1 (col1 number, col2 varchar2(100)) segment creation immediate tablespace TBS_BIG;
表が作成されました。

SQL> alter table TBL1 add constraint PK_TBL1_COL1 primary key(COL1);

表が変更されました。


TBL1は主キー索引を持ちます。

SQL> create table TBL2 as select * from TBL1;

表が作成されました。


TBL2はTBL1と同じデータを持ちます。
ただし、索引は持ちません。

[oracle@db01 ~]$ sqlplus / as sysdba

SQL> show parameter filesystemio

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none
SQL> alter system set FILESYSTEMIO_OPTIONS='SETALL' scope=spfile;

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

SQL> shutdown immediate;
SQL> startup

SQL> show parameter filesystemio

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      SETALL


パフォーマンスを比較する前に、OSのファイルシステムキャッシュを利用しない設定を行ないます。
これでダイレクトにディスクへの書き込みを行ないます。

[oracle@db01 ~]$ sqlplus / as sysdba

SQL> alter system FLUSH BUFFER_CACHE;

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

また、各テストを実施する前に、DBのバッファキャッシュを空にします。
各テストの実施条件を一緒にするためです。

SQL> set timing on
SQL> select * from TBL1 where col1 = 10000;

      COL1
----------
COL2
--------------------------------------------------------------------------------
     10000
10000AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAA


経過: 00:00:00.15


まずは索引の有る表でSELECTを実行します。
実行時間0.15秒

[oracle@db01 ~]$ sqlplus / as sysdba

SQL> alter system FLUSH BUFFER_CACHE;

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

もう一個の表を確認する前にバッファキャッシュをクリアします。

SQL> set timing on
SQL> select * from TBL2 where col1 = 10000;

      COL1
----------
COL2
--------------------------------------------------------------------------------
     10000
10000AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAA


経過: 00:00:02.91


索引の無い表でSELECTを実行します。
時間は2.91秒
明らかにパフォーマンスが違います。

次はそれぞれの実行計画を確認してみます。

Oracleのダイレクトパスインサートを試してみます。
DBバッファキャッシュを経由しない、REDOログを生成しないため、高速にデータを表にinsertすることが出来ます。
しかし、表に表ロックを掛けてしまう→他の更新、挿入などができなくなる。
HWM以上にデータをインサートする→表の使用効率が悪くなる。
・・・などのデメリットがあります。
恐らく初期データ移行なんかで利用するべきでしょう。

create bigfile tablespace TBS_BIG datafile '/data/tbs_big.dbf' size 500M;
create table TBL2 (col1 number, col2 varchar2(100)) segment creation immediate tablespace TBS_BIG;
SQL> insert into TBL2
  2    select i+j,rpad(to_char(i+j),100,'A')
  3      from  (
  4             with DATA2(j) as (
  5                               select 0 j from DUAL
  6                                 union all
  7                               select j+1000 from DATA2 where j < 999000
  8                              )
  9             select j from DATA2
 10            ),
 11            (
 12             with DATA1(i) as (
 13                               select 1 i from DUAL
 14                                 union all
 15                               select i+1 from DATA1 where i < 1000
 16                              )
 17             select i from DATA1
 18            );

1000000行が作成されました。


テスト用に100万行のデータが挿入された表を作成しました。

SQL> create table TBL3 as select * from TBL2;

表が作成されました。

経過: 00:00:17.04


例えば、create table ~ as select ~文で表から表へのコピーを行った場合は17秒で完了しました。

SQL> insert /*+APPEND */ into TBL3 select * from TBL2 ;

1000000行が作成されました。

経過: 00:00:11.84

ただし、ダイレクトパスインサート:insert文に「/*+APPEND */を」付与してinsertを実行した場合は、11秒で完了
明らかにダイレクトパスインサートの方が高速です。

そのメリットとデメリットを充分に考慮にいれて利用るべき機能です。

このページのトップヘ