Real-time Monitoring Scripts

Real-time Monitoring Scripts

Essential scripts for monitoring Oracle database performance and activity in real-time.

Active Session Monitoring

Current Active Sessions

-- Real-time session activity
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.osuser,
    s.machine,
    s.program,
    s.status,
    s.sql_id,
    s.sql_child_number,
    s.event,
    s.wait_class,
    s.seconds_in_wait,
    s.state,
    ROUND(s.wait_time_micro/1000000, 2) as wait_time_sec,
    sq.sql_text
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
    AND s.sql_child_number = sq.child_number
WHERE s.status = 'ACTIVE'
    AND s.username IS NOT NULL
    AND s.username NOT IN ('SYS', 'SYSTEM')
ORDER BY s.seconds_in_wait DESC;

Session Wait Events

-- Current wait events grouped by wait class
SELECT
    wait_class,
    event,
    COUNT(*) as session_count,
    ROUND(AVG(seconds_in_wait), 2) as avg_wait_sec,
    ROUND(MAX(seconds_in_wait), 2) as max_wait_sec
FROM v$session
WHERE wait_class != 'Idle'
    AND status = 'ACTIVE'
GROUP BY wait_class, event
ORDER BY session_count DESC, avg_wait_sec DESC;

Blocking Sessions

-- Identify blocking and blocked sessions
WITH blocking_tree AS (
    SELECT
        LEVEL as lock_level,
        s.sid,
        s.serial#,
        s.username,
        s.program,
        s.event,
        s.seconds_in_wait,
        s.blocking_session,
        s.blocking_session_status,
        sq.sql_text
    FROM v$session s
    LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
    WHERE s.username IS NOT NULL
    CONNECT BY PRIOR s.sid = s.blocking_session
    START WITH s.blocking_session IS NULL
        AND s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
)
SELECT
    LPAD(' ', (lock_level-1)*2) ||
    CASE
        WHEN lock_level = 1 THEN 'BLOCKER: '
        ELSE 'BLOCKED: '
    END || sid as session_tree,
    serial#,
    username,
    program,
    event,
    seconds_in_wait,
    SUBSTR(sql_text, 1, 100) as sql_text
FROM blocking_tree
ORDER BY ROWNUM;

Real-time Performance Metrics

Database Performance Overview

-- Key performance indicators
WITH metrics AS (
    SELECT name, value FROM v$sysstat
    WHERE name IN (
        'user commits', 'user rollbacks',
        'physical reads', 'physical writes',
        'redo size', 'parse count (total)',
        'execute count', 'user calls'
    )
)
SELECT
    name as metric,
    value as total_value,
    ROUND(value / (SYSDATE - startup_time) / 86400, 2) as per_second
FROM metrics,
    (SELECT startup_time FROM v$instance)
ORDER BY name;

Real-time SQL Monitoring

-- Top SQL by current activity
SELECT * FROM (
    SELECT
        sql_id,
        sql_plan_hash_value,
        COUNT(*) as active_sessions,
        ROUND(AVG(elapsed_time/1000000), 2) as avg_elapsed_sec,
        SUM(executions) as total_executions,
        ROUND(SUM(cpu_time/1000000), 2) as total_cpu_sec,
        ROUND(SUM(user_io_wait_time/1000000), 2) as total_io_wait_sec,
        MAX(last_active_time) as last_active,
        SUBSTR(sql_text, 1, 100) as sql_text
    FROM v$sql sq
    WHERE EXISTS (
        SELECT 1 FROM v$session s
        WHERE s.sql_id = sq.sql_id
            AND s.status = 'ACTIVE'
    )
    GROUP BY sql_id, sql_plan_hash_value, SUBSTR(sql_text, 1, 100)
    ORDER BY active_sessions DESC, total_cpu_sec DESC
)
WHERE ROWNUM <= 10;

Memory Usage Monitoring

-- Current memory usage by component
SELECT
    component,
    ROUND(current_size/1024/1024, 2) as current_mb,
    ROUND(min_size/1024/1024, 2) as min_mb,
    ROUND(max_size/1024/1024, 2) as max_mb,
    ROUND(user_specified_size/1024/1024, 2) as specified_mb,
    oper_count as resize_ops,
    last_oper_type,
    last_oper_mode,
    TO_CHAR(last_oper_time, 'DD-MON HH24:MI:SS') as last_resize
FROM v$memory_dynamic_components
WHERE current_size > 0
ORDER BY current_size DESC;

-- PGA memory usage by process
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    ROUND(p.pga_used_mem/1024/1024, 2) as pga_used_mb,
    ROUND(p.pga_alloc_mem/1024/1024, 2) as pga_alloc_mb,
    ROUND(p.pga_max_mem/1024/1024, 2) as pga_max_mb
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.username IS NOT NULL
    AND p.pga_alloc_mem > 10485760  -- > 10MB
ORDER BY p.pga_alloc_mem DESC;

I/O Performance Monitoring

Real-time I/O Statistics

-- File I/O statistics
SELECT
    df.tablespace_name,
    df.file_name,
    fs.phyrds as physical_reads,
    fs.phywrts as physical_writes,
    fs.phyblkrd as blocks_read,
    fs.phyblkwrt as blocks_written,
    ROUND(fs.readtim * 10, 2) as read_time_ms,
    ROUND(fs.writetim * 10, 2) as write_time_ms,
    ROUND(fs.avgiotim * 10, 2) as avg_io_time_ms
FROM v$filestat fs
JOIN dba_data_files df ON fs.file# = df.file_id
WHERE fs.phyrds + fs.phywrts > 0
ORDER BY fs.phyrds + fs.phywrts DESC;

-- Tempfile I/O statistics
SELECT
    tf.tablespace_name,
    tf.file_name,
    ts.phyrds as physical_reads,
    ts.phywrts as physical_writes,
    ts.phyblkrd as blocks_read,
    ts.phyblkwrt as blocks_written,
    ROUND(ts.readtim * 10, 2) as read_time_ms,
    ROUND(ts.writetim * 10, 2) as write_time_ms
FROM v$tempstat ts
JOIN dba_temp_files tf ON ts.file# = tf.file_id
WHERE ts.phyrds + ts.phywrts > 0
ORDER BY ts.phyrds + ts.phywrts DESC;

ASM Disk Group Monitoring

-- ASM disk group usage (if using ASM)
SELECT
    name as diskgroup,
    state,
    type,
    total_mb,
    free_mb,
    ROUND((total_mb - free_mb) / total_mb * 100, 2) as used_pct,
    ROUND(free_mb / 1024, 2) as free_gb,
    offline_disks
FROM v$asm_diskgroup
ORDER BY used_pct DESC;

Transaction and Lock Monitoring

Active Transactions

-- Current transactions with details
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    t.start_time,
    t.used_ublk as undo_blocks,
    t.used_urec as undo_records,
    ROUND((SYSDATE - t.start_date) * 24 * 60, 2) as minutes_active,
    t.xidusn || '.' || t.xidslot || '.' || t.xidsqn as transaction_id,
    sq.sql_text
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
ORDER BY t.start_date;

Lock Monitoring

-- Current locks in the database
SELECT
    l.sid,
    s.serial#,
    s.username,
    s.program,
    DECODE(l.type,
        'TM', 'Table Lock',
        'TX', 'Row Lock',
        'UL', 'User Lock',
        l.type) as lock_type,
    DECODE(l.lmode,
        0, 'None',
        1, 'Null',
        2, 'Row-S (SS)',
        3, 'Row-X (SX)',
        4, 'Share (S)',
        5, 'S/Row-X (SSX)',
        6, 'Exclusive (X)') as lock_mode,
    DECODE(l.request,
        0, 'None',
        1, 'Null',
        2, 'Row-S (SS)',
        3, 'Row-X (SX)',
        4, 'Share (S)',
        5, 'S/Row-X (SSX)',
        6, 'Exclusive (X)') as request_mode,
    o.object_name,
    o.object_type,
    l.ctime as lock_time_sec
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
LEFT JOIN dba_objects o ON l.id1 = o.object_id
WHERE l.type IN ('TM', 'TX')
    AND s.username IS NOT NULL
ORDER BY l.ctime DESC;

Resource Usage Monitoring

Top Resource Consumers

-- Sessions consuming most resources
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    ss.value as cpu_used,
    si.physical_reads,
    si.logical_reads,
    si.block_changes,
    ROUND(ss.value / (SYSDATE - s.logon_time) / 86400, 2) as cpu_per_sec,
    ROUND(si.physical_reads / (SYSDATE - s.logon_time) / 86400, 2) as phys_reads_per_sec
FROM v$session s
JOIN v$sesstat ss ON s.sid = ss.sid
JOIN v$statname sn ON ss.statistic# = sn.statistic#
JOIN v$sess_io si ON s.sid = si.sid
WHERE sn.name = 'CPU used by this session'
    AND s.username IS NOT NULL
    AND s.status = 'ACTIVE'
ORDER BY ss.value DESC
FETCH FIRST 20 ROWS ONLY;

Parallel Query Monitoring

-- Monitor parallel query execution
SELECT
    qc.sid as qc_sid,
    qc.serial# as qc_serial,
    qc.username,
    px.qcsid,
    COUNT(*) as slave_count,
    px.server_group,
    px.server_set,
    px.degree,
    px.req_degree
FROM v$px_session px
JOIN v$session qc ON px.qcsid = qc.sid
WHERE px.sid != px.qcsid
GROUP BY qc.sid, qc.serial#, qc.username,
         px.qcsid, px.server_group, px.server_set,
         px.degree, px.req_degree
ORDER BY qc.sid;

Alert Log Monitoring

Recent Alert Log Entries

-- View recent alert log entries (12c+)
SELECT
    originating_timestamp,
    message_type,
    message_level,
    message_text,
    component_id,
    host_id
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' HOUR
    AND message_level <= 2  -- Critical and Severe
ORDER BY originating_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

Continuous Monitoring Script

-- PL/SQL block for continuous monitoring
DECLARE
    v_interval NUMBER := 5; -- seconds
    v_duration NUMBER := 60; -- total seconds to run
    v_iterations NUMBER := v_duration / v_interval;
BEGIN
    FOR i IN 1..v_iterations LOOP
        DBMS_OUTPUT.PUT_LINE('=== Monitoring Iteration ' || i || ' at ' ||
                           TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' ===');

        -- Active sessions
        FOR rec IN (
            SELECT COUNT(*) as active_sessions,
                   COUNT(DISTINCT username) as unique_users,
                   COUNT(CASE WHEN wait_class != 'Idle' THEN 1 END) as waiting_sessions
            FROM v$session
            WHERE status = 'ACTIVE' AND username IS NOT NULL
        ) LOOP
            DBMS_OUTPUT.PUT_LINE('Active Sessions: ' || rec.active_sessions ||
                               ', Users: ' || rec.unique_users ||
                               ', Waiting: ' || rec.waiting_sessions);
        END LOOP;

        -- Top wait events
        DBMS_OUTPUT.PUT_LINE('Top Wait Events:');
        FOR rec IN (
            SELECT wait_class, COUNT(*) as cnt
            FROM v$session
            WHERE wait_class NOT IN ('Idle', 'Network')
                AND status = 'ACTIVE'
            GROUP BY wait_class
            ORDER BY cnt DESC
            FETCH FIRST 3 ROWS ONLY
        ) LOOP
            DBMS_OUTPUT.PUT_LINE('  ' || rec.wait_class || ': ' || rec.cnt);
        END LOOP;

        DBMS_OUTPUT.PUT_LINE('');
        DBMS_LOCK.SLEEP(v_interval);
    END LOOP;
END;
/

Best Practices

1. Frequency: Adjust monitoring frequency based on system load
2. Retention: Archive monitoring data for trend analysis
3. Alerts: Set up automated alerts for critical metrics
4. Baselines: Establish performance baselines during normal operations
5. Visualization: Use tools like Grafana for real-time dashboards
6. Impact: Ensure monitoring queries don’t impact performance
7. Automation: Schedule regular monitoring reports