DISPLAY_CURSORでCBOの見積りミスを簡単に確認する

前回のエントリで DBMS_XPLAN.DISPLAY_CURSOR の format引数に’TYPICAL’と ‘ALL ALLSTATS LAST’ を主に指定すると説明しました。 本エントリでは、format引数 ‘ALL ALLSTATS LAST’の機能を説明し、 その一例として、CBOの見積りミスを簡単に確認することができることを説明したいと思います。

### format引数 ‘ALL ALLSTATS LAST’ format引数 ‘ALL ALLSTATS LAST’を指定すると、 実行計画ツリーを示す PLAN_TABLE_OUTPUT、 フィルタ条件やアクセス情報を示す Predicate Informationに加えて、 複雑なSQLを分割したクエリブロック情報や表のエイリアス情報を示すQuery Block Name / Object Alias、 アクセスした列に関する情報を示すColumn Projection Information が表示されます。

また、PLAN_TABLE_OUTPUTの列に、オペレーション単位の各種実行統計が追加されます。 format引数’TYPICAL’を指定した場合は、確認できるのはCBOによって見積もられた見積統計 (見積もり行数、見積もりバイトなど)しか確認できませんでしたが、 実行時の実行統計(実際にオペレーションを実行した結果得られた行数など) が確認できます。

また、format引数 ‘LAST’が指定されていることで、 直近のSQL実行の統計情報を表示します。 逆に’LAST’を指定しないと、得られる統計情報が共有カーソル単位の累積値となってしま い(同じSQLを複数回実行すると複数回実行分の合算値となる)、1回実行分の統計を確認したい ような通常の用途では不適切であることに注意してください。

なお、前回のエントリで説明しましたが、format引数 ‘ALLSTAT’、’LAST’は 調査対象のSQLの実行前にstatistics_levelパラメータをALLに設定するか、 SQLに /*+ gather_plan_statistics */ […]

DBMS_XPLAN.DISPLAY_CURSORの使い方とちょっとした落とし穴

先日のエントリ [SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは(ほとんど)使わない](http://www.csus4.net/d/2011/02/24/whynotuse_explainplan_sqltrace/) でお勧めしたDBMS_XPLAN.DISPLAY_CURSORについて、基本的な使い方を説明します。

### 基本的なDBMS_XPLAN.DISPLAY_CURSORの使い方

詳細はマニュアル(Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス) のDBMS_XPLANパッケージの箇所をご確認いただきたいのですが、

* 10.2 : [DISPLAY_CURSORファンクション](http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19245-02/d_xplan.htm#i998364) * 11.1 : [DISPLAY_CURSORファンクション](http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/appdev.111/E05686-02/d_xplan.htm#i998364) * 11.2 : [DISPLAY_CURSORファンクション](http://download.oracle.com/docs/cd/E16338_01/appdev.112/b56262/d_xplan.htm#i998364)

DISPLAY_CURSORファンクションには以下の引数を指定します。

DBMS_XPLAN.DISPLAY_CURSOR( sql_id IN VARCHAR2 DEFAULT NULL, child_number IN NUMBER DEFAULT NULL, format IN VARCHAR2 DEFAULT ‘TYPICAL’);

sql_id: SQLの識別子 child_number : 子カーソル番号 format: 出力フォーマット(後ほど簡単に説明)

このファンクションはテーブルファンクションであるため、TABLE演算子で変換すると 通常の表のように扱えます。典型的には引数sql_idに調査対象のSQLのsql_idを指定して、 以下のように使用します。

[…]

SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは(ほとんど)使わない

Oracle DatabaseのSQLパフォーマンス問題が発生したときの、実行計画の取得についてまとめます。

SQLパフォーマンス問題が発生したときに、まず確認すべき情報はSQLの実行計画ですが、 Oracle Databaseには、実行計画を確認する方法が、大きく分けて3つあります。

* EXPLAN PLAN : (一般にSQL*Plusから)EXPLAN PLAN FOR < 実行したいSQL>を実行して、実行計画を確認します。 * SQLトレース : SQLの実行前にalter session sql_trace=true;を実行して、トレースファイルに出力された情報から実行計画を確認します。 * DBMS_XPLAN.DISPLAY_CURSORプロシージャ : 共有プールに保管された共有カーソルの情報から、実行計画を確認します。共有カーソルが共有プールからage-outされた場合、実行計画は確認できません。

### EXPLAIN PLANとSQLトレースを使わない理由

従来から存在しており、かつ、広く知られている実行計画の取得方法は、 EXPLAIN PLANとSQLトレースの2つです。しかし、私がOracle DatabaseのSQLパフォーマンス問題 に対応する場合、これらの2つの方法は原則的に使いません。

これら2つの方法には、いくつか使いにくい点があるからです。

[…]

優れたカーソル共有があればバッチ処理SQLのリテラル化は不要か?

[先日のエントリ](http://www.csus4.net/d/2010/12/29/batch_sq/) に、 Oracle Database 11.1 からは優れたカーソル共有(ACS, Adaptive Cursor Sharing, 適用カーソル共有) という機能が導入されているため、エントリの内容が当てはまらないと書きました。 優れたカーソル共有が機能すると、バインド変数化されたSQLと共有カーソルの対応関係が異なってきます。

具体的には、従来(10.2以前)では、バインド変数値によらず、 原則的にバインド変数化されたSQLと共有カーソルは1対1関係にありましたが、 11.1で優れたカーソル共有が機能する(デフォルトで有効)と、 バインド変数に指定された値によって、既存の共有カーソル(≒実行計画)を使用 すべきでないとOracleが判断すると、新規に別の共有カーソル(≒実行計画)を作成し、 SQL実行にこの共有カーソルを使用します。 すなわち、バインド変数化されたSQLと共有カーソルは1対多の関係となる可能性があります。

(*1) ここの説明において、共有カーソルは子カーソルを指します。

### では、優れたカーソル共有により、意図しない実行計画が使用される事態を予防できるか?

[…]

突然のSQLパフォーマンスダウンを防ぐためのバッチ処理SQLリテラル化のススメ

またまた前回の更新から時間が空いてしまいました・・・。 いろいろと忙しく時間が取れないのですが、このままだと2010年まったくblogを書いていないことになってしまうのと、 twitterで後押しをいただいたこともあり、何とか書き上げました。お役に立てば幸いです。

### はじめに

最近、Oracle Databaseのパフォーマンスに関連するトラブルシューティング を担当することが多いのですが、特に多い事例が、 ある特定のSQLのパフォーマンスが突然ダウンするような問題です。

原因はいろいろなのですが、バッチ処理で実行されるSQLに、バインド変数 を使用しているためにパフォーマンスが突然ダウンしてしまった状況が多くみられました。 (もちろんケースバイケースの側面はありますが、)バッチ処理で実行されるSQLは 一般的にバインド変数化するべきではありません。 SQLをバインド変数化することにはメリットもありますが、デメリットもあります。 特に、デメリットの部分が広く知られていないため、プログラムをコーディング するときに、開発者がある種機械的・盲目的にSQLをバインド変数化していることが この問題の背景にあるのではと思っています。

本エントリでは、SQLをバインド変数化のメカニズムと SQLをバインド変数化することのメリットとデメリットを説明した上で、 バッチ処理で実行されるSQLをリテラルSQL化することをお勧めします。

[…]

書籍 「プロとしてのOracle運用管理入門」を執筆しました

すみません、前回のエントリから5ヶ月近く間が空いてしまいました。 この間何をしていたかというと、(必死の思いで・・・)2冊めの書籍を執筆しておりました。 先に執筆した書籍「プロとしてのOracleアーキテクチャ入門」と同じ「プロとしてのOracle・・・」 シリーズの第8弾「プロとしてのOracle運用管理入門」です。 おそらく9/24ごろから書店に並ぶ予定です。

プロとしてのOracle運用管理入門 posted with amazlet at 09.09.19 株式会社コーソル 渡部 亮太 ソフトバンククリエイティブ 売り上げランキング: 320943 Amazon.co.jp で詳細を見る

* [ソフトバンククリエイティブの本:プロとしてのOracle運用管理入門](http://www.sbcr.jp/books/products/detail.asp?sku=4797355123)

「運用管理」という非常に幅の広い題材であることから、原稿のボリュームが一時500 ページ以上に達してしまいましたが、編集のOさんの「超・編集力」のおかげで、なんとか 416ページにまとめ上げることができました。Oさん、ありがとうございます!

### 目次

* Part1 データベース管理者の役割とOracleの起動・停止 * CHAPTER 01 データベース管理者の役割 * CHAPTER 02 Oracleの起動・停止

* Part2 領域管理 * CHAPTER 03 領域管理の全体像と永続表領域の管理 * CHAPTER 04 UNDO表領域の管理 * CHAPTER 05 一時表領域の管理

* Part3 […]

Oracleの文字化けに関する記事の続編

公開されてから、ちょっと時間がたってしまいましたが・・・ 2009/3/4から、以下の記事が公開されています。

* Oracleトラブル対策の基礎知識(5)文字化けに関するトラブルに強くなる【実践編】 ――UnicodeとJIS X 0213とサロゲートペア * [記事](http://www.atmarkit.co.jp/fdb/rensai/ora_admin/06/oraadmin06_01.html)

文字コード自体に関する解説は可能な限り最小限にして、細部にとらわれず、 大枠のメカニズムを理解できるように工夫してみました。 文字コード自体について、より詳細な理解がしたい場合は、別途他の資料等で補完して いただければと思います。お役に立ちましたらー

CHAPTER 03 データファイルと関連する構成要素

今回のentryでは、「SECTION I Oracleアーキテクチャ概要」の 「CHAPTER 03 データファイルと関連する構成要素」について補足説明します。 この章では、データを格納するファイルである「データファイル」 と1つ以上のデータファイルをグループ化した記憶領域である「表領域」、データファイルへのI/Oのバッファ/キャッシュとして機能する「データベースバッファキャッシュ」について説明しています。

書籍では、いわゆる通常の表領域である「SMALLFILEタイプの表領域」についてのみ説明 しています。ターゲットの観点から書籍では説明していませんでしたが、 Oracle 10gからは新しい表領域のタイプである「BIGFILEタイプの表領域」が導入されています。

### BIGFILEタイプの表領域

SMALLFILEタイプの表領域、すなわち従来の表領域が存在した目的の1つに、 複数のデータファイルを束ねた仮想的な記憶域として表領域という概念を用意することで、 Oracleから意識する対象を表領域のみとすること、すなわち、 具体的なファイルの構造や、ファイルが配置されてている ドライブ/パーティション/ファイルシステムを隠蔽して、いわば物理的なストレージを 仮想化することがあったと考えられます。

[…]

Oracleの文字化けに関する記事を執筆しました。

* [Oracleトラブル対策の基礎知識(5)文字化けに関するトラブルに強くなる【基礎編】](http://www.atmarkit.co.jp/fdb/rensai/ora_admin/05/oraadmin0501.html)

Oracleの文字コードの扱いの基本、文字化けのトラブルシューティングの概要について @ITに記事を執筆しました。 自分の勤務先で持ち回りで担当している [Oracleトラブル対策の基礎知識](http://www.atmarkit.co.jp/fdb/index/subindex/oraadmn_subindex.html) の文字化けに関連した記事となります。

今回の記事に執筆に当たっては、用語の導入を必要最小限にすることに留意しました。 文字コードについて正確に理解したり、応用の効く基礎知識を得るという観点では、 文字集合や文字列符号化方式などについて理解すべきなのですが、 Oracleに限定した文字コードの扱いについて、てっとり早く理解したいというニーズ もあると考えたためです。

なお、文字化けについては、2回構成となっており、 次回は、チルダ文字化けとJIS X 0213(Unicodeサロゲートペア)について説明する予定 ですので、よろしければこちらもご覧ください。

CHAPTER 02 クライアントアプリケーションとサーバープロセス – プロとしてのOracleアーキテクチャ入門

今回のentryでは、「SECTION I Oracleアーキテクチャ概要」の「CHAPTER 02 クライアントアプリケーションとサーバープロセス」について補足説明します。 この章では、SQL*PlusなどのOracleに接続するアプリケーションである「クライアントアプリケーション」 とクライアントから指示に従い実際に処理を実行する「サーバープロセス」について説明しています。

あるクライアントアプリケーションがOracleに接続すると、そのクライアントアプリケー ション専用のサーバープロセスが起動します。 ここで、クライアントアプリケーションとサーバープロセスの間に設定されるコネクションを、 Oracleではセッションと呼びます。 これらについては、書籍で説明したとおりですが、このentryでは、 Linux上でクライアントアプリケーションとしてSQL*PlusでOracleに接続した場合を例に、 クライアントアプリケーションとサーバープロセスの関係について、 プロセスの相互関係、セッションの観点から確認してみます。

まず、SQL*Plusを起動して、connectコマンドでOracleに接続すると、サーバープロセス が起動します。 これは、SQL*Plusにおいて、fork()、exec()システムコールを実行することで、 プロセスが複製され、複製されたプロセスの実体がoracleプログラムに変わるためです。 このような処理を行うことで、サーバープロセスに対応したプロセスが新規に作成されます。 そして、このサーバープロセスはSQL*Plusの子プロセスとなり、 サーバープロセスとSQL*Plusは子プロセス、親プロセスの関係をもちます。 そして、2つのプロセス間にパイプと呼ばれるプロセス間通信路を設定します。 すなわち、セッションはパイプにより実現されるわけです。

ここで説明した動作を実際に確認してみましょう。 まず、SQL*Plusを実行して、Oracleに接続してみます。

[o11106@hp1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 – Production on 日 11月 9 15:36:10 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> connect […]