Oracleパフォーマンス監視に役立つテクニック集

Last updated at 2024-01-15Posted at 2024-01-12

概要

Oracleパフォーマンス監視実務で使用されたSQLをまとめました。Oracle19cとOracle12cでの利用実績がありますが、他のデータベースまたバージョンでの検証は行っていません。
随時追加予定です。

現在のセッション情報とそれに関連するSQL文情報の抽出

SELECT
    a.username,
    a.sid,
    b.sql_text,
    b.sql_fulltext
FROM
    v$session a,
    v$sqlarea b
WHERE
    a.sql_address = b.address;

このSQL文を実行すると、セッションごとにユーザ名、セッションID、関連するSQL文の部分的なテキストと完全なテキストが表示されるでしょう。これは、特定のセッションが実行しているSQL文を理解し、パフォーマンスの監視やトラブルシューティングに役立ちます。

実行中のSQL文に関するセッションとプロセス情報の抽出

SELECT
    b.sid AS oracleid,
    b.username,
    b.serial#,
    spid,
    paddr,
    sql_text,
    b.machine
FROM
    v$process a,
    v$session b,
    v$sqlarea c
WHERE
    a.addr = b.paddr
    AND b.sql_hash_value = c.hash_value;

過去に実行されたSQL文の調査

SELECT
    b.sql_text,
    b.first_load_time,
    b.sql_fulltext
FROM
    v$sqlarea b
WHERE
-- ↓ここの時間を変えます。
    b.first_load_time BETWEEN '2024/01/01 18:00:47' AND '2024/01/07 18:00:47'
ORDER BY
    b.first_load_time;

ディスクアクセスが多いSQL文の抽出

SELECT
    *
FROM
    (
        SELECT
            parsing_user_id,
            executions,
            sorts,
            command_type,
            disk_reads,
            sql_text
        FROM
            v$sqlarea
        ORDER BY
            disk_reads DESC
    )
WHERE
-- ↓ここの数字を変えます。
    ROWNUM < 10;

ディスクアクセスが増加すると、パフォーマンスが低下します。

現在のセッションに対しI/O使用状況の調査

SELECT
    se.sid,
    se.serial#,
    pr.spid,
    se.username,
    se.status,
    se.terminal,
    se.program,
    se.module,
    se.sql_address,
    st.event,
    st.p1text,
    si.physical_reads,
    si.block_changes
FROM
    v$session se,
    v$session_wait st,
    v$sess_io si,
    v$process pr
WHERE
    st.sid = se.sid
    AND st.sid = si.sid
    AND se.paddr = pr.addr
    AND se.sid > 6
    AND st.wait_time = 0
    AND st.event NOT LIKE '%SQL%'
ORDER BY
    physical_reads DESC;

現在のセッション数の抽出

select count(*) from v$session;

イベント待機情報の抽出

SELECT
    event,
    SUM(DECODE(wait_time,0,0,1) ) "Prev",
    SUM(DECODE(wait_time,0,1,0) ) "Curr",
    COUNT(*) "Tot"
FROM
    v$session_wait
GROUP BY
    event
ORDER BY
    4;

待機イベントは、処理を継続する前にイベントが完了するまで待機する必要があることを示すために、サーバー・プロセスまたはスレッドによって増やされる統計です。待機イベント・データにより、ラッチ、バッファおよびI/Oの競合など、パフォーマンスへ悪影響を与える可能性のある症状が明らかになります。

ロールバッファ統計情報の抽出

SELECT
    name,
    waits,
    gets,
    waits / gets "Ratio"
FROM
    v$rollstat a,
    v$rollname b
WHERE
    a.usn = b.usn;

ロールバッファ統計の分析

待機回数 (waits):
- 待機回数が増加している場合、トランザクションがロールバッファを待っている可能性があります。これは、データベースが同時に多くのトランザクションを処理しようとしているか、長時間実行されているトランザクションが他のトランザクションを待っていることを示唆しています。
取得回数 (gets):
- 取得回数が高い場合、ロールバッファの頻繁な使用を示しています。これは、データベースが頻繁にロールバッファのセグメントを変更していることを示し、トランザクションの一貫性を確保している可能性があります。
待機回数と取得回数の比率 ("Ratio"):
- "Ratio" カラムは、待機回数と取得回数の比率を示しています。この比率が高い場合、ロールバッファが頻繁に待機していることを示し、トランザクションの実行が遅れている可能性があります。
ロールバッファの名前 (name):
- 各ロールバッファの名前を確認することで、どのロールバッファが統計情報に影響しているかを理解できます。特定のロールバッファが問題を引き起こしている場合、それに対処する必要があります。
他の関連情報との結びつき:
- ロールバッファの統計情報を他のデータベースの統計情報や待機イベントと組み合わせて分析することで、トランザクションのパフォーマンスに関する全体的な洞察を得ることができます。

以上のポイントに注意して、ロールバッファの統計情報を適切に解釈し、パフォーマンスの問題を特定して解決することが重要です。

表領域I/O統計情報の抽出

SELECT
    df.tablespace_name name,
    df.file_name "file",
    f.phyrds pyr,
    f.phyblkrd pbr,
    f.phywrts pyw,
    f.phyblkwrt pbw
FROM
    v$filestat f,
    dba_data_files df
WHERE
    f.file# = df.file_id
ORDER BY
    df.tablespace_name;

表領域I/O統計結果の分析

表領域（Tablespace）I/O統計結果を分析する際には、主に以下のポイントに注意を払います。これによりデータベースのI/Oパフォーマンスに関する洞察を得ることができます。

読み取りと書き込みの統計情報:
- 表領域I/O統計は通常、読み取りと書き込みの統計情報を含んでいます。これらの情報を確認し、どちらが主要な影響を与えているかを理解します。
I/Oの合計回数:
- 表領域における総読み取り回数と書き込み回数を確認します。回数が増加している場合、表領域へのI/Oが頻繁に発生していることを示し、これがパフォーマンスの低下の原因である可能性があります。
I/Oの合計サイズ:
- 読み取りおよび書き込みの合計サイズを確認します。大きなサイズのI/Oが行われている場合、データベースが大量のデータを処理している可能性があります。これが適切に最適化されているかどうかを考慮します。
平均I/Oサイズ:
- 平均I/Oサイズは、一回のI/O操作で処理される平均データサイズを示します。大きな平均I/Oサイズは、データベースがより効率的にデータを処理していることを示す可能性があります。
I/O待機時間:
- I/O待機時間を確認し、どの程度の時間がI/Oに費やされているかを評価します。待機時間が長い場合、データベースがI/O処理に対して効率的でない可能性があります。
表領域の配置:
- 表領域が物理的にどのデバイスやディスクに配置されているかを確認します。I/Oのボトルネックが特定のデバイスにある場合、ストレージの最適化や再配置を検討します。

これらの情報を総合的に考慮し、表領域I/Oのパフォーマンスに対する影響を理解します。必要に応じて、適切なチューニングや最適化手法を実施してデータベースのパフォーマンスを向上させることが重要です。

ファイルのI/O統計情報の抽出

SELECT
    substr(a.file#,1,2) "#",
    substr(a.name,1,30) "Name",
    a.status,
    a.bytes,
    b.phyrds,
    b.phywrts
FROM
    v$datafile a,
    v$filestat b
WHERE
    a.file# = b.file#;

OracleデータベースのファイルI/O統計情報の分析

OracleデータベースのファイルI/O統計情報を分析する際には、以下のポイントに注意を払います。これにより、データベースのディスクI/Oパフォーマンスに関する理解を深めることができます。

読み取りと書き込みの統計情報:
- ファイルI/O統計は通常、読み取りと書き込みの統計情報を含んでいます。これらの情報を確認し、データベースのI/Oパフォーマンスにどれくらいの影響を与えているかを理解します。
I/Oの合計回数と合計サイズ:
- ファイルにおける総読み取り回数、書き込み回数、合計読み取りサイズ、合計書き込みサイズを確認します。これにより、データベースがどれくらいのディスクI/Oを実行しているかと、そのサイズの大きさがわかります。
平均I/Oサイズ:
- 平均I/Oサイズは、一回のI/O操作で処理される平均データサイズを示します。大きな平均I/Oサイズは、ディスクがより効率的にデータを処理していることを示す可能性があります。
I/O待機時間:
- I/O待機時間を確認し、どの程度の時間がI/Oに費やされているかを評価します。待機時間が長い場合、ディスクI/O処理に対して効率的でない可能性があります。
ディスクの使用率:
- 各ディスクの使用率を確認し、どのディスクが最も負荷がかかっているかを把握します。特定のディスクがボトルネックになっている場合、ストレージの最適化や再構成を検討します。
ファイルの配置:
- ファイルが物理的にどのデバイスやディスクに配置されているかを確認します。ファイルが適切なデバイスに配置されているかどうかを確認し、必要に応じてディスクの追加や再配置を行います。

これらの情報をもとに、データベースのファイルI/Oのボトルネックや問題を特定し、適切な調整や最適化を行うことでディスクI/Oパフォーマンスを向上させることが求められます。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up