Oracle SQLチューニング Season2（第5回）第2章　SQLチューニング対象選定方法（2/8）

いつもSQLチューニングブログをご愛読いただきまして、ありがとうございます。

SQLチューニング対象の選定方法 の2回目は「チューニング対象選択のための情報及び活用ツール」の前編 です。

それでは早速スタートしましょう！！

2.2　「チューニング対象選択のための情報及び活用ツール」（前編）

　正しいSQLチューニング対象を選定するためには、SQLの実行内訳を精査する必要があります。
　（※SQLの実行内訳：実行計画、実行回数、実行時間、抽出件数、I/O処理量など）

　また、現在のシステム状況及び業務的な特性も一緒に把握しなければなりません。
　これらの情報を把握する方法には、大きく分けて下記の3つがあります。

1. 　業務担当者の要件やアドバイスを活用する方法
2. 　OracleのDictionary Viewを活用する方法
3. 　パフォーマンス管理ソリューションを活用する方法

2.2.1　業務担当者のアドバイスを活用

　業務担当者のアドバイスを参考にしてSQLチューニング対象を抽出する方法は非常に効果的です。
　たとえ有能なチューナーであっても、改善対象となるDBサーバー全般にわたる性能問題や業務特性を把握するためには多くの時間を費やす必要があるためです。

　チューナーがまずやるべきことは、それぞれの業務担当者と多くの会話を重ね、DBサーバーの性能問題に関する内容を把握することです。

　その一方で、当該業務の担当者は自分の業務内容に関することは、他の誰よりもよく理解しているため、どの部分で業務遅延（性能問題）が発生するのか？を把握できている可能性が非常に高いのです。

　そして、性能問題を抱えているプログラムのSQLについても把握できている場合も多くあります。

　したがって、業務担当者からの協力が得られるのであれば、積極的に関連情報を得ることが可能となるため正確な対象選定と選定作業のために割り当てられる時間を最小限に抑えることができます。

　このような場合、対象選定を進めるにあたっては大きな困難に直面することはありません。
　そして、確かな改善効果を見ることができるでしょう。その意味においては、かなり合理的な方法であると思います。

2.2.2　SQL性能に関するDictionary Viewを活用

　改善対象選定時、業務担当者のアドバイスを積極的に活用することと共に最も多く活用される方法は、Oracle内部に保存されている情報を活用することです。
　すべてのDBMS(Database Management System)は、ユーザーがDatabaseに対して管理ができるようにメタデータを提供します。

　通常、DBMSが提供するメタデータは大きく二つの側面のデータを提供しますが、それは管理及び運営に必要なデータと性能管理のためのデータです。
　Oracleも性能や運営の便宜のために内部的に性能関連データを保管し、ユーザーが簡単に照会できるように様々なViewを提供しています。

　ここで使用したDictionary Viewは、管理観点(DBA_)と性能観点(V$)のビューを総称して使用した用語と考えればよいです。
　性能データを照会することができるビューのうち、SQL関連情報は、実行履歴、SQL実行時に使用されたバインド変数、SQL実行計画、SQL実行履歴情報などがあり、これはSQLの性能を診断及び分析するときに使用できる有用な情報を持っています。

　このようなDictionary Viewで照会できるデータをよく理解して活用すれば、DBサーバーの性能問題を解決するための大きな助けになります。

　実際にDictionary Viewを活用することは、パフォーマンス改善作業のためのターゲット抽出時に汎用的に使われている方法です。

　Dictionary ViewにはOracleのバージョンが上がるにつれて、いろいろな便利情報が追加されています。

　Oracle new featureがSQLと関連した内容であれば、その機能が特定のSQLでどのように使用されたかを抽出することができるカラムがどこかに追加されたと考えることができます。

　したがって、チューナーはシステムがどのような機能を使うかパラメータ設定を確認し、その機能が正しく使われているか、パフォーマンスの問題はないかをDictionary View照会を通じて判断することができます。

2.2.3.1　SQL関連V$* VIEW

　SQL性能に関する代表的なDictionary Viewは、V$SQLAREA [V$SQL]、V$SQL_PLAN、V$SQLTEXT（10g以降のバージョンではほとんど使用されません)、V$SQL_BIND_CAPTUREが存在します。
　DBA_HIST_関連ビューもありますが、これらのビューは”SQL関連DBA_HIST_ VIEW”の部分で説明します。
　それでは、それぞれのビューが持つ意味とカラム情報について説明します。

V$SQL （V$SQLAREA）

　個々のSQLの実行統計を分析する目的でも多く活用されます。
　集中チューニングが必要な対象SQLを選定するために活用できる非常に有用な情報を照会できるビューです。
　V$SQLで表示される統計情報は、セッションで実行したSQLの実行段階で更新されます。
　さらに、長時間実行されるSQLは5秒（実際のテストでは3秒ごとに更新されたことを確認）ごとに更新が行われます。

V$SQL vs V$SQLAREA

　V$SQLは、各Child Cursor毎の実行情報を確認できる一方、V$SQLAREAは全てのChild CursorのSummary情報を表示します。二つのパフォーマンスビューの違いは、カラムの比較を通じてアプローチすると理解しやすいです。

　V$SQL には、 Child_number 列が存在しますが、V$SQLAREA には、 Child Cursor の個数を表示する VERSION_COUNT 列があることが確認できます。

　他にもV$SQL（V$SQLAREA）には、チューニング対象選定時に非常に便利なカラムがたくさんあります。

　各カラムの意味と原理を熟知して状況に合わせて使用すれば、非常に正確なチューニング対象を抽出することができます。また、Oracleのバージョンが上がるにつれて新規に追加されるカラムは、そのバージョンに追加された新機能に関連したデータを持っています。したがって、Oracleのバージョンに新規追加されたカラムの内容を理解してよく活用すれば、Buffer GetsとCPU Timeを利用する一般的なチューニング対象抽出以外にも、DBサーバーの状況に合わせた対象選定も
可能になります。

　下の表はOracle 9iR2、10gR1、10gR2バージョン別に追加されたカラムをまとめたものです。
　その中でもいくつかの便利なカラムを紹介します。

sharable_mem （9iR2）

　Shared_pool Free Memoryの断片化（Fragmentation）によってORA – 4031エラーが発生する場合、sharable_memを多く使ったSQLを探してsharable_memを多く使わないように変更することが解決方法の一つです。

sql_profile （10gR1）

　改善されたSQLをアプリケーションソースに適用（パッチする）時間的余裕がない場合、SQL Profileを利用して改善されたSQLのplanを変更することができます｡

　このように適用したSQL Profileの管理は、sql_profileカラムを利用して可能です。

program_id, program_line# （10gR1）

　program_id 列は DBA_OBJECTS の OBJECT_ID とマッピングされる列で、program_id の値を持つ SQL がどのプログラム（プロシージャ、関数など）に含まれているか知ることができます。

　そして、program_line#はSQLがプログラムのどの位置にあるかを知ることができます。
　二つのカラムを活用することで、照会するSQLがどのプログラムの何番目の行にあるかを見つけることができるようになります。
　二つのカラムは、プロシージャや関数などを多く使う環境にうまく活用することができれば、チューニング対象を選別する場合に便利です。

exact_matching_signature, force_matching_signature （10gR2）

　リテラルSQLを把握するために使います。
　このカラムが提供される前のバージョンでは、SQL Text を前の特定の位置まで（例えば、substr（sql_text,1,138））切り取った上で、その値が同じであった場合には、リテラルクエリとして判断していました。

　Oracle 10gからは、このカラムを利用してリテラルSQLを簡単に把握することができます。　exact と force は cursor_sharing パラメータの設定値と同じ意味を持ちます。

last_active_time（10gR2）

　last_active_timeカラムはSQLが最後に実行された時間データを照会することができます。
　このデータはV$SQLAREA, V$SQLを通じてチューニング対象を抽出する時、最近実行されたSQLかどうかを確認することができ、特定のセッションで順次SQLを実行した場合、どの順番でSQLを実行したのかも区別することができます。そのため、直接的にチューニング対象を選別する時にはあまり使われません。
　しかし、ごく稀に使われるカラムではあります。

次回ブログテーマ
『　チューニング対象選択のための情報及び活用ツール（後編）　』