sys スキーマ（SQL）による取得とセクション定義

-- 合計実行時間の多い SQL 上位20
SELECT
    query,
    db,
    exec_count,
    total_latency,
    avg_latency,
    max_latency,
    rows_examined_avg,
    rows_sent_avg,
    tmp_tables,
    tmp_disk_tables,
    full_scans
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 20;

SELECT query, db, exec_count, total_latency, no_index_used_count, no_good_index_used_count
FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC
LIMIT 20;

SELECT query, db, exec_count, total_latency, sort_merge_passes, sort_rows
FROM sys.statements_with_sorting
ORDER BY sort_merge_passes DESC
LIMIT 20;

-- 待機クラス別（平均待機時間順）
SELECT event_class, total_waited, avg_latency, max_latency, total_waits
FROM sys.wait_classes_global_by_avg_latency
ORDER BY total_waited DESC;

-- 個別待機イベント（合計待機時間順）
SELECT events, total_waited, avg_latency, max_latency, total_waits
FROM sys.waits_global_by_latency
ORDER BY total_waited DESC
LIMIT 20;

-- 待機時間の多いファイル順
SELECT file, total_latency, count_read, read_latency, count_write, write_latency
FROM sys.io_global_by_file_by_latency
ORDER BY total_latency DESC
LIMIT 20;

-- 転送バイト数の多いファイル順
SELECT file, total, read, write, misc
FROM sys.io_global_by_file_by_bytes
ORDER BY total DESC
LIMIT 20;

-- 現在のロック待機状況
SELECT
    wait_started,
    wait_age,
    waiting_trx_rows_locked,
    waiting_query,
    blocking_pid,
    blocking_query,
    blocking_trx_rows_locked,
    blocking_trx_rows_modified
FROM sys.innodb_lock_waits;

-- スキーマ・テーブルロック待機
SELECT
    waiting_pid,
    waiting_query,
    blocking_pid,
    blocking_query,
    wait_age,
    waiting_lock_type
FROM sys.schema_table_lock_waits;

-- テーブル別の合計 I/O 待機時間
SELECT
    table_schema,
    table_name,
    total_latency,
    rows_fetched,
    fetch_latency,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM sys.schema_table_statistics
ORDER BY total_latency DESC
LIMIT 20;

-- インデックス別の使用状況
SELECT
    table_schema,
    table_name,
    index_name,
    rows_selected,
    select_latency,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM sys.schema_index_statistics
ORDER BY select_latency DESC
LIMIT 20;

-- インデックスが使われていないテーブル（AWR Segment I/O 分析に対応）
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('performance_schema', 'sys', 'information_schema', 'mysql');

-- コンポーネント別現在使用メモリ
SELECT event_name, current_count, current_alloc, current_avg_alloc, high_alloc
FROM sys.memory_global_by_current_bytes
ORDER BY current_alloc DESC
LIMIT 20;

-- ユーザー別メモリ使用量
SELECT user, current_allocated, total_allocated
FROM sys.memory_by_user_by_current_bytes
ORDER BY current_allocated DESC;

-- Performance Schema 統計をすべてリセット
CALL sys.ps_truncate_all_tables(FALSE);  -- FALSE=確認なし

-- 現在実行中の長時間クエリを表示
CALL sys.ps_check_lost_instrumentation();

-- 特定ステートメントの実行計画を取得（MySQL 8.0+）
CALL sys.ps_trace_statement_digest('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 10, 0.1, FALSE, FALSE);

-- スレッドの詳細情報
SELECT * FROM sys.session WHERE command != 'Sleep';