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

* ```Oracle LiveLabs```をやってみました。
    * LiveLabs： [Automatic Partitioning in Autonomous Database Workshop](https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/view-workshop?wid=935&clear=180&session=100478386709889)

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

出典：[【完全無償】Oracle LiveLabs を使って OCI を触ってみた](https://qiita.com/Skogkatter112/items/209ae71fc71c3572e52d)）

## Automatic Partitioning（自動パーティショニング）とは
* 19cからの新機能
* アプリケーション・ワークロード等を分析し、対象表や索引を自動的にパーティション化
* メリット
    * パフォーマンスの向上
    * 大規模表の管理が改善
* マニュアル：[Autonomous Databaseでの自動パーティション化の管理](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/mdw-managing-automatic-partitioning-autonomous-database.html#GUID-03973BB7-BD09-4E5E-B47F-430D7CC949D6)
![a.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/0419df92-a09b-fc5b-0c00-e69daf6cc0d2.png)

出典：Speakerdeck-[Autonomous Database Cloud 技術詳細](https://speakerdeck.com/oracle4engineer/adb-s-technical-detail-jp?slide=13)

# 手順
## 1. Autonomous Data Database（ADB）の作成
* 作成方法は割愛します
    * 参考：[OCIチュートリアル-101: ADBインスタンスを作成してみよう](https://oracle-japan.github.io/ocitutorials/database/adb101-provisioning/)

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

### 2-2. テスト用の表を作成 
* ``sqlcl`` で接続し、ウォレットをダウンロード
```sql
$ sql /nolog
SQL> set cloudconfig wallet.zip
```
* ADMINユーザでログイン
```sql
SQL> connect admin/<パスワード>@<tns名>
SQL> show user
```

*  非パーティション表を作成
```sql
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';
```
(表示例)
```sql
   OBJECT_NAME 
______________ 
APART        
```

* ランダムなデータを表に挿入する
    * [optimizer_ignore_hints](https://docs.oracle.com/cd/F19136_01/refrn/OPTIMIZER_IGNORE_HINTS.html#GUID-D62CA6D8-D0D8-4A20-93EA-EEB4B3144347)：埋込みヒントを無視する初期化パラメータ
        * 18cからの機能
        * ``TRUE``：SQL文のオプティマイザ・ヒントを無視
        * ``FALSE``：SQL文のオプティマイザ・ヒントを有効

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

※TRUEの場合は次のSQLでFALSEに変更
```sql
alter session set optimizer_ignore_hints = false;
```

* APART表にデータを挿入(少し時間がかかる)
※補足
表のデータはデフォルトで圧縮されているので、ランダムなデータを挿入し、圧縮効果が薄れるようにする（できるだけはやく表を大きくするため）。
```sql
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;
```

```sql
select count(*) from apart;
```

* トランザクションをコミット
```sql
commit;
```

* ```optimizer_ignore_hints```をTRUEに設定
```sql
sho parameter optimizer_ignore_hints
alter session set optimizer_ignore_hints = true;
sho parameter optimizer_ignore_hints
```

### 2-3. 作成した表のサイズを確認 
* APART表のサイズを確認
自働パーティショニングの候補の表になるには、最低5GB以上のサイズが必要。

```sql
select sum(bytes)/(1024*1024) size_in_megabytes from   user_segments where  segment_name = 'APART';
```

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

```sql
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;
/
```
（表示例）
```sql
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. テストクエリを実行しワークロードを生成
```sql
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））。

* 次のクエリを実行し、スケジュール時間を監視。タスクが再度実行されるまで待つ
```sql
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](https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/DBA_AUTOTASK_SCHEDULE_CONTROL.html)

（表示例）
```sql
               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チューニングセットに取り込まれたことを確認

```sql
select sql_text from dba_sqlset_statements where sql_text like '%TEST_QUERY%' and sqlset_name = 'SYS_AUTO_STS';
```

```sql
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表が自動パーティショニングに候補になるか再度確認
```sql
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;
/
```
以下のメッセージが返ってきたら、自動パーティショニングの推奨ステップを実行する準備ができた
```sql
Auto partitioning validation: VALID
```

## 4. 推奨タスクの実行

* ```RECOMMEND_PARTITION_METHOD```ファンクション
    * 推奨を適用するためにAPPLY_RECOMMENDATIONプロシージャで使用できる推奨IDを返す
    * DBA_AUTO_PARTITION_RECOMMENDATIONSビューとともに使用してADBの自動パーティション化に関する推奨事項の詳細を取得
    * 参考：[DBMS_AUTO_PARTITION パッケージ](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/ref-dbms_auto_partition-package.html#GUID-D78DCF2C-090C-40F0-8CC1-0A4BEA196D68)

* 次のPL/SQLを実行（今回の場合は、完了まで約数十分）
```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;
/
```
（出力例）
```sql
=============================================
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```ビューから、パーティショニング方法やパーティション・キーなどの推奨事項の詳細を確認できる
    * [DBA_AUTO_PARTITION_RECOMMENDATIONS](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/mdw-managing-automatic-partitioning-autonomous-database.html#GUID-F07805B8-22BB-4675-AA66-60EDC083D908)

* 最新の推奨事項の詳細を確認
```sql
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);
```
（出力例）
```sql
                  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で次のコマンドを実行し、自動パーティショニングのレポートを取得
```sql
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ファイルをダウンロード。
    * ダウンロード・メニューのオプションから選択
    * ファイル名を指定し、「ダウンロード」ボタンをクリック

<Cloud Shellの図>

### 5.3 ダウンロードしたファイルをブラウザで開く
* ```General Information``` セクション
    * 表をパーティショニングした場合のクエリ・パフォーマンスの向上を測定するために実施されたパフォーマンス・テストに関する情報が含まれている
![1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/c37f14d9-5fcc-b495-3ba7-5cf3e47c6879.png)

* ```Report Summary``` セクション
    * アプリケーション・ワークロードのSQL文に対するパーティショニングのパフォーマンス効果を確認できる
![2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/711ed328-455c-6d9f-0cac-770d6cadc5e6.png)

* ```Report Details``` セクション
    * ワークロードの各SQLステートメントについて、パーティションなしとありのパフォーマンスを比較
![3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/1b5a09f7-597a-a126-6dc4-b394c87245c1.png)

* ```Execution Plan Before Change /After Change```
    * SQLの実行前と後のプランも確認可能
![4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/449014/ecaa5be0-06fd-e71f-74ac-865a59dd8f43.png)

など

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

```sql
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は次のような出力になる
```sql
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```コマンドを使用し、オンラインで構築される。このプロセスでは、元表へのロックなどの影響は発生しない（数十分かかる）

```sql
exec dbms_auto_partition.apply_recommendation('<your recommendation ID>');
```

### 6.2 表がパーティション化されたことを確認
* ```user_segments```ビューからパーティション化が成功したことを確認できる（**APART表がパーティション化されている**）

```sql
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;
```
表示例
```sql
   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表を削除
```sql
drop table APART purge';
select object_name from user_objects where object_name='APART';
```