System Monitoring

System performance statistics and monitoring (Access: DBA or performance views)

-- Database statistics
-- Access: DBA or users with DBA_HIST_SYSMETRIC_SUMMARY privilege
SELECT 
    metric_name,
    value,
    unit,
    time_interval
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',
    'Physical Reads Per Sec',
    'Physical Writes Per Sec'
)
ORDER BY metric_name;

-- Instance statistics
-- Access: DBA or users with DBA_HIST_SYSTEM_EVENT privilege
SELECT 
    statistic_name,
    value
FROM v$sysstat
WHERE statistic_name IN (
    'CPU used by this session',
    'CPU used when call started',
    'db block gets',
    'consistent gets',
    'physical reads',
    'physical writes',
    'redo entries',
    'redo size'
)
ORDER BY value DESC;

-- Session statistics
-- Access: DBA or users with DBA_SESSION_STATISTICS privilege
SELECT 
    s.sid,
    s.username,
    s.program,
    st.statistic_name,
    st.value
FROM v$session s, v$sesstat st, v$statname sn
WHERE s.sid = st.sid
AND st.statistic# = sn.statistic#
AND sn.statistic_name IN ('CPU used by this session', 'db block gets', 'consistent gets')
ORDER BY s.sid, st.value DESC;

-- Buffer pool statistics
-- Access: DBA or users with DBA_HIST_BUFFER_POOL_STATISTICS privilege
SELECT 
    name,
    block_size,
    buffers,
    target_buffers,
    prev_buffers,
    db_block_gets,
    db_block_gets + consistent_gets AS total_gets,
    physical_reads,
    ROUND((1 - (physical_reads / (db_block_gets + consistent_gets))) * 100, 2) AS hit_ratio
FROM v$buffer_pool_statistics
ORDER BY name;

-- Library cache statistics
-- Access: DBA or users with DBA_HIST_LIBRARYCACHE privilege
SELECT 
    namespace,
    gets,
    gethits,
    pins,
    pinhits,
    reloads,
    invalidations,
    ROUND(gethitratio * 100, 2) AS get_hit_ratio,
    ROUND(pinhitratio * 100, 2) AS pin_hit_ratio
FROM v$librarycache
ORDER BY namespace;

Database maintenance and statistics operations (Access: DBA or maintenance privileges)

-- Gather table statistics
-- Access: Users with ANALYZE privilege on table
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- Gather schema statistics
-- Access: Users with ANALYZE privilege on schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

-- Gather database statistics
-- Access: DBA or users with ANALYZE privilege
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

-- Gather system statistics
-- Access: DBA or users with ANALYZE privilege
EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

-- Update table statistics
-- Access: Users with ANALYZE privilege on table
EXEC DBMS_STATS.SET_TABLE_STATS('HR', 'EMPLOYEES', numrows => 1000, numblks => 100);

-- Lock table statistics
-- Access: Users with ANALYZE privilege on table
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- Unlock table statistics
-- Access: Users with ANALYZE privilege on table
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- Purge recyclebin
-- Access: Users with PURGE privilege
PURGE RECYCLEBIN;

-- Purge specific object
-- Access: Users with PURGE privilege
PURGE TABLE employees;

-- Purge user recyclebin
-- Access: Users with PURGE privilege
PURGE USER_RECYCLEBIN;

-- Shrink table
-- Access: Users with ALTER TABLE privilege
ALTER TABLE employees ENABLE ROW MOVEMENT;
ALTER TABLE employees SHRINK SPACE;

-- Shrink table with cascade
-- Access: Users with ALTER TABLE privilege
ALTER TABLE employees SHRINK SPACE CASCADE;

-- Rebuild table
-- Access: Users with ALTER TABLE privilege
ALTER TABLE employees MOVE TABLESPACE users_new;

-- Rebuild table with compression
-- Access: Users with ALTER TABLE privilege
ALTER TABLE employees MOVE COMPRESS;

-- Analyze table
-- Access: Users with ANALYZE privilege on table
ANALYZE TABLE employees COMPUTE STATISTICS;

-- Analyze table for structure
-- Access: Users with ANALYZE privilege on table
ANALYZE TABLE employees VALIDATE STRUCTURE;

-- Analyze table for chained rows
-- Access: Users with ANALYZE privilege on table
ANALYZE TABLE employees LIST CHAINED ROWS;