オラクる。

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

2016年08月

パーティショニングとはユーザーからは一つに見える表を、複数の表に分割する機能です。
１つの表で管理する場合と比べて、パーティショニングすることによってパフォーマンスの向上が見込めます。
例えば、2010年から2016年の売上を管理する表があって、2016年の売上を確認したいことがあったとします。
その場合、パーティショニングを利用していないと、2010年から2016年までのデータを検索しなければなりません。一方、パーティショニングを利用して、表を2010年表、2011年表・・・2016年表という感じに分割すると、ユーザーは2016年表だけ見ればいいのです。
ユーザーはどの表を見ているかを意識することがありません。

さて、パーティショニング機能ですが、表を分割する方法が複数存在します。
今回はそれぞれ見ていきます。

CREATE TABLE sales_range
(id NUMBER(5),
 name VARCHAR2(30),
 sales_amount NUMBER(10),
 sales_date DATE
)TABLESPACE tbs1
PARTITION BY RANGE(sales_date)
(PARTITION p2016q1 VALUES LESS THAN(TO_DATE('2016-04-01',
'YYYY-MM-DD')),
 PARTITION p2016q2 VALUES LESS THAN(TO_DATE('2016-07-01',
'YYYY-MM-DD')),
 PARTITION p2016q3 VALUES LESS THAN(TO_DATE('2016-10-01',
'YYYY-MM-DD')),
 PARTITION p2016q4 VALUES LESS THAN(TO_DATE('2017-01-01',
'YYYY-MM-DD')));

１つはレンジパーティション
基準となる値を範囲を区切ってパーティショニングします。
例えば上記の場合、2016年を四半期ごとの区切っています。
１つの表が４つの表に分割されました。

CREATE TABLE sales_list
(id NUMBER(5),
 name VARCHAR2(30),
 sales_state VARCHAR2(20),
 sales_amount NUMBER(10),
 sales_date DATE
)TABLESPACE tbs1
PARTITION BY LIST(sales_state)
(PARTITION sales_kanto  VALUES ('Kanagawa','Tokyo'),
 PARTITION sales_kansai VALUES ('Osaka','Kobe'),
 PARTITION sales_kyusyu VALUES ('Fukuoka','Nagasaki'),
 PARTITION sales_tohoku VALUES ('Miyagi','Aomori'));

二つ目はリストパーティション
連続していない値を基準にして、特定の値で分割しています。
例えば上記の場合、地域ごとに４分割しています。

CREATE TABLE sales_hash
(id NUMBER(5),
 name VARCHAR2(30),
 sales_amount NUMBER(10),
 week_no NUMBER(2)
)TABLESPACE tbs1
PARTITION BY HASH(id)
PARTITIONS 4;

三つ目はハッシュパーティション
基準となる列のハッシュ値を取得して、それに基づいて表を分割します。
分割数は任意に決めることが出来ますが、２の累乗である必要があります。
上記の場合、id列のハッシュ値を取得して、４分割しています。

CREATE TABLE sales_composite
(id NUMBER(5),
 name VARCHAR2(30),
 sales_amount NUMBER(10),
 sales_date DATE
)TABLESPACE tbs1
PARTITION BY RANGE(sales_date)
SUBPARTITION BY HASH(id)
SUBPARTITIONS 4
(PARTITION p2016q1 VALUES LESS THAN(TO_DATE('2016-04-01',
'YYYY-MM-DD')),
 PARTITION p2016q2 VALUES LESS THAN(TO_DATE('2016-07-01',
'YYYY-MM-DD')),
 PARTITION p2016q3 VALUES LESS THAN(TO_DATE('2016-10-01',
'YYYY-MM-DD')),
 PARTITION p2016q4 VALUES LESS THAN(TO_DATE('2017-01-01',
'YYYY-MM-DD')));

最後に、それらの分割手法を複数利用することが出来ます。
例えば上記の場合、日付のレンジパーティションで４分割して、それぞれはidのハッシュ値で４分割しています。
結局、４＊４＝１６分割されます。
また、レンジパーティションで分割して、されにリストパーティションで分割することも可能です。

前回を作成したルールを実行してみましょう。
評価の結果は必ず真になるので、表領域移動、圧縮は必ず実行されます。

var v_taskid NUMBER
exec DBMS_ILM.EXECUTE_ILM(task_id=>:v_taskid, -
owner=>'SCOTT',object_name=>'EMP2')

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

スキーマscott、表EMP2に対するルールが実行されました。
実行したルールのタスクIDはv_taskidに格納されます。

SQL> SELECT task_id, policy_name, selected_for_execution
  2  FROM dba_ilmevaluationdetails WHERE task_id=:v_taskid;

   TASK_ID POLICY_NAME                                                                                                                     SELECTED_FOR_EXECUTION
---------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------
        13 P4                                                                                                                              SELECTED FOR EXECUTION
        13 P3                                                                                                                              SELECTED FOR EXECUTION

実行結果を確認します。
先ほどのv_taskidを条件に利用します。
「SELECTED FOR EXECUTION」で実行されたことを確認できました。

SELECT job_name,job_state,start_time,completion_time
FROM dba_ilmresults WHERE task_id=:v_taskid;

JOB_NAME                                                                                                                        JOB_STATE
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------
START_TIME                                                                  COMPLETION_TIME
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
ILMJOB16                                                                                                                        COMPLETED SUCCESSFULLY
16-08-19 01:00:44.183299                                                    16-08-19 01:01:06.404331

dba_ilmresultsビューでルールの実行結果を確認できます。
job_state列がCOMPLETES SUCCESSFULLYとなっていることを確認します。

SQL> select tablespace_name from dba_segments where segment_name='EMP2';

TABLESPACE_NAME
------------------------------
TBS1

ルールが実行されてemp2表のテーブルスペースが移動したことを確認できました。

ADOの評価の基準として、そのオブジェクトのアクセス頻度があります。
アクセス頻度を一定の条件で評価し、その条件が満たされれば、圧縮や表領域移動などのアクションを実行します。
また、カスタムの評価基準を作成することが出来ます。

CREATE OR REPLACE FUNCTION scott.ilm_rule1
(p_object_id IN NUMBER)
RETURN BOOLEAN
AS
BEGIN
    RETURN TRUE;
END;
/

ファンクションが作成されました。

カスタムの評価基準はPL/SQLで作成します。
今回は、評価すると必ず「真」で返す評価基準を作成しました。

ALTER TABLE scott.emp2 ILM ADD POLICY
ROW STORE COMPRESS ADVANCED
SEGMENT ON ilm_rule1;

表が変更されました。

ALTER TABLE scott.emp2 ILM ADD POLICY
TIER TO tbs1 ON ilm_rule1;

表が変更されました。

その評価基準を利用した圧縮と表領域移動のルールを作成します。
対象は表はインデックスなどのセグメントレベルです。

SELECT p.* FROM dba_ilmdatamovementpolicies p,dba_ilmobjects o
WHERE p.policy_name=o.policy_name AND o.object_name='EMP2';

POLICY_NAME                                                                                                                     ACTION_TYPE SCOPE   COMPRESSION_LEVEL
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------- ------------------------------
TIER_TABLESPACE                                                                                                                 TIER_STAT CONDITION_TYPE         CONDITION_DAYS
-------------------------------------------------------------------------------------------------------------------------------- --------- ---------------------- --------------
CUSTOM_FUNCTION
--------------------------------------------------------------------------------------------------------------------------------
P3                                                                                                                              COMPRESSION SEGMENT ADVANCED
                                                                                                                                          USER DEFINED                        0
ILM_RULE1

P4                                                                                                                              STORAGE     SEGMENT
TBS1                                                                                                                                      USER DEFINED                        0
ILM_RULE1

ポリシーが作成され、カスタムの評価基準が利用されていることを確認します。
次回はそのルールを実行してみて、どうなるかを確認してみます。

12cでは自動データ最適化（ADO）という機能が導入されました。
この機能によって、アクセス頻度に応じたデータの圧縮、表領域移動が自動化されます。

最適化を実行するにはアクセス頻度の収集（heat_map)と自動データ最適化を有効にする必要があります。

[oracle@DB01 ~]$ sqlplus / as sysdba

SQL> ALTER SYSTEM SET heat_map = ON;

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

SQL> show parameter heat_map

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      ON

インスタンスレベルでheat_mapが有効化されました。
ちなみにマルチテナント環境ではheat_mapを有効化することが出来ませんでした。
非マルチテナント環境のみ利用可能なようです。

SQL> ALTER TABLE SCOTT.emp ILM ADD POLICY
  2  COLUMN STORE COMPRESS FOR QUERY LOW
  3  GROUP AFTER 6 MONTHS OF LOW ACCESS;

表が変更されました。

表に対して６ヶ月アクセスが少ないと圧縮するという最適化のポリシーを作成しました。

SQL> ALTER TABLE scott.emp ILM ADD POLICY
  2  TIER TO tbs1;

これはemp表に対する表領域の使用率が一定以上になったら別の表領域に移動するポリシーです。

SQL> column NAME format a20
SQL> SELECT * FROM dba_ilmparameters;

NAME                      VALUE
-------------------- ----------
ENABLED                       1
RETENTION TIME               30
JOB LIMIT                     2
EXECUTION MODE                2
EXECUTION INTERVAL           15
TBS PERCENT USED             85
TBS PERCENT FREE             25
POLICY TIME                   0

8行が選択されました。

デフォルトでは85%以上になったら上記のポリシーが作動します。

SQL> exec DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_USED,90);

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

SQL> exec DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,10);

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

SQL> SELECT * FROM dba_ilmparameters;

NAME                      VALUE
-------------------- ----------
ENABLED                       1
RETENTION TIME               30
JOB LIMIT                     2
EXECUTION MODE                2
EXECUTION INTERVAL           15
TBS PERCENT USED             90
TBS PERCENT FREE             10
POLICY TIME                   0

8行が選択されました。

DBMS_ILM_ADMIN.CUSTOMIZE_ILMプロシージャで85%という使用率を変更することも出来ます。

マルチテナント環境のフラッシュバックデータベースはCDB単位で行ないます。
特定のPDBやルートコンテナのみを戻すことは出来ません。

[oracle@DB01 ~]$ sqlplus / as sysdba

SQL> ALTER DATABASE FLASHBACK ON;

データベースが変更されました。

フラッシュバックデータベースを行なうには、まずルートコンテナに接続しフラッシュバックを有効化します。
アーカイブログモードであることが前提条件です。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2276329

検証時に戻したい時点を確認するため、現時点のSCNを表示します。

SQL> alter session set container = PDB1;

セッションが変更されました。

SQL> show con_name;

CON_NAME
------------------------------
PDB1

PDBに接続します。

SQL> create table test_table (id number);

表が作成されました。

SQL> insert into test_table values (1);

1行が作成されました。

SQL> insert into test_table values (2);

1行が作成されました。

SQL> insert into test_table values (3);

1行が作成されました。

SQL> commit;

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

SQL> select * from test_table;

        ID
----------
         1
         2
         3

PDB内に表作成、データをインサートし、コミットします。
これでSCNが進みます。

SQL> alter session set container = cdb$root;

セッションが変更されました。

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

再度、ルートコンテナに接続します。

SQL> shutdown immediate;
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。

SQL> startup mount;
ORACLEインスタンスが起動しました。

Total System Global Area 1.3522E+10 bytes
Fixed Size                  2939704 bytes
Variable Size            6912214216 bytes
Database Buffers         6576668672 bytes
Redo Buffers               30613504 bytes
データベースがマウントされました。

CDB全体を停止し、マウント状態で起動します。

SQL> FLASHBACK DATABASE TO SCN 2276329;

フラッシュバックが完了しました。

最初で確認したSCNにフラッシュバックします。
これでSCN確認以降に作成した表は無くなるはずです。

SQL> ALTER DATABASE OPEN READ ONLY;

データベースが変更されました。

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;

データベースが変更されました。

確認のため、cdb、pdbを読み取り専用でオープンします。

SQL> alter session set container = PDB1;

セッションが変更されました。

SQL> select * from test_table;
select * from test_table
              *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。

PDBに接続し、作成した表がフラッシュバックにより無くなっていることを確認します。

SQL> shutdown immediate;
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。

SQL> startup mount;
ORACLEインスタンスが起動しました。

Total System Global Area 1.3522E+10 bytes
Fixed Size                  2939704 bytes
Variable Size            6912214216 bytes
Database Buffers         6576668672 bytes
Redo Buffers               30613504 bytes
データベースがマウントされました。
SQL> alter database open resetlogs;

データベースが変更されました。

確認後、再度、CDBを停止、マウント状態で起動し、resetlogsオプション付きでオープンします。

このページのトップヘ