Query Profiling

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

-- Conceptual view of instrumentation flow during query execution
-- (This illustrates the internal process, not actual executable SQL)
 
/*
  Query Lifecycle with Profiling Instrumentation:
  
  ┌────────────────────────────────────────────────────────────────┐
  │                    CLIENT SENDS QUERY                          │
  └────────────────────────────────────────────────────────────────┘
                               │
                               ▼
  ┌────────────────────────────────────────────────────────────────┐
  │  PHASE 1: PARSING                                              │
  │  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ │
  │  📊 Instrumentation:                                           │
  │     • parse_start_time = NOW()                                 │
  │     • token_count++                                            │
  │     • syntax_tree_nodes++                                      │
  │     • parse_end_time = NOW()                                   │
  │  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ │
  └────────────────────────────────────────────────────────────────┘
                               │
                               ▼
  ┌────────────────────────────────────────────────────────────────┐
  │  PHASE 2: OPTIMIZATION                                         │
  │  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ │
  │  📊 Instrumentation:                                           │
  │     • plans_evaluated++                                        │
  │     • cardinality_estimates[]                                  │
  │     • cost_calculations[]                                      │
  │     • optimization_elapsed_ms                                  │
  │  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ │
  └────────────────────────────────────────────────────────────────┘
                               │
                               ▼
  ┌────────────────────────────────────────────────────────────────┐
  │  PHASE 3: EXECUTION                                            │
  │  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ │
  │  📊 Instrumentation (per operator):                            │
  │     • rows_examined++                                          │
  │     • rows_returned++                                          │
  │     • buffer_reads++                                           │
  │     • buffer_hits++                                            │
  │     • temp_space_used++                                        │
  │     • operator_elapsed_ns                                      │
  │  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ │
  └────────────────────────────────────────────────────────────────┘
                               │
                               ▼
  ┌────────────────────────────────────────────────────────────────┐
  │  PHASE 4: RESULT TRANSMISSION                                  │
  │  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ │
  │  📊 Instrumentation:                                           │
  │     • bytes_sent++                                             │
  │     • network_round_trips++                                    │
  │     • client_wait_time_ms                                      │
  │  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ │
  └────────────────────────────────────────────────────────────────┘
*/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

-- ================================================================
-- MySQL Performance Schema: Core Profiling Infrastructure
-- ================================================================
 
-- Check if Performance Schema is enabled
SHOW VARIABLES LIKE 'performance_schema';
 
-- Result: Should show 'ON' for profiling capabilities
 
-- ----------------------------------------------------------------
-- Configure statement instrumentation
-- ----------------------------------------------------------------
 
-- Enable statement event consumption
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_statements%';
 
-- Enable statement instrumentation
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';
 
-- ----------------------------------------------------------------
-- Query recently executed statements with timing
-- ----------------------------------------------------------------
 
-- View current session's last statement details
SELECT
    THREAD_ID,
    EVENT_ID,
    SQL_TEXT,
    DIGEST_TEXT,
    TIMER_WAIT / 1000000000 AS elapsed_seconds,
    TIMER_START / 1000000000 AS start_seconds,
    LOCK_TIME / 1000000000 AS lock_seconds,
    ROWS_EXAMINED,
    ROWS_SENT,
    ROWS_AFFECTED,
    CREATED_TMP_TABLES,
    CREATED_TMP_DISK_TABLES,
    SELECT_FULL_JOIN,
    SELECT_SCAN,
    SORT_ROWS,
    NO_INDEX_USED,
    NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_current
WHERE THREAD_ID = (
    SELECT THREAD_ID 
    FROM performance_schema.threads 
    WHERE PROCESSLIST_ID = CONNECTION_ID()
);
 
-- View statement history for current thread
SELECT
    SQL_TEXT,
    TIMER_WAIT / 1000000000000 AS elapsed_ms,
    ROWS_EXAMINED,
    ROWS_SENT,
    CREATED_TMP_TABLES,
    CREATED_TMP_DISK_TABLES,
    NO_INDEX_USED
FROM performance_schema.events_statements_history
WHERE THREAD_ID = (
    SELECT THREAD_ID 
    FROM performance_schema.threads 
    WHERE PROCESSLIST_ID = CONNECTION_ID()
)
ORDER BY EVENT_ID DESC
LIMIT 10;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

-- ================================================================
-- Stage-Level Profiling: Understanding Execution Phases
-- ================================================================
 
-- Enable stage instrumentation for detailed phase timing
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'stage/%';
 
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_stages%';
 
-- Execute a query to profile
 
-- Then view stage breakdown for the statement
SELECT
    EVENT_NAME AS stage_name,
    TIMER_WAIT / 1000000000 AS duration_ms,
    NESTING_EVENT_ID
FROM performance_schema.events_stages_history
WHERE NESTING_EVENT_ID = (
    SELECT EVENT_ID
    FROM performance_schema.events_statements_history
    WHERE THREAD_ID = (
        SELECT THREAD_ID 
        FROM performance_schema.threads 
        WHERE PROCESSLIST_ID = CONNECTION_ID()
    )
    ORDER BY EVENT_ID DESC
    LIMIT 1
)
ORDER BY EVENT_ID;
 
-- Sample output understanding:
-- ┌──────────────────────────────────────┬─────────────┐
-- │ stage_name                           │ duration_ms │
-- ├──────────────────────────────────────┼─────────────┤
-- │ stage/sql/starting                   │ 0.025       │
-- │ stage/sql/checking permissions       │ 0.008       │
-- │ stage/sql/Opening tables             │ 0.145       │
-- │ stage/sql/init                       │ 0.012       │
-- │ stage/sql/System lock                │ 0.005       │
-- │ stage/sql/optimizing                 │ 0.089       │
-- │ stage/sql/statistics                 │ 12.450      │ ← Cardinality estimation
-- │ stage/sql/preparing                  │ 0.034       │
-- │ stage/sql/executing                  │ 847.230     │ ← Actual row processing
-- │ stage/sql/Sending data               │ 2.340       │
-- │ stage/sql/end                        │ 0.003       │
-- │ stage/sql/query end                  │ 0.004       │
-- │ stage/sql/closing tables             │ 0.018       │
-- │ stage/sql/freeing items              │ 0.012       │
-- │ stage/sql/cleaning up                │ 0.002       │
-- └──────────────────────────────────────┴─────────────┘
 
-- ================================================================
-- Sys Schema: Simplified Performance Analysis Views
-- ================================================================
 
-- The sys schema provides human-readable views over Performance Schema
 
-- Top queries by total execution time
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;
 
-- Queries with full table scans
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY exec_count DESC
LIMIT 10;
 
-- Queries sorted by total latency
SELECT 
    query,
    db,
    exec_count,
    total_latency,
    avg_latency,
    rows_sent_avg,
    rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;
 
-- View I/O statistics per table
SELECT * FROM sys.schema_table_statistics
WHERE table_schema = 'your_database'
ORDER BY total_latency DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124

-- ================================================================
-- PostgreSQL EXPLAIN ANALYZE: Integrated Query Profiling
-- ================================================================
 
-- Basic EXPLAIN ANALYZE: Plan + Actual Execution Metrics
EXPLAIN ANALYZE
SELECT 
    c.customer_name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.created_at >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) > 5
ORDER BY total_spent DESC
LIMIT 100;
 
/*
Output with ANALYZE includes actual timing:
 
Hash Join  (cost=312.50..1245.80 rows=500 width=68)
           (actual time=15.234..89.456 rows=487 loops=1)
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                    ACTUAL execution metrics
                    
  Hash Cond: (o.customer_id = c.customer_id)
  -> Seq Scan on orders o  (cost=0.00..850.00 rows=25000 width=24)
                           (actual time=0.015..35.234 rows=24856 loops=1)
                                              ^^^^^^^^^^^^^^^^^^^^^^
                                              Actual rows vs estimated
  -> Hash  (cost=250.00..250.00 rows=5000 width=44)
           (actual time=12.890..12.890 rows=4892 loops=1)
     -> Seq Scan on customers c  (cost=0.00..250.00 rows=5000 width=44)
                                 (actual time=0.008..8.234 rows=4892 loops=1)
           Filter: (created_at >= (CURRENT_DATE - '1 year'::interval))
           Rows Removed by Filter: 1108
                                   ^^^^
                                   Rows filtered (not returned)
*/
 
-- ================================================================
-- EXPLAIN Options for Comprehensive Profiling
-- ================================================================
 
-- Full profiling with all available metrics
EXPLAIN (
    ANALYZE,     -- Actually execute the query (required for actual metrics)
    BUFFERS,     -- Show buffer/page access statistics
    TIMING,      -- Show timing information (can be disabled for less overhead)
    VERBOSE,     -- Show additional detail (output columns, schema names)
    FORMAT JSON  -- Machine-parseable output format
)
SELECT product_id, SUM(quantity) 
FROM order_items 
GROUP BY product_id
HAVING SUM(quantity) > 100;
 
/*
Key metrics from BUFFERS option:
 
Seq Scan on order_items  (cost=0.00..4000.00 rows=200000 width=12)
                         (actual time=0.015..125.890 rows=198500 loops=1)
  Buffers: shared hit=1234 read=890
                 ^^^^^^^^      ^^^^
           Cache hits    Physical disk reads
           (fast)        (slow - disk I/O)
 
When shared read >> shared hit: Table not cached, I/O bottleneck
When shared hit >> shared read: Well-cached, CPU-bound execution
*/
 
-- ================================================================
-- Understanding Key Metrics in EXPLAIN ANALYZE Output
-- ================================================================
 
/*
CRITICAL METRICS TO ANALYZE:
 
1. ACTUAL TIME (startup..total)
   - startup: Time to return first row
   - total: Time to return all rows
   - "actual time=0.015..125.890" means 0.015ms to first row, 125ms total
   
2. ROWS (actual vs estimated)
   - Significant mismatch indicates stale statistics or complex predicates
   - "rows=5000" (estimated) vs "rows=4892" (actual) = good estimate
   - "rows=100" (estimated) vs "rows=45000" (actual) = BAD, reanalyze table
   
3. LOOPS
   - Number of times operator executed (nested loop iterations)
   - Total time = (actual time) × loops
   - "loops=1000" with "actual time=0.5ms" = 500ms total contribution
   
4. BUFFERS
   - shared hit: Pages read from buffer cache (fast)
   - shared read: Pages read from disk (slow)
   - temp read/written: Pages in temporary files (very slow)
   
5. ROWS REMOVED BY FILTER
   - Rows examined but not returned
   - High value suggests potential index opportunity
*/
 
-- Practical example with buffer analysis
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT * FROM large_table 
WHERE indexed_column = 42 AND unindexed_column = 'value';
 
/*
Output analysis:
Index Scan using idx_indexed_column on large_table
  (actual time=0.045..125.890 rows=50 loops=1)
  Index Cond: (indexed_column = 42)
  Filter: (unindexed_column = 'value')
  Rows Removed by Filter: 9950
                          ^^^^
  High filter removal = index found 10000 rows, but only 50 matched
  SOLUTION: Consider composite index (indexed_column, unindexed_column)
  
  Buffers: shared hit=150 read=25
           ^^^^^^^^^^^^^^^^
           175 total pages accessed; 25 required disk I/O
*/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

-- ================================================================
-- pg_stat_statements: Workload-Level Query Profiling
-- ================================================================
 
-- Enable the extension (requires superuser, typically in postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- View parameters controlling statistics collection
SHOW pg_stat_statements.max;        -- Maximum tracked statements
SHOW pg_stat_statements.track;      -- What to track: top, all, none
 
-- ================================================================
-- Finding Resource-Intensive Queries
-- ================================================================
 
-- Top queries by total execution time
SELECT 
    calls,
    total_exec_time::NUMERIC(12,2) AS total_time_ms,
    mean_exec_time::NUMERIC(12,2) AS avg_time_ms,
    stddev_exec_time::NUMERIC(12,2) AS stddev_ms,
    rows,
    shared_blks_hit,
    shared_blks_read,
    ROUND(100.0 * shared_blks_hit / 
          NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct,
    LEFT(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
 
-- Top queries by I/O (disk reads)
SELECT 
    calls,
    shared_blks_read AS disk_reads,
    shared_blks_hit AS cache_hits,
    rows,
    total_exec_time::NUMERIC(10,2) AS total_ms,
    LEFT(query, 100) AS query_preview
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 20;
 
-- Queries with high variability (inconsistent performance)
SELECT 
    calls,
    mean_exec_time::NUMERIC(10,2) AS avg_ms,
    stddev_exec_time::NUMERIC(10,2) AS stddev_ms,
    min_exec_time::NUMERIC(10,2) AS min_ms,
    max_exec_time::NUMERIC(10,2) AS max_ms,
    (max_exec_time / NULLIF(min_exec_time, 0))::NUMERIC(10,1) AS variance_ratio,
    LEFT(query, 80) AS query_preview
FROM pg_stat_statements
WHERE calls > 100  -- Sufficient sample size
  AND mean_exec_time > 10  -- Non-trivial queries
ORDER BY stddev_exec_time DESC
LIMIT 20;
 
-- Reset statistics (typically after optimization round)
SELECT pg_stat_statements_reset();
 
-- ================================================================
-- Normalized Query Analysis
-- ================================================================
 
/*
pg_stat_statements normalizes queries by replacing literal values:
 
Original queries:
  SELECT * FROM users WHERE id = 123;
  SELECT * FROM users WHERE id = 456;
  SELECT * FROM users WHERE id = 789;
 
Normalized as:
  SELECT * FROM users WHERE id = $1;
 
This groups equivalent queries for aggregate analysis,
revealing which query PATTERNS consume resources—
not just which specific parameter values are slow.
*/
 
-- View query patterns with parameter placeholders
SELECT queryid, query, calls, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%users%'
ORDER BY total_exec_time DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

-- ================================================================
-- SQL Server Extended Events: Modern Query Profiling
-- ================================================================
 
-- Create an Extended Events session for query profiling
CREATE EVENT SESSION QueryProfiling ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.query_hash,
        sqlserver.query_plan_hash,
        sqlserver.database_name,
        sqlserver.username,
        sqlserver.client_app_name
    )
    WHERE (
        duration > 1000000  -- Queries taking > 1 second (microseconds)
        AND database_name = N'YourDatabase'
    )
),
ADD EVENT sqlserver.rpc_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.query_hash,
        sqlserver.database_name
    )
    WHERE (
        duration > 1000000
    )
)
ADD TARGET package0.ring_buffer (
    SET max_memory = 4096  -- KB
)
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE = OFF
);
 
-- Start the session
ALTER EVENT SESSION QueryProfiling ON SERVER STATE = START;
 
-- View captured events
SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_time,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000000.0 AS duration_seconds,
    event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') / 1000000.0 AS cpu_seconds,
    event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
    event_data.value('(event/data[@name="physical_reads"]/value)[1]', 'bigint') AS physical_reads,
    event_data.value('(event/data[@name="writes"]/value)[1]', 'bigint') AS writes,
    event_data.value('(event/data[@name="row_count"]/value)[1]', 'bigint') AS row_count,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM (
    SELECT CAST(target_data AS XML) AS target_xml
    FROM sys.dm_xe_session_targets xst
    JOIN sys.dm_xe_sessions xs ON xs.address = xst.event_session_address
    WHERE xs.name = 'QueryProfiling'
) AS raw_data
CROSS APPLY target_xml.nodes('RingBufferTarget/event') AS events(event_data)
ORDER BY event_time DESC;
 
-- Stop and drop session when done
ALTER EVENT SESSION QueryProfiling ON SERVER STATE = STOP;
DROP EVENT SESSION QueryProfiling ON SERVER;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

-- ================================================================
-- SQL Server DMV-Based Query Profiling
-- ================================================================
 
-- ----------------------------------------------------------------
-- sys.dm_exec_query_stats: Aggregated Query Statistics
-- ----------------------------------------------------------------
 
-- Top queries by total CPU time
SELECT TOP 20
    qs.total_worker_time / 1000000.0 AS total_cpu_seconds,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000.0 AS avg_cpu_ms,
    qs.total_elapsed_time / 1000000.0 AS total_elapsed_seconds,
    qs.total_logical_reads,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_physical_reads,
    qs.last_execution_time,
    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 query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;
 
-- Top queries by logical reads (memory pressure indicator)
SELECT TOP 20
    qs.total_logical_reads,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_reads_per_exec,
    qs.total_worker_time / 1000000.0 AS total_cpu_sec,
    qs.total_elapsed_time / 1000000.0 AS total_elapsed_sec,
    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 query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_logical_reads DESC;
 
-- ----------------------------------------------------------------
-- sys.dm_exec_requests: Currently Executing Queries
-- ----------------------------------------------------------------
 
-- View all currently executing queries with performance metrics
SELECT 
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.total_elapsed_time / 1000.0 AS elapsed_seconds,
    r.logical_reads,
    r.reads AS physical_reads,
    r.writes,
    r.row_count,
    r.percent_complete,
    s.login_name,
    s.host_name,
    s.program_name,
    db.name AS database_name,
    SUBSTRING(st.text, 
        (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset 
            WHEN -1 THEN DATALENGTH(st.text) 
            ELSE r.statement_end_offset 
         END - r.statement_start_offset) / 2) + 1
    ) AS current_statement,
    qp.query_plan
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT JOIN sys.databases db ON r.database_id = db.database_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.session_id > 50  -- Exclude system sessions
  AND r.session_id != @@SPID  -- Exclude current session
ORDER BY r.total_elapsed_time DESC;
 
-- ----------------------------------------------------------------
-- Query Store: Historical Query Performance (SQL Server 2016+)
-- ----------------------------------------------------------------
 
-- Enable Query Store on a database
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO
);
 
-- Top resource-consuming queries from Query Store
SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_executions,
    SUM(rs.avg_duration * rs.count_executions) / 1000000.0 AS total_duration_sec,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
    SUM(rs.avg_cpu_time * rs.count_executions) / 1000000.0 AS total_cpu_sec,
    SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_reads,
    AVG(rs.avg_logical_io_reads) AS avg_logical_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_cpu_sec DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151

-- ================================================================
-- Oracle Database: Comprehensive Profiling Infrastructure
-- ================================================================
 
-- ----------------------------------------------------------------
-- V$SQL: Real-Time SQL Statistics
-- ----------------------------------------------------------------
 
-- Top SQL by CPU consumption
SELECT 
    sql_id,
    child_number,
    plan_hash_value,
    executions,
    ROUND(cpu_time / 1000000, 2) AS cpu_seconds,
    ROUND(elapsed_time / 1000000, 2) AS elapsed_seconds,
    buffer_gets,
    disk_reads,
    rows_processed,
    ROUND(buffer_gets / NULLIF(executions, 0)) AS buffer_gets_per_exec,
    ROUND(cpu_time / NULLIF(executions, 0) / 1000) AS cpu_ms_per_exec,
    SUBSTR(sql_text, 1, 200) AS sql_preview
FROM v$sql
WHERE executions > 0
ORDER BY cpu_time DESC
FETCH FIRST 20 ROWS ONLY;
 
-- ----------------------------------------------------------------
-- V$SQL_PLAN_STATISTICS_ALL: Operator-Level Profiling
-- ----------------------------------------------------------------
 
-- View actual row counts vs estimates per plan operator
-- (Requires STATISTICS_LEVEL=ALL or gather_plan_statistics hint)
SELECT 
    id,
    parent_id,
    operation || ' ' || options AS operation,
    object_name,
    cardinality AS estimated_rows,
    output_rows AS actual_rows,
    CASE 
        WHEN output_rows > cardinality * 10 THEN '⚠️ UNDERESTIMATE'
        WHEN output_rows < cardinality / 10 THEN '⚠️ OVERESTIMATE'
        ELSE '✓'
    END AS estimate_accuracy,
    ROUND(elapsed_time / 1000) AS elapsed_ms,
    starts,
    buffer_gets,
    disk_reads
FROM v$sql_plan_statistics_all
WHERE sql_id = '&sql_id'
  AND child_number = 0
ORDER BY id;
 
-- ----------------------------------------------------------------
-- DBMS_XPLAN: Display Execution Plan with Statistics
-- ----------------------------------------------------------------
 
-- Explain plan with actual statistics
SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        sql_id => '&sql_id',
        cursor_child_no => 0,
        format => 'ALLSTATS LAST OUTLINE +PREDICATE +COST'
    )
);
 
/*
Sample DBMS_XPLAN output with statistics:
 
----------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows | Buffers |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |    100 |    2500 |
|   1 |  SORT ORDER BY     |        |      1 |    100 |    100 |    2500 |
|*  2 |   HASH JOIN        |        |      1 |    100 |    100 |    2500 |
|   3 |    TABLE ACCESS... | CUST   |      1 |   1000 |   1000 |     250 |
|*  4 |    TABLE ACCESS... | ORDERS |      1 |   100K | 100000 |    2250 |
----------------------------------------------------------------------
 
Key columns:
- Starts: Times operator invoked
- E-Rows: Estimated rows (optimizer prediction)
- A-Rows: Actual rows (measured)
- Buffers: Buffer gets (logical I/O)
*/
 
-- ----------------------------------------------------------------
-- Active Session History (ASH): Session-Level Wait Analysis
-- ----------------------------------------------------------------
 
-- Find what a slow session was doing
SELECT 
    sample_time,
    session_id,
    session_serial#,
    event,
    wait_class,
    sql_id,
    sql_opname,
    ROUND(time_waited / 1000) AS wait_ms,
    blocking_session,
    module,
    action
FROM v$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '30' MINUTE
  AND session_id = &session_id
ORDER BY sample_time DESC;
 
-- Top wait events across all sessions
SELECT 
    event,
    wait_class,
    COUNT(*) AS sample_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS pct_of_db_time,
    MIN(sample_time) AS first_seen,
    MAX(sample_time) AS last_seen
FROM v$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '1' HOUR
  AND wait_class != 'Idle'
GROUP BY event, wait_class
ORDER BY sample_count DESC
FETCH FIRST 20 ROWS ONLY;
 
-- ----------------------------------------------------------------
-- SQL Monitoring: Real-Time Operations Profiling
-- ----------------------------------------------------------------
 
-- View actively monitored long-running operations
SELECT 
    sql_id,
    status,
    sid,
    sql_text,
    ROUND(elapsed_time / 1000000, 2) AS elapsed_sec,
    ROUND(cpu_time / 1000000, 2) AS cpu_sec,
    buffer_gets,
    disk_reads,
    ROUND(buffer_gets / NULLIF(disk_reads, 0), 0) AS hit_ratio
FROM v$sql_monitor
WHERE status IN ('EXECUTING', 'DONE')
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;
 
-- Generate detailed SQL Monitor report
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
    sql_id => '&sql_id',
    type => 'HTML',
    report_level => 'ALL'  -- BASIC, TYPICAL, ALL
) AS report
FROM dual;