オラクる。

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

カテゴリ: oracle

前回の続きです。
今日は早速、仮想マシンを作成してみましょう。
まずはセキュリティグループの作成から

000011

コンソール画面のクイックスタートから、「仮想マシンの起動」をクリックします。

000013

左側のメニューからセキュリティグループの作成をクリックします。

000014

「セキュリティグループの作成」ボタンをクリックします。

000015

セキュリティグループはファイアーウォールのルールのようなものです。
22番のSSHを許可するように設定します。

000017

上記のように設定されました。

000018

早速仮想マシンの作成です。
左側のメニューからインスタンスを選択して、インスタンスの作成ボタンをクリックします。

000019

AMIという仮想マシンのイメージを選択する画面になります。
今回はRHEL6のイメージを選択しました。

000020

インスタンスタイプは無料利用枠対象のt2.microを選択します。
1vCPU、mem1GBです。

次回以降、設定が続きます。

AWSのEC2を利用してOracleを構築できないか検証してみました。
無料利用枠の範囲内で検証してみます。

まずは、http://aws.amazon.com/jp/にアクセスします。

000000

「まずは無料で始める」をクリックします。

000002

「今すぐ無料アカウントを作成」をクリックします。

000003

「I am a new user」を選択した状態でEmailアドレスを上に入力し、下のSign in~をクリックします。

000004

自分の名前、Emailアドレス、パスワードを設定し、Create Accountをクリックします。

000005
住所、電話番号などの連絡先情報を入力します。

000006
無料利用枠でも支払情報は必要です。
クレジット番号を入力します。

000007

電話を利用した本人確認を行ないます。

000008
電話がかかってきたら画面に表示されているpin番号を入力して本人確認完了です。

000009

サポートプランを選択します。
通常はベーシックで問題ありません。

000010

これで登録完了です。

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

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より確認できました。

このページのトップヘ