I/O & Storage Tuning

-- Step 1: System-wide I/O wait events (all time, sorted by total wait)
SELECT event, wait_class,
       total_waits,
       ROUND(time_waited_micro/1e6, 1)              AS total_wait_secs,
       ROUND(time_waited_micro/total_waits/1000, 2)  AS avg_wait_ms,
       ROUND(time_waited_micro * 100 /
         SUM(time_waited_micro) OVER (), 1)          AS pct_of_all
FROM   v$system_event
WHERE  wait_class IN ('User I/O', 'System I/O')
AND    total_waits > 0
ORDER BY time_waited_micro DESC;

-- Thresholds to know:
-- avg_wait_ms > 20ms on SSD      = storage subsystem struggling
-- avg_wait_ms > 5ms  on NVMe     = possible I/O saturation
-- avg_wait_ms > 100ms on any disk = critical bottleneck

SELECT df.name                          AS datafile_path,
       ts.name                          AS tablespace_name,
       fs.phyrds                        AS physical_reads,
       fs.phywrts                       AS physical_writes,
       ROUND(fs.readtim  / NULLIF(fs.phyrds,  0), 2) AS avg_read_ms,
       ROUND(fs.writetim / NULLIF(fs.phywrts, 0), 2) AS avg_write_ms,
       fs.singleblkrds                  AS single_block_reads,
       ROUND(fs.singleblkrdtim /
         NULLIF(fs.singleblkrds, 0), 2)              AS avg_singleblk_ms
FROM   v$filestat   fs
JOIN   v$datafile   df ON df.file# = fs.file#
JOIN   v$tablespace ts ON ts.ts#   = df.ts#
ORDER BY fs.phyrds + fs.phywrts DESC;

-- Current: top segments by physical reads
SELECT owner, object_name, object_type,
       logical_reads, physical_reads, physical_writes,
       ROUND(physical_reads*100/NULLIF(logical_reads,0),1) AS miss_pct
FROM   v$segment_statistics
WHERE  owner NOT IN ('SYS','SYSTEM')
AND   (physical_reads > 1000 OR logical_reads > 100000)
ORDER BY physical_reads DESC
FETCH FIRST 20 ROWS ONLY;

-- Historical: top segments from AWR (last 24 hours)
SELECT o.owner, o.object_name, o.object_type,
       SUM(s.logical_reads_delta)  AS logical_reads,
       SUM(s.physical_reads_delta) AS physical_reads
FROM   dba_hist_seg_stat      s
JOIN   dba_hist_seg_stat_obj  o  ON  o.dataobj# = s.dataobj#
                                AND o.obj#     = s.obj#
JOIN   dba_hist_snapshot      sn ON sn.snap_id = s.snap_id
WHERE  sn.begin_interval_time > SYSDATE - 1
AND    o.owner NOT IN ('SYS','SYSTEM')
GROUP BY o.owner, o.object_name, o.object_type
ORDER BY physical_reads DESC
FETCH FIRST 20 ROWS ONLY;

-- Current log groups, sizes, status
SELECT l.group#, l.members, l.bytes/1024/1024 AS size_mb,
       l.status, l.archived, lf.member AS log_file_path
FROM   v$log     l
JOIN   v$logfile lf ON lf.group# = l.group#
ORDER BY l.group#, lf.member;

-- Log switch heatmap (should be every 15-30 min in OLTP)
-- More frequent = redo logs too small
SELECT TO_CHAR(first_time,'YYYY-MM-DD HH24') AS hour,
       COUNT(*)                              AS switches
FROM   v$log_history
WHERE  first_time > SYSDATE - 2
GROUP BY TO_CHAR(first_time,'YYYY-MM-DD HH24')
ORDER BY hour;

-- What size does Oracle recommend for your workload?
SELECT optimal_logfile_size AS recommended_log_mb
FROM   v$instance_recovery;

-- Check for log file switch waits (ARCH falling behind)
SELECT event, total_waits, time_waited,
       ROUND(time_waited/total_waits,2) AS avg_wait_cs
FROM   v$system_event
WHERE  event LIKE 'log file switch%'
ORDER BY time_waited DESC;

-- Step 1: Add new larger groups (multiplexed on two disk groups)
ALTER DATABASE ADD LOGFILE GROUP 4
  ('/u02/oradata/redo4a.log',
   '/u03/oradata/redo4b.log') SIZE 500M;

ALTER DATABASE ADD LOGFILE GROUP 5
  ('/u02/oradata/redo5a.log',
   '/u03/oradata/redo5b.log') SIZE 500M;

-- Step 2: Force log switches to make old groups INACTIVE
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

-- Step 3: Drop old small groups (only when INACTIVE + ARCHIVED=YES)
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;

-- Who is using temp right now and how much
SELECT s.sid, s.username, s.sql_id,
       SUM(u.blocks) * 8 / 1024 AS temp_mb,
       s.event, s.seconds_in_wait
FROM   v$session    s
JOIN   v$sort_usage u ON u.session_addr = s.saddr
GROUP BY s.sid, s.username, s.sql_id, s.event, s.seconds_in_wait
ORDER BY temp_mb DESC;

-- Temp tablespace size vs used
SELECT tf.tablespace_name, tf.file_name,
       tf.bytes/1024/1024      AS total_mb,
       NVL(su.used_mb, 0)       AS used_mb
FROM   dba_temp_files tf
LEFT JOIN (SELECT tablespace,
                  SUM(blocks)*8/1024 AS used_mb
           FROM   v$sort_usage
           GROUP BY tablespace) su
  ON su.tablespace = tf.tablespace_name;

-- Add more temp files for parallel I/O striping
ALTER TABLESPACE TEMP ADD TEMPFILE
  '/u04/oradata/temp02.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 50G;
ALTER TABLESPACE TEMP ADD TEMPFILE
  '/u05/oradata/temp03.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 50G;

-- Separate temp tablespace for report users (isolation)
CREATE TEMPORARY TABLESPACE TEMP_REPORTS
  TEMPFILE '/u04/oradata/temp_reports01.dbf' SIZE 50G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER USER report_user1 TEMPORARY TABLESPACE TEMP_REPORTS;
ALTER USER report_user2 TEMPORARY TABLESPACE TEMP_REPORTS;

-- Tell Oracle: recover in under 60 seconds after a crash
-- Oracle works backwards to determine checkpoint frequency
ALTER SYSTEM SET fast_start_mttr_target = 60 SCOPE=BOTH;

-- Check estimated vs target MTTR
SELECT target_mttr, estimated_mttr,
       recovery_estimated_ios, actual_redo_blks,
       optimal_logfile_size AS recommended_log_mb
FROM   v$instance_recovery;
-- estimated_mttr should be <= target_mttr

-- Check DBWR is keeping up (free_buffer_waits > 0 = DBWR bottleneck)
SELECT name, value FROM v$sysstat
WHERE  name = 'free buffer waits';

-- Add DBWR processes (rule: 1 per 4-8 CPU cores / storage controller)
-- Requires restart
ALTER SYSTEM SET db_writer_processes = 4 SCOPE=SPFILE;

-- Enable async I/O (massive write throughput improvement)
-- Check current state first
SELECT name, value FROM v$parameter
WHERE  name IN ('disk_asynch_io', 'filesystemio_options');

-- Enable for both reads and writes (best option)
ALTER SYSTEM SET disk_asynch_io        = TRUE   SCOPE=SPFILE;
ALTER SYSTEM SET filesystemio_options  = 'SETALL' SCOPE=SPFILE;
-- SETALL = enable both async I/O and direct I/O
-- Restart required for filesystemio_options

-- Interval partitioning: auto-creates new partitions as months arrive
CREATE TABLE orders_partitioned (
  order_id     NUMBER,
  customer_id  NUMBER,
  order_date   DATE NOT NULL,
  status       VARCHAR2(20),
  total_amount NUMBER
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_before_2024
    VALUES LESS THAN (DATE '2024-01-01')
);
-- Oracle auto-creates new monthly partitions going forward

-- Verify partition pruning in the plan (look for these operations):
-- PARTITION RANGE SINGLE     → best: exactly 1 partition accessed
-- PARTITION RANGE ITERATOR   → range of partitions (acceptable)
-- PARTITION RANGE ALL        → all partitions: PRUNING FAILED
EXPLAIN PLAN FOR
SELECT COUNT(*) FROM orders_partitioned
WHERE  order_date >= DATE '2026-03-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'PARTITION'));
-- Look for: Pstart=1 Pstop=1 (one partition) vs Pstart=1 Pstop=ALL (all)

-- Partition pruning FAILS with functions on partition key:
-- WHERE TRUNC(order_date) = TRUNC(SYSDATE)  → ALL partitions scanned
-- Fix: WHERE order_date >= TRUNC(SYSDATE) AND order_date < TRUNC(SYSDATE)+1

-- STEP 1: I/O pain in ASH (last 30 min)
SELECT event, wait_class,
       COUNT(*)                                          AS ash_samples,
       ROUND(COUNT(*)*100/SUM(COUNT(*)) OVER(),1)     AS pct
FROM   v$active_session_history
WHERE  sample_time > SYSDATE - 30/1440
AND    wait_class IN ('User I/O','System I/O')
GROUP BY event, wait_class
ORDER BY ash_samples DESC;

-- STEP 2: Top SQL causing I/O in that window
SELECT sql_id, COUNT(*) AS samples
FROM   v$active_session_history
WHERE  sample_time > SYSDATE - 30/1440
AND    wait_class IN ('User I/O','System I/O')
GROUP BY sql_id
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

-- STEP 3: Hottest datafiles
SELECT df.name, ts.name AS ts_name,
       fs.phyrds, fs.phywrts,
       ROUND(fs.readtim/NULLIF(fs.phyrds,0),2)   AS avg_read_ms,
       ROUND(fs.writetim/NULLIF(fs.phywrts,0),2) AS avg_write_ms
FROM   v$filestat fs
JOIN   v$datafile df ON df.file# = fs.file#
JOIN   v$tablespace ts ON ts.ts# = df.ts#
WHERE  fs.phyrds + fs.phywrts > 0
ORDER BY fs.phyrds + fs.phywrts DESC
FETCH FIRST 10 ROWS ONLY;

-- STEP 4: Hot segments in that tablespace
SELECT s.segment_name, s.segment_type,
       s.bytes/1024/1024 AS size_mb
FROM   dba_segments s
JOIN   dba_data_files df ON df.tablespace_name = s.tablespace_name
WHERE  df.file_name = '&hot_datafile_from_step3'
ORDER BY s.bytes DESC
FETCH FIRST 20 ROWS ONLY;