しばちょう先生の試して納得!DBAへの道

しばちょう先生の試して納得!DBAへの道 indexページ▶▶

しばちょう先生による技術解説セミナー。
動画、資料を公開中です。

>> 最新のセミナーをチェック

しばちょう先生の試して納得!DBAへの道
第55回 SQLパフォーマンスの高速化の限界を目指せ!(3)

皆さん、こんにちは。またまた間が空いてしまいましたが前回に引き続き、[Oracle Code Tokyo 2017]においてLive Demoでチャレンジさせて頂いた、「SQLパフォーマンスの高速化の限界を目指せ!」の内容をお届けしたいと思います。

三回目の今回は、これまでにご紹介したパーティショニングやパラレル実行に上乗せする形で圧縮技術やインメモリ技術を適用することで、性能課題が発生しているSELECT文をさらに高速化させていきます。実践でどのように活用するのかの参考になれば幸いです。

以下の演習をOracle Database 12c Release 12.2.0.1 Enterprise Editionのデータベースで試してみてください。Oracle Database 12c Release 12.1.0.2環境でも動作させることは可能ですが、前回もお勧めさせて頂きましたが、是非、Oracle Database Cloud Service(以降、DBCS)のトライアルでのご活用もお勧めします。その際には、Oracle Database 18cもご利用頂けますし、Database In-Memory機能を使用可能なExtreme Performance Packageをご利用ください(2018年5月時点の情報)。DBCS上にデータベースを作成する手順は、「第50回 [Oracle Database 12c Release 2] Oracle Database Cloud Service上にデータベースを作成」を参考にしてみてください。ちなみに、Oracle Code Tokyo 2017の公開資料は、「こちら」からダウンロードすることが可能ですので、そちらも参考にしてみてくださいね。


1. 前回までの復習、チューニング方法とその効果をおさらいしましょう

shibata-55-1

前々回から2回にわたり、演習用のSales Historyスキーマを作成して頂き、それに対して性能問題が発生している2つの傾向の異なるクエリに対し得、パーティション化とパラレル実行によるチューニング効果を「リアルタイムSQL監視」で確認してきました。

パーティション化の効果としては、データを任意のルールでグルーピングしてディスクに保存するため、クエリの結果を戻すために必要となるデータを限定することが可能となります。つまり、ディスクから読み込むデータ量の削減が期待できるので、ストレージからデータベース・サーバーへデータを転送する時間を含むクエリ実行時間の削減効果があります。これは、「時間 = 道のり ÷ 速さ」の公式の右辺の「道のり(=処理データ量)」を小さくすることで、左辺の「時間(=クエリ実行時間)」が小さくなっているだけですね。

次に、パラレル実行の効果としては、クエリの実行を担当する一つのサーバープロセスが同一タイミングにおいては一つのCPUコア上でしか動作しない(= 一つのCPUコアしか同時に使えない)制約を取り払い、一つのクエリを同時に複数のCPUコアを使用して処理することで、クエリ実行時間の短縮が期待できます。これは、「時間 = 道のり ÷ 速さ」の公式の右辺の「速さ(= 処理速度)」を大きくすることで、左辺の「時間(=クエリ実行時間)」が小さくなる理屈ですね。ただし、パラレル実行の効果としてはCPUがボトルネックになっているクエリに対して効果が大きいため、今回の私が用意したI/OバウンドなSQLではほとんど処理時間が短縮しない結果となっています。ということで、このI/Oボトルネックを次の演習ではチューニングしていきます!

2. I/Oバウンドなクエリをパラレル実行した際のボトルネックの確認

これまでの復習となりますが、次の画像はI/Oバウンドなクエリをパーティション化+パラレル実行した際のリアルタイムSQL監視レポートとなります。「Time & Wait Statistics」セクションの「Database Time」の帯グラフに注目してください。この帯グラフは主にCPUを使用した時間、ディスクとI/Oした時間の比率を確認するのに非常に便利です。今回は青帯(ディスクI/O時間)で多くの割合を占められていることから、I/Oボトルネックであることが明らかですね。

shibata-55-2

ここで例の公式「時間 = 道のり ÷ 速さ」を思い出してください。クエリの処理時間(時間)を短くするためには、処理量(道のり)を減らすか、速さを速くしなければなりません。「速さを速く」する方法としてお金に頼ることができるのであれば、ディスク性能の増強であることは思いつきますよね。しかし、今回はOracle Databaseの機能を最大限に活用して実現する「処理量を減らす」にチャレンジしてみましょう。というのが表圧縮の機能となります!

shibata-55-3

Oracle Databaseでは様々な表圧縮の方法をご提供しています。また、私の過去の連載(第14回第15回第16回)でもご紹介させて頂いています。ぜひ、そちらの記事もご参考にしてくださいね。

shibata-55-4

今回は、圧縮レベル(圧縮率)が高いがCPUオーバーヘッドが最小で、DSS系だけではなくOLTP系のワークロードにも対応しているバランスの良い「高度な行圧縮」を体験して頂きましょう。

3. Oracle Database 12cからの新機能である、オンラインでのパーティション移動を利用して、SALES表のTIME_ID列が2014年1月1日よりも古いレコードが格納されるパーティションに対して「高度な行圧縮」を適用してください。

$ sqlplus sh/oracle12345@localhost:1521/pdb1
SQL> 
-- パーティションの確認
select TABLE_NAME, PARTITION_NAME, HIGH_VALUE
  from USER_TAB_PARTITIONS
where TABLE_NAME = 'SALES'
order by 1 DESC, 2 ASC ;

TABLE_NAME PARTITIO HIGH_VALUE
---------- -------- ---------------------------------------------------------
SALES      P2013Q1  TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
SALES      P2013Q2  TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
SALES      P2013Q3  TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
SALES      P2013Q4  TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
SALES      P2014Q1  TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
...(省略)...


-- 高度な行圧縮の適用
alter session force parallel ddl parallel 8 ;
alter table SALES move partition P2013Q1 row store compress advanced online ;
alter table SALES move partition P2013Q2 row store compress advanced online ;
alter table SALES move partition P2013Q3 row store compress advanced online ;
alter table SALES move partition P2013Q4 row store compress advanced online ;

USER_TAB_PARTISIONSビューでSALES表の各パーティションの定義を確認すると、2014年1月1日よりも古いレコードが格納されるパーティションはP2013Q1~P2013Q4の合計4つであることが理解できたので、この4つのパーティションに対して「オンラインでのパーティション移動」(alter table move partition online)を実行します。今回は、「高度な行圧縮」を適用するので「row store compress advanced」句も追加しています。表全体を圧縮しなくてもパーティション表の場合には、特定のパーティションに限定してデータを圧縮させることができる点も非常に便利ですね。また、オマケとなりますが、このalter table文の処理時間を短縮するためにも前回学習したパラレル実行を利用することが可能です。Alter table文は種別としてはDDL文になりますから「alter session force parallel ddl parallel n; 」となる点だけ把握しておいてくださいね。

4. データ圧縮の効果を測定するために、性能課題のある2つのクエリを並列度16でパラレル実行し、それぞれの実行時間、傾向やボトルネックをリアルタイムSQL監視レポートで確認してください。(2つのクエリは前々回の演習7と演習8を参照してください)

$ sqlplus sh/oracle12345@localhost:1521/pdb1
SQL> 
set time on timing on pages 0 lines 200

-- セッション単位でのパラレルクエリ有効化(強制的にパラレル度16)
alter session force parallel query parallel 16 ;

-- CPUバウンドなSQLの実行
@query_CPU.sql

...(省略)...

経過: 00:00:08.57


-- I/OバウンドなSQLの実行
@query_IO.sql

...(省略)...

経過: 00:00:07.35

いかがでしょうか?実行方法はパラレル実行を適用した際と全く同じですから解説は不要かと思いますが、それぞれのクエリの速度は・・・どちらも見事に高速化していますね!ここまでを整理しておくと次の表の通りとなり、両クエリともに当初の実行時間と比較して12倍まで高速化しています。しかも、問題のSELECT文は一切書き換えていませんからね!

shibata-55-5

しかし、データを圧縮することで何故早くなったのかの裏付けをしておきましょう。ということで、毎度お馴染みのリアルタイムSQL監視レポートをそれぞれ見ていく事にしましょう。まずは、CPUバウンドなSQLの実行結果からです。

shibata-55-6

今回注目すべきは、「IO Statistics」セクションの「IO Bytes」の数字です。パーティション化の効果を確認する際にも見て頂きましたが、これはクエリ実行時にディスクとI/Oしたデータ総量を示していますね。データ圧縮前に実行した際は、この値が「6GB」でしたが今回は「2GB」と三分の一に削減されています。つまり、ディスクからデータを読み込むために要した時間が三分の一に減少しているので、クエリ実行時間が短縮・高速化したわけですね。データ圧縮機能恐るべしですね。

次に、I/OバウンドなSQLの実行結果も確認してみると、同様に「IO Bytes」の値が「6GB」から「2GB」に削減されていますね。

shibata-55-7

ということで、いよいよ最後の高速化チューニング方法にチャレンジしていきましょう!

5. 演習4のI/OバウンドなSQLを実行した際のリアルタイムSQL監視レポートを再度確認し、ボトルネックの発生状況と更なるチューニング方法を検討してください。

shibata-55-8

演習2の復習となりますね。「Time & Wait Statistics」セクションの「Database Time」の帯グラフに注目すると、今回も青帯(ディスクI/O時間)で多くの割合を占められていることが確認できます。はい、まだまだI/Oボトルネックが発生していると推測することができるのです。このI/Oボトルネックを解消するためのOracle Databaseの最新テクノロジーとは・・・、はい!Oracle Database 12c Release 1から登場したOracle Database In-Memoryですね!

Oracle Database In-Memoryの特徴は、その導入のし易さだと私は考えます。データベースをインメモリ用にゼロから作成し直す必要はないですし、格納されているデータを入れなおすことも必要ありません。インメモリ化したい表の属性をalter table文で一瞬で変更するだけで良いのです。また、インメモリ化したからデータの更新ができなくなるわけではありませんし、更新処理が大幅に劣化することもありません。インメモリ化することでメモリ上にデータ分析のワークロードに最適な列型フォーマットでキャッシュされます。しかも、圧縮も可能なのでより多くの列データをメモリ上に配置できます!さらに、DML処理はこれまで通り行型フォーマットで扱えるというデュアル・フォーマットをご提供する、なんともワクワク・ドキドキな機能となっております。

6. 性能課題のある2つのクエリがアクセスしている表をインメモリ化してください。

$ sqlplus / as sysdba
SQL> -- 今回はCDBレベルでインメモリが使える状態に変更
alter system set inmemory_size=3g scope=spfile ;

-- SPFILEの設定を読み込ませるため、データベース・インスタンスを再起動して、SGA内にインメモリ領域を確保
$ srvctl stop database -d orcl -o immediate
$ srvctl start database -d orcl

$ sqlplus sh/oracle12345@localhost:1521/pdb1
SQL> -- 対象表のインメモリ属性を有効化
alter table TIMES inmemory priority high;
alter table PRODUCTS inmemory priority high;
alter table CHANNELS inmemory priority high;
alter table SALES modify partition P2013Q1 inmemory priority high;
alter table SALES modify partition P2013Q2 inmemory priority high;
alter table SALES modify partition P2013Q3 inmemory priority high;
alter table SALES modify partition P2013Q4 inmemory priority high;

まずは、データベース・インスタンスのSGA内に列データが格納される領域が必要となりますので、今回はCDBレベルで初期化パラメータinmemory_sizeにサイズを指定して再起動を行いました。次に、PDBレベルで対象表のインメモリ属性を有効化するだけで設定は完了します。この後、自動的にディスクから対象データが読み込まれつつ、列フォーマットに変換されたデータがインメモリカラムストア領域(バッファ・キャッシュとは別)にキャッシュされる仕組みとなっています。(キャッシュ処理が終わる前にもクエリを実行できますが、その場合はディスクからの読み込みとなり、インメモリ処理にはなりません)メモリサイズは有限ですから、どのセグメントを優先的にキャッシュすべきかの優先度も表・パーティションの属性として設定しておくことも可能です。とても便利ですね。

7. インメモリ化の効果を測定するために、能課題のある2つのクエリを並列度16でパラレル実行し、それぞれの実行時間、傾向やボトルネックをリアルタイムSQL監視レポートで確認してください。(2つのクエリは前々回の演習7と演習8を参照してください)

$ sqlplus sh/oracle12345@localhost:1521/pdb1
SQL> 
set time on timing on pages 0 lines 200

-- セッション単位でのパラレルクエリ有効化(強制的にパラレル度16)
alter session force parallel query parallel 16 ;

-- CPUバウンドなSQLの実行
@query_CPU.sql

...(省略)...

経過: 00:00:07.67


-- I/OバウンドなSQLの実行
@query_IO.sql

...(省略)...

経過: 00:00:00.42

さて、いかがでしょうか!!な、なんと・・・I/OバウンドなSQLの実行処理時間が0.42秒と見事に1秒の壁を突破することができましたね!恐るべし、Oracle Database In-Memoryの効果を体験して頂けたかと思います。これは例の公式の「時間 = 道のり ÷ 速さ」の右辺である「速さ」部分を高速化したチューニング方法であることは説明の必要が無いですね。

全てのチューニング結果を整理すると次の通りとなります。性能問題が発生しているSELECT文を一切書き換えずに、Oracle Databaseの最新テクノロジーを適用していくだけで、最大214倍まで高速化することが実現できましたね!(あくまで私のデモ環境での効果であり、お客様すべての環境においての効果を保証するものではございませんのでご注意ください)

shibata-55-9