Go to Qiita Advent Calendar Top
LoginSignup
4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@billtench(bill tench)

Autonomous DatabaseのAutomatic Partitioning（自動パーティショニング）の確認

Last updated at Posted at 2022-03-08

「Oracle LiveLabs」とは、個人用の 学習コンテンツ を提供しているサービスです。
AWS/GCP では Qwiklabs、Azure では Microsoft Learn といったサービスがありますが、それと同じく、わざわざ 個人で クラウドサービスの契約をせずとも、「ハンズオンのシナリオに沿って」「実際の環境を触りながら」学べる というのがポイントです。

出典：【完全無償】Oracle LiveLabs を使って OCI を触ってみた）

Automatic Partitioning（自動パーティショニング）とは

出典：Speakerdeck-Autonomous Database Cloud 技術詳細

手順

1. Autonomous Data Database（ADB）の作成

2. 非パーティション表の作成

2-1. Cloud Shellの起動

  • Cloud Shellで以下を実行
  • ホームディレクト
  • 環境変数設定
$ db_ocid=<ADBのOCID>
$ echo $db_ocid
  • OCI CLIでウォレットをダウンロード
$ oci -v
$ oci db autonomous-database generate-wallet --autonomous-database-id $db_ocid --file wallet.zip --password <ウォレット・ファイルに付与するパスワード>
$ ls -l

2-2. テスト用の表を作成

  • sqlcl で接続し、ウォレットをダウンロード
$ sql /nolog
SQL> set cloudconfig wallet.zip
  • ADMINユーザでログイン
SQL> connect admin/<パスワード>@<tns名>
SQL> show user
  • 非パーティション表を作成
create table apart (a number(10), b number(10), c number(10), d date, pad varchar2(1000));

select object_name from user_objects where object_name='APART';

(表示例)

   OBJECT_NAME 
______________ 
APART
  • ランダムなデータを表に挿入する
    • optimizer_ignore_hints：埋込みヒントを無視する初期化パラメータ
      • 18cからの機能
      • TRUE：SQL文のオプティマイザ・ヒントを無視
      • FALSE：SQL文のオプティマイザ・ヒントを有効

※補足
特定の結合順序を強制し特定のInsert文に対しオプティマイザ・ヒントを有効にする。これにより、ストレージに書き込まれる際にランダムな文字列をばらばらにするので、圧縮の効果は低くなる。表をできるだけはやく大きくして、自動パーティショニングの対象になるようにするために実行。

sho parameter optimizer_ignore_hints

（表示例）

NAME                   TYPE    VALUE 
---------------------- ------- ----- 
optimizer_ignore_hints boolean FALSE

※TRUEの場合は次のSQLでFALSEに変更

alter session set optimizer_ignore_hints = false;
  • APART表にデータを挿入(少し時間がかかる)
    ※補足
    表のデータはデフォルトで圧縮されているので、ランダムなデータを挿入し、圧縮効果が薄れるようにする（できるだけはやく表を大きくするため）。
insert /*+ APPEND */ into apart
with
r as ( select /*+ materialize */ dbms_random.string('x',500) str 
     from dual connect by level <= 2000 ),
d as ( select /*+ materialize */ to_date('01-JAN-2020') + mod(rownum,365) dte 
     from dual connect by level <= 2500 ),
m as ( select 1 
     from dual connect by level <= 3 )
select /*+ leading(m d r) use_nl(d r) */
rownum, rownum, rownum, dte, str
from m,d,r;

select count(*) from apart;
  • トランザクションをコミット
commit;
  • optimizer_ignore_hintsをTRUEに設定
sho parameter optimizer_ignore_hints
alter session set optimizer_ignore_hints = true;
sho parameter optimizer_ignore_hints

2-3. 作成した表のサイズを確認

  • APART表のサイズを確認
    自働パーティショニングの候補の表になるには、最低5GB以上のサイズが必要。
select sum(bytes)/(1024*1024) size_in_megabytes from   user_segments where  segment_name = 'APART';

3. 自動パーティショニングの対象表になるか確認

3-1. 非パーティション表のAPART表が自動パーティショニングの候補とみなされるか確認

set serveroutput on

declare
 ret varchar2(1000);
begin
 ret := dbms_auto_partition.validate_candidate_table (table_owner=>user,table_name=>'APART');
  dbms_output.put_line(' ');
  dbms_output.put_line(' ');
  dbms_output.put_line('Auto partitioning validation: ' || ret);
end;
/

（表示例）

Auto partitioning validation: INVALID: table is too small (8.8 GB actual, 64 GB required)


PL/SQL procedure successfully completed.

※補足
INVALIDと表示される。
今回、64GB必要と表示されているため、先程実行したInsert文を繰り返して必要なサイズにする。
また、自動パーティショニングの候補になるには表のサイズだけでなく、ワークロードが必要になるため。

3-2. テストクエリを実行しワークロードを生成

select /* TEST_QUERY */ sum(a) from apart where d between to_date('01-MAR-2020') and to_date('05-mar-2020');
select /* TEST_QUERY */ sum(a) from apart where d = to_date('01-MAR-2020');
select /* TEST_QUERY */ sum(b) from apart where d between to_date('01-JAN-2020') and to_date('05-JAN-2020');
select /* TEST_QUERY */ sum(c) from apart where d between to_date('01-APR-2020') and to_date('05-APR-2020');
select /* TEST_QUERY */ sum(a) from apart where d between to_date('01-JUN-2020') and to_date('02-JUN-2020');
select /* TEST_QUERY */ sum(b) from apart where d between to_date('01-DEC-2020') and to_date('31-DEC-2020');
select /* TEST_QUERY */ sum(a) from apart where d between to_date('01-AUG-2020') and to_date('31-AUG-2020');
select /* TEST_QUERY */ sum(b) from apart where d between to_date('01-OCT-2020') and to_date('01-OCT-2020');
select /* TEST_QUERY */ sum(c) from apart where d between to_date('01-FEB-2020') and to_date('05-FEB-2020');
select /* TEST_QUERY */ sum(a) from apart where d between to_date('01-MAY-2020') and to_date('02-MAY-2020');
select /* TEST_QUERY */ avg(a) from apart where d between to_date('01-JUL-2020') and to_date('02-JUL-2020');

3-3. ADBが自動的にアプリケーション・ワークロードを取得するのを待つ

※補足
Autonomous Database（ADB）では、定期的（15分ごと）にワークロードの情報を収集する。Auto STS Capture Taskは、 SYS_AUTO_STS というSQLチューニングセットのワークロードSQLをキャプチャする（自動SQLチューニングセット（ASTS））。

  • 次のクエリを実行し、スケジュール時間を監視。タスクが再度実行されるまで待つ
select current_timestamp now from dual;

select task_name,
       status,
       enabled,
       interval,
       last_schedule_time, 
       systimestamp-last_schedule_time as ago 
from dba_autotask_schedule_control 
where dbid = sys_context('userenv','con_dbid') 
and   task_name like '%STS%';

上記クエリを実行し続け、 LAST_SCHEDULE_TIME列の値を監視。これが変化するのを待つ。または、 AGO列の値を見て、数秒前にタスクが実行されたことを示すまで待つ。
デフォルトは900秒（15分）間隔。

※参考：dba_autotask_schedule_control

（表示例）

               TASK_NAME       STATUS    ENABLED    INTERVAL                     LAST_SCHEDULE_TIME                    AGO 
________________________ ____________ __________ ___________ ______________________________________ ______________________ 
Auto STS Capture Task    SUCCEEDED    TRUE               900 08-MAR-22 12.38.26.239000000 AM GMT    +00 00:04:02.324562    ```
  • ワークロードを収集できたら、ワークロードのクエリが自動SQLチューニングセットに取り込まれたことを確認
select sql_text from dba_sqlset_statements where sql_text like '%TEST_QUERY%' and sqlset_name = 'SYS_AUTO_STS';

SQL_TEXT                                                                                                                                              
---------------------------------------------------
                                                                           SQL_TEXT 
___________________________________________________________________________________ 
select /* TEST_QUERY */ sum(a) from apart where d between to_date('01-JUN-2020')    
select /* TEST_QUERY */ sum(a) from apart where d between to_date('01-MAR-2020')    

（略）

3-4. 自動SQLチューニングセットにワークロードが取り込まれたので、APART表が自動パーティショニングに候補になるか再度確認

set serveroutput on

declare
 ret varchar2(1000);
begin
 ret := dbms_auto_partition.validate_candidate_table (table_owner=>user,table_name=>'APART');
  dbms_output.put_line(' ');
  dbms_output.put_line(' ');
  dbms_output.put_line('Auto partitioning validation: ' || ret);
end;
/

以下のメッセージが返ってきたら、自動パーティショニングの推奨ステップを実行する準備ができた

Auto partitioning validation: VALID

4. 推奨タスクの実行

  • RECOMMEND_PARTITION_METHODファンクション

    • 推奨を適用するためにAPPLY_RECOMMENDATIONプロシージャで使用できる推奨IDを返す
    • DBA_AUTO_PARTITION_RECOMMENDATIONSビューとともに使用してADBの自動パーティション化に関する推奨事項の詳細を取得
    • 参考：DBMS_AUTO_PARTITION パッケージ

  • 次のPL/SQLを実行（今回の場合は、完了まで約数十分）

set timing on
set serveroutput on
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
set heading off
set feedback off

exec dbms_auto_partition.configure('AUTO_PARTITION_MODE','REPORT ONLY');

declare
  r raw(100);
  cursor c1 is
       select partition_method, partition_key, report
       from   dba_auto_partition_recommendations
       where recommendation_id = r;
begin
   r :=
      dbms_auto_partition.recommend_partition_method(
       table_owner    => 'ADMIN',
       table_name     => 'APART',
       report_type    => 'TEXT',
       report_section => 'ALL',
       report_level   => 'ALL');
for c in c1
loop
    dbms_output.put_line('=============================================');
    dbms_output.put_line('ID:     '||r);
    dbms_output.put_line('Method: '||c.partition_method);
    dbms_output.put_line('Key   : '||c.partition_key);
    dbms_output.put_line('=============================================');
end loop;

end;
/

（出力例）

=============================================
ID:     D9AD0D84352DEF34E0530A18000A5AC0
Method: LIST(SYS_OP_INTERVAL_HIGH_BOUND("D", INTERVAL '1' MONTH, TIMESTAMP
'2020-01-01 00:00:00')) AUTOMATIC
Key   : D
=============================================

※出力されたIDをコピー

5. 推奨事項の確認

5.1 推奨事項の基本情報を確認

自動パーティショニングが候補表に対して適切な推奨事項を特定するたびに、その結果がデータディクショナリに保存される。

  • DBA_AUTO_PARTITION_RECOMMENDATIONSビューから、パーティショニング方法やパーティション・キーなどの推奨事項の詳細を確認できる

  • 最新の推奨事項の詳細を確認

set linesize 180
column partition_method format a100
column partition_key format a13

select partition_method,partition_key 
from dba_auto_partition_recommendations
where generate_timestamp = (select max(generate_timestamp) 
from   dba_auto_partition_recommendations);

（出力例）

                  PARTITION_METHOD    PARTITION_KEY
________________________________________________________________________________________________________ ________________
LIST(SYS_OP_INTERVAL_HIGH_BOUND("D", INTERVAL '1' MONTH, TIMESTAMP '2020-01-01 00:00:00')) AUTOMATIC     D

5.2 自動パーティショニングのレポートのダウンロード

  • Cloud Shellで次のコマンドを実行し、自動パーティショニングのレポートを取得
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
set heading off
set feedback off

spool autoPartitionFinding.html
select dbms_auto_partition.report_last_activity(type=>'HTML') from dual;
exit;
  • Cloud Shellをしようし、ローカルマシンに htmlファイルをダウンロード。
    • ダウンロード・メニューのオプションから選択
    • ファイル名を指定し、「ダウンロード」ボタンをクリック

5.3 ダウンロードしたファイルをブラウザで開く

  • General Information セクション

    • 表をパーティショニングした場合のクエリ・パフォーマンスの向上を測定するために実施されたパフォーマンス・テストに関する情報が含まれている
      1.png

  • Report Summary セクション

    • アプリケーション・ワークロードのSQL文に対するパーティショニングのパフォーマンス効果を確認できる
      2.png

  • Report Details セクション

    • ワークロードの各SQLステートメントについて、パーティションなしとありのパフォーマンスを比較
      3.png

  • Execution Plan Before Change /After Change

    • SQLの実行前と後のプランも確認可能
      4.png

など

5.4 modify-tableのDDLを表示

自動パーティショニングが候補表を変更するために使用するDDLコマンドを確認
この例では、DBA_AUTO_PARTITION_RECOMMENDATIONSビューから最新の推奨事項を表示する

set long 100000

select modify_table_ddl 
from  dba_auto_partition_recommendations
where generate_timestamp = 
   (select max(generate_timestamp) 
    from   dba_auto_partition_recommendations)
order by recommendation_seq;

非パーティション表と生成されたワークロードのDDLは次のような出力になる

MODIFY_TABLE_DDL 
__________________

  -- DBMS_AUTO_PARTITION recommendation_ID 'D9AD0D84352DEF34E0530A18000A5AC0'
  --   for table "ADMIN"."APART"
  --   generated at 03/08/2022 02:56:58

  dbms_auto_partition.begin_apply(expected_number_of_partitions => 13);

  execute immediate 
'alter table "ADMIN"."APART"
 modify partition by 
LIST(SYS_OP_INTERVAL_HIGH_BOUND("D", INTERVAL ''1'' MONTH, TIMESTAMP ''2020-01-01 00:00:00'')) AUTOMATIC (PARTITION P_NULL VALUES(NULL))
 auto online parallel';

  dbms_auto_partition.end_apply;
exception when others then
  dbms_auto_partition.end_apply;
  raise;
end;

6. 推奨事項の適用

6.1 推奨事項を適用しパーティショニングを実装

  • 手順4でコピーしていたID(例：D9AD0D84352DEF34E0530A18000A5AC0）を使用する
    • ALTER TABLE MODIFY PARTITION ONLINEコマンドを使用し、オンラインで構築される。このプロセスでは、元表へのロックなどの影響は発生しない（数十分かかる）
exec dbms_auto_partition.apply_recommendation('<your recommendation ID>');

6.2 表がパーティション化されたことを確認

  • user_segmentsビューからパーティション化が成功したことを確認できる（APART表がパーティション化されている）
set trims on
set linesize 300
column partition_name format a20
column segment_name format a15

select segment_name, partition_name,segment_type,  bytes/(1024*1024) as mb from user_segments order by partition_name;

表示例

   SEGMENT_NAME    PARTITION_NAME       SEGMENT_TYPE        MB 
_______________ _________________ __________________ _________ 
APART           P_NULL            TABLE PARTITION       0.0625 
APART           SYS_P1744         TABLE PARTITION         5888 
APART           SYS_P1745         TABLE PARTITION         6272 
APART           SYS_P1746         TABLE PARTITION         6272 
APART           SYS_P1747         TABLE PARTITION         6272 
APART           SYS_P1748         TABLE PARTITION         6080 
APART           SYS_P1749         TABLE PARTITION         6080 
APART           SYS_P1750         TABLE PARTITION         6080 
APART           SYS_P1751         TABLE PARTITION         6272 
APART           SYS_P1752         TABLE PARTITION         6272 
APART           SYS_P1753         TABLE PARTITION         5376 
APART           SYS_P1754         TABLE PARTITION         6272 
APART           SYS_P1755         TABLE PARTITION         5248

7. テスト表の削除

  • APART表を削除
drop table APART purge';
select object_name from user_objects where object_name='APART';
4
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?