Oracle:SQLチューニングの基盤となる統計情報（4/4） － ＠IT

SQLチューニングの基盤となる統計情報（4/4） － ＠IT: "SQLチューニングの基盤となる統計情報"

遅いSQLは、
SET AUTOTRACE ON
で確認せよ。

■SQL統計情報の基本的な見方

まずは、
・consistent gets：SQLが検索のためにアクセスしたブロック数
　　　　　　　　　→実行に費やすCPU時間はほぼこれに比例する
・physical reads：ディスクから読み込んだブロック数
を見る。

その後
・recursive calls：再帰SQLの実行を見る。
何回か同じSQLを実行しても、この値が大きい場合は、バッファキャッシュを使い果たしている
可能性が高い。

Linux:ユーザアプリ使用メモリサイズ

Linux、負荷まわりの話 - goungoun技術系雑記帳:
"ユーザアプリ使用メモリサイズ＝
MemTotal
-MemFree
-Buffers
-Cached
-SwapCached
-Slab
-PageTables
-VmallocUsed
"

Linux ページキャッシュ - naoyaのはてなダイアリー

Linux のページキャッシュ - naoyaのはてなダイアリー

* Linux はメモリがある限りページ単位でブロック型デバイスの入出力をキャッシュする
* I/O はページキャッシュに任せよう
* ページキャッシュの状態は sar -r で確認できる
* DB はメモリにフィットさせよう (http://d.hatena.ne.jp/stanaka/20070427/1177651323)
* ページキャッシュがクリアされてしまったら read してキャッシュに載せよう
* VFS 周りの実装はインタフェースにコールバックを登録していく実装になっている
* tmpfs は read / write が tmpfs 用に実装されている。I/Oに伴うページキャッシュの扱いが通常と違う。

Oracle:Document Library 10g 11g

Oracle Database オンライン・ドキュメント 11g リリース1（11.1）

日本語
http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/index.htm
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/index.htm

英語
http://www.oracle.com/pls/db111/homepage
http://www.oracle.com/pls/db102/homepage

OracleCoding Tips － コネクション・プーリングのメリットデメリット

Coding Tips － コネクション・プーリングを利用するには

メリット：
CPU使用量や応答時間におけるコストが高い

デメリット：
メモリリソースを無駄に消費する


コネクションプーリングに付随する技術
・文キャッシュ
・共有サーバ構成、専用サーバ構造
共有サーバ構成は、ディスパッチャが共有サーバとの通信を中断し、SQL単位で共有サーバへ処理を振り分ける
共有サーバ構成でコネクションプール管理をモジュールに当たるのがディスパッチャである
→ディスパッチャは接続形態が専用サーバ構成とは異なり複雑です。
同一のセッションでも異なる共有サーバにまたがって処理が行われるためSQLトレースなどを取得して分析を行うなどの作業も困難となる。
そのため、アプリケーション側にコネクションプーリングが実装されることの多い現在ではほとんど使用されず、まれに大量のアイドル接続が存在するときや、物理接続の生成切断が頻繁に行われる場合に、コネクションプーリングと併用される程度。
→コネクションプーリングが使用されない場合は有効だが

・OCIコネクションプーリング

・Oracle Connection Cache

★WebLogicのJDBCプーリングは最大接続を超える場合は我慢型
→待ち行列の監視が必要
HighestNumWaiters
ConnectionReserveTimeoutSeconds
InactiveConnectionTimeoutSeconds

自動SQLチューニング

自動SQLチューニング: "自動チューニング・オプティマイザ"

自動チューニング・オプティマイザ

Oracle:動的サンプリング - オラクル・Oracleをマスターするための基本と仕

動的サンプリング - オラクル・Oracleをマスターするための基本と仕

■必要なし
1秒未満の高速なレスポンスが要求される
短期間で大きくデータが変更することのないシステム

■価値のある代表例
・TemporaryTableに対するアクセス
→動的サンプリングは最適！！！
・単一表の列堂氏に相関関係がある場合
→DWH

サンプリング・レベル
サンプリングレベルは OPTIMIZER_DYNAMIC_SAMPLING 初期化パラメータの値 または、
SQL に記述される /*+ DYNAMIC_SAMPLING(table_spec sampling_level) */ といったヒント句によって指定する。

Oracle:統計情報の戻し

統計情報の取得と実行計画の固定について - SQL> shutdown abort

user_tab_stats_historyを見れば、いつ統計情報取得のJOBがながれたか
select * from user_tab_stats_history order by stats_update_time

統計情報をリストアするには、
exec dbms_stats.restore_schema_stats
を使用する

Oracle:統計情報の自動収集 メンテナンス・ウィンドウを使用した自動システム・タスクの�

メンテナンス・ウィンドウを使用した自動システム・タスクの�: "自動統計収集ジョブ

Oracle10gでは、DBを作成するとデフォルトで
GATHER_STATS_JOB
と呼ばれる自動収集のためのスケジューラジョブが準備される。

■確認方法
select job_name,program_name, schedule_name, stop_on_window_close from dba_scheduler_jobs where JOB_NAME LIKE 'GATHER%'
→結果：
GATHER_STATS_JOB GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP TRUE

実際に実行されるストアドプロシージャは以下の通り
select * from dba_scheduler_programs where program_name = 'GATHER_STATS_PROG'

・スケジュールが、MAINTENANCE_WINDOW_GROUPとは？
select * from dba_scheduler_wingroup_members
→MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW

select window_name, repeat_interval, duration from dba_scheduler_windows
→WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 0 8:0:0.0
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 2 0:0:0.0

■自動統計時間にかかった時間を確認
select job_name, actual_start_date, run_duration from dba_scheduler_job_run_details
where job_name = 'GATHER_STATS_JOB'
→run_durationをみれば、どのくらい時間がかかったかわかる

★自動統計収集のメリット
・スケジュール済みなので、不注意による統計の取り忘れがなくなる
・ある程度変更があった表のみ統計が再収集されるため、１回の統計収集の負荷は最小限である
・内部的に記録した列の使用状況に基づいて網羅的に統計収集されるため、開発者が必要性に気付かなかった列にもヒストグラムが作成され、実行計画の精度が高くなる
・統計収集はウィンドウの範囲内でしか実行されないため、時間帯を区切った運用がしやすい

★実運用を考えると？
・スケジューラウィンド設定を変更し、オンライン時間帯やバッチ時間帯と重ならないようにすること
・バッチ処理後に統計情報をとる
・例外として、複雑な問い合わせを実行するようなバッチの場合は、バッチ実行前に統計情報をとる
・ウィンドウのオープン期間を短くしすぎない。サイズの翁オブジェクトの統計収集が期間何に完了しなくなる可能性がある

★自動統計収集のデメリット
・実際には必要のないオペレーションの可能性がある
→とはいえ、それが自動統計収集の本質であり、無駄であろうとも機械的に情報を収集して陳腐化を防ぐアプローチが重要

★自動統計収集の監査
--30日以上統計収集されていない表の確認
select * from user_tables where last_analyzed is not null
and last_analyzed < trunc(sysdate) -30
order by last_analyzed

--全く統計収集されていない表の確認
select * from user_tables where last_analyzed is null



スケジューラ・ジョブGATHER_STATS_JOBは、Oracle Databaseのインストール時に事前定義されます。 GATHER_STATS_JOBは、データベース内で、統計がないかまたは失効した統計のみがあるすべてのオブジェクトのオプティマイザ統計を収集します。

統計収集を手動で管理するほうがよい場合は、次のように、このスケジューラ・ジョブを使用禁止にします。

EXECUTE DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

ONにする場合は
EXECUTE DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
とする

Oracle:CBOはどの情報を元に実行計画を決めるのか - SQL> shutdown abort

CBOはどの情報を元に実行計画を決めるのか - SQL> shutdown abort

--システムレベルの設定値
select * from v$SYS_OPTIMIZER_ENV order by name


--セッションごとの設定値
select * from v$SES_OPTIMIZER_ENV order by name


--SQLごとの設定値
select * from v$SQL_OPTIMIZER_ENV order by name