シャーディング・アドバイザの使用

シャーディング・アドバイザについて

シャーディング・アドバイザは、Oracle Globally Distributed Databaseへの移行を検討している10g以降のリリースの本番以外の任意のOracle Databaseに対して実行する、クライアント側のコマンドライン・ツールです。

シャーディング・アドバイザ分析では、新しいシャード・データベース環境で重複データを削減しながらパフォーマンスを最大化するスキーマの設計に必要な情報が提供されます。

シャーディング・アドバイザを使用してスキーマ設計を支援する利点は、次のとおりです。

問合せワークロードのパフォーマンスの最大化
シャード間結合を必要とするマルチシャード操作の最小化
複合問合せの並列化の最大化(すべてのシャードに問合せ処理を分散します)
各シャードの重複データの量の最小化

シャーディング・アドバイザ・ユーティリティGWSADVは、スタンドアロン・ツールとしてOracle Databaseとともにインストールされ、認証済OCI接続を使用してデータベースに接続します。

スキーマおよびその他の優先事項を理解するために、シャーディング・アドバイザは対話型ダイアログの一部としてユーザーにいくつかの質問を行います。

次に、シャーディング・アドバイザは、ソースとも呼ばれる既存の非シャード・データベースに接続し、そのスキーマおよび問合せワークロードを分析して、シャード・データベースの一連の代替設計を生成します。この設計には、効率的なシャーディング・キーの推奨、シャーディングする表、およびすべてのシャードで重複表にする表が含まれています。

シャーディング構成は問合せパフォーマンスの観点でランク付けされます。ランキングで優先される構成は、シャード間結合を必要としない単一のシャードの問合せおよびマルチシャード問合せを最大化し、シャード間結合を必要とするマルチシャード問合せを最小化する構成です。

要件に最適な設計を選択します。設計はアドバイザによってランク付けされるため、特定の優先事項がない場合は、最もランクの高い設計をデフォルトで選択できます。

ノート:

シャーディング・アドバイザ機能には制限があります。

ソース・データベースは、Oracle Database 10g以上のリリースである必要があります。

稼働中の本番データベースに対してシャーディング・アドバイザを実行できない場合は、本番データベースからインポートされたスキーマとワークロードがある別のサーバーでシャーディング・アドバイザを実行できます。

シャーディング・アドバイザは、主キーと外部キーの関係に基づいて表ファミリを検出します。スキーマに主キーと外部キーの制約がない場合は、PARENT句によるシャーディングをお薦めします。

現在、シャーディング・アドバイザは、ソース・データベースに外部キー制約がある場合、単一表ファミリのシステム管理のシャーディング(参照によるシャーディング)構成のみを推奨します。それ以外の場合、シャーディング・アドバイザはPARENT句を使用したシャーディングを推奨します。

シャーディング・アドバイザの実行

推奨されるシャード・データベース構成を取得するには、シャーディング・アドバイザのコマンドライン・ツールを既存の非シャードOracle Databaseに対して実行します。

シャーディング・アドバイザを実行しているユーザーには、次の権限が必要です。

SQL> ALTER SYSTEM SET statistics_level=all;
SQL> grant create session to sharding_advisor_user;
SQL> grant alter session to sharding_advisor_user;
SQL> grant select on v_$sql_plan to sharding_advisor_user;
SQL> grant select on v_$sql_plan_statistics_all to sharding_advisor_user;
SQL> grant select on gv_$sql_plan to sharding_advisor_user;
SQL> grant select on gv_$sql_plan_statistics_all to sharding_advisor_user;
SQL> grant select on DBA_HIST_SQLSTAT to sharding_advisor_user;
SQL> grant select on dba_hist_sql_plan to sharding_advisor_user;
SQL> grant select on dba_hist_snapshot to sharding_advisor_user;

シャーディング・アドバイザのコマンドライン・ユーティリティGWSADVは、$ORACLE_HOME/binから実行されます。

次に示すように、コマンドラインからシャーディング・アドバイザを実行します。

$ gwsadv -u username -p password -c –w sch=\(schema1,schema2\)

ノート:

このコマンドのカッコは、Linuxシステムではエスケープされます。

ここで、-uおよび-pは、シャーディング・アドバイザを実行するユーザーのユーザー名およびパスワードです。

ソースのGV$SQL_PLAN_STATISTICS_ALLビューから述語情報を取得するには、既存の問合せワークロードに対してシャーディング・アドバイザを初めて実行するときに、ワークロード取得パラメータ-cを使用します。同じワークロードの後続の問合せで-cを使用する必要はありません。

必須の-wフラグは、シャーディング・アドバイザがシャーディング構成の生成およびランキングのために問合せワークロードを使用することを示します。

この場合、シャーディング・アドバイザを実行するスキーマのリストが、schパラメータで指定されています。シャーディング・アドバイザで使用できるその他のオプションについては、シャーディング・アドバイザの使用方法およびオプションで説明されています。

非本番システムでのシャーディング・アドバイザの実行

稼働中の本番システムへの影響を最小限に抑えるには、本番システムとは別のサーバーに配置されたデータベース・スキーマおよびワークロードのコピーに対して、シャーディング・アドバイザを実行します。

稼働中の本番システムと同様の結果を得るために、Oracle Data Pumpユーティリティを使用して本番データベースのスキーマおよびワークロードをエクスポートし、別のサーバーにコピーできます。その後、インポートされたスキーマに対してシャーディング・アドバイザを実行できます。

データベース・スキーマおよびシステム表のみをエクスポートします。実際のデータをエクスポートする必要はありません。

次の手順では、例としてHRスキーマを使用します。

ソース(本番)データベース・サーバーで次のステップを実行します。

データ・ポンプ・エクスポートを使用してスキーマをエクスポートします。

> expdp system/password SCHEMAS=HR DIRECTORY=HR_DIR CONTENT=METADATA_ONLY
 DUMPFILE=hr_metadata.dmp LOGFILE=hr_exp.lst

自動ワークロード・リポジトリ(AWR)のスナップショットをエクスポートします。
```
SQL> @$ORACLE_HOME/rdbms/admin/awrextr.sql
```

ターゲット・データベース・サーバーで次のステップを実行します。

ダンプ・ファイルをソースからターゲットにコピーします。

たとえば、ダンプ・ファイルを/scratch/dumpにコピーします。
スキーマに対してシャーディング・アドバイザを実行できるユーザーを作成します。
```
SQL> CREATE USER hr IDENTIFIED BY password;
```
データ・ポンプ・インポートが参照できるダンプ・ファイル・ディレクトリ変数を作成(または置換)します。
```
SQL> CREATE DIRECTORY HR_DIR AS '/scratch/dump'

SQL> CREATE OR REPLACE DIRECTORY  HR_DIR AS '/scratch/dump'
```

スキーマをインポートします。

> impdp system/password DIRECTORY=HR_DIR DUMPFILE=hr.dmp LOGFILE=imp.lst SCHEMAS=HR

AWRデータをロードします。

SQL> @$ORACLE_HOME/rdbms/admin/awrload.sql

ターゲットの非本番のデータベースのコピーに対して、作成したユーザーでシャーディング・アドバイザを実行できるようになりました。
```
> gwsadv –u hr –p password –c -awr_snap_begin begin_timestamp –awr_snap_end end_timestamp -w
```

シャーディング・アドバイザの出力の確認

シャーディング・アドバイザは、問合せワークロードから抽出したシャーディングの候補となる各列の表ファミリを検出し、問合せ分類ルールとランキング・アルゴリズムに基づいて表ファミリをランク付けします。

シャーディング・アドバイザを実行しているユーザーが所有するシャーディング構成および関連情報を確認するには、ソース・データベースと同じスキーマに格納されている次の出力データベース表を問い合せることができます。

SHARDINGADVISOR_CONFIGURATIONSには、ランク付けされたシャード構成内の表ごとに1つの行があり、各表の詳細が含まれています(シャード表または重複表のどちらにするか、およびシャードの場合、表ファミリ階層内のレベル、その親表、ルート表のシャーディング・キー、外部キー参照制約、シャードごとの推定サイズなど)。
SHARDINGADVISOR_CONFIGDETAILSには、ランク付けされたシャーディング構成ごとに1つの行があり、シャード表の数とシャードごとの累積サイズ、および重複表の数と累積サイズなどのランク付けされた各シャーディング構成の詳細が含まれています。また、ソース・データベースの現在のワークロードおよび見積りコストに基づく、本番で予想される単一のシャード問合せおよびマルチシャード問合せの数、およびシャード間結合を必要とするマルチシャード問合せの数も含まれています。
SHARDINGADVISOR_QUERYTYPESは、ワークロードの各問合せに関して、各シャーディング構成の問合せタイプをリストします。シャーディング構成に応じて、同じ問合せが異なる問合せタイプになることがあります。

シャーディング・アドバイザの出力は、通常のデータベース表に含まれているため、各種のSQL問合せを実行して様々な観点から出力を参照できます。

たとえば、シャーディング構成をランキング順序で表示するには、次のコマンドを実行します

SELECT rank, tableName as tname, tabletype as type,
           tablelevel as tlevel, parent, shardby as shardBy,
           shardingorreferencecols as cols, unenforceableconstraints,
           sizeoftable  
FROM SHARDINGADVISOR_CONFIGURATIONS
ORDER BY rank, tlevel, tname, parent;

シャーディング・アドバイザの出力表の詳細と問合せの例は、「シャーディング・アドバイザの出力表」および「シャーディング・アドバイザの出力を確認するためのSQLの例」を参照してください

シャーディング・アドバイザの推奨構成の選択

シャード・データベースに選択する構成を決定する際に、考慮する必要があるデータベース・シャーディングのいくつかの特性があります。

シャードの数を増やすと、シャード・データベースの可用性とスケーラビリティが向上します。

重複したデータを最小化すると、複数シャードを結合する必要があるマルチシャード問合せを最小限にするニーズと競合する可能性があります。通常、シャード・データベースの結合は関連するデータに対して実行されるため、同じシャード内に関連するデータを格納すると、このような結合の処理が劇的に速くなることがあります。

問合せワークロードに関して、推奨されるシャーディング構成の全体のコストは、ワークロードの各問合せタイプ(単一シャード、マルチシャード、シャード間結合を使用するマルチシャード)の数に基づいています。ここでは、クロスシャード結合を使用するマルチシャード問合せのコストが最も高く、単一シャード問合せのコストが最も低くなります。コスト情報は、シャーディング・アドバイザのSHARDINGADVISOR_CONFIGDETAILS出力表のCOST列にあります。

シャーディング・アドバイザの使用方法およびオプション

シャーディング・アドバイザは、既存の非シャード・データベースに接続してシャーディング構成の推奨事項を提供するクライアント用のコマンドライン・ツールです。

構文

gwsadv
 [-n nodeName[:portnum]]
 [-s serviceName]
  -u username
  -p password
 [-c]
 [-awr_snap_begin timestamp] 
 [-awr_snap_end timestamp]
  –w
 [sch=(schema1, schema2, …)]
 [-tab importantTabsFile]
 [-pr numpreds:n]
 [-t trace_file]

オプション

sch引数を除き、各オプションの前にマイナス記号(-)を付ける必要があることに注意してください。

オプション	説明	必須(Y/N)
`-awr_snap_begin timestamp`	開始タイムスタンプを「YYYY-MM-DD HH24:MI:SS」という書式で指定して、ワークロードを取得するAWRスナップショットを指定します。	N
`-awr_snap_end timestamp`	終了タイムスタンプを「YYYY-MM-DD HH24:MI:SS」という書式で指定して、ワークロードを取得するAWRスナップショットを指定します。	N
`-c`	新しいワークロードまたは変更されたワークロードを取得します `-pr`を使用して、取得する述語の数を制限します新しいワークロードまたは変更されたワークロードに対して初めてシャーディング・アドバイザを実行するときに必要となります。同じワークロードの後続の実行では必要ありません。デフォルトでは、ワークロードは`V$SQL_PLAN_STATISTICS_ALL`表から取得されます。または、ワークロードを自動ワークロード・リポジトリ(AWR)スナップショットから取得するには、`-c`オプションとともに`-awr_snap_begin`および`-awr_snap_end`オプションを使用し、AWRスナップショットの開始および終了のタイムスタンプを指定します。	N
`-n nodeName[:portnum]`	ノード名およびポート番号(別のホストのデータベースに接続する場合)	N
`-p password`	Oracleパスワード	Y
`-pr numpreds:n`	`-c`を使用して新規または変更されたワークロードを取得する際に取得される述語の数を制限します。	N
`-s serviceName`	サービス名(別のホストのデータベースに接続する場合)	N
`sch`	`sch`オプションは、別のユーザーとして実行する場合に、シャーディング・アドバイザを実行する対象のスキーマのリストを指定します。	N
`-t trace_file`	シャーディング・アドバイザによって実行されるすべてのアクティビティのトレースを有効にします。出力ファイル名を指定します。	N
`-tab importantTabsFile`	表名(schemaname.tablenameという形式で1行に1つ)が含まれているファイルの名前。シャーディング・アドバイザで分析する必要がある表の数を制限します。	N
`-u username`	Oracleユーザー名	Y
`-w`	シャーディング構成の生成およびランキングのために問合せワークロードを使用するように、シャーディング・アドバイザに指示します。	Y

使用上のノート

シャーディング・アドバイザの通常の使用方法は、–prオプションを指定しません。–prオプションを付けなくても、すぐに問合せワークロードの取得が高速になります。ただし、速度をさらに上げる場合は、–prオプションを使用できます。使用する場合は、–cオプションとともに使用する必要があります。指定しない場合、取得する述語の数が制限されません。

シャーディング・アドバイザの実行方法をコマンド例によって説明する手順については、「シャーディング・アドバイザの実行」および「非本番システムでのシャーディング・アドバイザの実行」を参照してください。

シャーディング・アドバイザの出力表

シャーディング構成および関連情報を確認するには、ソース・データベースと同じスキーマに格納されている次の出力データベース表を問い合せることができます。

SHARDINGADVISOR_CONFIGURATIONS表

SHARDINGADVISOR_CONFIGURATIONS表の各行は、ランク付けされたシャード構成内の表を表し、シャードまたは重複のいずれを行うか、およびシャードの場合は、表ファミリ階層内のレベル、その親表、ルート表のシャーディング・キー、外部キー参照制約およびシャードごとの表のサイズに関する情報が含まれています。

SHARDINGADVISOR_CONFIGURATIONSスキーマ

列	説明
`RANK`	ランキング・アルゴリズムに基づくシャーディング構成のランク
`TABLENAME`	シャーディング構成の表の名前
`TABLETYPE`	「S」(シャード)、「D」(重複)または「L」(ローカル)
`TABLELEVEL`	表ファミリ階層内の表のレベル(重複表の場合はNULL)
`PARENT`	表ファミリ階層内の表の親(重複表の場合はNULL)
`SHARDBY`	シャーディング方法。参照によるシャーディングの場合は子表の`REFERENCE`、または`PARENT`句によるシャーディングの場合は子表の`PARENT`です。
`SHARDINGORREFERENCECOLS`	表ファミリの子表の`REFERENCE`または`PARENT`によってパーティション化されるルート表のシャーディング・キー(重複表の場合はNULL)
`UNENFORCEABLECONSTRAINTS`	強制できない、参照列以外の外部キー制約
`SIZEOFTABLE`	シャードごとの表のサイズ

SHARDINGADVISOR_CONFIGDETAILS表

SHARDINGADVISOR_CONFIGDETAILS表の各行は、ランク付けされたシャーディング構成を表し、各タイプの表の数とシャードごとの累積サイズ、各タイプの問合せの数、およびソース・データベースの現在のワークロードに基づく見積りコストが含まれています。

SHARDINGADVISOR_CONFIGDETAILSスキーマ

列	説明
`RANK`	ランキング・アルゴリズムに基づくシャーディング構成のランク
`CHOSENBYUSER`	シャーディング構成がユーザーによって選択されている場合はY、他のシャーディング構成が選択されている場合はNULL
`NUMSHARDEDTABLES`	このシャーディング構成のシャード表の数
`SIZEOFSHARDEDTABLES`	このシャーディング構成のシャード表の累積サイズ(シャードごと)
`NUMDUPLICATEDTABLES`	このシャーディング構成の重複表の数
`SIZEOFDUPLICATEDTABLES`	このシャーディング構成の重複表の累積サイズ(シャードごと)
`NUMSINGLESHARDQUERIES`	このシャーディング構成の問合せワークロード内の単一のシャード問合せの数
`NUMMULTISHARDQUERIES`	このシャーディング構成の問合せワークロード内のマルチシャード問合せの数
`NUMCROSSSHARDQUERIES`	このシャーディング構成の問合せワークロード内の外部結合を必要とするマルチシャード問合せの数
`COST`	コスト計算アルゴリズムに基づくシャーディング構成のコスト

SHARDINGADVISOR_QUERYTYPES表

SHARDINGADVISOR_QUERYTYPES表の各行はワークロードの問合せを表し、問合せタイプおよびSQL IDをリストします。シャーディング構成に応じて、同じ問合せが異なる問合せタイプになることがあります。

SHARDINGADVISOR_QUERYTYPESスキーマ

列	説明
`RANK`	ランキング・アルゴリズムに基づくシャーディング構成のランク
`SQLID`	問合せのSQL ID
`QUERYTYPE`	このシャーディング構成の問合せのタイプ: `SINGLE SHARD QUERY`、`MULTI SHARD QUERY`または`CROSS SHARD QUERY`

シャーディング・アドバイザの出力を確認するためのSQLの例

シャーディング・アドバイザの出力は、通常のデータベース表に含まれているため、各種のSQL問合せを実行して様々な観点から出力を参照できます。

例 13-1 ランキング順でのシャーディング構成の表示

SELECT rank, tableName as tname, tabletype as type,
           tablelevel as tlevel, parent, shardby as shardBy,
           shardingorreferencecols as cols, unenforceableconstraints,
           sizeoftable  
FROM SHARDINGADVISOR_CONFIGURATIONS
ORDER BY rank, tlevel, tname, parent;

例13-2 最上位ランク・シャーディング構成の表ファミリの表示

SELECT rank, tableName as tname, tabletype as type,
        tablelevel as tlevel, parent, shardby as shardBy,
        shardingorreferencecols as cols, unenforceableconstraints,
        sizeoftable
FROM SHARDINGADVISOR_CONFIGURATIONS 
WHERE rank = 1 AND tabletype = 'S' 
ORDER BY tlevel, tname, parent;

例 13-3 ランキング順での表ファミリの表示

SELECT rank, tableName as tname, tabletype as type,
        tablelevel as tlevel, parent, shardby as shardBy,
        shardingorreferencecols as cols, unenforceableconstraints,
        sizeoftable 
FROM SHARDINGADVISOR_CONFIGURATIONS
WHERE tabletype = 'S'
ORDER BY rank, tlevel, tname, parent;

例13-4 最上位ランクのシャーディング構成の重複表の表示

SELECT rank, tableName as tname, tabletype as type,
           tablelevel as tlevel, parent, shardby as shardBy,
           shardingorreferencecols as cols, unenforceableconstraints,
           sizeoftable   
FROM SHARDINGADVISOR_CONFIGURATIONS   
WHERE rank = 1 AND tabletype = 'D'   
ORDER BY tlevel, tname, parent;

例 13-5 table_nameがルート表であるシャーディング構成の数の表示

SELECT COUNT(*)
FROM SHARDINGADVISOR_CONFIGURATIONS 
WHERE tablename = 'TABLE_NAME' AND tablelevel = 0;

例 13-6 table_nameがルート表であるシャーディング構成の表ファミリの表示

SELECT rank, tableName as tname, tabletype as type,
        tablelevel as tlevel, parent, shardby as shardBy, 
        shardingorreferencecols as cols 
FROM SHARDINGADVISOR_CONFIGURATIONS 
WHERE tabletype = 'S'
    AND rank IN
        (SELECT rank 
        FROM SHARDINGADVISOR_CONFIGURATIONS
        WHERE tablename = 'TABLE_NAME' and tablelevel = 0)
ORDER BY rank, tlevel, tname, parent;

例13-7 ランキング順でのシャーディング構成の詳細の表示

SELECT rank, chosenbyuser,
        numshardedtables as stabs, sizeofshardedtables as sizestabs,
        numduplicatedtables as dtabs,
        sizeofduplicatedtables as sizedtabs,
        numsingleshardqueries as numssq,
        nummultishardqueries as nummsq,
        numcrossshardqueries as numcsq, cost
FROM SHARDINGADVISOR_CONFIGDETAILS
ORDER BY rank;

例 13-8 選択したシャーディング構成の詳細の表示

SELECT rank,
        numshardedtables as stabs, sizeofshardedtables as sizestabs,
        numduplicatedtables as dtabs,
        sizeofduplicatedtables as sizedtabs,
        numsingleshardqueries as numssq,
        nummultishardqueries as nummsq,
        numcrossshardqueries as numcsq, cost
FROM SHARDINGADVISOR_CONFIGDETAILS
WHERE CHOSENBYUSER = ‘Y’
ORDER BY RANK;

シャーディング・アドバイザのセキュリティ

シャーディング・アドバイザは、認証されたOCI接続を使用して非シャード・データベースに接続するクライアント側のユーティリティです。

シャーディング・アドバイザでは、非シャードのソース・データベースに接続するために、適切な資格証明(ユーザー名およびパスワード)が必要となります。シャーディング・アドバイザは、シャーディング・アドバイザが分析するソース・データベースのスキーマを所有するユーザーとは別のユーザーとして実行できます。このユーザーには、非シャード・スキーマの表に対するSELECT権限が必要となります。
このユーザーには、GV$SQL_PLANビュー、GV$SQL_PLAN_STATISTICS_ALLビュー、DBA_HIST_SQL_PLAN表、DBA_HIST_SQLSTAT表およびDBA_HIST_SNAPHSOT表に対するSELECT権限が必要となります。ユーザーに他の特別な権限は必要ありません。
シャーディング・アドバイザは、権限昇格およびサービス拒否に対して脆弱ではありません。
シャーディング・アドバイザでは、パスワード、データベース・サービス名、ユーザー名などの機密データは格納または公開されません。
シャーディング・アドバイザは、製品の内部動作に関する機密詳細を公開しません。
シャーディング・アドバイザには、外部に文書化されていないインタフェースまたはAPIは含まれていません。
シャーディング・アドバイザでは、安全でないプロトコルを有効にする必要はありません。
シャーディング・アドバイザは、保護されていないモードの操作を使用しません。
シャーディング・アドバイザでは、データやその他の情報はファイルに格納されません。
データベースへのすべての接続は、認証されたOCI接続を介して行われます。
SETUID実行可能ファイルは作成されません。
PUBLICに対する新規の付与は行われません。
新しいデフォルト・スキーマは作成されませんが、シャーディング・アドバイザの実行に使用されるユーザーの配下に、シャーディング・アドバイザの内部表が作成されます。