Oracle SPMは実行計画を評価する仕組みで、実行計画を承認と未承認に分けます。
未承認の実行計画は評価して承認することで、その実行計画が利用されます。
create table test_table(seq_no number(10,0), flg number(10,0));
BEGIN
FOR i IN 1..10000 LOOP
insert into test_table values(i,0);
commit;
END LOOP;
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'TEST'
,tabname => 'TEST_TABLE'
);
END;
/
SPMの検証用にテストテーブルを作成し、統計情報を取得します。
SQL> alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;
システムが変更されました。
SQL> show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
初期化パラメーターの「optimizer_capture_sql_plan_baselines」をtrueに設定することで、実行計画が自動的にSYSAUX内のベースラインという専用領域に格納されます。
SQL> show parameter OPTIMIZER_USE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_sql_plan_baselines boolean TRUE
格納された実行計画を利用するには「optimizer_use_sql_plan_baselines」をtrueに設定する必要がありますが、デフォルトでtrueになっています。
SQL> set autotrace trace
SQL> select * from test_table where seq_no = 1;
実行計画
----------------------------------------------------------
Plan hash value: 3979868219
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_TABLE | 1 | 6 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SEQ_NO"=1)
統計
----------------------------------------------------------
5 recursive calls
4 db block gets
25 consistent gets
0 physical reads
580 redo size
630 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
早速、SQLを実行して、実行計画を作成してみます。
先程のテストテーブルに対してSELECTを実行します。
indexが作成されていないので、テーブルスキャンという実行計画が作成されて利用されます。
同じ実行計画が２回利用されないと専用領域に格納されないので、この時点では専用領域に格納されていません。
SQL> set autotrace trace
SQL> select * from test_table where seq_no = 1;
実行計画
----------------------------------------------------------
Plan hash value: 3979868219
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_TABLE | 1 | 6 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SEQ_NO"=1)
Note
-----
- SQL plan baseline "SQL_PLAN_b9yjqq5k6nrjr1dfd7a6a" used for this statement
統計
----------------------------------------------------------
25 recursive calls
57 db block gets
38 consistent gets
0 physical reads
7108 redo size
630 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
もう一回同じSQLを実行します。
「Note」に書かれているように、今度は実行計画がベースラインに格納され、利用されたことがわかります。
SQL> SELECT sql_text,sql_handle
2 FROM dba_sql_plan_baselines
3 WHERE plan_name='SQL_PLAN_b9yjqq5k6nrjr1dfd7a6a';
SQL_TEXT
--------------------------------------------------------------------------------
SQL_HANDLE
--------------------------------------------------------------------------------
select * from test_table where seq_no = 1
SQL_b4fa36b1646a5e37
実際にベースラインに格納されたことを「dba_sql_plan_baselines」ビューで確認します。
SQL> select * from table(
2 dbms_xplan.display_sql_plan_baseline(
3 plan_name=>'SQL_PLAN_b9yjqq5k6nrjr1dfd7a6a',
4 format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_b4fa36b1646a5e37
SQL text: select * from test_table where seq_no = 1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_b9yjqq5k6nrjr1dfd7a6a Plan id: 503151210
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3979868219
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TEST_TABLE |
----------------------------------------
20行が選択されました。
さらに、ベースラインに格納された実行計画の詳細はdbms_xplan.display_sql_plan_baselineプロシージャで格納することが出来ます。
「Accepted: YES」となっていますが、最初に格納された実行計画は自動的に承認され、利用されます。
次回は異なる実行計画を作成した際のSPMの動きを確認してみます。
未承認の実行計画は評価して承認することで、その実行計画が利用されます。
create table test_table(seq_no number(10,0), flg number(10,0));
BEGIN
FOR i IN 1..10000 LOOP
insert into test_table values(i,0);
commit;
END LOOP;
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'TEST'
,tabname => 'TEST_TABLE'
);
END;
/
SPMの検証用にテストテーブルを作成し、統計情報を取得します。
SQL> alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;
システムが変更されました。
SQL> show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
初期化パラメーターの「optimizer_capture_sql_plan_baselines」をtrueに設定することで、実行計画が自動的にSYSAUX内のベースラインという専用領域に格納されます。
SQL> show parameter OPTIMIZER_USE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_sql_plan_baselines boolean TRUE
格納された実行計画を利用するには「optimizer_use_sql_plan_baselines」をtrueに設定する必要がありますが、デフォルトでtrueになっています。
SQL> set autotrace trace
SQL> select * from test_table where seq_no = 1;
実行計画
----------------------------------------------------------
Plan hash value: 3979868219
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_TABLE | 1 | 6 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SEQ_NO"=1)
統計
----------------------------------------------------------
5 recursive calls
4 db block gets
25 consistent gets
0 physical reads
580 redo size
630 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
早速、SQLを実行して、実行計画を作成してみます。
先程のテストテーブルに対してSELECTを実行します。
indexが作成されていないので、テーブルスキャンという実行計画が作成されて利用されます。
同じ実行計画が２回利用されないと専用領域に格納されないので、この時点では専用領域に格納されていません。
SQL> set autotrace trace
SQL> select * from test_table where seq_no = 1;
実行計画
----------------------------------------------------------
Plan hash value: 3979868219
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_TABLE | 1 | 6 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SEQ_NO"=1)
Note
-----
- SQL plan baseline "SQL_PLAN_b9yjqq5k6nrjr1dfd7a6a" used for this statement
統計
----------------------------------------------------------
25 recursive calls
57 db block gets
38 consistent gets
0 physical reads
7108 redo size
630 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
もう一回同じSQLを実行します。
「Note」に書かれているように、今度は実行計画がベースラインに格納され、利用されたことがわかります。
SQL> SELECT sql_text,sql_handle
2 FROM dba_sql_plan_baselines
3 WHERE plan_name='SQL_PLAN_b9yjqq5k6nrjr1dfd7a6a';
SQL_TEXT
--------------------------------------------------------------------------------
SQL_HANDLE
--------------------------------------------------------------------------------
select * from test_table where seq_no = 1
SQL_b4fa36b1646a5e37
実際にベースラインに格納されたことを「dba_sql_plan_baselines」ビューで確認します。
SQL> select * from table(
2 dbms_xplan.display_sql_plan_baseline(
3 plan_name=>'SQL_PLAN_b9yjqq5k6nrjr1dfd7a6a',
4 format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_b4fa36b1646a5e37
SQL text: select * from test_table where seq_no = 1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_b9yjqq5k6nrjr1dfd7a6a Plan id: 503151210
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3979868219
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TEST_TABLE |
----------------------------------------
20行が選択されました。
さらに、ベースラインに格納された実行計画の詳細はdbms_xplan.display_sql_plan_baselineプロシージャで格納することが出来ます。
「Accepted: YES」となっていますが、最初に格納された実行計画は自動的に承認され、利用されます。
次回は異なる実行計画を作成した際のSPMの動きを確認してみます。
コメント