Performance Monitoring

Session monitoring and performance analysis (Access: DBA or performance views)

-- Active sessions
-- Access: DBA or users with DBA_SESSIONS privilege
SELECT 
    sid,
    serial#,
    username,
    program,
    machine,
    status,
    logon_time,
    last_call_et/60 AS "Idle (min)"
FROM v$session
WHERE status = 'ACTIVE'
ORDER BY last_call_et;

-- Top SQL by CPU usage
-- Access: DBA or users with DBA_HIST_SQLSTAT privilege
SELECT 
    sql_id,
    sql_text,
    executions,
    cpu_time/1000000 AS "CPU Time (sec)",
    elapsed_time/1000000 AS "Elapsed Time (sec)",
    buffer_gets,
    disk_reads
FROM v$sql
WHERE cpu_time > 0
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

-- Session wait events
-- Access: DBA or users with DBA_SESSION_WAITS privilege
SELECT 
    s.sid,
    s.username,
    w.event,
    w.wait_time,
    w.seconds_in_wait,
    w.state
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND s.status = 'ACTIVE'
ORDER BY w.seconds_in_wait DESC;

-- Long running queries
-- Access: DBA or users with DBA_SESSIONS privilege
SELECT 
    sid,
    serial#,
    username,
    sql_id,
    last_call_et/60 AS "Running (min)",
    sql_text
FROM v$session s, v$sql q
WHERE s.sql_id = q.sql_id
AND s.status = 'ACTIVE'
AND s.last_call_et > 300
ORDER BY s.last_call_et DESC;

Database performance metrics and statistics (Access: DBA or performance views)

-- Database performance statistics
-- Access: DBA or users with DBA_HIST_SYSMETRIC_SUMMARY privilege
SELECT 
    metric_name,
    value,
    unit
FROM v$sysmetric
WHERE metric_name IN (
    'Database CPU Time Ratio',
    'Database Wait Time Ratio',
    'SQL Service Response Time',
    'User Commits Per Sec',
    'User Rollbacks Per Sec'
)
ORDER BY metric_name;

-- Buffer cache hit ratio
-- Access: DBA or users with DBA_HIST_SYSMETRIC_SUMMARY privilege
SELECT 
    (1 - (physical_reads / (db_block_gets + consistent_gets))) * 100 AS "Buffer Cache Hit Ratio %"
FROM v$buffer_pool_statistics;

-- Library cache hit ratio
-- Access: DBA or users with DBA_HIST_LIBRARYCACHE privilege
SELECT 
    namespace,
    gets,
    gethits,
    ROUND(gethitratio * 100, 2) AS "Hit Ratio %"
FROM v$librarycache
ORDER BY namespace;

-- Top wait events
-- Access: DBA or users with DBA_HIST_SYSTEM_EVENT privilege
SELECT 
    event,
    total_waits,
    total_timeouts,
    time_waited/100 AS "Time Waited (sec)",
    average_wait/100 AS "Average Wait (sec)"
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

-- Database size and growth
-- Access: DBA or users with DBA_DATA_FILES privilege
SELECT 
    tablespace_name,
    SUM(bytes)/1024/1024/1024 AS "Size (GB)",
    COUNT(*) AS "Files"
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY SUM(bytes) DESC;

Comprehensive SQL performance analysis with detailed explanations and tuning recommendations (Access: DBA or performance views)

-- SQL PERFORMANCE ANALYSIS - COMPREHENSIVE GUIDE
-- ============================================
-- This guide shows how to analyze SQL performance, identify bottlenecks,
-- and optimize query execution

-- 1. TOP SQL BY EXECUTION TIME
-- ============================
-- Identifies the most time-consuming SQL statements
-- Access: DBA or users with DBA_HIST_SQLSTAT privilege
SELECT 
    sql_id,                   -- Unique SQL identifier
    sql_text,                 -- SQL statement text (truncated)
    executions,               -- Number of times executed
    elapsed_time/1000000 AS "Total Elapsed (sec)",  -- Total execution time
    cpu_time/1000000 AS "Total CPU (sec)",         -- Total CPU time
    (elapsed_time/1000000)/executions AS "Avg Elapsed (sec)",  -- Average execution time
    buffer_gets,              -- Number of buffer gets (logical reads)
    disk_reads,               -- Number of disk reads (physical reads)
    rows_processed,           -- Number of rows processed
    -- Calculate efficiency metrics
    ROUND((buffer_gets/executions), 2) AS "Avg Buffer Gets",
    ROUND((disk_reads/executions), 2) AS "Avg Disk Reads",
    ROUND((rows_processed/executions), 2) AS "Avg Rows Processed"
FROM v$sql
WHERE executions > 0
AND elapsed_time > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

-- 2. SQL EXECUTION PLANS - UNDERSTANDING THE PLAN
-- ==============================================
-- Execution plans show how Oracle executes a SQL statement
-- Access: DBA or users with DBA_HIST_SQL_PLAN privilege

-- Get execution plan for specific SQL ID
-- Replace '&sql_id' with actual SQL ID
SELECT 
    plan_table_output
FROM table(dbms_xplan.display_cursor('&sql_id', null, 'ALLSTATS LAST'));

-- Explanation of execution plan components:
-- - COST: Estimated cost of operation (lower is better)
-- - CARDINALITY: Estimated number of rows
-- - BYTES: Estimated bytes to be processed
-- - TIME: Estimated execution time
-- - Operation types:
--   * TABLE ACCESS FULL: Full table scan (usually expensive)
--   * INDEX RANGE SCAN: Index scan (usually efficient)
--   * NESTED LOOPS: Join method (good for small result sets)
--   * HASH JOIN: Join method (good for large result sets)
--   * SORT: Sorting operation (may be expensive)

-- 3. SQL STATISTICS ANALYSIS
-- ==========================
-- Detailed statistics for specific SQL statement
-- Access: DBA or users with DBA_HIST_SQLSTAT privilege
SELECT 
    sql_id,                   -- SQL identifier
    plan_hash_value,          -- Execution plan hash
    executions,               -- Number of executions
    elapsed_time/1000000 AS "Elapsed Time (sec)",    -- Total elapsed time
    cpu_time/1000000 AS "CPU Time (sec)",            -- Total CPU time
    buffer_gets,              -- Total buffer gets
    disk_reads,               -- Total disk reads
    rows_processed,           -- Total rows processed
    -- Calculate ratios and efficiency
    ROUND(buffer_gets/executions, 2) AS "Avg Buffer Gets Per Execution",
    ROUND(disk_reads/executions, 2) AS "Avg Disk Reads Per Execution",
    ROUND(rows_processed/executions, 2) AS "Avg Rows Per Execution",
    -- Calculate hit ratios
    ROUND((1 - (disk_reads/NULLIF(buffer_gets, 0))) * 100, 2) AS "Buffer Hit Ratio %"
FROM v$sql
WHERE sql_id = '&sql_id'  -- Replace with actual SQL ID
AND executions > 0;

-- 4. SQL TEXT RETRIEVAL
-- ====================
-- Get full SQL text for analysis
-- Access: DBA or users with DBA_HIST_SQLTEXT privilege
SELECT 
    sql_id,                   -- SQL identifier
    sql_text                  -- Complete SQL statement
FROM v$sql
WHERE sql_id = '&sql_id';    -- Replace with actual SQL ID

-- 5. SQL EXECUTION HISTORY
-- =======================
-- Historical performance data for SQL statement
-- Access: DBA or users with DBA_HIST_SQLSTAT privilege
SELECT 
    sql_id,                   -- SQL identifier
    snap_id,                  -- AWR snapshot ID
    executions_delta,         -- Executions in this snapshot
    elapsed_time_delta/1000000 AS "Elapsed Time (sec)",  -- Elapsed time in snapshot
    cpu_time_delta/1000000 AS "CPU Time (sec)",          -- CPU time in snapshot
    buffer_gets_delta,        -- Buffer gets in snapshot
    disk_reads_delta,          -- Disk reads in snapshot
    -- Calculate averages for this snapshot
    ROUND(elapsed_time_delta/1000000/NULLIF(executions_delta, 0), 4) AS "Avg Elapsed (sec)",
    ROUND(cpu_time_delta/1000000/NULLIF(executions_delta, 0), 4) AS "Avg CPU (sec)"
FROM dba_hist_sqlstat
WHERE sql_id = '&sql_id'     -- Replace with actual SQL ID
ORDER BY snap_id DESC;

-- 6. PERFORMANCE TUNING RECOMMENDATIONS
-- ====================================
-- Based on the analysis above, here are common tuning strategies:

-- For high buffer gets:
-- - Add appropriate indexes
-- - Optimize WHERE clauses
-- - Use proper join methods

-- For high disk reads:
-- - Increase buffer cache size
-- - Optimize queries to use indexes
-- - Consider partitioning large tables

-- For high elapsed time:
-- - Analyze execution plan
-- - Consider query rewrite
-- - Optimize table statistics

-- For high CPU time:
-- - Simplify complex calculations
-- - Use appropriate data types
-- - Consider function-based indexes

-- 7. INDEX USAGE ANALYSIS
-- ======================
-- Check if indexes are being used effectively
SELECT 
    i.index_name,             -- Index name
    i.table_name,             -- Table name
    i.column_name,            -- Column name
    s.used,                   -- Whether index is used
    s.startup_time,           -- When statistics were collected
    s.usage_count            -- How many times index was used
FROM dba_indexes i, v$object_usage s
WHERE i.index_name = s.index_name
AND i.owner = s.owner
ORDER BY s.usage_count DESC;

-- 8. QUERY OPTIMIZATION CHECKLIST
-- ===============================
-- 1. Ensure statistics are up to date
-- 2. Use appropriate indexes
-- 3. Write efficient WHERE clauses
-- 4. Use proper join methods
-- 5. Avoid unnecessary functions in WHERE clauses
-- 6. Use appropriate data types
-- 7. Consider partitioning for large tables
-- 8. Monitor and analyze execution plans regularly