カテゴリー別アーカイブ: 性能関連

インデックス再構築・再構成(SQL Server)

ITコーディネータのシュウです。

IMG_1531

先日、妻の実家のある富山のほうに行く機会があり、そこできれいな夕焼けが見れたので、撮った写真です。車に乗っていた娘が、きれいだよ!というので、慌てて車の中から撮ったものです。
そのときも、本当に昼間は暑い日が続いていて、これは大変だと思っていましたが、その後もさらに暑さが増して、今年は本当に危険な暑さになってますよね。埼玉の熊谷では、41.1℃という、国内最高気温を更新するという状況になりました。クーラーが効いていないところでは、本当に大変かと思います。
皆さん、本当にお体には注意して頂きたいと思います。

<本日の題材>
インデックス再構築・再構成(SQL Server)

前回、SQL Serverの動的管理ビューを使用することで、パフォーマンスに影響があるクエリーの確認や不足しているインデックスなどを確認できることをブログで取り上げましたが、今回もその続きということで、取り上げてみたいと思います。
データベースはインデックスをうまく設定することで、抽出処理のレスポンスを効果的に向上させることができますが、当初それほど遅くなかった抽出処理が、いつの間にかとても遅くなっていることに気づくことがあります。その原因の一つに、インデックスの断片化というものがあります。

以前、大手企業の分析用のシステムを見ていたときがありました。日々基幹システム側から日次のデータが分析用のデータベースに送られてくるので、削除・登録などの処理を夜間に行って、分析用データベースを本番環境の前日終わり時点のデータにしていました。毎日、大量のトランザクションデータがあるため、夜間に送られてくるデータもかなりのボリュームがあり、夜間バッチ処理も結構な時間がかかっていました。
そして、分析用に作成したクエリーやプロシージャなどの処理が、作成した当初はそれほど遅くなかったのに、とても遅いと感じるようになることがありました。
原因を調べてみると、インデックスの断片化がかなりの割合で発生しており、ページ密度が大きく低下し、読み取りのページ数もその分多くなって、効率が悪くなっていたことが大きな要因でした。
当時は、SQL Server 2000を使っていたので、DBCC SHOWCONTIG コマンドで断片化を確認し、DBCC DBREINDEXコマンドで再構築を行ったり、 DBCC INDEXDEFRAGコマンドで再構成を行ったりということを、不定期に行っていました。

今は、SQL Server のバージョンも上がり、動的管理ビューを使用することで、インデックスの断片化も確認できます。以下がそのSQLの例になります。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.page_count,s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), NULL, NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
  and i.index_id = s.index_id
WHERE s.avg_fragmentation_in_percent > 10
ORDER BY avg_fragmentation_in_percent DESC

断片化1

ここでの、主な項目の意味としては、
page_count                                                 ページ数
fragment_count                                       断片化しているページ数
avg_fragmentation_in_percent      断片化の割合(%)

上記の結果では、断片化の割合が10%以上のものを抽出していますが、結構高い割合のものがあることが確認できます。

また、抽出SQLのdm_db_index_physical_stats 関数の5番目の引数であるスキャンモードには、LIMITED, SAMPLED, DETAILEDの3種類があります。(上記の例は、LIMITED モード)
それぞれのスキャンモードの意味は以下になります。

LIMITED           
        最も高速なモードで、スキャンするページ数は最小

SAMPLED         
        SAMPLED モードでは、インデックスまたはヒープの全ページの 1% のサンプルに基づく統計情報が返されます。インデックスまたはヒープのページが 10,000 ページに満たない場合は、SAMPLED モードの代わりに DETAILED モードが使用されます。
 
DETAILED       
        DETAILED モードではすべてのページがスキャンされ、すべての統計が返されます。

それで、断片化を解消するには、以下の3つの方法があります。1)     インデックスのオフライン再構築
2)     インデックスのオンライン再構築(Enterprise エディション のみで利用可能)
3)     インデックスの再構成

1)     インデックスのオフラインでの再構築のコマンドは以下になります。

ALTER INDEX インデックス名 ON テーブル REBUILD

インデックスの再構築(オフライン)は、新しい領域へインデックスを再作成し、古いインデックスを削除することで、断片化を解消します。したがって、インデックスの再構築中は、インデックス全体がロックされるので、再構築が完了するまでは、ユーザー操作は待ち状態になり、注意が必要です。

例)
ALTER INDEX PK_T_USER_LOG ON dbo.T_USER_LOG REBUILD

インデックス再構築_v8_1

再構築の後で、再度このテーブルについての断片化を見てみます。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.page_count, s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), OBJECT_ID(N'T_USER_LOG'), NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
  and i.index_id = s.index_id;

インデックス再構築後_v8_2

上記の結果から、断片化の割合は 0% になり、断片化が解消されたことがわかります。

2)     インデックスのオンライン再構築のコマンドは以下になります。

ALTER INDEX インデックス名 ON テーブル REBUILD WITH ONLINE = ON

3)     インデックスの再構成のコマンドは以下になります。

ALTER INDEX インデックス名 ON テーブル REORGANIZE

例)
先ほどの、断片化の一覧で、断片化が35%程度であった、「T_USER_LOG_V6」について、再構成を行ってみます。最初に、現状の断片化を再度確認してみます。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.index_id, s.alloc_unit_type_desc, s.page_count, s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), OBJECT_ID(N'T_USER_LOG_V6'), NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
and i.index_id = s.index_id;

インデックス再構成前_3

再構成を実施してみます。

ALTER INDEX PK_T_USER_LOG_V6 ON dbo.T_USER_LOG_V6 REORGANIZE;

インデックス再構成後_4

件数が多少多かったこともあり、少し時間がかかった感じがありました。

断片化を再度確認してみます。先ほどと同じSQLを実行してみます。

インデックス再構成後_5

先ほど35%程度あったところは、2.4% ほどに変わりました。断片化がかなり解消されたことがわかります。

★インデックスの再構築と再構成の違いについて簡単に記します。
〇再構築
・ひとつのトランザクションでインデックスを完全に再作成。
 そのため、キャンセルすればそのトランザクションはロールバ
 ックされ、断片化した状態に戻る。
・インデックスを作り直すので、テーブルのレコード数が増えな
 い限り、大きく処理時間が変わることはない。
・一時的には、断片化した古いインデックスと再作成した新しい
 インデックスがふたつ存在する状況となり、それらを格納する
 ための容量、及びソート用の領域が必要になる。

〇再構成
・処理単位ごと (処理単位はページ) にトランザクションが分割さ
 れていて、リーフページ間でインデックス行を移動させること
 で行を前に詰めて断片化を解消している。
 キャンセルしても、既に完了しているトランザクションの結果
 は維持される。
・前のページに空きがある場合に後ろのページから前のページへ
 行を移動するという処理であるため、断片化の度合が大きいほ
 ど処理時間が長くなる。
・追加の領域は基本的には必要としない。

実際の運用では、上記のような違いを考慮してどちらを選択するかを決定する必要があると思います。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

動的管理ビュー(SQL Server)

ITコーディネータのシュウです。

IMG_1498

いよいよ梅雨の季節が近づいてきました。上の写真は、少し前に娘がスマホで撮って送ってくれたきれいな夕方の空です。少し神秘的ですね。梅雨になるとこういう空もなかなか見れなくなっちゃうな。
さて、最近のスポーツでは、サッカーのワールドカップがいよいよ始まります。2か月前に監督の交代劇があり、このところの強化試合も負け続けているので、難しいと思っちゃいますが、どうか奇跡よ起きて~! 2002年の日韓共催のワールドカップのベスト16を超えてほしい!
また、卓球では先日の荻村杯・ジャパンオープンで、男子は張本智和選手、女子では伊藤美誠選手が優勝しました。接戦を最後まで粘って勝ち切った精神力は本当にすごい、感動します。日本選手が頑張っていると、元気が湧いてきます。よーし、仕事も頑張るぞー!

<本日の題材>
動的管理ビュー(SQL Server)

SQL Serverを使用していて、パフォーマンスがいまいちよくない場合など、最もCPUを多く使っているクエリーは何か?、最も多くのI/Oを発生させているクエリは何か?、不足しているインデックスはないか?などを確認したくなることがあると思います。そんなときに、知っていると便利な動的管理ビューについて、今回は取り上げてみたいと思います。

SQL Serverというと、パフォーマンスモニターや利用状況モニターなど、GUIのツールでいろいろと調べるというイメージが強く、逆にOracleは、V$表といわれる動的パフォーマンスビューなどを駆使していろいろと調べるというイメージが強いですね。(最近はOracleもEnterprise ManagerなどでGUIでも見れるかたちにしてきていますが)
ただ、SQL Serverでも、動的管理ビューというものから、いろいろと上記のような内容を調査することができます。
ちなみに、動的管理ビュー(DMV:Dynamic Management View)は、SQL Server2005で追加された機能で、パフォーマンス・チューニングを手掛けるエンジニアにとっては画期的といえるものでした。それまで、ほとんどドキュメント化されず、その状態を参照する方法も知らされていなかったSQL Serverの内部が、広く可視化されたからです。
それでは、以下に例を挙げてみましょう。

●最もCPUを多く使っているクエリーの上位10位までを確認する

SELECT TOP 10
  total_worker_time/execution_count AS avg_cpu_cost,
  execution_count,
  SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset 
         WHEN -1 THEN DATALENGTH(st.text) 
        ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) AS query_text,
  query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY avg_cpu_cost DESC;

avg_cpu_cost

上記は、「sys.dm_exec_query_stats」という動的管理ビューから抽出できる情報で、「total_worker_time」(プランの実行で使用されたcpu時間の合計)を「execution_count」(プランが実行された回数)で割った値を平均のcpuコストとして、それの多い順に10件を表示しています。ただし、実行頻度(execution_count)が少なければ、それほど重要ではないと思われます。
(※注意点としては、現在キャッシュされているクエリに対する集計情報のみを示していること。メモリが多くなければ、過去に行ったコストの高いクエリがキャッシュから削除されている可能性もあるので、定期的にこの抽出を行えば、コストの高いクエリを特定できる可能性が高くなると思われます。)

なお、上記のSQLでは、前回取り上げた「CROSS APPLY」を使用しています。また、「querry_plan」の列を最後に表示していますが、これをクリックすると、以下のように「querry_text」で表示されたSQLの実行時の実行プランが表示できます。(ただし、一緒に表示するとその分抽出処理が重たくなると思われるので、後から plan_handleを元に実行プランを抽出することも可能です。)

querr_plan

参考:https://docs.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-2017

●最も多くのI/Oを発生させているクエリの上位10位までを確認する

SELECT TOP 10
  (qs.total_logical_reads/qs.execution_count) AS avg_logical_reads,
  (qs.total_logical_writes/qs.execution_count) AS avg_logical_writes,
  (qs.total_physical_reads/qs.execution_count) AS avg_phys_reads,
  execution_count,
  SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset 
         WHEN -1 THEN DATALENGTH(st.text) 
        ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) AS query_text,
  query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY (total_logical_reads + total_logical_writes) DESC;

io_top10

 こちらは、「total_logical_reads」(論理読み取りの合計数)と「total_logical_writes」(論理書き込みの合計数)の合計の多いものから順に上位10件を表示しています。

●不足していると思われるインデックスの一覧

SELECT gs.avg_user_impact AS [予測されるクエリパフォーマンス改善率],
       gs.avg_total_user_cost AS [削減できたクエリの平均コスト],
       gs.last_user_seek AS [最後にシークした時間],
       id.statement AS [テーブル名] ,
       id.equality_columns AS [等値述語に使用できる列],
       id.inequality_columns AS [不等値述語に使用できる列] ,
       id.included_columns AS [包括列として必要な列],
       gs.unique_compiles AS [コンパイルおよび再コンパイルの数],
       gs.user_seeks AS [クエリによって発生したシーク数]
FROM  sys.dm_db_missing_index_group_stats AS gs
       INNER JOIN sys.dm_db_missing_index_groups AS ig
                                          ON gs.group_handle = ig.index_group_handle
       INNER JOIN sys.dm_db_missing_index_details AS id
                                          ON ig.index_handle = id.index_handle
-- WHERE id.[database_id] =DB_ID(‘db名’)
   -- コメントをはずすと指定したデータベースでの抽出となる
Order By gs.avg_user_impact DESC;

インデックス不足

 上記は、「sys.dm_db_missing_index_group_stats」「sys.dm_db_missing_index_groups」「sys.dm_db_missing_index_details」という動的管理ビューから抽出できる情報で、インデックスが存在していたなら改善されるであろうパフォーマンス改善率などを表示するものになっています。
上記の動的管理ビューの結果からインデックスを追加作成するとした場合には、[equality_columns(等値述語に使用できる列)]を最初に指定し、その次に、[inequality_columns(不等値述語に使用できる列)]を指定し、INCLUDE句に[included_columns(包括列として必要な列)]を指定するかたちになります。

※参考:https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008-r2/ms345405(v=sql.105)

例えば、1番目の結果からインデックスを追加するとしたら、

CREATE NONCLUSTERED INDEX [KEY1_製品区分別積上] ON [dbo].[製品区分別積上]
(
              [年度], [月], [バージョン], [部門コード], [製品区分], [製品項目]
)
INCLUDE (
[金額], [粗利率], [粗利額]
);

レスポンス改善を行いたいときに、結構有効かも知れませんね。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

バインド変数(Oracle)

ITコーディネータのシュウです。

IMG_1388

ゴールデンウイークも終わり、ときに夏を思わせるような暑い日もある5月も後半になりました。
上の写真は、ゴールデンウイークに家族で群馬県館林市にあるつつじが岡公園のつつじまつりに行ってきたときのものです。
樹齢800年を超えるヤマツツジの巨樹群が自然形のままで保存され、歴史的価値も高いとのことです。時期的には少し遅かったので、見頃の時期は過ぎてしまっていましたが、それでもまだ咲いているつつじは結構あって、たくさんのお客様で賑わっていました。海外の人も結構いましたね!
天気がよくて気持ちよかったです。ちょっとでも自然に触れるのはいいものですね。
なかなか家族サービスができない現状だけど、少しは喜んでくれたかな(^^!

<本日の題材>
バインド変数(Oracle)

SQLでパフォーマンスを改善させる方法、及びセキュリティの観点からSQLインジェクションを防ぐ方法ということで、バインド変数を使うということがよく話題に上がります。今回は、Oracle環境でのバインド変数について取り上げてみたいと思います。
バインド変数とは、SQL文に埋め込む変数のことで、実際の値を後から設定できるようにSQL文の一部を変数にしたもので、これを利用することで、SQL文の記述を統一する(データベースからみて同一のものとみなせる)ことができます。

OracleがSQLを実行するステップは、大きくは以下のようになっています。
・解析処理(PARSE)
・実行処理(EXECUTE)
・データの取り出し処理(FETCH:SELECT文のときのみ)

解析処理には、構文チェック、表・列の定義チェック、権限チェック、実行計画の生成などが含まれ、一度解析したSQLは実行計画とともにSGAの共有プールのライブラリキャッシュにキャッシュされます。
SQLが発行されると、Oracleはライブラリキャッシュに同一のSQLの解析結果がキャッシュされていないかを確認し、キャッシュされていればそれ以降の解析処理をスキップして、キャッシュ上の実行計画を使ってSQLを実行します。(このことをSOFT PARSEと呼びます)
同一の解析結果が存在しない場合、上記の解析処理を実行しますが、このHARD PARSEが比較的重たい処理のために、これを省くことができればスループットの向上に繋がります。

そして、この共有プールにキャッシュされた情報は、SQLが完全に一致するときのみ再利用されるため、条件値が異なったり、SQL文の大文字・小文字や改行位置、スペースの個数など一部でも異なると違うSQLとして新しく解析されてしまいます。こういう仕組みのため、バインド変数を使うことで同一のSQLとみなせれば、HARD PARSEを回避できるというメリットがあります。
(ちなみに、SQLのソースコードの中に、直接記述した数値や文字列のことをリテラルと呼びます)

具体的に試してみます。
以前、バルク処理というところで作成した顧客テーブル(20万件)を使って、SELECT文の処理速度をリテラルの場合とバインド変数を使った場合で比較してみます。

テーブル定義は以下:

CREATE TABLE customer(
 c_id number(8)
,c_name varchar2(20)
,Constraint PK_customere Primary key(c_id));

テストデータを作成します。

BEGIN
 FOR i IN 1..200000 LOOP
   INSERT INTO customer(c_id, c_name) VALUES(i, '
顧客名_'||LPAD(TO_CHAR(i),8,'0'));
 END LOOP;
END;
/

このデータを、バインド変数を使わずに、主キーである c_id 列の値で SELECTしてみます。以前、実行の度にSQLが変わる場合について、動的SQLのEXECUTE IMMEDIATE文を使用する方法について取り上げたことがありましたが、今回もそれを使ってみます。

時間を計測したいので、時間の計測を可能にします。
SET TIMING ON

BEGIN
   FOR I IN 1..200000 LOOP
    EXECUTE IMMEDIATE 'SELECT * FROM customer WHERE c_id = ' || TO_CHAR(I);
   END LOOP;
END ;
/

動的sqlリテラル

3分2秒かかりました。

それでは、次は c_id = の条件のところを、バインド変数を使って行ってみます。
処理時間を厳密に確認するために、一旦、バッファキャッシュ、共有プールの情報をクリアします。

ALTER SYSTEM FLUSH  BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

メモリクリア

先ほどのSELECTの処理を、バインド変数を使って実行します。
 
BEGIN
    FOR I IN 1..200000 LOOP
        EXECUTE IMMEDIATE 'SELECT * FROM customer WHERE c_id =:C1' USING I;
             -- :C1 のところがバインド変数 、USING I で変数 I を使用
    END LOOP;
END ;
/

動的sqlバインド変数

5秒あまりで処理が終了しました。全然違いますね。

実際に、for loop 処理で生成したSELECT文について、共有SQL領域についての統計情報を示すV$SQLを抽出して確認してみます。 

select count(*) from V$SQL where SQL_TEXT like 'SELECT * FROM customer WHERE c_id%';

v$sql_統計情報

結果は1件と出ましたので、20万回のSELECT文がすべて同じ1回のSQL文を共有したということがわかります。実行計画を生成する HARD PARSEは1回のみであり、残りはすべて共有プール上のその実行計画を使って行ったということですね。

今回は、バインド変数を使うことで処理が非常に高速化しましたが、そうならないケースもあるようです。それについては、今回は特には記述はしませんが、また機会があれば確認してみたいと思います。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

ビットマップインデックス(Oracle)

ITコーディネータのシュウです。

1395797133260_2

今月27日に再び発生した大規模なサイバー攻撃によって、世界各地で被害が出ています。今回のランサムウェアは専門家の間で「Petya」と呼ばれるウイルスが使われたとみられているそうです。WannaCryと同様に、WindowsのSMB(Server Message Block)の脆弱性(通称Eternal Blue)を利用しているとのことで、対策としては、Windowsを常に最新の状態にすること、アンチウィルスソフトウェアを最新のものにしておくこと、さらにメールにあるURLをクリックする際には十分な注意が必要ですね。また、いざというときにバックアップを取ることも推奨されています。
IPA独立行政法人情報処理推進機構セキュリティセンターが出している「情報セキュリティ10大脅威 2017」でも、個人向け、組織向けともに、ランサムウェアによる被害が2位に入っていますね。組織向けの1位は、こちらもよく話題になる標的型攻撃による情報流出です。様々な情報を扱う企業としては、社員への教育を含め様々なリスクを考慮した対策が必要ですね。
本当に、いつも危険と隣り合わせていることを自覚しながらPCを使わないといけない時代になってきたと感じます。

さて、dbSheetClientに新しい事例がまたアップされています。
dbSheetClientで内製化を実現、MS-Accessで開発した『総合管理システム』をdbSheetClientでWeb化し、抱えていた課題を全てクリア !!ということで、綜合エナジー株式会社様の事例です。
興味のある方は以下をご参照ください。
http://www.newcom07.jp/dbsheetclient/usrvoice/sogo_energy.html

<本日の題材>
ビットマップインデックス(Oracle

Oracleで検索の性能を向上するためにインデックスを作成しますが、データウェアハウスなどを構築した際には、カーディナリティの値が低い(取りうる値が限られている)列に対しては、ビットマップインデックスを作成することで効果があるという話しを聞くことがよくありますが、今回、それを試してみたいと思います。

例)
性別、血液型などのような取りうる値が限られている列を持つ顧客マスタを作成し、そこに300万件の顧客データをテスト的に作成してみたいと思います。前回題材に上げたバルク処理を使って、データの登録処理時間を短縮したいと思います。

まず、顧客マスタテーブルを作成します。

CREATE TABLE t_customer(
 c_id number(7)
,c_gender  varchar2(4)          -- 性別
,c_blood_type varchar2(2)  -- 血液型
,c_age     number(3)                  -- 年齢
,constraint PK_t_customer Primary key (c_id));

次に、前回題材として取り上げたバルク処理を使って、テストデータを作成してみます。

 DECLARE
  TYPE c_id_t IS TABLE OF t_customer.c_id%TYPE
  INDEX BY PLS_INTEGER;
  c_id c_id_t;

  TYPE c_gender_t IS TABLE OF t_customer.c_gender%TYPE
  INDEX BY PLS_INTEGER;
  c_gender c_gender_t;

  TYPE c_blood_type_t IS TABLE OF t_customer.c_blood_type%TYPE
  INDEX BY PLS_INTEGER;
  c_blood_type c_blood_type_t;

  TYPE c_age_t IS TABLE OF t_customer.c_age%TYPE
  INDEX BY PLS_INTEGER;
  c_age c_age_t;

BEGIN
 FOR i IN  1..3000000  LOOP
   c_id(i) := i;
   c_gender(i) := CASE mod(c_id(i), 3) WHEN 0 THEN '男性' WHEN 1 THEN '女性' ELSE null END;
   c_blood_type (i) := CASE mod(c_id(i), 5) WHEN 0 THEN 'A' WHEN 1 THEN 'B' WHEN 2 THEN 'O' WHEN 3 THEN 'AB' ELSE null END;
   c_age(i) := 20+mod(c_id(i), 50);
 END LOOP;

 FORALL j IN 1..3000000
   INSERT INTO t_customer(c_id, c_gender, c_blood_type, c_age) VALUES(c_id(j), c_gender(j), c_blood_type(j), c_age(j));
END;
/

今回も、処理時間を測定したいので、sqlplus 上で以下を実行してから、上記の処理を実行します。
SET TIMING ON
上記のpl/sqlを実行します。

blog80_buld

24.89秒で処理が終了しています。これを、通常のLOOP処理で実行すると、3分22秒程度時間がかかりましたので、やはりバルク処理が有効だということを改めて確認しました。

データを確認してみます。

SELECT * FROM t_customer
ORDER BY c_id;

blog80_select

件数も確認してみます。
Select count(*) from t_customer;

blog80_count

データが確かに300万件登録されているのが確認できます。

この t_customer テーブルのデータの中に、男性以外(NULLデータも含む)で、血液型が「A」型と「B」型の人が何人いるかを抽出してみます。そのときも実行計画も合わせて表示させてみます。

set autotrace on

select count(*) from t_customer
where c_gender <> '男性'
 and c_blood_type in ('A','B');

blog80_jiko1

実行計画を見ると、t_customerテーブルを「TABLE ACCESS FULL」で全レコードにアクセス(フルスキャン)して該当レコードの件数を確認していることがわかります。

この t_customer テーブルの性別、血液型項目に通常のインデックスを作成してみます。

CREATE INDEX cust_idx2 ON t_customer (c_gender);
CREATE INDEX cust_idx3 ON t_customer (c_age);

blog80_btree_ind

それぞれ、7.2秒、5.98秒と、多少時間がかかることがわかります。

この状態で、再度先ほどの男性以外で、血液型が「A」型と「B」型の人が何人いるかを、同じSQLで実行してみます。

blog80_jiko2

実行計画は、先ほどのインデックスがないときと同じで、特に追加したインデックスを使用しない動作になっています。
カーディナリティの値が小さい列へのBtreeのインデックスは作成してもあまり効果がないとも言われます(実行するSQLにも当然依ります)が、DBがフルスキャンを選択したことは今回のケースではBtreeインデックスは有効ではないことをがわかります。

今度は、先ほどのインデックスを削除し、性別、血液型項目にビットマップインデックスを作成してみます。

CREATE BITMAP INDEX cust_bm_idx2 ON t_customer (c_gender);
CREATE BITMAP INDEX cust_bm_idx3 ON t_customer (c_blood_type);

blog80_bitmap_ind

ビットマップインデックスの作成時間はかなり短いですね。

この状態で、再度先ほどの男性以外で、血液型が「A」型と「B」型の人が何人いるかを、同じSQLで実行してみます。

blog80_jiko3

今度は、作成したビットマップインデックスを使用した実行計画になり、処理の実行時間も0.17秒とかなり短縮されることがわかります。このように、データにnullが含まれている場合でも、カーディナリティが低い列であれば、ビットマップインデックスを作成することで、抽出処理が高速化することが確認できると思います。
※ただし、ビットマップインデックスを作成した状態での、データの登録や更新は、b-treeのインデックスがある場合に比べてかなり遅くなることも事実であり、データウェアハウスなどの抽出が中心のデータベースの場合などに絞ったほうがよいようです。
 
今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

バルク処理(Oracle)

ITコーディネータのシュウです。

IMG_0682

5月もあと2日になりました。あっという間に日が過ぎて行きますね。今年もG/Wのときに、加須市の玉敷神社の藤まつりに行って来ました。上記はその写真です。たまに家族みんなで出かけるのもいいものです。
そう言えば、車に乗っているときに、家内が道端にきれいに咲いていた芝桜があったらしく、きれいね~と言ったので、桜ということで、思わず空のほうを見て桜を探してしまったところ、また笑われてしまいました。

芝桜と言えば、日本各地にきれいで有名なところはあると思いますが、宮崎県新富町に、目の見えなくなってしまった奥様に笑顔を取り戻したいということで、20年以上かけて庭に一面のピンクの芝桜を育てた黒木さんという方の庭の芝桜がきれいだということで有名みたいですね。フジテレビのMr.サンデーという番組でも放送され、とても感動的で反響が多かったそうです。黒木さんは毎年芝桜のシーズンになるとご自宅の庭をボランティアで開放、オープンガーデンとし、連日多くの見物客に花を楽しませてくださっておられるとのことです。そこに込められた夫婦愛が素晴らしいですね。そのように妻を愛せる夫になりたいものです。
http://www.pmiyazaki.com/etc/sibazakura/
ただ、一面に広がるシバザクラを皆さんに開放するのは、今年(2017年)で最後となるとのことです。芝桜の手入れはとても大変なようで、ご高齢ということもあり、残念ですが、本当に長い間お疲れ様でした。

さて、dbSheetClientに新しい事例がまたアップされています。dbSheetClientを使って全社をカバーする「予算実績管理システム」を構築! SAP(ERP)システムとのデータ連携も実現!ということで、クレスコ・イー・ソリューション株式会社様の事例です。興味のある方は以下をご参照ください。
http://www.newcom07.jp/dbsheetclient/usrvoice/cresco_esol.html

<本日の題材>
バルク処理(Oracle

OracleでPL/SQLを使って、ループの処理を行うことはよくあることだと思います。そのループ文などのPL/SQLプログラムは、内部的にはSQLエンジンとPL/SQLエンジンの2つのエンジンが、それぞれSQL文とPL/SQL文の処理を担当して実行するため、ループの回数分エンジンの切替が発生しています。この制御の移行はコンテキスト・スイッチと呼ばれ、その都度オーバーヘッドが発生して、パフォーマンスが低下することになります。
このようなオーバーヘッドを削減できる機能として、バルク処理というものがあり、エンジンの切替を最小限に抑えることができるということです。今回は、このバルク処理について取り上げてみたいと思います。

例)
顧客マスタを作成し、そこに20万件の顧客データをテスト的に作成してみたいと思います。それを、今回は FOR LOOP文で作成してみますが、その際に、普通にLOOP処理を行うのと、バルク処理を行うので、処理時間も比較して見ます。

まず、顧客マスタテーブルを作成します。

CREATE TABLE customer(
 c_id number(8)
,c_name varchar2(20)
,Constraint PK_customere Primary key(c_id));

次に、FOR LOOP文で、テストデータを作成します。

BEGIN
 FOR i IN 1..200000 LOOP
   INSERT INTO customer(c_id, c_name) VALUES(i, '顧客名_'||LPAD(TO_CHAR(i),8,'0'));
 END LOOP;
END;
/

今回は、処理時間を測定したいので、sqlplus 上で以下を実行してから、上記の処理を実行します。

SET TIMING ON

forloop

処理に8.25秒かかっていますね。データを念のため確認してみます。

SELECT * FROM customer
ORDER BY c_id;

blog79_select_cust

データが登録されていることが確認できます。

このようなINSERTの処理を、バルク処理で行う場合、FORALL文を指定して実行します。

DECLARE    -- ①
 TYPE c_id_t IS TABLE OF customer.c_id%TYPE
 INDEX BY PLS_INTEGER;
 c_id c_id_t;

 TYPE c_name_t IS TABLE OF customer.c_name%TYPE
 INDEX BY PLS_INTEGER;
 c_name c_name_t;

BEGIN
 FOR i IN  1..200000  LOOP        -- ②
   c_id(i) := i;
   c_name(i) := '顧客名_'||LPAD(TO_CHAR(i),8,'0');
 END LOOP;

  FORALL j IN 1..200000
   INSERT INTO customer(c_id, c_name) VALUES(c_id(j), c_name(j));       -- ③

END;
/

まず、①宣言部で、TABLE型のコレクション c_id, c_name を定義します。ここで、コレクション変数とは、同じデータ型の値を複数格納できる変数のことです。そして、②のFOR LOOP文でそのコレクションに200000行を代入します。その後、③のINSERT文のVALUES句にこのコレクションを指定し、200000行のデータを customer表に一括挿入します。このとき、DML分の直前に FORALL文を指定します。

構文は以下:
  FORALL <索引名> IN <下限値>..<上限値> <DML文>

この処理では、INSERT文はSQLエンジンで一度に処理できるため、エンジンの切替は1回で済むことになり、高速化されます。

実際に試してみようと思いますが、データを削除した後、パフォーマンスの比較を正確に行いたいので、一旦メモリをフラッシュします。

truncate table customer;

connect / as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;

上記のpl/sqlを実行します。

blog79_bulk

通常のLOOP処理で8.25秒かかっていた20万件の登録処理が、0.88秒で終わったことが確認できます。

データを再度確認します。

SELECT * FROM customer
ORDER BY c_id;

blog79_select_cust2

SELECT COUNT(*) FROM customer;

blog79_count

データが確かに20万件登録されているのが確認できます。
バルク処理を行うことで、処理はかなり高速化されることがわかりますね。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

実行計画の取得(Oracle)

ITコーディネータのシュウです。

DSC_0007

あっという間に桜は散ってしまい、かなりの日数がたってしまいました。もうGWですね。
上の写真は、桜が咲いていたときに、娘が近くの公園で夕方にきれいだったと撮ったものです。空が青くなって幻想的な雰囲気がしますね。
ちょうど満開のときの週末に雨が降ったので、お花見ができなかったのが残念でした。来年を期待しよ~っと!

さて、dbSheetClientに新しい事例が2つまたアップされています。1つは6年前にシステムを構築した後、改定を続けながらフル活用し、データベースもPostgreSQLにダウンサイジングしたSOLIZE Products株式会社様の事例、もう一つは、dbSheetClientを使って金型製作における基幹システムを構築、内製化した株式会社黒田製作所様の事例です。
興味のある方は以下をご参照ください。
http://www.newcom07.jp/dbsheetclient/usrvoice_sequel/solize_products.html
http://www.newcom07.jp/dbsheetclient/usrvoice/kurodaseisakusyo.html

<本日の題材>
実行計画の取得(Oracle

データベースがSQL Serverであれ、Oracleであれ、性能の問題が発生した場合には、実行計画を取得して、SQLがどのような手順で処理を行っているのかを確認して、対策を取るということが行われます。

SQL Serverの場合には、Management StudioでSQLを実行する際に、ツール上で推定実行プランの表示や実行計画を結果に含めるなどの設定ができるので、簡単に実行計画を取得できますが、Oracleの場合はどうだったか?とすぐにやり方が出て来ない場合も多いと思いますので、簡単にやり方を上げておきます。※SQLPlusやSQL Developerなどのツールを使って実行計画を取得する方法です。

以下の2つのやり方がよく利用されると思います。
①EXPLAIN PLAN文を使用する
②AUTOTRACE を ON にする

まず、①についてです。
①は、EXPLAIN PLAN FOR + SQL文によってオラクルのオプティマイザが選択した「実行計画(予定)」を取得するというものです。
※ EXPLAIN PLAN 文による実行計画の取得では、SQLの実際の実行は行なわれないため、実行結果やSQL統計情報は取得できません。

・事前準備
EXPLAIN PLAN FOR + SQL文によって、実行計画は、PLAN_TABLE という表に格納されますので、あらかじめそれを用意しておく必要があります。

sqlplusなどで、実行計画を取りたいSQLを実行するユーザにログインします。

sqlplus test/*****
接続されました。

Oracle側で用意している utlxplan.sql というスクリプトを実行します。これは、オラクルのホームディレクトリの下の、rdbms/adminフォルダの下にあるので、以下のようにしてスクリプトファイルを実行します。

SQL> @?/rdbms/admin/utlxplan.sql

utlxplan

 sqlplusにおいて「?」はOracle Homeディレクトリに置換されます。つまり、「@?/sqlplus/admin/plustrce.sql」は、「@$ORACLE_HOME/sqlplus/admin/plustrce.sql」の意味となります。

このスクリプトでは、実行計画の情報を格納する「PLAN_TABLE」表を作成しています。念のため、表の定義を見てみます。

DESC PLAN_TABLE

plan_table

表が作成されれば準備ができましたので、それでは、試してみます。以前、ブログで取り上げた「MINUS」という集合演算子を使ったSQLの実行計画を見てみます。

explain plan for
SELECT * FROM tbl_B
MINUS
SELECT * FROM tbl_A;

explain_plan_1

 ※tbl_A、tbl_B は同じ構造のテーブルです。

次に、実行計画を表示します。このとき、DBMS_XPLAN パッケージに用意されている表関数を使用します。

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain_plan_1_結果

上記の結果から、このSQLは、「TBL_B」に対する全表スキャンを行った後にソート処理(SORT UNIQUE)を行い、さらに「TBL_A」に対する全表スキャン、ソート処理(SORT UNIQUE)を行って、最後にその差分を取っていることがわかります。このことから、件数が増えてくればかなり性能的には遅くなる可能性のある処理であることがわかりますね。

これと同等の結果を出す処理を、EXISTS句を使って、別のSQLで実現し、それについても実行計画を取得してみます。

explain plan for
SELECT * FROM TBL_B
WHERE NOT EXISTS
(SELECT 'X' FROM TBL_A
   WHERE CD_A = TBL_B.CD_B);

explain_plan_2

実行計画を表示してみます。

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain_plan_2_結果

今回のSQLでは、「TBL_B」に対して全表スキャンを行い、それぞれの行に対して「TBL_A」に対しては索引一意スキャンを行って、ネステッドループ結合を行うかたちになっているのがわかります。先ほどの処理に比べれば、Costの値も低く、こちらのほうが性能がよいと言えます。

次は、②AUTOTRACE を ON にするについてです。

sqlplus などで次のコマンドを実行します。
set autotrace on

ただし、実行計画と統計情報は、動的パフォーマンス・ビューも参照する必要があるため、必要な権限がないと一般ユーザーには使用できません。その場合は、以下のようにエラーが出ます。

SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが有効かを確認してください。
SP2-0611: STATISTICSレポートを使用可能にするときにエラーが発生しました。

・事前準備
PLUSTRACEロールを作成して、対象のユーザに付与する必要があります。そのためには、SYSDBA 権限で接続した後に、plustrace.sql というスクリプトを実行します。

sqlplus / as sysdba
@?/sqlplus/admin/plustrce.sql

plustrace

次に、対象のユーザに、PLUSTRACE ロールを付与します

GRANT PLUSTRACE TO test;

grant_plustrace

対象のユーザに接続して、AUTOTRACE を ON にします。

CONNECT test/*****
SET AUTOTRACE ON

autotrace

先ほど試したSQLを実行します。

SELECT * FROM TBL_B
WHERE NOT EXISTS
 (SELECT 'X' FROM TBL_A
   WHERE CD_A = TBL_B.CD_B);

autotraceon_sql

すると、上記のように、SQLを実行した結果と、実行計画、及び統計情報が表示されるのが確認できます。
実行計画の表示を止めたい場合には、set autotrace off を実行します。

autotrace_off

 
今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

ファンクションインデックス

ITコーディネータのシュウです。

082

コスモスがとてもきれいだったので写真に撮りました。ここしばらく、仕事のほうが忙しく、なかなかブログを作成できなかったのですが、久しぶりの投稿です。

さて、実りの秋、食欲の秋、スポーツの秋、読書の秋、いろいろな言葉で表現される秋もかなり深まってきました。
私も先月は、妻に引っ張られながら、なまった体に鞭打って運動会にも何とか頑張って参加しました。子供の学校においては、高校では文化祭、中学では合唱のクラス対抗発表会などもあり、たまには父親らしいことをしようと、子供たちの姿を見に行って来ました。
プロ野球は日本シリーズが終わり、今はプレミア12が始まっています。日本は3連勝で頑張っていますね。体操の世界選手権では日本が37年ぶりの金メダル、そして、内村航平選手が、前人未到の個人総合6連覇! いやあ、挙げ出すといろいろありますね。そしてみんな頑張っているんですよね。私も、ちょっと疲れているけど、頑張るぞ!
...と気合は入れてみたんですが、なかなか力が出ないのも事実。今日は久しぶりに早く帰ろうかな~。

<本日の題材>
ファンクションインデックス

いろいろなシステムを担当すると、あるデータを抽出しなければならないときに、テーブル同士のジョインに、既存のキーとなる項目をそのまま使用することができず、関数を使用して項目を加工したかたちで条件を設定しなければならない場合に時折遭遇します。
そんなとき、データ件数が多い場合には、インデックスをうまく使えないために処理時間がかなりかかってしまい、問題になることがあります。

最近の開発案件でもそういうケースがあり、どうしたらよいかを検討したところ、ORACLEの機能にファンクションインデックスというものがあり、それを使うことで処理時間を短縮することができました。
今日は、それを取り上げてみたいと思います。

実際に行ったケースはちょっと複雑だったため、簡単な例で試してみたいと思います。

テーブル「TAB_C」、テーブル「TAB_D」があり、定義は以下のようだとします。

CREATE TABLE TAB_C(
 C_CODE_1   VARCHAR2(20)
,C_数量       NUMBER(12)
,CONSTRAINT PK_TAB_C PRIMARY KEY (C_CODE_1));

CREATE TABLE TAB_D(
 D_CODE_1   VARCHAR2(20)
,D_数量       NUMBER(12)
,CONSTRAINT PK_TAB_D PRIMARY KEY (D_CODE_1));

データを以下のように作成します。
TAB_Cの「C_CODE_1」は、最初の文字が「C」で後は1からの連番、TAB_Dの「D_CODE_1」は、最初の文字が「D」で後は1からの連番とします。また、C_数量、D_数量については、1~1000000 の間のランダムな整数を設定することにします。

DECLARE
  v_count NUMBER := 0;
  v_ccode VARCHAR2(20) := ' ';
  v_dcode VARCHAR2(20) := ' ';
BEGIN
  WHILE v_count < 1000000 LOOP
    v_count := v_count + 1;
    v_ccode := 'C'||CAST(v_count AS VARCHAR2);
    v_dcode := 'D'||CAST(v_count AS VARCHAR2);

    INSERT INTO TAB_C(C_CODE_1, C_数量)VALUES
  (v_ccode, FLOOR(DBMS_RANDOM.VALUE(1, 1000001)));
   INSERT INTO TAB_D(D_CODE_1, D_数量)VALUES
  (v_dcode, FLOOR(DBMS_RANDOM.VALUE(1, 1000001)));
END LOOP;
END;
/

※DBMS_RANDOM.VALUEは乱数を取得するのに使えます。

データの作成結果を確認してみます。
SELECT * FROM TAB_C
ORDER BY CAST(SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) AS NUMBER);
TAB_C結果1

SELECT * FROM TAB_D
ORDER BY CAST(SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1) AS NUMBER);
TAB_D結果1

TAB_C、TAB_Dとも1000000件作成されていて、数量はランダムな整数(1~1000000の間)になっているのが確認できます。

この2つのテーブルは、それぞれのテーブルの主キーである「C_CODE_1」「D_CODE_1」の2桁目以下の値でジョインすることで、1対1のデータを抽出できます。

このときのジョインの条件は、例えば以下のようになります。
SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) = SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1)

この場合、C_CODE_1、C_CODE_2は、それぞれのテーブルのプライマリーキーであったとしても、SUBSTRやLENGTHという関数を使っているためにうまくインデックスを使った検索をしてくれない(全件検索になる)ので、テーブル件数が多い場合には、処理時間が非常にかかってしまいます。

実際に、ジョインした結果を抽出してみます。
(条件として、キーの2桁目以降が700000~710000のものに絞っています)

SELECT
SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1), SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1)
  FROM TAB_C C
  JOIN TAB_D D
    ON SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) = SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1)
 WHERE SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1) BETWEEN 700000 AND 710000
 ORDER BY SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1);

ファンクションインデックス作成前2

実際に実行計画を取得してみると、
------------------------ 実行計画 --------------------------
SELECT STATEMENT   Cost = 126399
    SORT ORDER BY 
        HASH JOIN  
            TABLE ACCESS FULL TAB_C
            TABLE ACCESS FULL TAB_D
-----------------------------------------------------------------
「TAB_C」「TAB_D」とも「TABLE ACCESS FULL」となっていて、フルスキャンしていることがわかります。
 
そこで、ファンクションインデックスを作成してみます。
CREATE  INDEX  IX_TAB_C_FUNC  ON  TAB_C
(SUBSTR(C_CODE_1,2,LENGTH(C_CODE_1)-1));
 
CREATE  INDEX  IX_TAB_D_FUNC  ON  TAB_D
 (SUBSTR(D_CODE_1,2,LENGTH(D_CODE_1)-1));

ファンクションインデックスは、索引自体と索引が定義される表が分析されるまで、使用されないということなので、分析します。

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'BLOG_TEST', TABNAME => 'TAB_C');
ファンクションインデックス作成1

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'BLOG_TEST', TABNAME => 'TAB_D');
ファンクションインデックス作成2

この状態で、再度、先ほどのSQLの実行計画を取得してみます。

------------------------ 実行計画 --------------------------
SELECT STATEMENT   Cost = 1445
    SORT ORDER BY 
        HASH JOIN  
            INDEX FAST FULL SCAN IX_TAB_C_FUNC
            INDEX FAST FULL SCAN IX_TAB_D_FUNC
-----------------------------------------------------------------

すると、確かに作成したファンクションインデックス「IX_TAB_C_FUNC」「IX_TAB_D_FUNC」が利用されていることが確認できますし、COSTもかなり小さな値になっています。
実際の抽出結果は、

ファンクションインデックス作成後

処理自体、先ほどよりは早く結果が返って来ました。
こういうケースで、ファンクションインデックスを作成することは、レスポンス改善としては効果があることが分かります。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村