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

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

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

## 目次

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


---

## 1. セッション分析

### セッションのCPU使用状況確認
> **主な確認項目**:
> - セッションごとのCPU使用率
> - 経過時間の把握
> - リソース使用状況

 ```sql
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セッションの特定
> - ロック競合の調査
> - 待機時間の確認

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

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

### 長時間実行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負荷

 ```sql
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使用状況
> **主な確認項目**:
> - セッションごとのメモリ割り当て
> - 使用中のメモリ量
> - 最大使用メモリ量

 ```sql
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使用状況
> **主な確認項目**:
> - コンポーネントごとのメモリ使用量
> - プール別の使用状況
> - メモリの断片化状態

 ```sql
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の特定
> - ユーザーごとの負荷

 ```sql
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負荷

 ```sql
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待機時間

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

### TEMP領域使用状況
> **主な確認項目**:
> - 使用中の一時表領域サイズ
> - 空き容量
> - ファイルごとの使用状況

 ```sql
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統計
> - ブロックアクセス統計

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

### OSレベルのリソース状態
> **主な確認項目**:
> - CPU数
> - 物理メモリ量
> - システム負荷

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

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

### オブジェクト依存関係分析
> **主な確認項目**:
> - オブジェクト依存関係洗い出し

```sql
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使用状況を詳細に分析します。

```sql
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のボトルネック特定
- **システム全体の状態**: 総合的なリソース使用状況の把握

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

## ⚠️ 注意事項

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

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