Part 1. パフォーマンス・チューニング入門
Part 2. パフォーマンス・チューニング入門:過去を診断
Part 3. パフォーマンス・チューニング入門:アクティブ・セッション履歴

掲載元
Oracle Magazine
2012年7月/8月

テクノロジー: チューニング

  

パフォーマンス・チューニング入門

Arup Nanda Oracle ACE Director

 

Oracle Databaseのセッションのパフォーマンス問題を解決

サイドバーを参照してください。

 

セットアップ

この記事のテスト・ケースをセットアップするには、この"セットアップ"の項にあるSQLを実行します。 このSQLは、SYSユーザーへのアクセス権があること、ARUPというユーザーを作成できること(つまり、同じ名前のユーザーがいないこと)、空き領域が64KB以上のUSERSという表領域があることを前提としています。

 

SYSで接続し、次のSQLを実行します。

 

connect sys/<password> as sysdba

create user arup identified by arup
default tablespace users
/

alter user arup quota unlimited on users
/ 

-- now connect as arup
connect arup/arup

create table t1
(
	col1 number,
	col2 varchar2(1)
)
/
insert into t1 values
(1,’z’) 

/
commit
/

セッションの状態

データベースの動作が遅い原因に関するトラブルシューティングを始める前に、まずデータベース自体は遅くも速くもなく、安定した速度を保っていることを理解する必要があります。 一方で、データベースに接続しているセッションの動作は、セッション内での一時的な問題の発生時に遅くなります。 セッションのパフォーマンス問題を解決するためには、そのような一時的な問題を特定して解消する必要があります。 幸いにも、一時的な問題の特定と解消は、ほとんどの場合に非常に簡単な作業です。

セッションのパフォーマンス問題を解決するための最初のステップは、そのデータベース・セッションの現在の動作状況を突き止めることです。 Oracle Databaseセッションの状態は常に、以下の3つのいずれかに該当します。

  1. アイドル:何も実行しておらず、作業が割り当てられるのを待っています。
  2. 処理中:何らかの有益な処理を行っており、CPUを消費しています。
  3. 待機中:ディスクからのブロックの受信やロックの解放など、何らかのイベントの発生を待っています。

作業が割り当てられるのを待っている(アイドル状態の)セッションでは、動作が遅くなることはありません。単純に実行することが何もないだけです。 ブロックやロックなどのリソース待ちの状態にあるセッションの場合は、処理を停止しています。 セッションは、そのリソースを取得するまで待ち続けます。 そのリソースを取得したときには、何らかの処理を実行し、次の必要なリソースに移って、そのリソースが利用可能になるまで待機し、また処理を開始します。そのセッションで他に行うべきことがなくなるまで、このサイクルが継続します。 リソース待ちの頻度が高い場合、セッションの動作は遅く感じられますが、 実際に遅いわけではありません。単純に、実行、停止、再実行、再停止と続くパターンに従っているだけです。 そのため、ここでの目標は、セッション内で"停止"している問題を特定して解消することになります。

セッション停止の原因に関する情報を取得する作業は、どれほどの難易度でしょうか。 実際のところ、この作業は非常に簡単です。 Oracle Databaseは、データベースのセッションの動作状況について表示する機能を搭載しています。 必要な作業は、注意深く調査し、その情報を正しい場所で探すことです。V$SESSIONというビューがその正しい場所となります。 分析に必要となる情報は、すべてこのビューから取得できます。

V$SESSIONビューの使用法を説明するために、ごく一般的なシナリオである行ロックを例として取り上げます。 この記事に従って作業を進めるには、まず前述の表のセットアップを行います(この記事のオンライン版で説明)。 次に、異なる2つのセッションから、ARUPユーザーで接続します。 1つ目のセッションでは、次のSQL文を発行します。

 

update t1
set col2 = 'x' where col1 = 1;

 

出力結果は"1 row updated"です。これは、指定した行が更新されたことを示します。 この文の後にCOMMITを発行しないでください。 コミットしないことで、このセッションにT1表の1行目のロックを取得して保持させます。 次に、2つ目のセッションで次のSQL文を発行します。

 


update t1
set col2 = 'y' 
where col1 = 1;

 

この文は、応答がない状態になります。 その理由は 簡単です。 1つ目のセッションが行のロックを保持しているために、2つ目のセッションの応答がないのです。その結果、ユーザーは、セッションの動作が遅いと不満を言うことになります。 2つ目のセッションの動作状況を把握するために最初に確認すべき情報は、V$SESSIONのSTATE列です。

 

select sid, state
from v$session
where username = 'ARUP'; 

SID   STATE
————  ——————————————————
3346  WAITING
2832  WAITED KNOWN TIME

 

出力結果をよく見てみましょう。 セッション3346(SID列)は待機中となっています。つまり、現在動作していません。 これが、そのセッションでパフォーマンス上の一時的な問題が発生していることを示す最初の手がかりとなります。 しかし、セッションが何を待っているのかを明らかにする前に、出力結果のセッション2832の状態を確認しましょう。セッション2832は、以前に一定期間待機していたことが分かります。 重要な点は、セッション2832が現在待機中ではないことです。つまり、生産的に動作しています。

次に、2つ目のセッション(3346)が何を待っているかについて確認します。 その情報は、同じV$SESSIONビューのEVENT列で簡単に取得できます。 EVENT列には、現在セッションが待っているイベントだけではなく、セッションが以前に待っていたイベントについても示されます。 リスト1のV$SESSIONに対する問合せによって、両方のセッションに関するEVENT列の情報が表示されます。

コード・リスト1:セッションの情報、セッションの状態、イベントを表示するための問合せ

 

select sid, state, event
from v$session
where username = 'ARUP'; 

SID   STATE              EVENT

————— —————————————————  ————————————————————————————
2832  WAITED KNOWN TIME  SQL*Net message from client
3346  WAITING            enq: TX - row lock contention

 

リスト1の出力結果から、セッション3346が現在イベント待ちの状態であることが分かります。そのイベントの"enq: TX – row lock contention"は、"enqueue for transaction-level lock on row"の省略形であり、簡単に言えば行レベル・ロックを指しています。 セッションが待機中である原因は、ロックしようとした行(複数行の場合もあります)に対して別のセッションがすでにロックを保持していることにあります。 その別のセッションでトランザクションのコミットまたはロールバックが実行されない限り、セッション3346は必要なロックを取得できず、待つしかありません。 一方、セッション2832の状態は"WAITED KNOWN TIME"です。これは、現時点で待機中ではなく動作中であることを表します。 ただし、セッション2832は以前に"SQL*Net message from client"というイベントを待っていました(このイベントについては後ほど取り上げます)。 これらの結果から非常に重要な点を学ぶことができます。 それは、EVENT列を確認するだけでは、セッションが何を待っているかを理解できないということです。 まず、STATE列でセッションが待機中か動作中かを確認して、その後EVENT列を調査する必要があります。

セッションが待機中であると分かった後、次に確認すべきことは、それまでのセッションの待機時間です。 非常に長い間待機中の場合は、何らかのボトルネックが発生しています。 では、待機時間に関する情報はどこで取得できるのでしょうか。 その答えは、V$SESSIONビューのSECONDS_IN_WAIT列です。

セッションの待機時間の取得は、現時点で待機中のセッションに対しては意味がありますが、現在動作中のセッションについてはどうでしょうか。 前述のとおり、EVENT列には、セッションで現在発生しているイベントに加えて、最後に発生した待機イベントについても示されます。 さらに、同じV$SESSIONビューにある別の列のWAIT_TIMEに、その待機時間が示されます (WAIT_TIMEの単位は100分の1秒です)。

これまでに、待機中および動作中のセッションに関する情報の取得方法が分かりました。次に、これらすべての情報を1つの問合せ内にまとめて表示します(リスト2を参照)。 この問合せによって、セッションの状態が明らかになります。その状態とは、セッションが動作中か待機中か、動作中の場合は以前に何をどれほどの時間待っていたのか、待機中の場合は現在何をどれほどの時間待っているのか、です。

コード・リスト2:セッションの情報、セッションの状態、待機状態の詳細を表示するための問合せ

 

col "Description" format a50
select sid,
        decode(state, 'WAITING','Waiting',
                'Working') state,
        decode(state,
                'WAITING',
                'So far '||seconds_in_wait,
                'Last waited '||
                wait_time/100)||
        ' secs for '||event
        "Description"
from v$session
where username = 'ARUP';

 

出力結果:

 

SID   STATE       Description
————— ——————————  ———————————————————————————————————————————————————————
2832  Working     Last waited 2029 secs for SQL*Net message from client
3346  Waiting     So far 743 secs for enq: TX - row lock contention
4208  Waiting     So far 5498 secs for SQL*Net message from client

 

アイドル・イベント

ここで、リスト2のセッション4208の詳細を確認すると、セッションは現在待機中であり、その時間は5,498秒で、対象は"SQL*Net message from client"イベントです。 前項で説明しましたが、Oracle Databaseセッションの状態は、動作中、リソース待ち、作業待ちのいずれかに該当します。 では、セッションがアイドル状態かどうかを判断するにはどうすれば良いでしょうか。 セッション4208は、クライアントからSQL*Net経由で作業が割り当てられることを期待しています。しかし、クライアントからの作業割当ての有無をセッションが事前に把握する手段はなく、 SQL*Net経由で何らかの命令が来るのを待つことしかできません。 それまでにセッションが実行できることは、SQL*Netインタフェースの状況を積極的に監視することだけです。そして、この状況を表している箇所が、V$SESSIONビューのEVENT列の"SQL*Net message from client"であり、これは実質的に単なるアイドル状態であることを表しています。

同じくEVENT列の値である"rdbms ipc message"は無視してかまいません。この値は、アイドル状態にあるセッション向けのイベントの状態だからです。 アイドル状態のセッションでも、STATE列の値にIDLEと示されることはなく、"Waiting"と示されることに注意してください。 セッションが本当にアイドル状態であるかを判断するには、EVENT列を確認する必要があります。

リスト2の問合せを修正し、"SQL*Net message from client"および"rdbms ipc message"のアイドル・イベントを含むセッションをフィルタリングしたいと思うかもしれません。 このフィルタリング自体は可能ですが、さまざまな理由によりお勧めしません。 まず、"SQL*Net message from client"イベントのすべてのインスタンスが、セッションがアイドル状態であることを示すわけではありません。 たとえば、ネットワークの動作が本当に遅くなっている可能性もあります。その場合、セッションではネットワーク関連のイベント待ちも発生します。 注意すべき点として、クライアントが本当にアイドル状態なのか、動作の遅い命令を送信中なのか、ネットワークで遅延が発生しているのかをセッションで判別することはできません。 セッションが実行できることは待つことだけで、その待機に"SQL*Net message from client"イベントが使用されるのです。 2つ目に、アイドル・イベントには、Oracle Supportにとって役立つような、セッション内部の他の状況に関する手がかりが示されることがあります。 そのため、これらの"アイドル"状態を示すEVENTの値を表示することをお勧めします。

ロックの診断

リスト2には、記載された3つのセッションのパフォーマンスについて診断を行うための十分な情報が出力されています。 セッション4208はアイドル状態であるため、セッション4208の動作が遅いという苦情があっても、それはデータベース関連の遅延ではありません。 このセッションに関連するパフォーマンス問題は、無限ループに陥るようなコード内のバグや、アプリケーション・サーバー上の高いCPU使用率に関連するものです。 そのため、パフォーマンスのトラブルシューティングの照準をアプリケーション・クライアントに合わせることができます。

一方、セッション3346の状況は異なります。 このセッションはまさに、アプリケーションにとってのボトルネックとなっています。 これまでに、行ロックを待っていることが、このセッションの動作が遅いと感じる原因であると分かりました。次に、論理的に考えれば、どのセッションがそのロックを保持しているのかという疑問がわきます。 お分かりかと思いますが、その答えはやはりV$SESSIONビューにあります。具体的に言えば、BLOCKING_SESSION列です (Oracle Real Application Clusters(Oracle RAC)環境では、ブロック中のセッションが別のインスタンスに属している場合があります。 その場合、ブロック中のインスタンスは、V$SESSIONビューのBLOCKING_INSTANCE列に表示されます)。

ブロック中のセッションおよびインスタンスは、次のSQL文を発行することで調査できます。

 

select 
  blocking_session B_SID,
  blocking_instance B_Inst
from v$session
where sid = 3346;

B_SID   B_INST

——————  ———————
 2832      1

 

出力結果から、SID 3346が待っているロックをSID 2832が保持していることが、はっきりと分かります。 これで、行の更新がブロックされているセッションと、その行に対するロックを保持しているセッションとの因果関係を辿れるようになりました。

ロックされている特定の行を確認するために、まずその行を含む表を把握します。 その表を把握するためにも、同じV$SESSIONビューを使用します。この例の場合、ROW_WAIT_OBJ#列にその情報があり、この列には、現在行がロックされている表のオブジェクト番号が示されます。 次に、このオブジェクト番号を使用して、DBA_OBJECTSビューからその表の名前を取得できます(リスト3を参照)。

コード・リスト3:行ロック情報の取得

 

select row_wait_obj#,
       row_wait_file#,
       row_wait_block#,
       row_wait_row#
from v$session 
where sid = 3346;

ROW_WAIT_OBJ#  ROW_WAIT_FILE#  ROW_WAIT_BLOCK#  ROW_WAIT_ROW#
—————————————  ——————————————  ———————————————— ——————————————
241876         1024            2307623          0

 

オブジェクトの情報を取得するには、次のSQLを発行します。

 

select owner, object_type, object_name, data_object_id
from dba_objects
where object_id = 241876;

OWNER  OBJECT_TYPE  OBJECT_NAME   DATA_OBJECT_ID
—————  ———————————— ————————————  ——————————————
ARUP   TABLE        T1                    241877

 

出力結果から、T1表のいずれかの行が行ロックの競合ポイントになっていることが分かります。 では、どの行がロックされているのでしょうか。 そのデータは、V$SESSIONビューの3つの列(ROW_WAIT_FILE#、ROW_WAIT_BLOCK#、ROW_WAIT_ROW#)で取得できます。その特定の行について、ROW_WAIT_FILE#は関連ファイルIDを、ROW_WAIT_BLOCK#はそのファイル内のブロックIDを示し、ROW_WAIT_ROW#はそのブロック内部の行のスロット番号を示します。 これらの情報を使用して、行のROWIDを特定できます。 ROWIDとは、Oracle Databaseインスタンス内部のすべての行に割り当てられた物理アドレスのことであり、行を一意に特定するために使用できます。

リスト4は、これまでに収集した情報を使用して、表からブロック中の特定の行を選択するためのSQLスクリプトです。 このスクリプトをrowinfo.sqlというファイル名で保存します。 このスクリプトは、次の入力値をこの順で受け取ります。 入力値は、所有者、表名、オブジェクト番号、ファイル番号、ブロック番号、行番号です。 このスクリプトを呼び出して、入力を求められたすべてのパラメータを渡します。リスト3の対応する出力結果をコピーして貼り付けることで、これらのパラメータを指定できます。

コード・リスト4:行情報の特定

 

REM Filename: rowinfo.sql
REM This shows the row from the table when the
REM components of ROWID are passed. Pass the
REM following in this exact order
REM  1. owner 
REM  2. table name 
REM  3. data_object_id
REM  4. relative file ID 
REM  5. block ID
REM  6. row Number 
REM
select *
from &1..&2

where rowid =
        dbms_rowid.rowid_create (
                rowid_type      =>  1, 
                object_number   => &3,
                relative_fno    => &4,
                block_number    => &5,
                row_number      => &6

        )
/

SQL> @rowinfo ARUP T1 241877 1024 2307623 0

COL1  C

————— —
  1   x

 

リスト4の出力結果から、ロックを要求中だが別のセッションによってロックされている特定の行が分かります。 これまでに、ロックしているセッションに加えて、ロック中の特定の行についても確認できました。

ところで、ロックを保持しているセッション(SID 2832)が何らかの理由でクライアントから切断される可能性はあるでしょうか。 このような状況は接続プール内で発生する可能性があります。あるいは、Oracle SQL Developerなどのシック・クライアント・ツールを使用してユーザーがデータベースにアクセスしている場合にも発生する可能性があります。 ロックを保持しているセッションを特定した後は、そのセッションでトランザクションのコミットかロールバックが実行されるまで待つこともできます。 このどちらのアクションでもロックが解放されます。

接続に障害が発生した場合は、セッションの停止という方法も選択できます。セッションを停止するとロールバックが強制的に実行され、ブロック中のセッションで保持されているロックが解放されて、待機中のセッションが処理を再開できます。 また、問題が非常に単純である場合もあります。 たとえば、シック・クライアント・ツールからUPDATE文を発行したユーザーがコミットをし忘れたために、すべてのセッションがその更新対象の行を待っているような状況です。 そのようなブロック中のセッションを特定することで、そのユーザーに対して状況をただちに修正するように優しく注意できます。

セッションに関するその他の情報

多くのトラブルシューティングにおいて、各セッションのSIDを把握するだけでは十分ではありません。 場合によっては、他の詳細情報も把握する必要があります。セッションの接続元のクライアント・マシン名、ユーザー名(データベースのユーザーとオペレーティング・システムのユーザーの両方)、サービス名などが該当します。 これらの情報はすべて、これまでと同じV$SESSIONビューで簡単に取得できます。 リスト5に示すスクリプトを実行して、これらの情報を表示する各列について簡単に確認しましょう。

コード・リスト5:特定のユーザーによるセッション

 

select SID, osuser, machine, terminal, service_name, 
       logon_time, last_call_et
from v$session
where username = 'ARUP'; 

SID   OSUSER  MACHINE   TERMINAL  SERVICE_NAME  LOGON_TIME LAST_CALL_ET
————— ——————  ———————   ————————  ————————————  —————————— ————————————

3346  oradb   prodb1    pts/5     SYS$USERS     05-FEB-12          6848
2832  oradb   prodb1    pts/6     SERV1         05-FEB-12          7616
4408  ANANDA  ANLAP     ANLAP     ADHOC         05-FEB-12             0

 

OSUSER:接続中のクライアントのオペレーティング・システム・ユーザー名。 出力結果から、セッション4408がANLAPマシンから接続されており、このマシンにはWindowsユーザーのANANDAがログインしていることが分かります。

MACHINE:クライアントを実行しているマシン名。 データベース・サーバー自体を指す場合もあります。 2つのセッションで、マシン名が"prodb1"と示されています。 セッション4408は、ANLAPという別のマシン(おそらくはラップトップ)で実行されています。

TERMINAL:セッションがUNIXサーバーから接続されている場合の、実行中のターミナル。

LOGON_TIME:セッションが最初にOracle Databaseインスタンスに接続された日付。

リスト5に示された列を使用して、ユーザーのセッションに関する詳細情報を得ることができます。

ここで、appsvr1というアプリケーション・サーバーで実行中のアプリケーションでパフォーマンス問題が発生しているという苦情が来たとします。 リスト6は、そのマシンから接続しているセッションの情報を取得するための問合せ(V$SESSIONビューに対して実行)と出力結果です。問合せには、この記事のこれまでの問合せで使用した列が含まれています。

コード・リスト6:特定のマシンのセッション待機状況

 


col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
        decode(state, 'WAITING', 'Waiting',
                'Working') state,
last_call_et, seconds_in_wait, event
from v$session
where machine = 'appsvr1'
/
                                       Called      Waiting
SID   USERNAME  PROGRAM       STATE    secs ago    for secs   EVENT 
————— ———————   ———————————   ———————  —————————   ————————   ——————————————————
2832  ARUP      sqlplus.exe   Waiting       152         151   SQL*Net message 
                                                              from client
3089  ARUP      sqlplus.exe   Waiting       146         146   enq: TX - row lock 
                                                              contention
3346  ARUP      sqlplus.exe   Working        18          49   SQL*Net message 
                                                              from client

 

出力結果から、appsvr1アプリケーション・サーバーより3つのセッションに接続していることが簡単に分かります。 すべてのセッションがSQL*Plusを実行しています(PROGRAM列を参照)。 SID 3346は現在動作中の唯一のセッションです(STATE列の"Working"より)。 動作中であるため、EVENT列にはセッションが最後に待機したときの情報が示されています。 この場合、セッションは現在待機中ではなく動作中であるため、待機時間に重要な意味はありません。 "Called secs ago"列(V$SESSIONの"last_call_et"を表す)には18と表示されています。この数値は、このセッションが18秒前にSQLコールを行ったことを表します。

他の2つのセッションは待機中です。 SID 3089は行ロック待ちの状態です。 出力結果から、このセッションが146秒間待機中であること、および146秒前に最後のSQLコールを行っていることが分かります。 つまり、そのSQLコールを行ってからずっと、セッションがその特定のロックを待っていることになります。

最後に、セッション2832も同様に待機中ですが、この場合の待機には"SQL*Net message from client"イベントが使用されています。つまり、アイドル状態であり、作業が割り当てられるのを待っています。 このセッションは152秒前に最後のSQL文を発行しており、151秒間アイドル状態を保っています。

これらの情報を用意すれば、パフォーマンス問題を正確に診断できます。 苦情を言ってきたユーザーには、次のように伝えることができます。appsvr1アプリケーション・サーバーから3つのセッションに接続しており、そのうちの1つはアイドル状態、1つは動作中、もう1つはロック待ちの状態です。 そのユーザーはおそらく、そのロック待ちのセッションの動作が遅いことについて問い合わせています。 この時点で、DBAはその原因と修正方法を把握しています。

SQLの取得

パフォーマンス・チューニングに関する他の重要な情報に、現在セッションで実行中のSQL文に関する情報があります。このSQL文より、セッションの動作について詳しく考察できます。 これまでと同じV$SESSIONビューに、SQL文の情報も示されます。 V$SESSIONビューのSQL_ID列は、最後に実行されたSQL文のIDです。 SQL_ID値を使用して、そのSQL文のテキストをV$SQLビューから取得できます。 以下に、ユーザーが遅いと感じるセッションによって実行されたSQL文を特定する方法の一例を示します。

 

select sql_id
from v$session
where sid = 3089;

SQL_ID
—————————————————
g0uubmuvk4uax

set long 99999
select sql_fulltext
from v$sql
where sql_id = 'g0uubmuvk4uax';
SQL_FULLTEXT
————————————————————————————————————————
update t1 set col2 = 'y' where col1 = 1

 

データ・アクセスの問題

この記事では、遅延の原因として行レベル・ロックを取り上げてきました。 ロック関連の競合は非常によく起きる原因の1つですが、パフォーマンス問題の原因はこれだけではありません。 競合のおもな原因として、ディスクI/Oも挙げられます。 セッションでディスク上のデータベース・データファイルからデータを取得しバッファ・キャッシュに配置する際に、ディスクがデータを送信するまで待機する必要があります。 この待機については、以下のように、そのセッションのEVENT列に、"db file sequential read"(索引スキャンの場合)または"db file scattered read"(全表スキャンの場合)として表示されます。

 

select event
from v$session
where sid = 3011;

EVENT
—————————————————————————
db file sequential read

 

このイベントが表示された場合は、ディスクからのI/Oが終了するまでセッションが待機中の状態です。 セッションの動作速度を上げるには、その待機時間を短縮する必要があります。 待機時間の短縮については、次のようないくつかの方法があります。

  1. SQL文によって取得されるブロック数を削減します。 SQL文を調査し、索引を使用すべきときに全表スキャンを実行していないか、誤った索引を使用していないか、取得するデータ量を削減するように書き直せないかを検討します。
  2. SQL文の中で使用されている表を、より高速なディスク領域に配置します。
  3. バッファ・キャッシュを増やすことで、拡張後のサイズで追加のブロックを格納し、I/Oの削減や待機時間の短縮を図れないかを検討します。
  4. データをより高速に取得できるようにI/Oサブシステムのチューニングを行います。

次のステップ


パフォーマンス・チューニングの詳細を参照してください。
 Oracle Database 2日でパフォーマンス・チューニング・ガイド11g Release 2 (11.2)
 Oracle Databaseパフォーマンス・チューニング・ガイド11g Release 2 (11.2)

その他のオプションもありますが、これまでに説明した修正方法が一般的です。 実際に実施するアクティビティはそれぞれの状況によって異なりますが、1つ目の方法(SQL文によって取得されるブロック数の削減)は、ほぼどのような状況でも機能します。

ブロック数を削減するためのチューニングを検討する際には、SQL文でデータの選択を行っている表を確認できます。 しかし、SQL文で複数の表を使用している場合はどうすれば良いでしょうか。 どうすれば待機の原因となっている表を特定できるでしょうか。

待機の原因となっている表を特定するには、やはりV$SESSIONビューを使用します。 ビューのP1列とP2列に、セッションが待っているセグメントに関する情報が表示されます。 リスト7は、P1およびP2の問合せと、その出力結果です。

コード・リスト7:データ・アクセスの待機状態の確認

 

select SID, state, event, p1, p2
from v$session
where username = 'ARUP'; 

SID  STATE     EVENT                   P1 P2
———— ———————   ——————————————————————— —— ————

2201 WAITING   db file sequential read  5 3011

 

P1列はファイルID、P2列はブロックIDです。 リスト7の出力結果にあるこれらの情報を使用して、DBA_EXTENTS内のエクステント情報からセグメント名を取得できます(以下を参照)。

 

select owner, segment_name
from dba_extents
where file_id = 5
and 3011 between block_id 
and block_id + blocks;

OWNER  SEGMENT_NAME
—————— —————————————
ARUP   T1

 

出力結果より、ARUPが所有しているT1表が、セッション内でディスクによって選択されていることが分かります。 この表に注目してチューニングする必要があります。 この表を高速ディスクに移動してI/Oを高速化できます。または、この表内のI/Oの高速化に重点を置いて、新しい索引の作成、マテリアライズド・ビューの作成、結果のキャッシュの増強など、この表に影響する変更を行うこともできます。

まとめ

この記事では、成功するセッションのパフォーマンス・チューニングを始めるための以下の手順について説明しました。

  1. セッションが動作中か待機中かを確認します。 待機中の場合は、何をどの程度の時間待っているのかを明らかにします。
  2. セッションの待機時間と、SQLコールを発行してから現在までの時間を比較します。
  3. 待機状態の原因がロックの競合である場合は、ロックを保持しているセッションを特定して、そのセッションの詳細情報を取得します (ロックを保持しているセッションが孤立したセッションの場合は、そのセッションを停止してロックを解放できます)。
  4. セッションで実行中のSQL文を特定します。
  5. セッションがI/O待ちの状態の場合は、そのI/Oがどのセグメント(表、マテリアライズド・ビュー、索引など)を待っているのかを特定します。

この記事で紹介した方法により、DBAとして経験するパフォーマンス問題の約20%を解決できます。 Oracle Databaseは、内部の動作状況に関する情報を表示する機能を搭載しており、DBAは問題の本当の原因に照準を合わせることができます。DBAが行うべきことは調査だけです。

よく発生するけれども一見すると厄介なOracle Databaseのパフォーマンス問題は、適切な情報源を特定することで簡単に診断できます。この記事が、そのような診断を実現するためのお役に立てれば幸いです。 それでは、楽しいチューニングを。

 

Part 1. パフォーマンス・チューニング入門
Part 2. パフォーマンス・チューニング入門:過去を診断
Part 3. パフォーマンス・チューニング入門:アクティブ・セッション履歴


Arup Nandaの写真

Arup Nanda
arup@proligence.com)は、Oracle DBAとして16年以上の経験を持ち、パフォーマンス・チューニングからセキュリティや障害時リカバリまで、データベース管理のさまざまな分野に携わってきました。 2003年のOracle MagazineでDBA of the Yearに選ばれました。

ご意見ご感想をお寄せください。

 

▲ ページTOPに戻る

記事一覧へ戻る