Oracleパフォーマンス分析のための実用SQLクエリ集

概要

Oracle DBのパフォーマンス分析や障害調査でよく使用するSQLクエリをまとめました。システムの状態把握やボトルネック特定に役立つクエリ集です。

目次

1. セッション分析
2. SQL文パフォーマンス分析
3. メモリ使用状況分析
4. CPU使用状況分析
5. I/O・ディスク分析
6. その他の分析クエリ
7. オブジェクト依存関係分析
8. ボトルネック抽出スクリプト

1. セッション分析

セッションのCPU使用状況確認

主な確認項目:

セッションごとのCPU使用率

経過時間の把握

リソース使用状況

SELECT A.SID || ',' || A.SERIAL# "SID/SERIAL",
     A.OSUSER,
     A.MACHINE,
     A.TERMINAL,
     A.PROGRAM,
     A.TYPE,
     A.USERNAME,
     A.STATUS,
     (sysdate - a.logon_time)*86400 "経過時間(sec)",
     b.value /100 "CPU使用時間(sec)",
     b.value /((sysdate - a.logon_time)*86400+1) "CPU占有率(%)",
     c.block_gets "ブロック取得回数",
     c.physical_reads "物理読み込み回数",
     A.SQL_ID
FROM v$session a,
   v$sesstat b,
   v$sess_io c,
   v$statname d
WHERE a.sid = b.sid
AND b.sid = c.sid
AND b.statistic# = d.statistic#
AND d.name like '%CPU%session'
ORDER BY "CPU占有率(%)" desc;

ロック状態の確認

主な確認項目:

WAITINGセッションの特定

ロック競合の調査

待機時間の確認

SELECT SID,
     SERIAL#,
     BLOCKING_SESSION,
     EVENT,
     WAIT_CLASS,
     SECONDS_IN_WAIT
FROM V$SESSION
WHERE STATE = 'WAITING';

2. SQL文パフォーマンス分析

長時間実行SQLの特定

主な確認項目:

実行時間が長いSQL

リソース消費状況

実行頻度

SELECT SQL_ID,
     PLAN_HASH_VALUE,
     ELAPSED_TIME / 1000000 AS ELAPSED_TIME_SEC,
     CPU_TIME / 1000000 AS CPU_TIME_SEC,
     DISK_READS,
     BUFFER_GETS,
     EXECUTIONS,
     ROWS_PROCESSED,
     PARSE_CALLS,
     FETCHES
FROM V$SQL
WHERE ELAPSED_TIME / 1000000 > 10
ORDER BY ELAPSED_TIME DESC;

リソース消費の多いSQL

主な確認項目:

CPU使用時間

メモリ使用量

I/O負荷

SELECT SQL_ID,
     ELAPSED_TIME / 1000000 AS ELAPSED_TIME_SEC,
     CPU_TIME / 1000000 AS CPU_TIME_SEC,
     DISK_READS,
     BUFFER_GETS,
     PGA_MEMORY / (1024 * 1024) AS PGA_MEMORY_MB,
     EXECUTIONS,
     ROWS_PROCESSED
FROM V$SQL
WHERE ELAPSED_TIME / 1000000 > 10
ORDER BY CPU_TIME DESC;

3. メモリ使用状況分析

PGA使用状況

主な確認項目:

セッションごとのメモリ割り当て

使用中のメモリ量

最大使用メモリ量

SELECT S.SID,
     S.SERIAL#,
     P.PGA_ALLOC_MEM / (1024 * 1024) AS PGA_ALLOC_MB,
     P.PGA_USED_MEM / (1024 * 1024) AS PGA_USED_MB,
     P.PGA_MAX_MEM / (1024 * 1024) AS PGA_MAX_MB,
     P.PROGRAM,
     P.BACKGROUND
FROM V$SESSION S,
   V$PROCESS P
WHERE S.PADDR = P.ADDR
ORDER BY P.PGA_ALLOC_MEM DESC;

SGA使用状況

主な確認項目:

コンポーネントごとのメモリ使用量

プール別の使用状況

メモリの断片化状態

SELECT POOL,
     NAME,
     BYTES / (1024 * 1024) AS SIZE_MB
FROM V$SGASTAT
WHERE POOL IS NOT NULL
ORDER BY SIZE_MB DESC;

4. CPU使用状況分析

セッションごとのCPU使用率

主な確認項目:

セッション別のCPU使用時間

プロセスIDの特定

ユーザーごとの負荷

SELECT S.SID,
     S.SERIAL#,
     S.USERNAME,
     S.PROGRAM,
     P.SPID AS OS_PID,
     ROUND(VALUE / 100, 2) AS CPU_TIME_SEC
FROM V$SESSION S,
   V$SESSTAT SS,
   V$PROCESS P,
   V$STATNAME ST
WHERE S.SID = SS.SID
AND SS.STATISTIC# = ST.STATISTIC#
AND ST.NAME = 'CPU used by this session'
AND S.PADDR = P.ADDR
ORDER BY CPU_TIME_SEC DESC;

システム全体のCPU状態

主な確認項目:

全体的なCPU使用状況

セッション開始時のCPU状態

データベース全体のCPU負荷

SELECT NAME,
     VALUE
FROM V$SYSSTAT
WHERE NAME IN ('CPU used by this session',
             'DB CPU',
             'CPU used when call started');

5. I/O・ディスク分析

ファイルI/O状況

主な確認項目:

物理読み込み回数

物理書き込み回数

I/O待機時間

SELECT FILE_ID,
     FILE_NAME,
     PHYSICAL_READS,
     PHYSICAL_WRITES,
     READ_TIME,
     WRITE_TIME
FROM V$FILESTAT;

TEMP領域使用状況

主な確認項目:

使用中の一時表領域サイズ

空き容量

ファイルごとの使用状況

SELECT TABLESPACE_NAME,
     FILE_ID,
     BYTES_USED / (1024 * 1024) AS USED_MB,
     BYTES_FREE / (1024 * 1024) AS FREE_MB
FROM V$TEMP_SPACE_HEADER
ORDER BY USED_MB DESC;

6. その他の分析クエリ

システム統計情報

主な確認項目:

CPU使用統計

物理I/O統計

ブロックアクセス統計

SELECT NAME,
     VALUE
FROM V$SYSSTAT
WHERE NAME IN ('CPU used by this session',
             'physical reads',
             'physical writes',
             'db block gets');

OSレベルのリソース状態

主な確認項目:

CPU数

物理メモリ量

システム負荷

SELECT STAT_NAME,
      VALUE
FROM V$OSSTAT
WHERE STAT_NAME IN ('NUM_CPUS',
                   'PHYSICAL_MEMORY_BYTES',
                   'LOAD');

7. オブジェクト依存関係分析

オブジェクト依存関係分析

主な確認項目:

オブジェクト依存関係洗い出し

SELECT a.owner,
       a.object_name,
       b.referenced_owner,
       b.referenced_name,
       b.referenced_type,
       c.ほにゃらら
FROM dba_objects a,
     dba_dependencies b,
     dba_dependencies c
WHERE a.owner = b.owner(+)
  AND a.object_name = b.name(+)
  AND b.referenced_owner = c.owner(+)
  AND b.referenced_name = c.name(+);

8. ボトルネック抽出スクリプト

以下のスクリプトを使用して、セッションCPU使用状況を詳細に分析します。

spool C:\Users\awc\Desktop\temp/show_oracle_session_cpu_report.txt
SET echo off
SET LINESIZE 300
SET PAGESIZE 50
SET COLSEP |
SET FEEDBACK OFF
SET TERM OFF
COLUMN USERNAME FORMAT A10
COLUMN "SID/SERIAL" FORMAT A15
COLUMN OSUSER FORMAT A10
COLUMN MACHINE FORMAT A30
COLUMN TERMINAL FORMAT A10
COLUMN PROGRAM FORMAT A50
COLUMN TYPE FORMAT A10
COLUMN COMMAND FORMAT A20
COLUMN "経過時間(sec)　" FORMAT 9,999,999,990
COLUMN "CPU使用時間(sec)" FORMAT 9,999,990.000
COLUMN "CPU占有率(%)" FORMAT 990.000
COLUMN "ブロック取得回数" FORMAT 9,999,999,990
COLUMN "物理読み込み回数" FORMAT 9,999,999,990
COLUMN SQL_ID FORMAT A20
TTITLE LEFT '【 セッションの確認 】' SKIP 1 LINE
SELECT  A.SID || ',' || A.SERIAL# "SID/SERIAL",
        A.OSUSER,
        A.MACHINE,
        A.TERMINAL,
        A.PROGRAM,
        A.TYPE,
        A.USERNAME,
        A.STATUS,
        DECODE(A.COMMAND,
               1,'CREATE TABLE',
               2,'INSERT',
               3,'SELECT',
               4,'CREATE CLUSTER',
               5,'ALTER CLUSTER',
               6,'UPDATE',
               7,'DELETE',
               8,'DROP CLUSTER',
               9,'CREATE INDEX',
               10,'DROP INDEX',
               11,'ALTER INDEX',
               12,'DROP TABLE',
               13,'CREATE SEQUENCE',
               14,'ALTER SEQUENCE',
               15,'ALTER TABLE',
               16,'DROP SEQUENCE',
               17,'GRANT',
               18,'REVOKE',
               19,'CREATE SYNONYM',
               20,'DROP SYNONYM',
               21,'CREATE VIEW',
               22,'DROP VIEW',
               23,'VALIDATE INDEX',
               24,'CREATE PROCEDURE',
               25,'ALTER PROCEDURE',
               26,'LOCK TABLE',
               27,'NO OPERATION',
               28,'RENAME',
               29,'COMMENT',
               30,'AUDIT',
               31,'NOAUDIT',
               32,'CREATE DATABASE LINK',
               33,'DROP DATABASE LINK',
               34,'CREATE DATABASE',
               35,'ALTER DATABASE',
               36,'CREATE ROLLBACK SEG',
               37,'ALTER ROLLBACK SEGMENT',
               38,'DROP ROLLBACK SEGMENT',
               39,'CREATE TABLESPACE',
               40,'ALTER TABLESPACE',
               41,'DROP TABLESPACE',
               42,'ALTER SESSION',
               43,'ALTER USER',
               44,'COMMIT',
               45,'ROLLBACK',
               46,'SAVEPOINT',
               47,'PL/SQL EXECUTE',
               48,'SET TRANSACTION',
               49,'ALTER SYSTEM SWITCH',
               50,'EXPLAIN',
               51,'CREATE USER',
               52,'CREATE ROLE',
               53,'DROP USER',
               54,'DROP ROLE',
               55,'SET ROLE',
               56,'CREATE SCHEMA',
               57,'CREATE CONTROL FILE',
               58,'ALTER TRACING',
               59,'CREATE TRIGGER',
               60,'ALTER TRIGGER',
               61,'DROP TRIGGER',
               62,'ANALYZE TABLE',
               63,'ANALYZE INDEX',
               64,'ANALYZE CLUSTER',
               65,'CREATE PROFILE',
               66,'DROP PROFILE',
               67,'ALTER PROFILE',
               68,'DROP PROCEDURE',
               69,'DROP PROCEDURE',
               70,'ALTER RESOURCE COST',
               71,'CREATE SNAPSHOT LOG',
               72,'ALTER SNAPSHOT LOG',
               73,'DROP SNAPSHOT LOG',
               74,'CREATE SNAPSHOT',
               75,'ALTER SNAPSHOT',
               76,'DROP SNAPSHOT',
               79,'ALTER SESSION',
               85,'TRUNCATE TABLE',
               86,'TRUNCATE CLUSTER',
               88,'ALTER VIEW',
               91,'CREATE FUNCTION',
               92,'ALTER FUNCTION',
               93,'DROP FUNCTION',
               94,'CREATE PACKAGE',
               95,'ALTER PACKAGE',
               96,'DROP PACKAGE',
               97,'CREATE PACKAGE BODY',
               98,'ALTER PACKAGE BODY',
               99,'DROP PACKAGE BODY',
               '待機中') COMMAND,
        (sysdate - a.logon_time)*86400 "経過時間(sec)",
        b.value  /100 "CPU使用時間(sec)",
        b.value  /((sysdate - a.logon_time)*86400+1) "CPU占有率(%)",
        c.block_gets "ブロック取得回数",
        c.physical_reads "物理読み込み回数",
        A.SQL_ID
FROM    v$session a,
        v$sesstat b,
        v$sess_io c,
        v$statname d
WHERE   a.sid        = b.sid
    AND b.sid        = c.sid
    AND b.statistic# = d.statistic#
    AND d.name like '%CPU%session'
ORDER BY "CPU占有率(%)" desc;
spool off;
exit;

まとめ

主な分析可能項目:

セッションの状態把握: リソース使用状況の詳細な確認
重いSQLの特定: パフォーマンス影響の大きいクエリの発見
メモリ使用状況: PGA/SGAの使用状況確認
CPU負荷分析: プロセスごとのCPU使用率確認
I/O性能評価: ディスクI/Oのボトルネック特定
システム全体の状態: 総合的なリソース使用状況の把握

重要: これらの情報を総合的に分析することで、システムのボトルネックを特定し、パフォーマンスチューニングの方針を立てることができます。

⚠️ 注意事項

これらのクエリを実行するには適切な権限が必要です
本番環境での実行は、システムに負荷がかかる可能性があるため注意が必要です
環境によって一部のビューやカラムが利用できない場合があります

tags: Oracle, データベース, パフォーマンスチューニング, SQL