オラクる。

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

カテゴリ:oracle > 12c

未承認の実行計画を自動展開タスクで承認する手順です。

SET SERVEROUTPUT ON
DECLARE
  l_return VARCHAR2(32767);
BEGIN
  l_return := DBMS_SPM.create_evolve_task(sql_handle => 'SQL_7b76323ad90440b9');
  DBMS_OUTPUT.put_line('Task Name; ' || l_return);
END;
/

Task Name; タスク_443

PL/SQLプロシージャが正常に完了しました。


SQL HANDLEを指定した評価タスクを作成します。
タスクID「タスク_443」が次に必要になってきます。

SET SERVEROUTPUT ON
DECLARE
  l_return VARCHAR2(32767);
BEGIN
  l_return := DBMS_SPM.execute_evolve_task(task_name => 'タスク_443');
  DBMS_OUTPUT.put_line('Execution Name; ' || l_return);
END;
/

Execution Name; EXEC_467

PL/SQLプロシージャが正常に完了しました。


タスクIDを指定して作成したタスクを実行します。
今度は実行ID「EXEC_467」が必要になります。

SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100

SELECT DBMS_SPM.report_evolve_task(task_name => 'タスク_443', execution_name => 'EXEC_467') AS output
FROM   dual;

OUTPUT
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
------------------------------------------------------------------------
---------------------

 Task Information:
 ---------------------------------------------
 Task Name            : タスク_443
 Task Owner           : SYS
 Execution Name       : EXEC_467
 Execution Type       : SPM EVOLVE
 Scope                : COMPREHENSIVE
 Status               : COMPLETED
 Started              : 10/23/2016 16:28:21
 Finished             : 10/23/2016 16:28:21
 Last Updated         : 10/23/2016 16:28:21
 Global Time Limit    : 2147483646
 Per-Plan Time Limit  : UNUSED
 Number of Errors     : 0
------------------------------------------------------------------
---------------------------

SUMMARY SECTION
-------------------------------------------------------
--------------------------------------
  Number of plans processed  : 1
  Number of findings         : 1
  Number of recommendations  : 1
  Number of errors           : 0
-----------------------------------------------------------------------
----------------------

DETAILS SECTION
------------------------------------------------------------
---------------------------------
 Object ID          : 2

 Test Plan Name     : SQL_PLAN_7qxjk7bch8h5t17ba446f

 Base Plan Name     : SQL_PLAN_7qxjk7bch8h5tb65c37c8

 SQL Handle         : SQL_7b76323ad90440b9

 Parsing Schema     : TEST

 Test Plan Creator  : TEST

 SQL Text           : SELECT description FROM spm_test_
tab WHERE id = 99

Execution Statistics:
-----------------------------
                    Base Plan                     Test Plan

                    ----------------------------  ----------------
------------
 Elapsed Time (s):  .000018                       .000003

 CPU Time (s):      .000011                       0

 Buffer Gets:       4                             0

 Optimizer Cost:    14                            2

 Disk Reads:        0                             0

 Direct Writes:     0                             0

 Rows Processed:    0                             0

 Executions:        10                            1
0


FINDINGS SECTION
----------------------------------------------------
-----------------------------------------

Findings (1):
-----------------------------
 1. 計画は0.02000秒で検証されました。検証されたパフォーマンスが、ベースライン計画のパフォーマンスを1
5.00370倍上回ったため、利点基準に達しま
    した。


Recommendation:
-----------------------------
 Consider accepting the plan. Execute

 dbms_spm.accept_sql_plan_baseline(task_name => 'タスク_443', object_id => 2
,
 task_owner => 'SYS');



EXPLAIN PLANS SECTION
--------------------------------------------------------------
-------------------------------

Baseline Plan
-----------------------------
 Plan Id          : 365
 Plan Hash Value  : 3059496904

----------------------------------------------------------
-------------------
| Id  | Operation           | Name         | Rows | Byt
es | Cost | Time     |
----------------------------------------------------
-------------------------
|   0 | SELECT STATEMENT    |              |    1 |    25 |   14 | 00:00:0
1 |
| * 1 |   TABLE ACCESS FULL | SPM_TEST_TAB |    1 |    25 |   14 | 00:0
0:01 |
--------------------------------------------------------------------
---------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ID"=99)


Test Plan
-----------------------------
 Plan Id          : 366
 Plan Hash Value  : 398083183

-------------------------------------------------------------------
----------------------------
| Id  | Operation                             | Name         | Rows | B
ytes | Cost | Time     |
--------------------------------------------------
---------------------------------------------
|   0 | SELECT STATEMENT                      |
       |    1 |    25 |    2 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | SPM_TEST_T
AB |    1 |    25 |    2 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SPM_TEST_IDX |
    1 |       |    1 | 00:00:01 |
------------------------------------------------------------------
-----------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=99)

---------------------------------------------------
------------------------------------------


評価タスクの実行結果を確認します。
インデックススキャンとテーブルスキャンのコストが比較され、インデックススキャンの方がコストが低いと判断されました。

SET SERVEROUTPUT ON
DECLARE
  l_return NUMBER;
BEGIN
  l_return := DBMS_SPM.implement_evolve_task(task_name => 'タスク_443');
  DBMS_OUTPUT.put_line('Plans Accepted: ' || l_return);
END;
/

Plans Accepted: 1

PL/SQLプロシージャが正常に完了しました。


評価タスクで生成された推奨事項を受け入れます。
これでインデックススキャンの実行計画が承認されました。

SQL> SELECT sql_handle, plan_name, enabled, accepted
  2  FROM dba_sql_plan_baselines
  3  WHERE sql_handle = 'SQL_7b76323ad90440b9';

SQL_HANDLE           PLAN_NAME                      ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t17ba446f YES YES
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES

ACCEPTED=NO→YESに変化したことがわかります。

SET AUTOTRACE TRACE LINESIZE 130
SELECT description
FROM   spm_test_tab
WHERE  id = 99;

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

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SPM_TEST_TAB |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SPM_TEST_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("ID"=99)

Note
-----
   - SQL plan baseline "SQL_PLAN_7qxjk7bch8h5t17ba446f" used for this statement


統計
----------------------------------------------------------
         13  recursive calls
         13  db block gets
         14  consistent gets
          0  physical reads
       3416  redo size
        591  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


再度、インデックス作成列を条件に利用した検索を実行します。
今度はインデックススキャンとなったことがわかります。

前回に作成したテスト表にインデックスを作成して、実行計画、ベースラインがどう変化するかを確認します。

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

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

SQL> CONN test/password
接続されました。
SQL> CREATE INDEX spm_test_idx ON spm_test_tab(id);

索引が作成されました。

SQL> EXEC DBMS_STATS.gather_table_stats(USER,'SPM_TEST_TAB', cascade=>TRUE);

PL/SQLプロシージャが正常に完了しました。


共有プールを空にした後、インデックスの作成、統計情報の収集を行ないます。

SQL> SET AUTOTRACE TRACE
SQL> SELECT description
  2  FROM   spm_test_tab
  3  WHERE  id = 99;


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

--------------------------------------------------------------------------------
--

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
 |

--------------------------------------------------------------------------------
--

|   0 | SELECT STATEMENT  |              |     1 |    25 |    14   (0)| 00:00:01
 |

|*  1 |  TABLE ACCESS FULL| SPM_TEST_TAB |     1 |    25 |    14   (0)| 00:00:01
 |

--------------------------------------------------------------------------------
--


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

   1 - filter("ID"=99)

Note
-----
   - SQL plan baseline "SQL_PLAN_7qxjk7bch8h5tb65c37c8" used for this statement


統計
----------------------------------------------------------
        755  recursive calls
         58  db block gets
        876  consistent gets
          1  physical reads
      10292  redo size
        584  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         49  sorts (memory)
          0  sorts (disk)
          1  rows processed


インデックスを作成した列を検索条件として利用する、SELECTを実行し、実行計画を確認します。
「TABLE ACCESS FULL」よりテーブルスキャンが行なわれていることがわかります。
さらに「SQL plan baseline ~ used」よりベースラインの実行計画が利用されていることがわかります。

SQL> CONN / as sysdba
接続されました。

SQL> SELECT sql_handle, plan_name, enabled, accepted
  2  FROM dba_sql_plan_baselines
  3  WHERE sql_handle = 'SQL_7b76323ad90440b9';

SQL_HANDLE           PLAN_NAME                      ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t17ba446f YES NO
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES


ベースラインの確認をしてみましょう。
「SQL_PLAN_7qxjk7bch8h5t17ba446f」が追加されました。
これがインデックススキャンの実行計画になります。
ACCEPTED列がNOなので、承認されていない実行計画になり、まだ利用することが出来ません。
そのために、テーブルスキャンになったのです。

次回はその実行計画を承認してみましょう。

12cよりSQL計画管理は自動展開タスクにより検証が自動化されました。
自動展開タスクはメンテナンスウィンドウを使用したアドバイザタスクとして実行されるか、DBMS_SPMパッケージのプロシージャによって、手動でタスクを作成し、実行します。

SQL> COLUMN parameter_name FORMAT a25
SQL> COLUMN parameter_value FORMAT a15
SQL> SELECT parameter_name, parameter_value
  2  FROM dba_advisor_parameters
  3  WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
  4  AND parameter_value != 'UNUSED'
  5  ORDER BY parameter_name;

PARAMETER_NAME            PARAMETER_VALUE
------------------------- ---------------
ACCEPT_PLANS              FALSE
DAYS_TO_EXPIRE            UNLIMITED
DEFAULT_EXECUTION_TYPE    SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE  30
JOURNALING                INFORMATION
MODE                      COMPREHENSIVE
TARGET_OBJECTS            1
TIME_LIMIT                3600
_SPM_VERIFY               TRUE

9行が選択されました。


自動展開タスクが有効化されているかは、dba_advisor_parametersのACCEPT_PLANSがTRUEになっていることを確認します。

SQL> BEGIN
  2  DBMS_SPM.set_evolve_task_parameter(
  3  task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
  4  parameter => 'ACCEPT_PLANS',
  5  value     => 'TRUE');
  6  END;
  7  /

PL/SQLプロシージャが正常に完了しました。


無効(FASLSE)になっている場合はset_evolve_task_parameterにより有効化します。

SQL> CREATE TABLE spm_test_tab (
  2  id NUMBER,
  3  description VARCHAR2(50)
  4  );

表が作成されました。

SQL> INSERT /*+ APPEND */ INTO spm_test_tab
  2  SELECT level,
  3         'Description for ' || level
  4  FROM dual
  5  CONNECT BY level <= 10000;

10000行が作成されました。

SQL> COMMIT;

コミットが完了しました。


自動展開タスクを検証するためテストテーブルを作成し、10000件のデータを挿入します。

SQL> SET AUTOTRACE TRACE
SQL> SELECT description
  2  FROM   spm_test_tab
  3  WHERE  id = 99;


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

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |    25 |    14   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| SPM_TEST_TAB |     1 |    25 |    14   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - filter("ID"=99)


統計
----------------------------------------------------------
          1  recursive calls
          3  db block gets
         53  consistent gets
         42  physical reads
        124  redo size
        584  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


10000件のデータから1件のデータを検索します。
インデックスが存在しないのでテーブルスキャンになります。

SQL> CONN / as sysdba
接続されました。
SQL> SELECT sql_id
  2  FROM v$sql
  3  WHERE plan_hash_value = 1107868462
  4  AND   sql_text NOT LIKE 'EXPLAIN%';

SQL_ID
-------------
gat6z1bc6nc2d


v$sqlビューより実行したSQLのSQL_IDをPlanのhash値より検索します。

SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
  sql_id => 'gat6z1bc6nc2d');
  DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/


該当のSQLの実行計画(Plan)をbaselineにロードします。

COLUMN sql_handle FORMAT a20
COLUMN plan_name FORMAT a30

SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%spm_test_tab%'
AND   sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE           PLAN_NAME                      ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES


実行計画がベースラインに格納されたことが、dba_sql_plan_baselinesより確認できました。

DBが利用するCPUやメモリのリソースをデータベース側で制御出来るのがリソースマネージャーです。
12cからは新しくPDB向けの機能が追加されました。
各PDBでは非CDB環境に近い形のリソースマネージャーによる制御を行なうことが出来ます。
さらにCDB単位でPDB間のリソースの割り当てを制御することが出来ます。

SQL> COLUMN NAME FORMAT A15
SQL> COLUMN RESTRICTED FORMAT A10
SQL> COLUMN OPEN_TIME FORMAT A30
SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

NAME            OPEN_MODE  RESTRICTED OPEN_TIME
--------------- ---------- ---------- ------------------------------
PDB$SEED        READ ONLY  NO         16-10-03 15:22:54.133 +09:00
PDB1            READ WRITE NO         16-10-03 15:24:16.603 +09:00
PDB2            READ WRITE NO         16-10-03 15:25:31.001 +09:00
PDB3            READ WRITE NO         16-10-03 16:32:33.375 +09:00


1CDBに3PDBが存在するデータベースを作成しました。
これらにリソースマネージャーを設定してみます。

BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;

DBMS_RESOURCE_MANAGER.create_cdb_plan(
  plan    => 'test_cdb_plan',
  comment => 'A test CDB resource plan');

DBMS_RESOURCE_MANAGER.create_cdb_plan_directive(
  plan                  => 'test_cdb_plan',
  pluggable_database    => 'pdb1',
  shares                => 3,
  utilization_limit     => 100,
  parallel_server_limit => 100);

DBMS_RESOURCE_MANAGER.create_cdb_plan_directive(
  plan                  => 'test_cdb_plan',
  pluggable_database    => 'pdb2',
  shares                => 3,
  utilization_limit     => 100,
  parallel_server_limit => 100);

DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

まず、リソースプランをプロシージャの「DBMS_RESOURCE_MANAGER.create_cdb_plan」を利用して作成します。
そのリソースプランに対して、それぞれのPDBにどのようにリソースを割り当てるのかというディレクティブを「DBMS_RESOURCE_MANAGER.create_cdb_plan_directive」を利用して作成します。

COLUMN plan FORMAT A30
COLUMN comments FORMAT A30
COLUMN status FORMAT A10
SET LINESIZE 100

SELECT plan_id,
       plan,
       comments,
       status,
       mandatory
FROM   dba_cdb_rsrc_plans
WHERE  plan = 'TEST_CDB_PLAN';

   PLAN_ID PLAN                           COMMENTS                       STATUS     MAN
---------- ------------------------------ ------------------------------ ---------- ---
     91990 TEST_CDB_PLAN                  A test CDB resource plan                  NO


作成したリソースプランはdba_cdb_rsrc_plansで確認することが出来ます。

COLUMN plan FORMAT A30
COLUMN pluggable_database FORMAT A25
SET LINESIZE 100

SELECT plan,
       pluggable_database,
       shares,
       utilization_limit AS util,
       parallel_server_limit AS parallel
FROM   dba_cdb_rsrc_plan_directives
WHERE  plan = 'TEST_CDB_PLAN'
ORDER BY pluggable_database;

PLAN                           PLUGGABLE_DATABASE            SHARES       UTIL   PARALLEL
------------------------------ ------------------------- ---------- ---------- ----------
TEST_CDB_PLAN                  ORA$AUTOTASK                                 90        100
TEST_CDB_PLAN                  ORA$DEFAULT_PDB_DIRECTIVE          1        100        100
TEST_CDB_PLAN                  PDB1                               3        100        100
TEST_CDB_PLAN                  PDB2                               3        100        100


作成したプランに紐次ぐディレクティブはdba_cdb_rsrc_plan_directivesビューで確認することが出来ます。

SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'TEST_CDB_PLAN';

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

SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      TEST_CDB_PLAN


作成したリソースプランはresource_manager_planパラメータにプラン名を設定することで有効になります。

oracle database 12cよりADR(自動診断レポジトリ)に実行されたDDLだけを抽出するDDLログが追加されました。

SQL> show parameter enable_ddl_logging

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging                   boolean     FALSE

SQL> alter system set enable_ddl_logging=true scope=both;

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

SQL> show parameter enable_ddl_logging

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging                   boolean     TRUE

DDLログを有効にするにはenable_ddl_loggingパラメータをtrueに設定します。

SQL> CREATE TABLE test1 (id NUMBER);

表が作成されました。

SQL> ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id);

表が変更されました。

SQL> DROP TABLE test1 PURGE;

表が削除されました。


色々なDDLを実行してみます。

[oracle@DB01 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/log/
[oracle@DB01 log]$ ls
ddl  ddl_orcl.log  debug  imdb  test

[oracle@DB01 log]$ cat ddl_orcl.log
Mon Oct 03 00:38:46 2016
diag_adl:drop user u1
diag_adl:alter database close immediate
diag_adl:alter database dismount
diag_adl:ALTER DATABASE OPEN
diag_adl:CREATE TABLE test1 (id NUMBER)
diag_adl:ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id)
diag_adl:DROP TABLE test1 PURGE


「$ORACLE_BASE/diag/rdbms/(DB名)/(インスタンス名)/log/」内にDDLログが出力されます。
例えばddl_orcl.logには実行したDDL文が記録されます。
時間は記録されていません。

[oracle@DB01 log]$ cd ddl
[oracle@DB01 ddl]$ cat log.xml
<msg time='2016-10-03T00:38:46.615+09:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4415:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='DB01' host_addr='172.17.9.198'
 version='1'>
 <txt>drop user u1
 </txt>
</msg>


さらにddlフォルダのlog.xmlはDDLがxml形式で記録されます。
こちらは実行時間、実行クライアント、実行DDL文が記録されます。

[oracle@DB01 ddl]$ adrci exec="set editor cat;show log -l ddl"

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
Output the results to file: /tmp/utsout_10546_13996_1.ado
[oracle@DB01 ddl]$ 2016-10-03 00:38:46.615000 +09:00
drop user u1
2016-10-03 00:39:17.929000 +09:00
alter database close immediate
2016-10-03 00:39:26.789000 +09:00
alter database dismount
2016-10-03 00:41:22.680000 +09:00
ALTER DATABASE OPEN
2016-10-03 00:42:02.616000 +09:00
CREATE TABLE test1 (id NUMBER)
2016-10-03 00:42:28.966000 +09:00
ALTER TABLE test1 ADD CONSTRAINT test1_pk PRIMARY KEY (id)
2016-10-03 00:42:39.486000 +09:00
DROP TABLE test1 PURGE


またadrclコマンドからDDLログを参照することも可能です。
こちらでは実行時間とDDL文が出力されます。

このページのトップヘ