実務に役立つSQLのテクニック集

概要

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

Oracleデータベースメタデータ抽出

オブジェクトの定義や作成に使用されるSQL文を抽出

SELECT
    sqlarea.sql_id AS sql_id,
    parsing_schema_name,
    CASE
            WHEN length(sql_fulltext) > 10000 THEN to_clob('sql is too long')
            ELSE sql_fulltext
        END
    AS sql_fulltext,
    sql_bind_capture.name AS param_name,
    sql_bind_capture.value_string AS bind_value,
    last_active_time
FROM
    v$sqlarea sqlarea
    INNER JOIN v$sql_bind_capture sql_bind_capture ON sqlarea.sql_id = sql_bind_capture.sql_id
WHERE
    parsing_schema_name NOT IN (
        'SYS',
        'SYSMAN',
        'DBSNMP',
        'MDSYS',
        'EXFSYS',
        'EXFSYS',
        'MASTER'
    )
    AND instr(sql_fulltext,'dynamic_sampling') <= 0
    AND instr(sql_fulltext,'ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL') <= 0

このSQLクエリは、Oracleデータベースのメタデータを抽出し、オブジェクト（プロシージャ、シーケンス、パッケージ、トリガー、テーブル、関数、インデックス、ビュー）の定義や作成に使用されるSQL文を取得します。

※ GitLabでオブジェクト定義情報を管理する際に、このクエリを活用しました。

過去に実行されたSQL文とそれに関連するバインド変数情報の抽出

SELECT
    sqlarea.sql_id AS sql_id,
    parsing_schema_name,
    CASE
            WHEN length(sql_fulltext) > 10000 THEN to_clob('sql is too long')
            ELSE sql_fulltext
        END
    AS sql_fulltext,
    sql_bind_capture.name AS param_name,
    sql_bind_capture.value_string AS bind_value,
    last_active_time
FROM
    v$sqlarea sqlarea
    INNER JOIN v$sql_bind_capture sql_bind_capture ON sqlarea.sql_id = sql_bind_capture.sql_id
WHERE
    parsing_schema_name NOT IN (
        'SYS',
        'SYSMAN',
        'DBSNMP',
        'MDSYS',
        'EXFSYS',
        'EXFSYS',
        'MASTER'
    )
    AND instr(sql_fulltext,'dynamic_sampling') <= 0
    AND instr(sql_fulltext,'ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL') <= 0;

このクエリは、指定された条件に一致するSQL文とそのバインド変数の情報を抽出します。非常に長いSQL文の場合は、制限して表示します。また、特定のスキーマや特定の文字列が含まれているSQL文は除外されます。

このクエリは、Oracle19cにバージョンアップした際に、Oracle12cデータベースのワークロード情報を抽出するために使用しました。

一時テーブルスペースの使用率の抽出

SELECT
    dt.tablespace_name,
    TO_CHAR(t.bytes_used / dt.bytes * 100, '990.00') AS capacity
FROM
    dba_temp_files dt,
    (
        SELECT
            tablespace_name,
            file_id,
            SUM(bytes_cached) AS hwm,
            SUM(bytes_used) AS bytes_used
        FROM
            v$temp_extent_pool
        GROUP BY
            tablespace_name,
            file_id
    ) t,
    v$tempfile v
WHERE
    t.file_id(+) = dt.file_id
    AND dt.file_id = v.file#

※このクエリは、一時テーブルスペースの使用率を監視する際に利用しました。

特定テーブルの主キー（PK）となるカラムの抽出

SELECT DISTINCT
    column_name
FROM
    dba_cons_columns
WHERE
    table_name =:table_name
    AND owner =:db_user
    AND constraint_name IN (
        SELECT
            constraint_name
        FROM
            dba_constraints
        WHERE
            table_name =:table_name
            AND owner =:db_user
            AND constraint_type = 'P'
    );

このクエリは、データベースのデータディクショナリビューをクエリして、指定されたテーブルと所有者に関連する主キー制約の構成に使用されるカラムを一意に抽出します。DISTINCTキーワードが使用されているため、結果には重複するカラムが含まれません。この情報はデータベーススキーマやデータモデリングの理解、データベースの設計や解析に役立つことがあります。

※大容量テーブルのデータを分割してエクスポートする際に、利用しました。

特定テーブルの索引情報の抽出

 SELECT DISTINCT
    dba_ind_columns.index_name,
    column_name
FROM
    dba_ind_columns
    INNER JOIN (
        SELECT
            index_name
        FROM
            dba_indexes
        WHERE
            owner = ':owner'
            AND table_name = 'table_name'
            AND uniqueness = 'UNIQUE'
    ) unique_idx_infos ON dba_ind_columns.index_name = unique_idx_infos.index_name
WHERE
    index_owner = 'owner'
    AND table_name = 'table_name'
ORDER BY
    index_name;

このクエリは、指定されたデータベースオブジェクト（所有者とテーブル）に関連するユニークなインデックスとそのインデックスに含まれるカラムの情報を取得します。DISTINCTキーワードが使用されているため、同じインデックスに対して重複するカラムが除外されます。最終的に、インデックス名の昇順で結果がソートされます。

※大容量テーブルのデータを分割してエクスポートする際に、利用しました。

ロック情報の抽出

ロックの基本情報の取得

SELECT
    sid,
    type,
    lmode,
    request,
    ctime
FROM
    v$lock
WHERE
    type IN (
        'TX',
        'TM'
    );

このクエリは、データベース内のロック情報を検索し、トランザクションロックおよびテーブルモードロックに関する情報を取得します。v$lockビューはデータベース内で発生しているロックに関する情報を提供するため、これによりシステムやアプリケーションのパフォーマンスのトラブルシューティングが可能になります。取得される情報には、セッションID、ロックの種類、ロックモード、リクエスト数、およびロックの作成時刻が含まれます。

ロックをかけているユーザーとプログラムの特定

SELECT
    saddr,
    sid,
    serial#,
    username,
    program
FROM
    v$session
WHERE
    sid IN (
        SELECT
            sid
        FROM
            v$lock
        WHERE
            type IN (
                'TX',
                'TM'
            )
    );

このクエリは、データベース内で特定のトランザクションロックまたはテーブルモードロックに関連するセッションの情報を抽出します。結果には、セッションのアドレス、ID、シリアル番号、ユーザー名、および実行中のプログラムが含まれます。これにより、データベース内のロックに関連するアクティブなセッションを把握し、トラブルシューティングや監視が容易になります。

ロックが発生した際に、かかわるSQL情報の抽出

SELECT
    a.sql_text,
    a.address
FROM
    v$sqlarea a,
    v$session b
WHERE
    a.address = b.sql_address
    AND b.sid IN (
        SELECT
            sid
        FROM
            v$lock
        WHERE
            type IN (
                'TX',
                'TM'
            )
    );

このクエリは、データベース内で特定のトランザクションロックまたはテーブルモードロックに関連するセッションで実行されているSQL文とそのアドレスを抽出します。これにより、ロックが発生している際に実行されているSQL文を特定し、トラブルシューティングやパフォーマンスの解析が容易になります。

ロックしている、されているオブジェクトを特定するSQL情報の抽出

SELECT
    b.object_name,
    a.oracle_username
FROM
    v$locked_object a,
    dba_objects b
WHERE
    a.object_id = b.object_id;

このクエリは、v\$locked_objectビューとdba_objectsビューを結合して、ロックされているオブジェクトの名前とロックを保持しているOracleユーザーの名前を取得します。結果には、ロックされたオブジェクトとその所有者の情報が含まれます。これはデータベースのロック状態を確認し、問題のトラブルシューティングに役立つことがあります。このクエリは、v$locked_objectビューとdba_objectsビューを結合して、ロックされているオブジェクトの名前とロックを保持しているOracleユーザーの名前を取得します。結果には、ロックされたオブジェクトとその所有者の情報が含まれます。これはデータベースのロック状態を確認し、問題のトラブルシューティングに役立つことがあります。

ロックをかけているセッションID、ユーザー名、プログラム名、ロック時間の取得

SELECT
    a.sid sid,
    a.username username,
    a.serial# serialno,
    b.type type,
    a.program program,
    TO_CHAR(b.ctime / 60,'999990.9') lock_time,
    c.sql_text sql
FROM
    v$session a,
    v$lock b,
    v$sqlarea c
WHERE
    a.sid = b.sid
    AND b.type IN (
        'TX',
        'TM'
    )
    AND a.sql_address = c.address;

このクエリは、データベース内で指定されたトランザクションロックまたはテーブルモードロックを保持しているセッションに関する情報を抽出します。結果には、セッションID、ユーザー名、シリアル番号、ロックの種類、実行中のプログラム、ロックの経過時間、および実行中のSQL文が含まれます。この情報は通常、データベースのパフォーマンスのトラブルシューティングやセッションの監視に使用されます。

ロックのため待ちが発生しているセッションID、ユーザー名、プログラム名、待たされている時間情報の抽出

SELECT
    a.username username,
    a.program program,
    a.serial# serialno,
    a.sid sid,
    b.type type,
    TO_CHAR(b.ctime / 60,'999990.9') lock_time
FROM
    v$session a,
    v$lock b
WHERE
    a.sid = b.sid
    AND b.type = 'TM'
    AND b.sid = (
        SELECT
            sid
        FROM
            v$lock c
        WHERE
            c.type = 'TX'
            AND c.request > 0
    );

このクエリは、データベース内でトランザクションモードロックを保持し、同時にトランザクションロックをリクエストしているセッションに関する情報を抽出します。結果には、ユーザー名、プログラム、シリアル番号、セッションID、ロックの種類、およびロックの経過時間が含まれます。通常、トランザクションの競合やロックの問題を特定するために使用されます。

ロックされているオブジェクトの抽出

SELECT
    l.session_id sid,
    s.serial#,
    l.locked_mode,
    l.oracle_username,
    l.os_user_name,
    s.machine,
    s.terminal,
    o.object_name,
    s.logon_time
FROM
    v$locked_object l,
    all_objects o,
    v$session s
WHERE
    l.object_id = o.object_id
    AND l.session_id = s.sid
ORDER BY
    sid,
    s.serial#;

このクエリは、データベース内でロックされているオブジェクトとそれに関連するセッション情報を抽出し、セッションIDおよびシリアル番号に基づいて結果を昇順で表示します。これはデータベースのパフォーマンスのトラブルシューティングやセッションのモニタリングなどに役立つことがあります。

ロック解除

 alter system kill session 'sid, serialno';

このコマンドを実行すると、指定されたセッションが即座に終了され、そのセッションが占有していたリソースが解放されます。ただし、セッションの強制終了は注意が必要であり、適切な理由がある場合にのみ使用されるべきです。
##　指定時間以上実行されたSQL文の抽出

SELECT DISTINCT
    s.sid,
    s.serial#,
    s.status,
    s.program,
    q.module,
    q.sql_id,
    dbms_lob.substr(q.sql_fulltext,3000,1) sql,
    s.last_call_et 実行時間,
    q.parsing_schema_name,
    q.first_load_time,
    q.last_load_time,
    s.osuser,
    s.machine
FROM
    v$session s,
    v$sql q
WHERE
    s.sql_id = q.sql_id				
 --↓ここの数字を変えます。このサンプルでは10秒以上実行され続けているSQLを特定				
    AND s.last_call_et > 10				
 --↑ここの数字を変えます。このサンプルでは10秒以上実行され続けているSQLを特定				
    AND q.parsing_schema_name <> 'SYSMAN'				
 --↓使用する開発環境ソフトウェアは除外すると見やすいかも				
    AND NOT q.module IN (
        'cse.exe',
        'osqledit.exe',
        'ob10.exe'
    )				
 --↑使用する開発環境ソフトウェアは除外すると見やすいかも				
    AND s.status = 'ACTIVE'
ORDER BY
    1;

このクエリは、データベース内で長時間実行され続けているSQL文とそれに関連するアクティブなセッションの情報を取得します。結果には、セッションID、シリアル番号、状態、実行中のプログラム、SQLモジュール、SQL ID、SQL文のテキストなどが含まれます。通常、パフォーマンスの問題やリソースの使用状況の確認などで使用されます。

Oracleの表領域使用状況の調査

表領域容量調査

SELECT
    a.tablespace_name "表領域名",
    total "表領域サイズ",
    free "空き表領域サイズ",
    ( total - free ) "使用済表領域サイズ",
    round( (total - free) / total * 100,2)
    || '%' "使用済み表領域の割合"
FROM
    (
        SELECT
            tablespace_name,
            SUM(bytes) / 1024 / 1024 total
        FROM
            dba_data_files
        GROUP BY
            tablespace_name
    ) a,
    (
        SELECT
            tablespace_name,
            SUM(bytes) / 1024 / 1024 free
        FROM
            dba_free_space
        GROUP BY
            tablespace_name
    ) b
WHERE
    a.tablespace_name = b.tablespace_name
ORDER BY
    ( total - free ) DESC;

このクエリは、各表領域の総サイズ、空きサイズ、使用済みサイズ、および使用済みサイズの割合を表示します。表領域の使用状況を把握することは、データベースの管理やパフォーマンスの監視に役立ちます。

セグメント使用するディスク容量調査

SELECT
    segment_name "セグメント名",
    segment_type "オブジェクトタイプ",
    SUM(bytes) / 1024 / 1024 "使用容量（MB）"
FROM
    dba_extents
WHERE
    segment_name = ':segment_name'
GROUP BY
    segment_name,
    segment_type;

※このクエリは、テーブルやインデックスなどのセグメント対象に対して、ディスク使用容量を調査する際に活用できます。dba_extents ビューを使用して、指定された segment_name に対して、セグメントタイプと使用容量を調べます。

ページング検索

LIMIT OFFSETを用いたSQL文

SELECT
    *
FROM
    sample
ORDER BY
    rank 
--↓ここの数字を変えます。スキップする行数を設定
OFFSET 1 ROWS 
--↓ここの数字を変えます。取得する行数を設定
 FETCH FIRST 3 ROWS ONLY;

ROWNUMを用いたSQL文

SELECT
    *
FROM
    (
        SELECT
            ROWNUM AS rnum,
            sample.*
        FROM
            sample
    --↓ここの数字を変えます。行数の上限を設定
        WHERE
            ROWNUM <= 120
    );

ROWIDを用いたSQL文

SELECT
    ROWID,
    ROWNUM AS rnum,
    sample.*
FROM
    sample
--↓ここの数字を変えます。ROWIDの上限を設定
WHERE
    ROWID <= 'AAAtYKAAAAAACUTAAH';

ページング検索に関する3つのデータ抽出方法を共有しましたが、実際の運用ケースに基づいて、どちらを利用するかを検討することをお勧めします。個人的には、ROWIDを使用した検索が最もパフォーマンスが良いと考えています。

ROWIDとROWNUMの違いについては、以下のように考えています。
・ROWIDはOracleデータベース内で行を一意に識別するための物理的なアドレスです。
・ROWNUMはクエリの結果において行が取得された順番を示す擬似列です。