Oracle Performance Tuning Scripts: Real-World Scenarios for CDB and Single Instance

-- [[email protected] ~] as sysdba
-- Step 1: Find the AWR snapshot IDs covering 09:00–09:30
SELECT snap_id,
       TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
       TO_CHAR(end_interval_time,   'YYYY-MM-DD HH24:MI') AS end_time
FROM dba_hist_snapshot
WHERE begin_interval_time >= TO_TIMESTAMP('2026-03-01 08:45', 'YYYY-MM-DD HH24:MI')
  AND end_interval_time   <= TO_TIMESTAMP('2026-03-01 09:45', 'YYYY-MM-DD HH24:MI')
ORDER BY snap_id;

-- Step 2: ASH activity timeline in 5-minute buckets for the problem window
-- Shows AAS (Average Active Sessions) per bucket — reveals where the spike was
SELECT TO_CHAR(TRUNC(sample_time, 'MI') -
               MOD(EXTRACT(MINUTE FROM sample_time), 5) * INTERVAL '1' MINUTE,
               'HH24:MI') AS bucket,
       COUNT(*) AS ash_samples,
       RPAD('█', ROUND(COUNT(*) / 5), '█') AS chart,   -- ASCII bar chart
       COUNT(CASE WHEN session_state = 'WAITING' THEN 1 END) AS waiting,
       COUNT(CASE WHEN session_state = 'ON CPU'  THEN 1 END) AS on_cpu
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN TO_TIMESTAMP('2026-03-01 09:00', 'YYYY-MM-DD HH24:MI')
                      AND TO_TIMESTAMP('2026-03-01 09:30', 'YYYY-MM-DD HH24:MI')
  AND con_id > 0
GROUP BY TRUNC(sample_time, 'MI') -
         MOD(EXTRACT(MINUTE FROM sample_time), 5) * INTERVAL '1' MINUTE
ORDER BY 1;

-- Step 3: What were they waiting for?
SELECT event,
       COUNT(*) AS samples,
       RPAD('█', ROUND(COUNT(*) * 40 / MAX(COUNT(*)) OVER (), 0), '█') AS pct_chart
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN TO_TIMESTAMP('2026-03-01 09:10', 'YYYY-MM-DD HH24:MI')
                      AND TO_TIMESTAMP('2026-03-01 09:20', 'YYYY-MM-DD HH24:MI')
  AND session_state = 'WAITING'
  AND wait_class   != 'Idle'
GROUP BY event
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

-- Step 4: Find the blocking session (from ASH historical data)
SELECT h1.sample_time,
       h1.session_id                     AS blocked_sid,
       h1.blocking_session               AS blocker_sid,
       h1.sql_id                         AS blocked_sql,
       h2.sql_id                         AS blocker_sql,
       h1.current_obj#,
       o.object_name                     AS locked_object
FROM dba_hist_active_sess_history h1
LEFT JOIN dba_hist_active_sess_history h2
  ON  h1.blocking_session  = h2.session_id
  AND h1.sample_id         = h2.sample_id
  AND h1.inst_id           = h2.inst_id
LEFT JOIN dba_objects o ON h1.current_obj# = o.object_id
WHERE h1.sample_time BETWEEN TO_TIMESTAMP('2026-03-01 09:08', 'YYYY-MM-DD HH24:MI')
                         AND TO_TIMESTAMP('2026-03-01 09:22', 'YYYY-MM-DD HH24:MI')
  AND h1.event LIKE 'enq: TX%'
ORDER BY h1.sample_time
FETCH FIRST 20 ROWS ONLY;

-- [[email protected] ~] as sysdba
-- Real-time: what is consuming CPU right now?
SELECT s.sql_id,
       SUBSTR(t.sql_text, 1, 60)           AS sql_text,
       s.status,
       s.username,
       s.module,
       s.event,
       s.seconds_in_wait                   AS wait_secs
FROM v$session s
JOIN v$sql t ON s.sql_id = t.sql_id AND s.sql_child_number = t.child_number
WHERE s.status    = 'ACTIVE'
  AND s.username IS NOT NULL
  AND s.type      = 'USER'
ORDER BY s.seconds_in_wait DESC
FETCH FIRST 20 ROWS ONLY;

-- Which SQL is consuming the most CPU in the last 15 minutes?
SELECT sql_id,
       ROUND(SUM(cpu_time_delta) / 1e6, 1)     AS cpu_secs,
       SUM(executions_delta)                   AS execs,
       ROUND(SUM(cpu_time_delta) /
             NULLIF(SUM(executions_delta),0) / 1000, 0) AS avg_cpu_ms,
       ROUND(SUM(buffer_gets_delta) /
             NULLIF(SUM(executions_delta),0))  AS avg_lio
FROM dba_hist_sqlstat
WHERE snap_id >= (SELECT MAX(snap_id) - 1 FROM dba_hist_snapshot)
GROUP BY sql_id
ORDER BY cpu_secs DESC
FETCH FIRST 10 ROWS ONLY;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('gx8n2bfm3q9rk', NULL, NULL, 'ALL'));

-- [[email protected] ~] as sysdba (CDB level)
-- Check buffer cache usage per PDB (which PDB owns the most buffer cache?)
SELECT s.con_id,
       p.pdb_name,
       COUNT(*) AS buffers_cached,
       ROUND(COUNT(*) * 8192 / 1024 / 1024, 0) AS cache_mb
FROM v$bh b
JOIN v$session s ON b.inst_id = USERENV('INSTANCE')  -- simplified
JOIN cdb_pdbs p ON s.con_id = p.con_id
GROUP BY s.con_id, p.pdb_name
ORDER BY buffers_cached DESC;

-- Better: use V$PDB_CACHE (available in 19c+)
SELECT con_id,
       name,
       ROUND(value / 1024 / 1024, 0) AS value_mb
FROM v$con_sysmetric
WHERE metric_name = 'Buffer Cache Hit Ratio'
ORDER BY con_id;

-- Check PGA usage per PDB
SELECT p.pdb_name,
       ROUND(SUM(pga_alloc_mem) / 1024 / 1024, 0) AS pga_alloc_mb,
       ROUND(SUM(pga_used_mem)  / 1024 / 1024, 0) AS pga_used_mb,
       COUNT(*)                                     AS sessions
FROM v$process pr
JOIN v$session s ON pr.addr = s.paddr
JOIN cdb_pdbs p  ON s.con_id = p.con_id
WHERE s.type = 'USER'
GROUP BY p.pdb_name
ORDER BY pga_alloc_mb DESC;

-- Fix: Set per-PDB PGA limit to prevent noisy-neighbour effect
ALTER PLUGGABLE DATABASE PDB_DWH SET PGA_AGGREGATE_LIMIT = 20G;
ALTER PLUGGABLE DATABASE PDB_OLTP SET PGA_AGGREGATE_LIMIT = 10G;

-- Set per-PDB SGA limits (prevents buffer cache monopolisation)
ALTER PLUGGABLE DATABASE PDB_DWH  SET SGA_MIN_SIZE = 4G, SGA_TARGET = 16G;
ALTER PLUGGABLE DATABASE PDB_OLTP SET SGA_MIN_SIZE = 8G, SGA_TARGET = 32G;

-- [[email protected] ~] as sysdba
-- Check log file sync average wait time
SELECT event,
       total_waits,
       ROUND(time_waited / total_waits / 100, 2) AS avg_wait_ms,
       ROUND(time_waited / 100, 0)               AS total_wait_secs
FROM v$system_event
WHERE event = 'log file sync'
ORDER BY time_waited DESC;

-- Check redo log write time (log file parallel write)
SELECT event,
       total_waits,
       ROUND(time_waited / total_waits / 100, 2) AS avg_wait_ms
FROM v$system_event
WHERE event = 'log file parallel write';

-- Identify high-commit-rate SQL
SELECT sql_id,
       ROUND(executions / (SYSDATE - to_date('2026-03-01', 'YYYY-MM-DD')) / 86400, 0) AS exec_per_sec,
       command_type
FROM v$sqlstats
WHERE command_type = 6   -- COMMIT
  AND executions > 10000
ORDER BY executions DESC
FETCH FIRST 10 ROWS ONLY;

-- Enable deferred commit group flush (reduces sync overhead)
ALTER SYSTEM SET "_use_adaptive_log_file_sync" = FALSE;
ALTER SYSTEM SET commit_logging = 'BATCH';
ALTER SYSTEM SET commit_wait    = 'NOWAIT';
-- Note: NOWAIT means asynchronous commit — only use if data loss on crash is acceptable for this workload

-- [[email protected] ~] as sysdba
-- Buffer cache hit ratio (current)
SELECT ROUND(
         (1 - (SUM(CASE WHEN metric_name = 'Physical Reads Per Sec'       THEN average ELSE 0 END)
             / NULLIF(SUM(CASE WHEN metric_name = 'Logical Reads Per Sec' THEN average ELSE 0 END), 0))
         ) * 100, 2) AS buffer_cache_hit_pct
FROM v$sysmetric_history
WHERE metric_name IN ('Physical Reads Per Sec', 'Logical Reads Per Sec')
  AND begin_time >= SYSDATE - 1/24;

-- Buffer cache sizing advisor: what would hit ratio be at different cache sizes?
SELECT size_for_estimate        AS cache_size_mb,
       estd_physical_read_factor,
       ROUND((1 - estd_physical_read_factor) * 100, 1) AS estimated_hit_pct
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
  AND block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size')
  AND advice_status = 'ON'
ORDER BY size_for_estimate;

ALTER SYSTEM SET SGA_TARGET  = 98304M SCOPE=BOTH;
ALTER SYSTEM SET SGA_MAX_SIZE = 98304M SCOPE=SPFILE;
-- Restart required for SGA_MAX_SIZE increase

-- [[email protected] ~] as sysdba
-- Check hard parse rate
SELECT metric_name,
       ROUND(value, 2) AS current_value
FROM v$sysmetric
WHERE metric_name IN (
    'Hard Parses Per Sec',
    'Soft Parses Per Sec',
    'Library Cache Hit Ratio',
    'Shared Pool Free %'
);

-- Find SQL statements NOT using bind variables (causing excessive hard parsing)
SELECT sql_id,
       SUBSTR(sql_text, 1, 80) AS sql_text_sample,
       parse_calls,
       executions,
       ROUND(parse_calls / NULLIF(executions, 0), 2) AS parse_ratio
FROM v$sql
WHERE parse_calls > 100
  AND (SELECT COUNT(*) FROM v$sql s2
       WHERE SUBSTR(s2.sql_text, 1, 40) = SUBSTR(v$sql.sql_text, 1, 40)) > 5
  AND executions > 0
ORDER BY parse_calls DESC
FETCH FIRST 15 ROWS ONLY;

-- Temporary fix: force cursor sharing for literal SQL
ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE=BOTH;

-- Permanent fix: increase shared pool size for high-parse-rate systems
ALTER SYSTEM SET SHARED_POOL_SIZE = 8G SCOPE=BOTH;

-- Enable cursor cache at session level for PL/SQL-heavy workloads
ALTER SYSTEM SET SESSION_CACHED_CURSORS = 50 SCOPE=BOTH;

-- [[email protected] ~] as sysdba
-- File: /u01/scripts/quick_health.sql
-- Purpose: 2-minute complete health snapshot, paste into incident ticket

SET LINESIZE 200 PAGESIZE 100
SET FEEDBACK OFF
SET ECHO OFF

PROMPT ============================================================
PROMPT ORACLE DATABASE HEALTH SNAPSHOT
PROMPT ============================================================
SELECT 'Database:  ' || name
       || '  Version: ' || version_full
       || '  Uptime: ' || ROUND((SYSDATE - startup_time) * 24, 1) || 'h' AS db_info
FROM v$instance;

PROMPT
PROMPT == DB Time / AAS (last 1 hour) ==
SELECT ROUND(m.value / 1000000 / 60, 1) AS db_time_mins,
       ROUND(m.value / 1000000 / 3600, 2) AS aas
FROM v$sysmetric m
WHERE metric_name = 'DB Time Per Sec'
  AND group_id = 2;

PROMPT
PROMPT == Top 5 Wait Events ==
SELECT event,
       ROUND(time_waited / 100, 1) AS total_wait_secs,
       ROUND(time_waited / total_waits / 100, 2) AS avg_wait_ms
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 5 ROWS ONLY;

PROMPT
PROMPT == Top 5 SQL by Elapsed Time (AWR, last snapshot) ==
SELECT sql_id,
       ROUND(elapsed_time_delta / 1e6, 1) AS elapsed_secs,
       executions_delta AS execs,
       ROUND(buffer_gets_delta / NULLIF(executions_delta,0)) AS avg_lio
FROM dba_hist_sqlstat
WHERE snap_id = (SELECT MAX(snap_id) - 1 FROM dba_hist_snapshot)
ORDER BY elapsed_time_delta DESC
FETCH FIRST 5 ROWS ONLY;

PROMPT
PROMPT == Memory ==
SELECT ROUND(sga_size / 1024, 0)           AS sga_gb,
       ROUND(pga_aggregate_limit / 1024, 0) AS pga_limit_gb,
       ROUND(pga_aggregate_target / 1024, 0) AS pga_target_gb
FROM v$pgastat
CROSS JOIN (SELECT SUM(value) / 1024 / 1024 AS sga_size FROM v$sga) s
WHERE v$pgastat.name = 'aggregate PGA target parameter';

PROMPT
PROMPT == Active Sessions Right Now ==
SELECT NVL(s.username, '(' || s.type || ')') AS username,
       s.status,
       s.event,
       s.seconds_in_wait,
       SUBSTR(t.sql_text, 1, 60) AS sql_text
FROM v$session s
LEFT JOIN v$sql t ON s.sql_id = t.sql_id AND s.sql_child_number = t.child_number
WHERE s.status = 'ACTIVE'
  AND s.type = 'USER'
ORDER BY s.seconds_in_wait DESC
FETCH FIRST 10 ROWS ONLY;

PROMPT
PROMPT == Redo Log Activity ==
SELECT sequence#, status, bytes/1024/1024 AS size_mb, archived
FROM v$log
ORDER BY sequence#;

PROMPT
PROMPT == Tablespace Usage ==
SELECT t.tablespace_name,
       ROUND(t.total_mb, 0) AS total_mb,
       ROUND(t.total_mb - f.free_mb, 0) AS used_mb,
       ROUND((t.total_mb - f.free_mb) / t.total_mb * 100, 1) AS pct_used
FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb FROM dba_data_files GROUP BY tablespace_name) t
JOIN (SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb  FROM dba_free_space     GROUP BY tablespace_name) f
  ON t.tablespace_name = f.tablespace_name
WHERE (t.total_mb - f.free_mb) / t.total_mb > 0.70   -- show only > 70% full
ORDER BY pct_used DESC;

# [[email protected] ~]
# Schedule the health snapshot to run every 15 minutes and email if issues found
crontab -e

# Add:
# */15 * * * * sqlplus -S / as sysdba @/u01/scripts/quick_health.sql \
#              > /u01/logs/health_$(date +\%H\%M).log 2>&1

# Full AWR analysis — run after each working day
# 30 18 * * 1-5 /u01/scripts/awr_daily_report.sh