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

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

さて、dbSheetClientに新しい事例が２つまたアップされています。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

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

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

DESC PLAN_TABLE

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

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

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

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

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

上記の結果から、この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);

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

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

今回の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 ロールを付与します

GRANT PLUSTRACE TO test;

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

CONNECT test/*****
SET AUTOTRACE ON

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

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

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

 
今日は以上まで

にほんブログ村

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

これは、ちょっと前に咲いた早咲きの桜の写真です。家の近くの川べりに咲いた桜がきれいでした。

さて、年度が変わって子供たちも春休みの後半を迎え、もうすぐ新しい学年として出発します。中学生の息子が数学の問題集のわからないところについて、聞いてきたのですが、なかなかスパッと解き方がひらめかず、かっこよく教えてあげることができませんでした。ん～！　俺って結構年を取ってきたかな～。頭が固くなってきたみたい。とほほほ。。。

話しは変わりますが、dbSheetClientにまたまた新しい事例がアップされました。
全社、約８００名が利用する、念願の「 予算申請（損益予算・設備投資予算）システム 」をdbSheetClientで構築！予算責任者及び担当者、財務部の作業負担が大幅に削減！という、JR九州様の事例第2弾です。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice_sequel/jr_kyushu2.html

また、dbSheetClient(ディービーシート・クライアント)の導入メリットや導入の様子を、マンガを使って解りやすく解説したページができていますので、こちらも是非ご参照ください。
http://www.newcom07.jp/dbsheetclient/intromanga/index.html

＜本日の題材＞
デッドロックその２（SQL Server）

前回、SQL Serverでのデッドロックの現象について、実際に簡単なSQLで発生することを確認しましたが、実際にデッドロックが発生した場合には、その情報を調査して対応しなければなりません。今回は、デッドロック発生時の情報の取得のやり方について見てみたいと思います。

SQL Server 2012以降では、拡張イベントという機能でデッドロックの情報も確認することができます。（それ以前は、トレースフラグ：1204と1222を設定して確認していました）

SQL Server Management Studioのオブジェクトエクスプローラーの「管理」→「拡張イベント」→「セッション」→「system_health」の下に、「package0.event_file」というものがあり、それをクリックすると下記のような表示が出ます。

ここで、メニューの「拡張イベント」→「フィルター」から

フィールドに「name」、演算子に「=」、値に「xml_deadlock_report」を設定して「OK」を押下すると、拡張イベントがデッドロックのイベントに絞られて表示されます。

このイベントにカーソルを合わせて、下側のウィンドウの「デッドロック」のタブをクリックすると、下記のように図でデッドロックの情報が確認できます。

 図の×がついているプロセスの上にカーソルを置くと、デッドロックが発生したSQLが確認できます。

また、「詳細」タブのほうで、その行をダブルクリックすると、デッドロックのより詳細な情報を確認できます。

ここでもデッドロックになったSQLが確認できますが、<deadlock> タグの中の、<process-list>タグのほうに
waitresource="KEY: 46:72057594039762944 (a0c936a3c965)"
というものがあり、実際のどのデータを処理しようとしたときにデッドロックになったかがわかるかたちになっています。

実際のデータを確認するのは、上記のKEY情報を使って以下のように抽出します。

SELECT *
FROM tbl_B(NOLOCK)
where %%lockres%% = '(a0c936a3c965)';

上記から、tbl_B テーブルの、CD_B = 4 のデータの UPDATE のときにデッドロックが起きたことがわかります。

今日は以上まで

にほんブログ村