小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


【Oracle】SQL DevelopperでSQLチューニング・アドバイザを試してみる

oracle
はじめに

SQLチューニング・アドバイザは、SQL文を分析して、どうチューニングしたら良いかアドバイスしてくれます。提示される推奨事項やアドバイスは、オブジェクトの統計情報の収集、新規索引の作成、SQL文の再構築、SQL Profileの作成などと関連しています。

環境
  • Oracle 11g(11.2.0.1.0)
  • SQL Developper 4.1.1.19
事前準備

以下のコマンドを実行し、scottユーザのスキーマを作成します。

cd C:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN
sqlplus sys/pwd as sysdba @utlsampl.sql

sysユーザで以下のコマンドを実行し、scottユーザに権限を付与します。

grant advisor to scott;
grant administer sql tuning set to scott;

続いて、sysユーザで以下のコマンドを実行し、scottユーザの統計情報を意図的に削除します。これは本来必要ない操作ですが、のちほど効いてきます。

exec DBMS_STATS.DELETE_SCHEMA_STATS ('scott');
SQLチューニング・アドバイザを試す

１．SQL DevelopperからscottユーザでOracle DBにログインします。
f:id:replication:20160103192134p:plain
２．ワークシートに以下のSQLを貼り付けます。

SELECT SUM(e.sal),
  AVG(e.sal),
  COUNT(1),
  e.deptno
FROM dept d,
  emp e
GROUP BY e.deptno
ORDER BY e.deptno;

３．SQLチューニング・アドバイザのアイコン(Ctrl + F12)をクリックします。
f:id:replication:20160103192443j:plain
４．分析結果が表示されます。
f:id:replication:20160103201037p:plain

分析結果1

表"SCOTT"."EMP"およびその索引は分析されませんでした。
表"SCOTT"."DEPT"およびその索引は分析されませんでした。

事前準備でexec DBMS_STATS.DELETE_SCHEMA_STATS ('scott');コマンドを実行して、scottユーザの統計情報を削除しました。統計情報が収集されていない場合、このメッセージが出力されます。
推奨事項のとおり、オプティマイザ統計情報収集を行うことで解消されます。

適切な実行計画を選択するには、表およびその索引の最新のオプティマイザ統計が必要です。

分析結果2

この文により適している可能性のある実行計画が見つかりました。
Recommendation (estimated benefit: 22.22%)

SQLチューニング・アドバイザが起動されると、SQLプロファイルが作成されます。
SQLプロファイルは、SQL文ごとの補足統計情報です。

SQLチューニングアドバイザがSQLを実行して取得した値とオプティマイザの見積もりを比較して、差異が大きいと判断された場合は、SQLプロファイルを訂正する準備をし、その訂正を受け入れるよう承認を求めます。
アドバイザはこのSQLプロファイルを承認すれば、22%改善されるとはじき出しています。

SQLプロファイルの承認は、sysユーザで以下のコマンドで実行します。このコマンドは推奨タブで確認できます。

execute dbms_sqltune.accept_sql_profile(task_name => 'staName51653', task_owner => 'SCOTT', replace => TRUE);

f:id:replication:20160109145004j:plain

SQLプロファイルで統計が修正されることにより、オプティマイザのカーディナリティの予測が改善され、より適切な計画を選択できるようになります。SQLプロファイルには、他の計画改善の方法より優れた次の利点があります。

  • SQLプロファイルは、ヒントおよびストアド・アウトラインとは異なり、オプティマイザを特定の計画またはサブプランに結び付けません。SQLプロファイルは、不適切な見積りを修正し、それぞれの状況に合った最適な計画を選択する柔軟性をオプティマイザに提供します。
  • SQLプロファイルを使用する場合は、ヒントとは異なり、アプリケーション・ソース・コードの変更は不要です。データベースによるSQLプロファイルの使用は、ユーザーに対して透過的です。

SQLプロファイルの管理 はてなブックマーク - SQLプロファイルの管理

分析結果3

コストの高いデカルト積が実行計画の行ID 2で見つかりました。

デカルト積とはCROSS JOIN(直積)のことです。とてもコストの高い演算なので避けてくださいという指摘です。

この文から結合されていない表またはビューの削除を検討するか、この文を参照する結合条件を追加してください。

おわりに

以上がSQLチューニング・アドバイザの使い方でした。指摘事項で使われる用語が小難しいのが難点ですが、使えそうです。

関連記事