SQL計画管理

DB

SHOW PARAMETER SQL_PLAN
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
ORIGIN, ENABLED, ACCEPTED
FROM DBA_SQL_PLAN_BASELINES;

 

未対応の模様

未対応の模様

※Auroraの場合apg_plan_mgmt 拡張機能が使用可能

--クエリストア

https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017
https://qiita.com/OMOIKANESAN/items/211ab9207a784be4c90a

(2016)

--クエリのストアを有効にする
ALTER DATABASE test SET QUERY_STORE = ON;
go

--クエリストア内のクエリとプランに関する情報

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
ON Qry.query_text_id = Txt.query_text_id ;
go

--クエリのストアのオプションを取得する
SELECT * FROM sys.database_query_store_options;

--クエリのストアの時間間隔を設定する
ALTER DATABASE test
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

--クエリのストアの使用領域
SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

--クエリのストアのオプションをすべて設定する
ALTER DATABASE test
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 3000,
MAX_STORAGE_SIZE_MB = 500,
INTERVAL_LENGTH_MINUTES = 15,
SIZE_BASED_CLEANUP_MODE = AUTO,
QUERY_CAPTURE_MODE = AUTO,
MAX_PLANS_PER_QUERY = 1000
);

--領域のクリーンアップ
ALTER DATABASE test SET QUERY_STORE CLEAR;

--データベースで最近実行された n 個のクエリ。
SELECT TOP 10 qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC;

--各クエリの実行回数。
SELECT q.query_id, qt.query_text_id, qt.query_sql_text,
SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY total_execution_count DESC;


SELECT rs.avg_logical_io_reads, qt.query_sql_text,
q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,
rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id


--プランの強制
EXEC sys.sp_query_store_force_plan @query_id = 1, @plan_id = 1

※プランの強制は内部的にはプランガイドの機能を使用している


--プランの強制解除
EXEC sys.sp_query_store_unforce_plan @query_id = 1, @plan_id = 1