Dion Cho - Oracle Performance Storyteller: October 2009

Thursday, October 29, 2009

オラクル性能に対する短い考え＃8

ジャヴァ開発者たちはオラクルに対してもっと知らなければならない

ジャヴァとJDBCを利用すれば具現に一月が掛かって性能は遅いプログラムをオラクルが提供する機能を利用すれば具現には一日が掛かって性能は優れるプログラムが作れます。いつもそうだと言うことではないんですけど多くのばあいそうです。

でもこんなノーハウたちがまだ多い開発者たちに広範囲に共有されていないみたいです。誰の責任なのかは分かりませんけど...

Tuesday, October 27, 2009

オラクル性能に対する短い考え　＃7

オラクル性能コンサルティングの単価は上がるか下がるか

オラクルに入門した人なら誰でもオラクル性能専門家を夢見て、究極的には高い収入が保障されるコンサルタントを目標にするようになります。

現実は、世界的な景気沈滞と共に大型プロジェクトが失踪しながらオラクル性能コンサルタントの単価は速い速度で下がっています。当分の間はこんな傾向が加速化するはずです。

もう一度の事実は、ソフトウエア業界が若い人たちに離れられて実力のある者の数がかえって次第に減っていることです。それなら実力を認められた人たちの単価はかえって上がらないんでしょうか。

今の選択が向後5－10年を左右します。皆さんの選択は何ですか。

Monday, October 26, 2009

なぜ診断イベントを理解すべきなのか。

たとえ開発者と言ってもオラクルが提供する診断イベントを理解するのが必要だと思います。たとえば次のような質問を見てください。

整列領域大きさ(Sort Area Size)によって整列がどのように行われるかを理解するように多様なテストを修行しています。どころでSORT_AREA_SIZEパラメータ値を300バイトで低く設定してもオラクルが最小値を自分で調整するらしいです。オラクルで使用可能な整列領域大きさの最小値がいくらなのか分かる方法は何でしょうか。

マニュアルには下記のように記述されています。

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams232.htm

すなわち、整列領域大きさの最小値は6*8K(データベースブロックサイズ)＝48Kバイトだというのが分かります。本当のエンジニアだったらここから進み、この最小値を目で確認する方法はないのか？と質問をするようになるはずです。

この類の質問に一番直接的に答ができるのが診断イベントです。たとえば10032イベントを活性化すれば、オラクルは整列作業の修行に対する詳細な統計情報を記録してくれます。


UKJA@ukja1021> @oerr 10032
10032
 "sort statistics (SOR*)"
// *Cause:
// *Action:

また10033イベントを利用すれば実際の整列を修行しながら個別Sort Runたちをどのように使用するのかまで分かります。大変有用な情報でしょう。


UKJA@ukja1021> @oerr 10033
10033
 "sort run information (SRD*/SRS*)"
// *Cause:
// *Action:
// *Cause:
// *Action:

10032イベントを利用してマニュアルの内容を検証してみましょうか。


drop table t1 purge;
create table t1
as select rpad('x',300,'x') as c1
from dual
connect by level <= 100
;

alter session set workarea_size_policy=manual;
alter session set sort_area_size=300;

alter session set events '10032 trace name context forever, level 1';

select c1 from t1 order by c1;

alter session set events '10032 trace name context off';

トレースファイルの内容は次のとおりです。SORT_AREA_SIZE値が正確に48Kバイトというのが確認できます。


---- Sort Parameters ------------------------------
sort_area_size                    49152
sort_area_retained_size           16384
sort_multiblock_read_count        1
max intermediate merge width      2
---- Sort Statistics ------------------------------
Initial runs                              1
Input records                             100
Output records                            100
Disk blocks 1st pass                      4
Total disk blocks used                    6
Total number of comparisons performed     102
  Comparisons performed by in-memory sort 101
  Comparisons while searching for key in-memory 1
Temp segments allocated                   1
Extents allocated                         1
Uses version 2 sort
Uses asynchronous IO
    ---- Run Directory Statistics ----
Run directory block reads (buffer cache)  2
Block pins (for run directory)            1
Block repins (for run directory)          1
    ---- Direct Write Statistics -----
Write slot size                           8192
Write slots used during in-memory sort    2
Number of direct writes                   4
Num blocks written (with direct write)    4
Block pins (for sort records)             4
Cached block repins (for sort records)    1
Waits for async writes                    3
    ---- Direct Read Statistics ------
Size of read slots for output             8192
Number of read slots for output           2
Number of direct sync reads               2
Number of blocks read synchronously       2
Number of direct async reads              2
Number of blocks read asynchronously      2
---- End of Sort Statistics -----------------------

立派な開発者ならデバッガ(Debugger)に上手なはずです。オラクルの診断イベントはオラクルデバッガと言えます。オラクルを深く理解しようとすれば必修的な知識だと言えます。

Sunday, October 25, 2009

Friday, October 23, 2009

オラクル性能に対する短い考え＃6

時間(Time)は一番危ない性能測定方法である

これ何のとんでもない言葉でしょうか。：)

もちろん時間は一番直観的で確実な性能測定方法です。これには異論の余地がありません。

でも、時間が改善された理由を明確に打ち明けずに済んだらこれより危険なものはありません。間違った結論を下しやすいです。オラクルがどんな作業をどのようにしたので時間が改善されたのか、これが重要なものですか、時間の改善自体は重要ではありません。

それでオラクルの性能を測る時は時間だけじゃなくてワーカロード(Logical Readsのような)、レッチの獲得、Mutexの獲得、待機イベント、必要な場合OSレベルのデータなどのバックグラウンドデータがずっと重要です。こんなデータをよく収集し分析したら時間は見る必要さえありません。不幸にも現在のオラクル性能教育ではこんなものを体系的に教える所はありません。

Wednesday, October 21, 2009

Tuesday, October 20, 2009

オラクル性能に対する短い考え　＃５

専門家の言うことを信じるな。

いわゆるオラクル性能専門家という方々が書いたこととか言ったことを反駁したら一冊の本を出せるかもと普段から考えています。

私も三冊の本を出したが、専門家たちが自分がもうよく分かっている内容を本で出すようでしょう。とんでもないんです。分からないものを研究しながら本を出すのです。だからどんなに多くの誤りがあるでしょうか。

皆が普通の人間なのでだんなにとんでもない失敗をするか知れません。大部分の開発者がアプリソースコードの100ラインあたり一ラインくらいでバグを作ると言われています。1％の誤謬がいつもあるのです。専門家たちの言うことや書くことも例外ではありません。私が書いた本を今見るといくら多くの誤りがあるか知れません。顔が真っ赤になります。

誰が専門家のように何かを言えば三番だけしつこく聞いてみてください。すぐ底をつくことになってしまいますよ。その過程を繰り返してみれば自分がいつの間にか専門家の水準に発展したのを見るようになるでしょう。

Monday, October 19, 2009

オラクル性能に対する短い考え　＃４

待機イベント(Wait Event)は原因じゃなくて結果である。

私が勤めているエクセムで一番積極的に紹介して、また一番よく活用するのが待機イベントです。性能低下の原因を手軽に見つけることができるというのが待機イベントの意味です。

でも注意しなければならない点があります。待機イベントは性能低下の原因じゃなくて逆に性能低下の結果ということです。すなわち、待機イベントを待つために性能が低くなるじゃなくて、性能に問題があるために待機イベントを待機するんです。

db file sequential readイベントを長い時間待機するためにI/O性能が遅くなるのじゃないでしょ。I/O呼び出しが過ぎるとかI/O性能が遅いためにdb file sequential readイベントの待機時間が長くなるのです。log file syncイベントを長い時間待機するためにCommitの性能が遅くなるのじゃないでしょ。Commit呼び出しが過ぎるとかLGWRがちゃんと働けないからlog file syncイベントを待機するようになるのです。

すなわち、待機イベントとは性能低下が発生したという結果をもっとよく視覚化してくれるだけ、性能低下の原因がどこかを直接言ってくれるのではないんです。おかげで私のようなエンジニアが今までもお金をもうけられるんじゃないですか。

なお待機イベントの概念が発達していない他のすべてのDBMS(MySQL, UDB, SQLServerなど)でも性能低下の原因を見つけるのが可能な理由となるのです。

Thursday, October 15, 2009

オラクル性能に対する短い考え＃3

自分の好奇心尊重しろ

オラクル性能専門家になるにとって一番必要な姿勢は他の仕事のために自分の好奇心を犠牲しないのです。自分の好奇心を解けるために極限まで行くのを繰り返してみたら、ある瞬間専門家の境地に至っている自身を見つけるようになります。

現実の問題は日常の仕事を処理するに多くの時間と努力が掛かるということです。上役に報告すること、ユーザーの電話応対、仲間たちとのお茶などのいろいろなものに気にしなければならないのが現実で自分の好奇心の解決に最善を尽くせないようになってしまいます。

でも、全ての世界的な専門家らはこんな制約を超えたおかげでそんな位置に至れたんです。皆さんも現実の限界を超える支度していますか。

Wednesday, October 14, 2009

Row Cache LockのホルダーをV$ビューで探すこと

Hanganalyzeコマンドを利用すると手軽にRow Cache Lockのホルダーを探すのができます。


connect sys/oracle as sydba

oradebug setmypid
oradebug hanganalyze 3


-- trace file
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 :  :
    <0/158/44363/0x3424fb7c/5504/No Wait>
 -- <0/134/928/0x3424f58c/4600/row cache lock>

トレースファイルを通じずにV$ビューだけでRow Cache Lockのホルダーを探し出そうとする場合もあります。V$ROWCACHE_PARENTを利用すれば可能です。簡単な例で説明してみます。

まず、次のようにNOCACHE属性のSequenceを作ってから二つのセッションからNextvalueの呼び出しを繰り返します。NOCACHEのSequenceはNextvalueを呼び出すたびにRow Cache Lockを5番(SRX)モードで獲得します。従ってRow Cache Lockの競合を再現しやすいです。


create sequence s1 nocache;

-- temp.sql
declare
 v_value   number;
begin
  for idx in 1 .. 100000 loop
   select s1.nextval into v_value from dual;
  end loop;
end;
/

ho start sqlplus ukja/ukja@ukja1021 @temp
ho start sqlplus ukja/ukja@ukja1021 @temp

V$ROWCACHE_PARENTビューを次のように検索してみれば、Row Cache Lockのホルダーセッションを探し出せます。Where以下の条件を注意深く見てください。


col sid new_value sid

select h.address, h.saddr, s.sid, h.lock_mode
from v$rowcache_parent h, v$rowcache_parent w, v$session s
where h.address = w.address and
   w.saddr = (select saddr from v$session where event = 'row cache lock' 
         and rownum = 1) and
   h.saddr = s.saddr and
   h.lock_mode > 0
;

ADDRESS  SADDR           SID  LOCK_MODE
-------- -------- ---------- ----------
283AFB50 3432EB34        145          5

セッションIDを通じてホルダーセッションの詳しい情報が獲得できます。


UKJA@ukja1021> @session &sid
UKJA@ukja1021> set echo off
01. basic session info
SID                           : 145
SERIAL#                       : 447
SPID                          : 2328
MACHINE                       : POWER_GROUP\UKJAX
PROGRAM                       : sqlplus.exe
PGA                           : 515668
UGA                           : 156280
LAST_CALL_ET                  : 13
LOGON_TIME                    : 2009/10/15 13:50:31
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
02. session wait
SID                           : 145
EVENT                         : row cache lock
P1                            : 13
P1RAW                         : 0000000D
P2                            : 0
P2RAW                         : 00
P3                            : 5
P3RAW                         : 00000005
SECONDS_IN_WAIT               : 0
STATE                         : WAITING
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
03. process info
PID                           : 23
PROGRAM                       : ORACLE.EXE (SHAD)
PGA_USED_MEM                  : 324361
PGA_ALLOC_MEM                 : 623853
PGA_MAX_MEM                   : 623853
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
04. sql info
SID                           : 141
SHARABLE_MEM                  : 8640
PERSISTENT_MEM                : 1156
RUNTIME_MEM                   : 592
EXECUTIONS                    : 100000
FETCHES                       : 100000
BUFFER_GETS                   : 403341
SQL_TEXT                      : SELECT S1.NEXTVAL FROM DUAL
-----------------


05. sql plan info

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------

SQL_ID  8c3n1ysfzzd1z, child number 0
-------------------------------------
SELECT S1.NEXTVAL FROM DUAL

Plan hash value: 2479889702

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  SEQUENCE        | S1   |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
...

使うことがぜんぜんなさそうなV$ROWCACHE_PARENTビューの有用な使用例となるでしょう。

Tuesday, October 13, 2009

オラクル性能に対する短い考え　＃2

すべての主張にはいつも正確な条件が付かなければならない。

いつか今更「日数データにVARCHAR2タイプを使うべきか、DATEタイプを使うべきか」という論議をしたことがあります。いつかどこかだれかによって「日数データにはVARCHAR2タイプを使わなければならない」という主張が出た以来発生した誤解と偏見たちが多い人々を混乱させています。

「日数データにはVARCHAR2タイプを使わなければならない」という主張は「こんなこんな条件下には実行計画の異常が発生可能だから格別に注意して、本当に必要な場合にはDATEタイプではないVARCHAR2タイプが使える」ほどになるべきです。

いくら資料を調べて研究をしてみても日数データにはDATEタイプを使うのが一般論ですし、VARCHAR2タイプを使うことが本当に特殊論であります。

これだけでありません。オラクル性能に対する多い断片的な主張たちはまるで「人間は一日に牛乳を一杯づつは必ず飲まなければならない」という主張のように単純すぎます。

Monday, October 12, 2009

DBMS_ADVANCED_REWRITEとDML

Oracle10gで紹介されたDBMS_ADVANCED_REWRITEパッケージを利用すれば特定のSQL文章のテキスト自体が変えられます。強力な機能ですけど、この機能はOLTPではなくてDW用で設計されたという限界があります。次のような制約を持っています。

バインド変数を含んだ場合には動作しません。(Metalink Doc ID. 392214.1)
SELECT文章についてだけ適用可能です。
ベーステーブルについてDMLが発生すれば動作しません。

三つ目の制約を避けられる方法はよく知られていません。テストを通じて見つけられるのはQUERY_REWRITE_INTEGRITYパラメータで制御可能だということです。

簡単なテストケースを通じて説明してみます。

1. 次のように簡単なテーブルを作ります。


UKJA@ukja1106> create table t1
  2  as select level as c1, level as c2
  3  from dual
  4  connect by level <= 1000;

Table created.

Elapsed: 00:00:00.04

UKJA@ukja1106> create index t1_n1 on t1(c1);

Index created.

Elapsed: 00:00:00.03

UKJA@ukja1106> exec dbms_stats.gather_table_stats(user,'t1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18

2. FULLのヒントを持っているSELECT文章があって、当然にFull Table Scanが選択されます。


UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     7 |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------

3. これを避けられる方法中一つは次のようにSQL文章を変えてしまうことです。この時QUERY_REWRITE_INTEGRITYパラメータの値をTRUSTEDに変更すべきです。


UKJA@ukja1106> begin
  2  
  3        sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
  4        'test_rewrite',
  5        'select /*+ full(t1) */ * from t1 where c1 = 1',
  6        'select /*+ index(t1) */ c1, c2 from t1 where c1 = 1',
  7        false,
  8        'text_match');
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

UKJA@ukja1106> alter session set query_rewrite_integrity=trusted;

Session altered.

Elapsed: 00:00:00.00

4. 次のように魔法のようにIndex Range Scanに実行計画が変わってしまいます。


UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

5. 問題は次のようにDMLが発生すればRewriteができないということです。


UKJA@ukja1106> insert into t1 values(1, 1);

1 row created.

Elapsed: 00:00:00.00

UKJA@ukja1106> commit;

Commit complete.

Elapsed: 00:00:00.00

UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     7 |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------

6. これを避けられる一つの方法はQUERY_REWRITE_INTEGRITYパラメータの値をSTALE_TOLERATEDに変えることです。データがSTALEでも(最新の状態ではなくても)、TOLERATEしろ(堪えろ)ということですよ。


UKJA@ukja1106> alter session set query_rewrite_integrity=stale_tolerated;

Session altered.

Elapsed: 00:00:00.00

UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

7. DMLが発生してもRewriteは成功的に整います。


UKJA@ukja1106> insert into t1 values(1, 1);

1 row created.

Elapsed: 00:00:00.01

UKJA@ukja1106> commit;

Commit complete.

UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

8. ただし、も一つの制約があります。DMLの発生後Commitが実行されなければRewriteはまた失敗してしまいます。


UKJA@ukja1106> insert into t1 values(1, 1);

1 row created.

Elapsed: 00:00:00.01

UKJA@ukja1106> explain plan for
  2  select /*+ full(t1) */ * from t1 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     7 |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------

何やかやでOLTPでは制約が多いんです。DW環境では本当に有用かも知れませんが。OLTPでも適当な所によく使用したら大きい効果が得られるでしょう。

Saturday, October 10, 2009

オラクル性能に対する短い考え　＃1

低い所の果物を先に取ろ

もちろんより先制的な性能管理、たとえ正確な業務ロジック把握、合理的な物理設計、正確な統計情報管理などが一番重要です。でも現実状況では一番少ない費用で最高の効果を得ることが一番重要なものであります。

ヒントとかヒドゥンパラメータのような方法たちは適用しやすいし効果が大きいという面で一番低い果物に該当します。低い果物を先に取ることによって費用を減らすのはある面で見れば美徳であります。僕らがいぜんとしてヒントとヒドゥンパラメータに沸き返るほかない理由です。こんな低い果物をたやすく取れるように多い勉強が必要です。

但し、低い果物にだけ慣れてしまったら絶対高いところへ行けなくなるでしょう。均衡感覚もまた必須的です。

Wednesday, October 7, 2009

どうしてV$ビューよりX$テーブルのほうが好きなのか。

オラクルの性能問題を扱ってみるとV$ビュー(Dynamic Performance View)を必ず使用するようになります。でも、V$ビューをしばらくの間使用してみると必ずと言ってもいいだけにX$テーブルを使用しようとする欲が生じます。

どうしてそうですか。なぜ暗号のようなまずい顔のX$テーブルが後ではもっと好きになるでしょうか。僕の経験から見ると三つぐらいの理由があるようです。

V$ビューの定義自体をもっとよく分かるように助けてくれる。
性能面でもっと効率的である。
V$ビューが見せてくれない情報を見せてくれる。

簡単な例で説明してみます。

1. V$ビューの定義自体をもっとよく分かるように助けてくれる。

次はマニュアルで説明しているV$SESSION_WAIT.STATE列の意味です。


V$SESSION_WAIT.STATE  VARCHAR2(19)  Wait state:
 WAITING - Session is currently waiting
 WAITED UNKNOWN TIME - Duration of the last wait is unknown; this is the value when the parameter TIMED_STATISTICS is set to false
 WAITED SHORT TIME - Last wait was less than a hundredth of a second
 WAITED KNOWN TIME - Duration of the last wait is specified in the WAIT_TIME column

何を話しているのかがわかるけど胸に応えないんです。でもV$SESSION_WAITビューの定義を見れば胸によく応えます。


SYS@ukja1021> select view_name, view_definition
  2  from v$fixed_view_definition
  3  where view_name = upper('&1')
  4  ;
old   3: where view_name = upper('&1')
new   3: where view_name = upper('GV$SESSION_WAIT')

select s.inst_id,s.indx,s.ksussseq,e.kslednam, e.ksledp1,s.ksussp1,s.ksussp1r,e.
ksledp2, s.ksussp2,s.ksussp2r,e.ksledp3,s.ksussp3,s.ksussp3r, e.ksledclassid, e.
ksledclass#, e.ksledclass, decode(s.ksusstim,0,0,-1,-1,-2,-2,   decode(round(s.k
susstim/10000),0,-1,round(s.ksusstim/10000))), s.ksusewtm, 
decode(s.ksusstim, 0, 'WAITING', 
                          -2, 'WAITED UNKNOWN TIME',  
                          -1, 'WAITED SHORT TIME', 
                          decode(round(s.ksusstim/10000),0,'WAITED SHORT TIME','WAITED KNOWN TIME')) 
from x$ksusecst s
, x$ksled e where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksus
sseq!=0 and s.ksussopc=e.indx

V$ビューってX$テーブルをベースにするビュ－に過ぎません。従ってビューの定義を見ればまるでアプリのソースコードを見るような効果が得られます。

2. 性能面でもっと効率的である。

バッファヘッダーの情報を表しているV$BHビューとX$BHビューに対する同じSQL文の実行計画を比べてみると次のようです。


UKJA@ukja1021> explain plan for
  2  select * from v$bh
  3  where file#=:b1 and block#=:b2
  4  ;

-------------------------------------------------------------------------- 
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |      |     1 |   138 |     1 (100)| 00:00:01 | 
|*  1 |  HASH JOIN OUTER  |      |     1 |   138 |     1 (100)| 00:00:01 | 
|*  2 |   FIXED TABLE FULL| X$BH |     1 |   108 |     0   (0)| 00:00:01 | 
|   3 |   FIXED TABLE FULL| X$LE |   100 |  3000 |     0   (0)| 00:00:01 | 
--------------------------------------------------------------------------

                                                                           
UKJA@ukja1021> explain plan for
  2  select * from sys.x$bh
  3  where file#=:b1 and dbablk=:b2
  4  ;

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |   533 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$BH |     1 |   533 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------

X$BHテーブルを使用するほうがもっと効率的だというのが分かります。

3. V$ビューが見せてくれない情報を見せてくれる。

一番重要な理由となります。情報は多ければ多いほどいいはずですね。V$ビューはユーザーの便宜性のために一部の情報を隠します。でもこの隠された情報が見たくなる時が多いです。

簡単な例で説明してみます。次のように大量のパースを修行するセッションがあります。


declare
  v_cursor      number;
begin
  for idx in 1 .. 200000 loop
    v_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor, 'select /* cursor_share */ * from t1 where c1 = :b1', dbms_sql.native);
    dbms_sql.close_cursor(v_cursor);
  end loop;
end;
/

このセッションがどんなLatchを獲得するのかを調べます。V$LATCHHOLDERビューを利用したらいいでしょう。


SYS@ukja1021> desc v$latchholder
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      PID                                      NUMBER
    2      SID                                      NUMBER
    3      LADDR                                    RAW(4)
    4      NAME                                     VARCHAR2(64)
    5      GETS                                     NUMBER

V$LATCHHOLDERビューの定義を次のようです。


SYS@ukja1021> @fixed_view GV$LATCHHOLDER
SYS@ukja1021> set long 100000
SYS@ukja1021> 
SYS@ukja1021> select view_name, view_definition
  2  from v$fixed_view_definition
  3  where view_name = upper('&1')
  4  ;
old   3: where view_name = upper('&1')
new   3: where view_name = upper('GV$LATCHHOLDER')

VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
GV$LATCHHOLDER
select inst_id,ksuprpid,ksuprsid,ksuprlat,ksuprlnm,ksulagts from x$ksuprlat

ベーステーブルがX$KSUPRLATテーブルでしょう。定義はつぎのようです。


SYS@ukja1021> desc sys.x$ksuprlat
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ADDR                                     RAW(4)
    2      INDX                                     NUMBER
    3      INST_ID                                  NUMBER
    4      KSUPRPID                                 NUMBER
    5      KSUPRSID                                 NUMBER
    6      KSUPRLLV                                 NUMBER
    7      KSUPRLTY                                 NUMBER
    8      KSUPRLAT                                 RAW(4)
    9      KSUPRLNM                                 VARCHAR2(64)
   10      KSUPRLMD                                 VARCHAR2(64)
   11      KSULAWHY                                 NUMBER
   12      KSULAWHR                                 NUMBER
   13      KSULAGTS                                 NUMBER

V$LATCHHOLDERビューに存在しない多様な情報を提供します。この中でKSULAWHR列はソースコードのどこから呼ばれたのかを意味するしKSULAWHY列は付加的な理由を表します。

過度なパースを修行するセッションについてV$LATCHHOLDERビューを通じて分析した場合とX$KSUPRLATテーブルを通じて分析した場合を比較しましょうか。まずV$LATCHHOLDERビューを利用した場合(prof_latch.sql)は次のようです。


UKJA@ukja1021> @prof_latch 200000 159 %

       SID LADDR    NAME                                 HITS
---------- -------- ------------------------------ ----------
       159 3253DE80 library cache                       12037
       159 3253E0F0 library cache lock                   1595
       159 3253DFB8 library cache pin                     883

平面的でしょう。 X$KSUPRLATテーブルを利用した場合(prof_latch2.sql)は次のようです。


UKJA@ukja1021> @prof_latch2 200000 159 %

 SID LADDR    CALLED            NAME              OBJECT               HITS
---- -------- ----------------- ----------------- ----------------- -------
 159 3253DFB8 kglpnal: child: a library cache pin                 2    1066
              lloc space

 159 3253DFB8 kglpndl           library cache pin                 2     402
 159 3253E0F0 kgllkal: child: m library cache loc                 2     862
              ultiinstance      k

 159 3253E0F0 kgllkdl: child: c library cache loc                 2     930
              leanup            k

 159 3253DE80 kglhdgc: child:   library cache                     2    1176

 SID LADDR    CALLED            NAME              OBJECT               HITS
---- -------- ----------------- ----------------- ----------------- -------
 159 3253DE80 kglhdgn: child:   library cache                     2     737
 159 3253DE80 kgllkdl: child: n library cache                     2    2523
              o lock handle

 159 3253DE80 kglpin: child: he library cache                     2    7206
              ap processing

 159 3253DE80 kglpndl: child: b library cache                     2    3293
              efore processing

V$LATCHHOLDERビューが隠した情報を利用するのによってずっと立体的な情報が得られます。

X$テーブルに対する情報を得る一番良い方法はV$ビューの定義をV$FIXED_VIEW_DEFINITIONビューを通じて確認してみることです。いくつかのビューを試してみたら以外の良い情報をたくさん得るようになります。より深いレベルに行こうとする方々はぜひ試してみてください。

オラクルが上手だいうこと

タレントコードという本を読んだら優れる専門家、または天才的なプレーヤーが誕生する原理があるほど分かります。

何かが本当に上手だと言うのは単純な幾個の原理を繰り返すしまた繰り返して本当に完璧に上手になるのを意味します。この繰り返しの程度は一般的に考える程度を超えた完全に一体になる程度の繰り返しを意味します。

オラクルでも同じです。いろいろな本を読むし、外国のサイトで知識を得るとまるで自分が大分上手になったという錯覚をするようになります。でもオラクルが上手だというのは幾個の核心的な原理と技法を練習また練習してそれをあまりによくできるようになったというのを意味します。

僕が属しているオラクル性能の世界を例にすればSQLをチューニングする幾つの核心的な原理たち、性能データを収集、分析してテストする核心的なツールたちと技法たちを練習また練習してまるでこれらが自分が生まれた時から分かっていたようになること、これがオラクルが上手だと言うことの意味です。

皆さんはそんな支度していますか。:）

Monday, October 5, 2009

サンプリングvs.イベント

次のような質問があります。

特定セッションが実行したすべてのSQLの履歴を追跡できる方法はないか

返事は簡単なのです。

SQL Trace(10046 Event)でできます。

でもこの簡単な質問と返事の中に性能データ収集に対する深奥な心理があります。

直接性能データを収集して分析してみたことがある方なら自分も分からず必ず次の二つの方法を持って悩みするようになります。これらはオラクルに構わずにデータを収集するのにおいていつも適用される普遍的な二つの方法です。

イベント(Event)方式: 10046イベントが代表的な方法です。すなわち、特定なイベントが発生する時それを取り込んで性能データを収集する方式であります。
サンプリング(Sampling)方式: MaxgaugeやASHが代表的な方法です。秒あたり、または分あたり幾度ずつデータを直接キャプチャする方式であります。

イベント方式はオラクルが内部的にあらかじめ定義しておいたイベントたちに対してだけ使用可能です。例えば10046(SQLの実行)、10053(Optimizationの実行)、10032(Sortの実行)などオラクルが決めておいた範囲内でだけ使用可能です。イベント方式のメリットはサンプリング方式と違って収集の間違いはほとんどないということです。すなわちすべてのデータが収集できます。

サンプリング方式はどんなデータも願う周期で収集できるというメリットがあります。オラクルでならDynamic Performance Viewが主対象となります。ただし、収集周期によって収集の間違いかとても大きくなれるというデメリットがあります。

次の質問をイベント方式ではなくてサンプリング方式で具現できるでしょうか。

特定セッションが実行したすべてのSQLの履歴を追跡できる方法はないか

1. 次のようにセッションA(SID=159)で10,000個のSQL文章を連続的に修行します。


declare
 v_cursor sys_refcursor;
 v_value  number;
begin
 for r in 1 .. 10000 loop
  open v_cursor for 'select /*+ case_' || r || ' */ 1 from dual';
  fetch v_cursor into v_value;
  close v_cursor;
 end loop;
end;
/

2. セッションBで4,000,000度のサンプリングをCPUを100％活用して修行しながらX$KSUSE(V$SESSIONビューのベーステーブル)テーブルからSQLを追跡します。(ヒントを利用してサンプリングが可能な実行計画を制御するのに注意してください)


select * from 
(
 select
  sql_id,
  (select substr(t.sql_text,1,25) from v$sqlarea t 
     where t.sql_id = a.sql_id) as sql_text,
  hitcnt
 from (
  select /*+ no_merge */
   a.sql_id,
   count(*) as hitcnt
  from (
   select /*+ ordered use_nl(x s) */
     s.ksusepsi as sql_id
   from
    (select /*+ no_merge */ level as r from dual connect by level <= 4000000) x,
    sys.xm$ksuse s
   where s.indx = 154
  ) a
  group by a.sql_id
 ) a
) where sql_text is not null
;

3. 次にその結果をあります。


...
7hbnjb7uhrs2n select /*+ case_8766 */ 1                                 144
6150v08vhwyzs select /*+ case_8767 */ 1                                 169
7pxf03wwjqzf3 select /*+ case_8773 */ 1                                 349
ggq5ruaj6b9zf select /*+ case_8781 */ 1                                 343
abg6fj0kntu55 select /*+ case_8782 */ 1                                 304
6akym930shhgn select /*+ case_8786 */ 1                                 163
044rsut971xtb select /*+ case_8792 */ 1                                1802
8mjjurfg59y40 select /*+ case_8798 */ 1                                 142
4q3vgvnv2chn9 select /*+ case_8822 */ 1                                 172
34ddvz7nc4mrv select /*+ case_8836 */ 1                                1385
58zhgmbwa3r6z select /*+ case_8860 */ 1                                1410
g3xjqmh2fmp18 select /*+ case_8919 */ 1                                 185
7fjm8yu32sfyv select /*+ case_8929 */ 1                                 233
6wkawk6brmx41 select /*+ case_8283 */ 1                                 197
4zp1k8mr17t70 select /*+ case_8288 */ 1                                1040
5pha5zp0catjt select /*+ case_8289 */ 1                                 303
dswcqaxvy7pr9 select /*+ case_8307 */ 1                                 227
39fjqykryfvn8 select /*+ case_8315 */ 1                                 184
3xk1sjqdd49td select /*+ case_8321 */ 1                                 123
...

理論的には一つのSQLが4,000,000/10,000 = 400番ぐらいサンプリングがしてなるのが理想的です。でも実際のサンプリング回数はすごく偏差が大きくて間違いも持っています。

こんな誤差はサンプリング方式では仕方ないのです。例えばASH(Active Session History)は1秒で1番ずつActive Sessionを収集します。1秒という単位はセッションを収集するのには充分かも知れませんが、SQLの履歴を収集するには大きすぎる値です。ASHのサンプリングの正確性は_ash_sampling_interval, _ash_size, _ash_sample_allなどのパラメータで調整できますが本質的な誤差は避けられません。

Exemで提供しているMaxgaugeというツールは普通1秒あたり10度～100度ぐらいActive SessionたちのSQL文章をサンプリング方式で収集します。このぐらいならすごく驚くべき数値みたいですが、実際のシステムで運営してみたら考えより大きい誤差が生じます。上の例でCPUを100％占有しながらもサンプリングをしても誤差が大きいという事実を考え合わせば充分に予想できる結果ですが…

自分だけの方法で性能データを収集して分析しようとする欲を持っている方々なら「サンプリングvs。イベント」というこの命題にいつも注意しなければなりません。

オラクルの家計簿書いていますか。

一般人がお金を惜しんでお金持ちになる近道は家計簿を書くことだと言います。

僕の場合は最近望まず車が二台になって車に入る費用がかなり多くなってしまいました。それで車計簿を書き始めました。車に入る費用を減らして見るためです。

核心はこれです。

測定しなければ改善できない。

No improvement without measurement.

幸いにオラクルはAWR(Automatic Workload Repository)だという名前で家計簿を自動的に書いています。僕たちが普通書いている家計簿よりもっと多様な項目を詳しく記録してくれます。このデータを十分に活用しなければシステム運営での改善を望みにくいでしょう。

もう一度言いますが、測定しなければ改善はできませんから。

Dion Cho - Oracle Performance Storyteller

Thursday, October 29, 2009

オラクル性能に対する短い考え＃8

Tuesday, October 27, 2009

オラクル性能に対する短い考え　＃7

Monday, October 26, 2009

なぜ診断イベントを理解すべきなのか。

Sunday, October 25, 2009

バージョン間パラメータの比較

Friday, October 23, 2009

オラクル性能に対する短い考え＃6

Wednesday, October 21, 2009

長い文字列(VARCHAR2)の使用

Tuesday, October 20, 2009

オラクル性能に対する短い考え　＃５

Monday, October 19, 2009

オラクルが知らせてくれないオラクルの小さなチップ＃1

オラクル性能に対する短い考え　＃４

Thursday, October 15, 2009

オラクル性能に対する短い考え＃3

Wednesday, October 14, 2009

Row Cache LockのホルダーをV$ビューで探すこと

Tuesday, October 13, 2009

オラクル性能に対する短い考え　＃2

Monday, October 12, 2009

DBMS_ADVANCED_REWRITEとDML

Saturday, October 10, 2009

オラクル性能に対する短い考え　＃1

Wednesday, October 7, 2009

どうしてV$ビューよりX$テーブルのほうが好きなのか。

オラクルが上手だいうこと

Monday, October 5, 2009

サンプリングvs.イベント

オラクルの家計簿書いていますか。

紹介

ティパック

Blog Archive

好きなブログ

私の本

訪問者

Thursday, October 29, 2009

Tuesday, October 27, 2009

Monday, October 26, 2009

Sunday, October 25, 2009

Friday, October 23, 2009

Wednesday, October 21, 2009

Tuesday, October 20, 2009

Monday, October 19, 2009

Thursday, October 15, 2009

Wednesday, October 14, 2009

Tuesday, October 13, 2009

Monday, October 12, 2009

Saturday, October 10, 2009

Wednesday, October 7, 2009

Monday, October 5, 2009

紹介

ティパック

Blog Archive

好きなブログ

私の本

訪問者

RSS 登録