見出し画像

各種RDBMSのパフォーマンスチューニング実用SQLクエリ備忘録

キョウスケ

概要

本資料では、主要なリレーショナルデータベース管理システム（RDBMS）である PostgreSQL、Oracle Database、MySQL、MariaDB、SQLite、Microsoft Access、FileMaker、Microsoft SQL Server、Amazon Aurora、Ingres、DB2、Sybase、Netezza のパフォーマンス分析やチューニングに役立つ実用的なSQLクエリを徹底的に網羅しました。

各データベースの バージョン間でのコマンドの違い や、CPU使用状況の詳細な分析 にも焦点を当てています。パフォーマンスチューニングの全体像を理解し、実践的なアプローチを学ぶための参考資料としてご活用ください。
またジョブでテーブルのハイウォーターマーク、インデックスの断片化があると思います。
より高速、より容量を保つために再編成処理も必要と考えております。 

この備忘録は、筆者の実績と知見を基に作成したものです。
一部、表現や内容の整理にAIのサポートを活用しましたが、
実際の経験に基づく知識とスキルを反映しています。
PostgreSQL、Oracle Database、MySQL、MariaDB、SQLite、Microsoft Access、Microsoft SQL Server、DB2、Sybase、Netezzaは
実務経験があります。
ほかのDBに関しましては独学にて勉強やAIを活用させていただきました。
・特にOracleの動的スキーマまたぎのアップデート
・文字列結合とEXECUTE IMMEDIATEの活用
・SQLSERVER、PostgreSQLのFrom区を動的にする方法
・ハイウォーターマーク、インデックスの断片化、ジョブでの再編成処理
上記の4つは実務で経験しており非常に役立っております。
※一部、表現や整理にAIを活用しました


PostgreSQL

1. セッション分析

現在のセッション情報を取得

  • 目的: 現在接続中のセッションを確認し、問題のあるセッションを特定する。

SELECT pid,
       usename AS ユーザー名,
       application_name AS アプリケーション名,
       client_addr AS クライアントIP,
       backend_start AS 接続開始時間,
       state AS 状態,
       state_change AS 状態変更時間,
       wait_event_type AS 待機イベントタイプ,
       wait_event AS 待機イベント,
       query AS 実行中のクエリ
FROM pg_stat_activity;

ロックの状況を確認

  • 目的: ロック競合やデッドロックを検出する。

SELECT t.relname AS テーブル名,
       l.locktype AS ロックタイプ,
       l.mode AS ロックモード,
       l.granted AS ロック取得済み,
       a.usename AS ユーザー名,
       a.query AS 実行中のクエリ
FROM pg_catalog.pg_locks l
JOIN pg_catalog.pg_stat_activity a ON l.pid = a.pid
JOIN pg_catalog.pg_class t ON l.relation = t.oid
WHERE NOT l.granted;

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

長時間実行クエリの特定

  • 目的: 実行時間の長いクエリを特定し、最適化の候補とする。

SELECT pid,
       now() - query_start AS 実行時間,
       usename AS ユーザー名,
       query AS 実行中のクエリ
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

クエリ実行計画の確認

  • 目的: クエリの実行計画を確認し、ボトルネックを特定する。

EXPLAIN ANALYZE
SELECT * FROM テーブル名 WHERE 条件;

3. メモリ使用状況分析

バッファキャッシュのヒット率確認

  • 目的: データベース全体のキャッシュヒット率を確認する。

SELECT datname AS データベース名,
       blks_hit,
       blks_read,
       ROUND(blks_hit::numeric / (blks_hit + blks_read) * 100, 2) AS ヒット率_パーセント
FROM pg_stat_database;

4. CPU使用状況分析

CPU負荷の高いクエリの特定

  • 目的: CPU負荷の高いクエリを特定する。

SELECT pid,
       usename AS ユーザー名,
       application_name AS アプリケーション名,
       client_addr AS クライアントIP,
       total_time AS 総実行時間_ms,
       query AS クエリ
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

注意: pg_stat_statements 拡張機能が必要です。

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

テーブルごとのI/O統計

  • 目的: テーブルごとのI/O負荷を確認する。

SELECT relname AS テーブル名,
       seq_scan AS シーケンシャルスキャン回数,
       idx_scan AS インデックススキャン回数,
       n_tup_ins AS 挿入行数,
       n_tup_upd AS 更新行数,
       n_tup_del AS 削除行数
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

6. バージョン間のコマンド違い

  • PostgreSQL 9.6以前: 一部のビューや関数が存在しない場合があります。

  • PostgreSQL 10以降: 並列クエリやパーティショニング機能が強化されています。

Oracle Database

1. セッション分析

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

  • 主な確認項目:

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

    • 経過時間の把握

    • リソース使用状況

SELECT A.SID || ',' || A.SERIAL# AS "SID/SERIAL",
       A.OSUSER,
       A.MACHINE,
       A.TERMINAL,
       A.PROGRAM,
       A.TYPE,
       A.USERNAME,
       A.STATUS,
       (SYSDATE - A.LOGON_TIME) * 86400 AS "経過時間(sec)",
       B.VALUE / 100 AS "CPU使用時間(sec)",
       ROUND(B.VALUE / ((SYSDATE - A.LOGON_TIME) * 86400 + 1) * 100, 2) AS "CPU占有率(%)",
       C.BLOCK_GETS AS "ブロック取得回数",
       C.PHYSICAL_READS AS "物理読み込み回数",
       A.SQL_ID
FROM V$SESSION A
JOIN V$SESSTAT B ON A.SID = B.SID
JOIN V$SESS_IO C ON B.SID = C.SID
JOIN V$STATNAME D ON B.STATISTIC# = D.STATISTIC#
WHERE 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
JOIN V$PROCESS P ON 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
JOIN V$SESSTAT SS ON S.SID = SS.SID
JOIN V$PROCESS P ON S.PADDR = P.ADDR
JOIN V$STATNAME ST ON SS.STATISTIC# = ST.STATISTIC#
WHERE ST.NAME = 'CPU used by this session'
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,
       PHYRDS AS 物理読み込み回数,
       PHYWRTS AS 物理書き込み回数,
       READTIM AS 読み込み時間_ms,
       WRITETIM AS 書き込み時間_ms
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
FROM DBA_OBJECTS A
JOIN DBA_DEPENDENCIES B ON A.OWNER = B.OWNER AND A.OBJECT_NAME = B.NAME
WHERE A.OWNER = '対象のスキーマ名';

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;

9.動的スキーマまたぎのアップデート

DBA_OBJECTをFOR文で回す

  • 目的: データベースオブジェクト（テーブル、ビュー、シーケンスなど）の動的管理や分析。

  • 例: 動的なオブジェクト操作やメタデータ管理を効率化するために、DBA_OBJECTSから取得した情報をループで処理する。

  • 技術的難易度: メタデータを駆使した操作は、通常のクエリ操作よりも設計力とSQLの深い理解が求められます。

  • 活用例: 全テーブルの統計情報を取得し、パフォーマンス改善のためのチューニング対象を特定。

スキーマ間の一括アップデート

  • 目的: 複数のスキーマに存在する類似構造のテーブルに対して、一括操作を行う。

  • 実装例:

BEGIN
    FOR rec IN (SELECT table_name FROM all_tables WHERE owner = 'SCHEMA1') LOOP
        EXECUTE IMMEDIATE 'UPDATE SCHEMA2.' || rec.table_name || ' SET col1 = col1 + 1';
    END LOOP;
END;

  • 技術的難易度: スキーマをまたぐ操作では依存関係の把握やトランザクション管理が重要となるため、高度な設計力が求められます。

10.文字列結合とEXECUTE IMMEDIATEの活用

  • 目的: SQLを動的に生成し、柔軟かつ汎用的な操作を実現。

  • 例: テーブル列や条件式を変数化し、柔軟なデータ処理を実現。

  • 実装例:

DECLARE
    v_sql VARCHAR2(4000);
BEGIN
    v_sql := 'UPDATE my_table SET ' || :dynamic_column || ' = :dynamic_value WHERE id = :dynamic_id';
    EXECUTE IMMEDIATE v_sql USING 'col_name', 42, 1;
END;

  • 技術的難易度: 動的SQLの生成と実行には、セキュリティ（SQLインジェクション対策）やエラーハンドリングを考慮する必要があり、難易度が高いです。

11.汎用的なSQL設計

  • 目的: 特定のタスクに依存しない、再利用可能なSQLやプロシージャを作成。

  • 意義: メンテナンスコストの削減やチーム内での効率向上に寄与。

MySQL / MariaDB

1. セッション分析

現在の接続状況の確認

  • 目的: 現在のセッションとその状態を確認する。

SHOW FULL PROCESSLIST;

ロックの状況を確認

  • 目的: ロック待ちやデッドロックの状況を確認する。

SELECT r.trx_id AS 待機トランザクションID,
       r.trx_mysql_thread_id AS 待機スレッドID,
       r.trx_query AS 待機クエリ,
       b.trx_id AS ブロックトランザクションID,
       b.trx_mysql_thread_id AS ブロックスレッドID,
       b.trx_query AS ブロッククエリ
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

バージョンによる違い:

  • MySQL 5.5以前: INNODB_LOCK_WAITS テーブルが存在しないため、代替手段が必要。

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

スロークエリログの有効化

  • 目的: 長時間実行されたクエリをログに記録する。

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 2秒以上のクエリを記録

  • ログの確認: サーバーの設定によりログファイルの場所が異なります。

クエリ実行計画の確認

  • 目的: クエリの実行計画を確認する。

EXPLAIN SELECT * FROM テーブル名 WHERE 条件;

  • より詳細な情報:

EXPLAIN FORMAT=JSON SELECT * FROM テーブル名 WHERE 条件;

バージョンによる違い:

  • MySQL 5.6以前: FORMAT=JSON オプションは使用不可。

3. メモリ使用状況分析

メモリ設定の確認

  • 目的: メモリ関連の設定値を確認する。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'key_buffer_size';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

キャッシュヒット率の確認

  • 目的: バッファプールのキャッシュヒット率を確認する。

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

  • 計算:

キャッシュヒット率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

4. CPU使用状況分析

  • 目的: CPU負荷の高いクエリやセッションを特定する。

  • 方法: performance_schema を利用。

SELECT THREAD_ID,
       EVENT_ID,
       CPU_TIME,
       SQL_TEXT
FROM performance_schema.events_statements_history_long
ORDER BY CPU_TIME DESC
LIMIT 10;

注意: performance_schema が有効である必要があります。

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

InnoDBのI/Oステータス確認

  • 目的: InnoDBエンジンのI/O状態を確認する。

SHOW ENGINE INNODB STATUS;

  • 解析ポイント:

    • File I/O セクション

    • Buffer Pool and Memory セクション

テーブルごとのI/O統計

  • 目的: テーブルごとのI/O負荷を確認する。

SELECT OBJECT_SCHEMA,
       OBJECT_NAME,
       COUNT_READ,
       COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY COUNT_READ DESC;

6. バージョン間のコマンド違い

  • MySQL 5.6以前: performance_schema の機能が限定的。

  • MySQL 8.0: 新しいシステム変数や機能が追加。

SQLite

1. パフォーマンス分析の基本

クエリ実行計画の確認

  • 目的: クエリの実行計画を確認する。

EXPLAIN QUERY PLAN SELECT * FROM テーブル名 WHERE 条件;

インデックスの確認

  • 目的: テーブルに設定されているインデックスを確認する。

PRAGMA index_list('テーブル名');

2. メモリ使用状況分析

キャッシュサイズの設定

  • 目的: キャッシュサイズを調整してパフォーマンスを向上させる。

PRAGMA cache_size;
PRAGMA cache_size = 2000;  -- キャッシュサイズを2000ページに設定

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

ジャーナルモードの設定

  • 目的: データの安全性とパフォーマンスのバランスを調整する。

PRAGMA journal_mode;
PRAGMA journal_mode = WAL;  -- Write-Ahead Loggingを有効化

同期モードの設定

  • 目的: データ書き込みの同期方法を設定する。

PRAGMA synchronous;
PRAGMA synchronous = NORMAL;  -- 同期設定をNORMALに変更

4. バージョン間のコマンド違い

  • SQLite 3.7.0以降: WAL モードがサポート。

  • SQLite 3.12.0以降: EXPLAIN QUERY PLAN の出力が詳細化。

Microsoft Access

パフォーマンス分析の基本

GUIツールの活用: Microsoft AccessはGUIベースのデータベースであり、直接的なSQLによるパフォーマンス分析は限定的。

パフォーマンスアナライザの使用

  • 目的: データベースのパフォーマンスを自動的に分析し、改善点を提案。

  1. メニューから [データベースツール] を選択。

  2. [パフォーマンスアナライザ] をクリック。

  3. 分析対象のオブジェクトを選択し、分析を実行。

インデックスの最適化

  • 目的: クエリの実行速度を向上させる。

  1. テーブルデザインビューを開く。

  2. インデックスを設定したいフィールドを選択。

  3. フィールドプロパティで [インデックス] を [はい] に設定。

FileMaker

パフォーマンス分析の基本

  • FileMaker Pro Advanced の [データビューア] や [スクリプトデバッガ] を使用してパフォーマンスを分析。

データビューアの使用

  • 目的: スクリプトや計算式の評価結果をリアルタイムで確認。

  1. [ツール] メニューから [データビューア] を選択。

  2. [現在の] タブでフィールドや変数の値を確認。

スクリプトデバッガの使用

  • 目的: スクリプトの実行をステップごとに追跡し、ボトルネックを特定。

  1. [ツール] メニューから [スクリプトデバッガ] を選択。

  2. 実行したいスクリプトを開始し、ステップ実行。

Microsoft SQL Server / Amazon Aurora

1. セッション分析

現在のセッション情報を取得

  • 目的: 現在の接続状況とセッションの詳細を確認。

SELECT session_id,
       login_name,
       status,
       host_name,
       program_name,
       cpu_time,
       memory_usage
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

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

実行時間の長いクエリの特定

  • 目的: 実行時間やCPU時間の長いクエリを特定。

SELECT TOP 10
       total_worker_time / execution_count AS 平均CPU時間,
       execution_count,
       SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
       ((CASE qs.statement_end_offset
         WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
       END - qs.statement_start_offset)/2) + 1) AS クエリ
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY 平均CPU時間 DESC;

3. メモリ使用状況分析

メモリ消費の高いクエリの特定

  • 目的: メモリ使用量の多いクエリを特定。

SELECT TOP 10
       (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS 平均メモリ使用量,
       qs.execution_count,
       SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
       ((CASE qs.statement_end_offset
         WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
       END - qs.statement_start_offset)/2) + 1) AS クエリ
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY 平均メモリ使用量 DESC;

4. CPU使用状況分析

CPU使用率の高いセッションの特定

  • 目的: CPU負荷の高いセッションを特定。

SELECT TOP 5
       s.session_id,
       r.cpu_time,
       r.total_elapsed_time,
       s.host_name,
       s.program_name,
       s.login_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
ORDER BY r.cpu_time DESC;

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

データベースファイルのI/O統計

  • 目的: 各データベースファイルのI/O統計を確認。

SELECT
    DB_NAME(vfs.database_id) AS データベース名,
    vfs.file_id,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs;

6. バージョン間のコマンド違い

  • SQL Server 2014以前: 一部のDMVが利用できない場合があります。

  • SQL Server 2016以降: クエリストア機能が追加され、パフォーマンス分析が容易に。

Ingres

パフォーマンス分析の基本

サーバーの状態を監視

  • iimonitorコマンドを使用:

iimonitor
> show sessions
> exit

パフォーマンス統計を取得

  • iistatコマンドを使用:

iistat -d

クエリ実行計画の確認

  • 目的: クエリの実行計画を確認。

EXPLAIN SELECT * FROM テーブル名 WHERE 条件;

IBM DB2

1. セッション分析

現在のアプリケーションのリスト

  • 目的: 接続中のアプリケーションを確認。

LIST APPLICATIONS;

詳細なセッション情報

  • 目的: 各セッションの詳細情報を取得。

SELECT appli_id,
       appli_name,
       auth_id,
       client_nname,
       status
FROM sysibmadm.snapappl;

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

リソース消費の多いSQLの特定

  • 目的: CPU時間やI/Oを多く消費するSQLを特定。

SELECT substr(stmt_text, 1, 1000) AS ステートメント,
       total_cpu_time,
       num_executions,
       total_rows_read
FROM sysibmadm.mon_stmt_summary
ORDER BY total_cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

3. メモリ使用状況分析

バッファプールのヒット率確認

  • 目的: バッファプールの効率を確認。

SELECT bp_name,
       pool_data_l_reads,
       pool_data_p_reads,
       DECIMAL((1 - (FLOAT(pool_data_p_reads) / FLOAT(pool_data_l_reads))) * 100, 5, 2) AS ヒット率_パーセント
FROM sysibmadm.bp_hitratio;

4. CPU使用状況分析

  • 目的: 高負荷のセッションやクエリを特定。

SELECT appl_id,
       appl_name,
       total_cpu_time,
       stmt_text
FROM sysibmadm.mon_current_sql
ORDER BY total_cpu_time DESC
FETCH FIRST 5 ROWS ONLY;

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

テーブルスペースのI/O統計

  • 目的: 各テーブルスペースのI/O統計を確認。

SELECT tbsp_name,
       tbsp_type,
       reads,
       writes,
       read_time,
       write_time
FROM sysibmadm.tbsp_utilization;

6. バージョン間のコマンド違い

  • DB2 9.7以前: 一部のモニタリングビューが存在しない。

  • DB2 10.5以降: BLU Acceleration 機能が追加。

Sybase

1. セッション分析

現在のプロセスを確認

  • 目的: 接続中のプロセスとその状態を確認。

SELECT spid,
       status,
       hostname,
       program_name,
       cpu,
       physical_io
FROM master..sysprocesses;

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

実行中のSQL文を確認

  • 目的: 現在実行中のSQL文を確認。

SELECT spid,
       status,
       cmd,
       sqltext
FROM master..sysprocesses
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE status = 'running';

3. メモリ使用状況分析

キャッシュの使用状況を確認

  • 目的: データキャッシュの使用状況を確認。

sp_cacheconfig;

4. CPU使用状況分析

  • 目的: CPU負荷の高いプロセスを特定。

SELECT spid,
       cpu,
       physical_io,
       program_name
FROM master..sysprocesses
ORDER BY cpu DESC;

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

デバイスごとのI/O統計

  • 目的: デバイスごとのI/O統計を確認。

sp_sysmon '00:01:00';

6. バージョン間のコマンド違い

  • Sybase ASE 15以前: 一部の機能やコマンドが制限。

  • SAP Sybase ASE: 新機能や改善が追加。

Netezza

パフォーマンス分析とチューニング

IBM Netezza は、高速なデータウェアハウスアプライアンスであり、大規模なデータ分析を効率的に行うための専用システムです。本節では、Netezza のパフォーマンス分析とチューニングに役立つクエリや手法を紹介します。

1. セッション分析

現在のセッション情報を取得

  • 目的: 現在接続中のセッションを確認し、問題のあるセッションを特定する。

SELECT SESSIONID,
       USERNAME,
       STARTTIME,
       LASTCMD,
       LASTSQL,
       CLIENTAPP,
       CLIENTHOST
FROM _V_SESSION;

  • 説明: _V_SESSION ビューから現在のセッション情報を取得します。各セッションの開始時間、実行中のコマンド、クライアント情報などが確認できます。

ロックの状況を確認

  • 目的: ロック競合やデッドロックを検出する。

SELECT * FROM _V_LOCK;

  • 説明: _V_LOCK ビューから現在のロック情報を取得します。どのテーブルがロックされているか、どのセッションがロックを保持しているかを確認できます。

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

実行中のクエリの確認

  • 目的: 現在実行中のクエリを確認し、長時間実行されているクエリを特定する。

SELECT Q.CLIENTUSER AS ユーザー名,
       Q.CLIENTAPP AS アプリケーション名,
       Q.STARTTIME AS 開始時間,
       Q.PRIORITY AS 優先度,
       Q.STATE AS 状態,
       Q.TOTALTIME AS 経過時間,
       Q.STATEMENT AS クエリ
FROM _V_QUERIES Q
WHERE Q.STATE = 'EXECUTING'
ORDER BY Q.TOTALTIME DESC;

  • 説明: _V_QUERIES ビューから実行中のクエリ情報を取得します。

クエリ実行計画の確認

  • 目的: クエリの実行計画を確認し、ボトルネックを特定する。

EXPLAIN VERBOSE
SELECT * FROM テーブル名 WHERE 条件;

  • 説明: EXPLAIN VERBOSE を使用して、クエリの詳細な実行計画を表示します。データ分散やフィルタリングの状況を確認できます。

3. メモリ使用状況分析

  • 注意: Netezza はアプライアンス製品であり、メモリ管理はシステムによって最適化されています。ユーザーが直接メモリ設定を調整することはできません。ただし、クエリ設計やデータモデルの最適化によって間接的にメモリ使用効率を向上させることが可能です。

4. CPU使用状況分析

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

  • 目的: 各セッションのCPU使用状況を確認する。

SELECT S.SESSIONID,
       S.USERNAME,
       M.CPU_TIME,
       M.ELAPSED_TIME,
       (M.CPU_TIME / NULLIF(M.ELAPSED_TIME, 0)) * 100 AS CPU使用率,
       S.LASTSQL AS クエリ
FROM _V_SESSION S
JOIN _V_MONITOR_SESSION M ON S.SESSIONID = M.SESSIONID
ORDER BY M.CPU_TIME DESC;

  • 説明: _V_MONITOR_SESSION ビューからセッションごとのCPU使用時間を取得し、CPU使用率を計算します。

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

テーブルごとのI/O統計

  • 目的: テーブルごとのI/O負荷を確認し、パフォーマンス改善の対象を特定する。

SELECT D.DATATYPE AS データタイプ,
       O.OBJNAME AS オブジェクト名,
       S.TOTALROWS AS 総行数,
       S.TOTALBYTES AS 総バイト数,
       S.DELETEDROWS AS 削除済み行数
FROM _V_OBJECTS O
JOIN _V_SYS_OBJECT_STAT S ON O.OBJID = S.OBJID
JOIN _V_DATATYPE D ON O.DATATYPE = D.TYPEID
ORDER BY S.TOTALBYTES DESC;

  • 説明: _V_OBJECTS や _V_SYS_OBJECT_STAT ビューを使用して、テーブルやビューのサイズや行数を確認します。

ディスク使用状況の確認

  • 目的: ディスク容量の使用状況を確認する。

SELECT DISKID,
       TOTAL_CAPACITY_MB,
       USED_SPACE_MB,
       FREE_SPACE_MB,
       (USED_SPACE_MB / TOTAL_CAPACITY_MB) * 100 AS 使用率_パーセント
FROM _V_DISK;

  • 説明: _V_DISK ビューから各ディスクの容量と使用状況を取得します。

6. システム統計情報

システム全体のパフォーマンス統計

  • 目的: システム全体のCPU、メモリ、I/Oの統計情報を確認する。

SELECT * FROM _V_SYS_RESOURCE_UTIL;

  • 説明: _V_SYS_RESOURCE_UTIL ビューから、システムリソースの利用状況を確認します。

7. バージョン間のコマンド違い

  • 注意: Netezza のバージョンによって、システムビューや利用可能な機能が異なる場合があります。使用しているバージョンに対応した公式ドキュメントを参照してください。

まとめ

主な分析可能項目:

  • セッションの状態把握: リソース使用状況やロック競合の詳細な確認

  • 重いSQLの特定: パフォーマンスに影響を与えるクエリの発見と最適化

  • メモリ使用状況: バッファキャッシュやメモリプールの使用状況確認

  • CPU負荷分析: セッションやプロセスごとのCPU使用率の確認

  • I/O性能評価: ディスクI/Oのボトルネック特定と最適化

  • システム全体の状態: 総合的なリソース使用状況の把握とチューニング

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

注意事項

  • 権限の確認: これらのクエリを実行するには、適切なデータベース権限が必要です。

  • 負荷への配慮: 本番環境での実行は、システムに負荷がかかる可能性があるため注意が必要です。

  • バージョン差異: 環境やバージョンによって、一部のビューやカラムが利用できない場合があります。公式ドキュメントを参照してください。

  • データの保全: 誤ってデータを変更・削除しないよう、特に DELETE や UPDATE 文の使用には注意してください。

以上が、各種データベース（PostgreSQL、Oracle Database、MySQL、MariaDB、SQLite、Microsoft Access、FileMaker、Microsoft SQL Server、Amazon Aurora、Ingres、IBM DB2、Sybase、Netezza）のパフォーマンス分析とチューニングに役立つSQLクエリ集です。バージョンやエディションによる違いも考慮し、最適なパフォーマンスを実現するための参考にしてください。

いいなと思ったら応援しよう！