Oracle vs MySQL

Audits log buffer space waits, redo switch completion delays, and identifies active blocking sessions across Oracle instances.

# 1. Inspect Oracle redo log contention & log buffer space waits
sqlplus -s dbre_monitor/secret@oraprod as sysdba << 'EOF'
SET PAGESIZE 50 LINESIZE 120 FEEDBACK OFF;
SELECT 
  event, 
  total_waits, 
  time_waited_micro / 1000000 AS time_waited_sec, 
  average_wait 
FROM v$system_event 
WHERE event IN (
  'log file sync', 
  'log file switch completion', 
  'buffer busy waits', 
  'free buffer waits'
)
ORDER BY time_waited_sec DESC;
EOF

# 2. Check active Oracle session blocking locks and wait states
sqlplus -s dbre_monitor/secret@oraprod as sysdba << 'EOF'
SET PAGESIZE 50 LINESIZE 120 FEEDBACK OFF;
SELECT 
  sid, 
  serial#, 
  username, 
  blocking_session, 
  seconds_in_wait, 
  wait_class 
FROM v$session 
WHERE blocking_session IS NOT NULL 
ORDER BY seconds_in_wait DESC;
EOF

Audits InnoDB undo history length, buffer pool hit ratio, dirty page volume, and monitors replica replication lag.

# 1. Audit InnoDB undo history length, buffer pool & dirty pages
mysql -h mysql-primary -u dbre_admin -p -e "
SELECT 
  NAME, 
  COUNT 
FROM information_schema.INNODB_METRICS 
WHERE NAME IN (
  'trx_rseg_history_len', 
  'buffer_pool_reads', 
  'buffer_pool_read_requests', 
  'buffer_pool_pages_dirty'
);"

# 2. Inspect replica thread status and replication lag behind primary
mysql -h mysql-replica -u dbre_admin -p -e "SHOW REPLICA STATUS\G" | 
grep -E "(Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source|Last_Errno|Retrieved_Gtid_Set)"