PA4

Transcription

1 SQL チューニングによる 性能改善の効果とポイント 株式会社アクアシステムズ PPA4003J-00-00

2 株式会社アクアシステムズ Oracle データベースを専門とする技術者集団 Oracle チューニング & 監視ツール Performance Analyzer の開発 / 販売 Oracle 診断及びパフォーマンスチューニング Oracle データベースに関するコンサルティング Oracle データベース運用管理 Oracle データベースを使用したアプリケーションの開発

3 Agenda Oracle データベースチューニング SQL 実行のステップ SQL チューニングツール Oracle チューニング & 監視ツール Performance Analyzer オプティマイザ ANALYZE インデックスソートパーティションプルーニングヒント句

4 Oracle データベースチューニング Oracle データベースのチューニングには様々な要素があります オブジェクトの設計初期化パラメータ I/O のコントロールメモリの有効活用競合の回避 SQL OS...etc

5 SQL 実行のステップ 解析 (parse( parse) SQL 文にあるテーブルや列などオブジェクトのチェック最適な実行計画の決定 実行 (execute( execute) SQL を実行 データのフェッチ (fetch( fetch) 問い合わせ結果を返す

6 SQL チューニングツール 解析 実行 フェッチでの動きを把握するため 実行計画や統計情報の確認を行います EXPLAIN PLAN コマンド SQL*Plus のAUTOTRACE SQL_TRACE & tkprof V$SQL_PLAN V$SQL_PLAN_STATISTICS ビュー Performance Analyzer 4

7 Oracle チューニング & 監視ツール Performance Analyzer SQL チューニング高負荷 SQL 及びボトルネック項目の抽出 SQL 解析 ( 実行計画 実行統計 ) 実行計画が変化した SQL の抽出 インスタンスチューニング Oracle データベース評価レポートボトルネックの検出 Oracle データベースの運用監視

8 SQL を共有する 解析時間を減らすためには解析処理を行わせないようにします 共有プール上に存在する SQL と同じ SQL が再度実行された場合は実行計画を作成するための解析処理を行いません 全く同じ SQL 文では 大文字 / 小文字や空白 / 改行の位置 数を同じにする 条件の値のみが異なる SQL 文では バインド変数を使用する CURSOR_SHARING パラメータの設定 (force( or similar) (A) select first_name, last_name from employees where department_id=90; (B) SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 90; (C) select first_name, last_name from employees where department_id=11; (D) select first_name, last_name from employees where department_id=:depid;

9 Oracle オプティマイザ SQL がどのようにデータにアクセスすればよいかを判断し 実行計画を作成します ルールベースオプティマイザ処理方法が順位付けされていて それに基づいて実施可能な処理方法を選択します コストベースオプティマイザオブジェクトの統計を収集し それにより見積もったコストが最も低い処理方法を選択します

10 ANALYZE をする コストベースオプティマイザは 収集した統計をもとに実行計画を決定します ANALYZE をしないとルールベースになります ANALYZE コマンド旧来のコマンド DBMS_STATS パッケージ GATHER_TABLE_STATS GATHER_INDEX_STATS execute dbms_stats.gather_table_stats ('SCOTT', 'EMP', null, dbms_stats.auto_sample_size);

11 インデックスを使う インデックスを使わずにデータにアクセスするとテーブル全体にアクセスすることになります インデックスを使うことで必要な行のみにアクセスすることができます B*Tree インデックスビットマップインデックス逆キーインデックスファンクションインデックス

12 インデックスを使うには (1)( where 句または order by 句にインデックスに指定した列がある where 句の列が複合インデックスの先頭にある インデックス COL1 COL2 ( ) where COL1 = 10 ( ) where COL2 = 10 インデックススキップスキャン 索引の値の比較対象が NULL でない ( ) where COL3 is NULL 索引の値との比較に NOT を使用しない ( ) where COL4!= 10 ( ) where COL5 <> 10

13 インデックスを使うには (2)( 索引の値に計算をさせない ( ) where COL = 100 where COL6 = ( ) where lower(col7) = 'scott' ファンクションインデックスを作成 like を使った場合は前方一致である ( ) where COL8 like 'ABC%' ( ) where COL9 like '%ABC' ( ) where COL10 like '%ABC%' 必ず実行計画でインデックスを使っているか確認しましょう

14 ソート処理をさせない ORDER BY 句などでソート処理が必要になると メモリ上のソート領域や メモリに入りきらない場合はディスクを使用して並べ替えを行います ORDER BY に指定した列を全て含むインデックスを作成 インデックス COL1 COL2 ( ) order by COL1, COL2 ( ) order by COL2, COL1 ( ) order by COL1, COL2, COL3 ソートメモリ領域を充分確保し ディスクソートをさせない PGA_AGGREGATE_TARGET, SORT_AREA_SIZE の調整 alter session set workarea_size_policy = manual; alter session set sort_area_size = ; -- 10MB

15 パーティションプルーニング Enterprise Edition では 大きなセグメントを効率よく扱うために パーティションという機能があります パーティションテーブルでは 必要なパーティションのみへのアクセスで済ませることができます レンジパーティションリストパーティションハッシュパーティション where month >= to_date('2004/02/21','yyyy/mm/dd') and month < to_date('2004/03/21','yyyy/mm/dd') 1 月 2 月 3 月 4 月 コンポジットパーティション ( レンジ ハッシュ レンジ リスト ) 実行計画の PARTITION_START, PARTITION_STOP を確認

16 ヒント句 Oracle のオプティマイザが期待した実行計画を選ばないときは ヒント句を使うことで 実行計画を制御することができます ヒント句は SELECT などの直後に /*+ */ で囲みます FULL フルスキャンをさせます INDEX, INDEX_FFS, INDEX_SS インデックスを指定の方法で使用するように指示します USE_NL, USE_MERGE, USE_HASH テーブルの結合方法を指定します その他にも多くのヒントがあります

17 遅くなる前に 今まで速かった SQL が ある日突然遅くなった 中にはそんな経験をした方もいるのではないでしょうか そんな時に慌てないためにも 問題のない時点での SQL の実行計画をとっておきましょう そうすれば 遅くなったときの実行計画と速かったときの実行計画を比べることで 問題解決への道すじを早くみつけることができるでしょう

18 評価版 評価版のダウンロードはこちらから

19