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
2009年5月31日日曜日
OracleCoding Tips － コネクション・プーリングのメリットデメリット
Coding Tips － コネクション・プーリングを利用するには
メリット：
CPU使用量や応答時間におけるコストが高い
デメリット：
メモリリソースを無駄に消費する
コネクションプーリングに付随する技術
・文キャッシュ
・共有サーバ構成、専用サーバ構造
共有サーバ構成は、ディスパッチャが共有サーバとの通信を中断し、SQL単位で共有サーバへ処理を振り分ける
共有サーバ構成でコネクションプール管理をモジュールに当たるのがディスパッチャである
→ディスパッチャは接続形態が専用サーバ構成とは異なり複雑です。
同一のセッションでも異なる共有サーバにまたがって処理が行われるためSQLトレースなどを取得して分析を行うなどの作業も困難となる。
そのため、アプリケーション側にコネクションプーリングが実装されることの多い現在ではほとんど使用されず、まれに大量のアイドル接続が存在するときや、物理接続の生成切断が頻繁に行われる場合に、コネクションプーリングと併用される程度。
→コネクションプーリングが使用されない場合は有効だが
・OCIコネクションプーリング
・Oracle Connection Cache
★WebLogicのJDBCプーリングは最大接続を超える場合は我慢型
→待ち行列の監視が必要
HighestNumWaiters
ConnectionReserveTimeoutSeconds
InactiveConnectionTimeoutSeconds
メリット：
CPU使用量や応答時間におけるコストが高い
デメリット：
メモリリソースを無駄に消費する
コネクションプーリングに付随する技術
・文キャッシュ
・共有サーバ構成、専用サーバ構造
共有サーバ構成は、ディスパッチャが共有サーバとの通信を中断し、SQL単位で共有サーバへ処理を振り分ける
共有サーバ構成でコネクションプール管理をモジュールに当たるのがディスパッチャである
→ディスパッチャは接続形態が専用サーバ構成とは異なり複雑です。
同一のセッションでも異なる共有サーバにまたがって処理が行われるためSQLトレースなどを取得して分析を行うなどの作業も困難となる。
そのため、アプリケーション側にコネクションプーリングが実装されることの多い現在ではほとんど使用されず、まれに大量のアイドル接続が存在するときや、物理接続の生成切断が頻繁に行われる場合に、コネクションプーリングと併用される程度。
→コネクションプーリングが使用されない場合は有効だが
・OCIコネクションプーリング
・Oracle Connection Cache
★WebLogicのJDBCプーリングは最大接続を超える場合は我慢型
→待ち行列の監視が必要
HighestNumWaiters
ConnectionReserveTimeoutSeconds
InactiveConnectionTimeoutSeconds
Oracle:動的サンプリング - オラクル・Oracleをマスターするための基本と仕
動的サンプリング - オラクル・Oracleをマスターするための基本と仕
■必要なし
1秒未満の高速なレスポンスが要求される
短期間で大きくデータが変更することのないシステム
■価値のある代表例
・TemporaryTableに対するアクセス
→動的サンプリングは最適！！！
・単一表の列堂氏に相関関係がある場合
→DWH
サンプリング・レベル
サンプリングレベルは OPTIMIZER_DYNAMIC_SAMPLING 初期化パラメータの値 または、
SQL に記述される /*+ DYNAMIC_SAMPLING(table_spec sampling_level) */ といったヒント句によって指定する。
■必要なし
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
を使用する
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');
とする
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
--システムレベルの設定値
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
Oracle:optimizer_mode オプティマイザの判断ミスを疑ってみよう（1/3） － ＠IT
オプティマイザの判断ミスを疑ってみよう（1/3） － ＠IT
２つの最適化の方向性がある。
１．レスポンス重視（最初の数行を返すまでの時間を最適化）
２．スループット重視（最後の行を返すまでの時間を最適化）
Oracle10Gの場合デフォルトは、
optimizer_mode string ALL_ROWS
これは、２．のスループット重視に最適（データフェアハウスやバッチ処理に）
OLTP系のWebアプリはレスポンス重視
→FIRST_ROWS_nを指定
n:1,10,100,1000
詳しくはここを参照
↓
http://otndnld.oracle.co.jp/products/database/oracle10g/performance/htdocs/burleson_cbo_pt1/burleson_cbo_pt1_2.html
Oracleでは、最適な実行計画の定義を選択できる複数のオプティマイザ･モードを用意しています。
* optimizer_mode=first_rows_ このCBOモードは、他の計画に比べ全問合せの実行により長い時間がかかる、またはより多くのコンピューティング資源を使用しますが、可能な限り速く行を戻します。 索引へアクセスすると行を迅速に戻すことができるため、first_rows optimizer_modeでは通常フル・テーブル・スキャンより索引スキャンを選択します。 first_rowsモードでは、フル・テーブル・スキャンより索引スキャンが有効なため、first_rowsモードは、エンド･ユーザーに対して小さな結果セットの高速な表示が必要なOLTPシステムに適しています。
* optimizer_mode=all_rows_ このCBOモードでは、問合せ全体が完了するまで行が戻されませんが、コンピューティング資源全体が最小限に抑えられます。 all_rowsアクセス方法は、全索引スキャンよりパラレル・フル・テーブル・スキャンが有効な場合が多く、索引経由の事前ソートの取得よりソートが選択されます。 フル・テーブル・スキャンが有効なall_rowsモードは、リアルタイム表示のための中間の行が不要なデータ･ウェアハウス、意思決定システムおよびバッチ指向のデータベースに適しています。
* optimizer_mode=first_rows_nこのOracle9i Databaseオプティマイザ・モードの拡張機能は、予期された小さなリターン･セットの問合せを最適化します。 値はfirst_rows_1、first_rows_10、first_rows_100およびfirst_rows_1000です。 CBOは、問合せ結果セットのカーディナリティを判断するための重要なドライバとしてfirst_rows_nのnを使用します。 CBOに事前に問合せから特定の行を戻す指定をすることで、CBOは表の行にアクセスするために索引を使用するかどうかをより的確に判断できます。
* Optimizer_mode=ruleルールベース・オプティマイザ（RBO）は、Oracleデータベースの初期リリースからの古いオプティマイザ･モードです。ルールベース・オプティマイザは、約10年更新されておらず、RBOは1994年以降のOracleの新機能（ビットマップ索引、表パーティションおよびファンクション・ベースの索引など）をサポートしないため、本番使用はお薦めしません。
２つの最適化の方向性がある。
１．レスポンス重視（最初の数行を返すまでの時間を最適化）
２．スループット重視（最後の行を返すまでの時間を最適化）
Oracle10Gの場合デフォルトは、
optimizer_mode string ALL_ROWS
これは、２．のスループット重視に最適（データフェアハウスやバッチ処理に）
OLTP系のWebアプリはレスポンス重視
→FIRST_ROWS_nを指定
n:1,10,100,1000
詳しくはここを参照
↓
http://otndnld.oracle.co.jp/products/database/oracle10g/performance/htdocs/burleson_cbo_pt1/burleson_cbo_pt1_2.html
Oracleでは、最適な実行計画の定義を選択できる複数のオプティマイザ･モードを用意しています。
* optimizer_mode=first_rows_ このCBOモードは、他の計画に比べ全問合せの実行により長い時間がかかる、またはより多くのコンピューティング資源を使用しますが、可能な限り速く行を戻します。 索引へアクセスすると行を迅速に戻すことができるため、first_rows optimizer_modeでは通常フル・テーブル・スキャンより索引スキャンを選択します。 first_rowsモードでは、フル・テーブル・スキャンより索引スキャンが有効なため、first_rowsモードは、エンド･ユーザーに対して小さな結果セットの高速な表示が必要なOLTPシステムに適しています。
* optimizer_mode=all_rows_ このCBOモードでは、問合せ全体が完了するまで行が戻されませんが、コンピューティング資源全体が最小限に抑えられます。 all_rowsアクセス方法は、全索引スキャンよりパラレル・フル・テーブル・スキャンが有効な場合が多く、索引経由の事前ソートの取得よりソートが選択されます。 フル・テーブル・スキャンが有効なall_rowsモードは、リアルタイム表示のための中間の行が不要なデータ･ウェアハウス、意思決定システムおよびバッチ指向のデータベースに適しています。
* optimizer_mode=first_rows_nこのOracle9i Databaseオプティマイザ・モードの拡張機能は、予期された小さなリターン･セットの問合せを最適化します。 値はfirst_rows_1、first_rows_10、first_rows_100およびfirst_rows_1000です。 CBOは、問合せ結果セットのカーディナリティを判断するための重要なドライバとしてfirst_rows_nのnを使用します。 CBOに事前に問合せから特定の行を戻す指定をすることで、CBOは表の行にアクセスするために索引を使用するかどうかをより的確に判断できます。
* Optimizer_mode=ruleルールベース・オプティマイザ（RBO）は、Oracleデータベースの初期リリースからの古いオプティマイザ･モードです。ルールベース・オプティマイザは、約10年更新されておらず、RBOは1994年以降のOracleの新機能（ビットマップ索引、表パーティションおよびファンクション・ベースの索引など）をサポートしないため、本番使用はお薦めしません。
Oracle:[Oracle] インデックスに関するコスト計算の調整によるオプティマ�
[Oracle] インデックスに関するコスト計算の調整によるオプティマ�
OPTIMIZER_INDEX_CACHING
インデックス・ブロックが何％くらいバッファ・キャッシュに存在すると仮定するかを示します。0～100の範囲で指定し、デフォルトは 0 となっています。この値を高くすればするほど、インデックスのキャッシュヒット率が高いものとみなされ、インデックス・スキャンのコストが低く見積もられるようです。
OPTIMIZER_INDEX_COST_ADJ
インデックス・スキャンのコストを標準のコストの何％で計算するかを示します。0～100の範囲で指定し、デフォルトは100となっています。この値を小さくすればするほど、インデックス・スキャンのコストが低く見積もられるようです。
デフォルト値は、データフェアハウス系システムには最適
一般的なOLTPでは、次の値を目安にすると良い（Oracle現場ワザから）
OPTIMIZER_INDEX_CACHING=90
OPTIMIZER_INDEX_COST_ADJ=25
索引を作ったけど、フルテーブルスキャンの発生率が高いことが問題視されている場合に良い
OPTIMIZER_INDEX_CACHING
インデックス・ブロックが何％くらいバッファ・キャッシュに存在すると仮定するかを示します。0～100の範囲で指定し、デフォルトは 0 となっています。この値を高くすればするほど、インデックスのキャッシュヒット率が高いものとみなされ、インデックス・スキャンのコストが低く見積もられるようです。
OPTIMIZER_INDEX_COST_ADJ
インデックス・スキャンのコストを標準のコストの何％で計算するかを示します。0～100の範囲で指定し、デフォルトは100となっています。この値を小さくすればするほど、インデックス・スキャンのコストが低く見積もられるようです。
デフォルト値は、データフェアハウス系システムには最適
一般的なOLTPでは、次の値を目安にすると良い（Oracle現場ワザから）
OPTIMIZER_INDEX_CACHING=90
OPTIMIZER_INDEX_COST_ADJ=25
索引を作ったけど、フルテーブルスキャンの発生率が高いことが問題視されている場合に良い
Oracle：データベースを起動後に、spfileを使って起動したか、pfileを使って起動したのかを調べる手段
SQLPLUSから
show parameter spfile
を実行する
pfileを使用している場合には、show parameter spfileの結果が出力されない。
show parameter spfile
を実行する
pfileを使用している場合には、show parameter spfileの結果が出力されない。
登録:
投稿 (Atom)
