Most Oracle outages I have been called into were not caused by a missing feature or an exotic bug. They came from something nobody was looking at: a tablespace at 99%, a backup that had never been restored, one session holding a lock for forty minutes while a POS chain queued behind it.
I have run Oracle in production for over 20 years, mostly behind ERP and POS systems where a slow query means a cashier waiting with a customer in front of them. This article is the practical core of what I check, in the order I check it, on Oracle 19c. Every query here runs as-is; adjust the thresholds to your system.
One rule runs through all of it: measure before you change anything. Oracle tells you exactly where time is going. Most tuning mistakes come from guessing instead of asking.

  1. Start with waits, not guesses When someone says "the database is slow", the first question is: slow doing what? Every active session in Oracle is either on CPU or waiting on something specific, and the wait event names it. This is the first query I run during an incident: SELECT s.sid, s.serial#, s.username, s.sql_id, s.event, s.wait_class, s.state, ROUND(s.wait_time_micro / 1e6, 1) AS wait_sec, s.blocking_session FROM v$session s WHERE s.status = 'ACTIVE' AND s.type = 'USER' AND s.wait_class <> 'Idle' ORDER BY s.wait_time_micro DESC; Read the event column before anything else: • db file sequential read: single-block reads, usually index access. Many of these from one sql_id often means a bad index path or a nested loop over too many rows. • db file scattered read / direct path read: full scans. Fine for reports, suspicious in OLTP. • enq: TX - row lock contention: someone is holding a row lock. Go to section 5. • log file sync: commits waiting on redo writes. Too-frequent commits, or slow redo storage. • library cache: mutex X / cursor: pin S wait on X: parsing pressure. Go to section 3. For what happened ten minutes ago rather than right now, use Active Session History. It samples active sessions every second: SELECT sql_id, event, COUNT(*) AS samples -- roughly seconds of DB time FROM v$active_session_history WHERE sample_time > SYSDATE - INTERVAL '15' MINUTE GROUP BY sql_id, event ORDER BY samples DESC FETCH FIRST 10 ROWS ONLY; For trends across hours or days, generate an AWR report with @?/rdbms/admin/awrrpt.sql and start at the Top Timed Events and SQL ordered by Elapsed Time sections. Licensing note: ASH and AWR are part of the Diagnostics Pack, an extra-cost option on Enterprise Edition. If you are not licensed, set control_management_pack_access = NONE and use Statspack (@?/rdbms/admin/spcreate.sql) instead. V$SESSION is free to query on every edition.
  2. Read the plan Oracle actually ran EXPLAIN PLAN shows what the optimizer would do, with estimated row counts. It can differ from what really ran, especially with bind variables. When a query is slow, I want the real plan with real row counts. Run the statement once with the gather_plan_statistics hint: SELECT /*+ gather_plan_statistics */ i.item_code, SUM(l.qty) FROM sales_lines l JOIN items i ON i.item_id = l.item_id WHERE l.branch_id = :branch AND l.sale_date >= TRUNC(SYSDATE) - 7 GROUP BY i.item_code; Then, in the same session: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')); For a statement already in the shared pool that you found in section 1, pass its sql_id instead of NULL. The most useful thing in that output is the gap between E-Rows (estimated) and A-Rows (actual) on each line. When the optimizer expects 10 rows and gets 400,000, every decision above that line was built on a wrong number: the join method, the join order, the index choice. Find the first line, reading from the most indented, where the two diverge by an order of magnitude. That is where the real problem lives, and it is usually stale or missing statistics, a skewed column without a histogram, or a function wrapped around an indexed column. That last one is the most common mistake I see from developers: -- index on sale_date cannot be used WHERE TRUNC(sale_date) = TRUNC(SYSDATE)

-- index on sale_date can be used
WHERE sale_date >= TRUNC(SYSDATE)
AND sale_date < TRUNC(SYSDATE) + 1

  1. Bind variables are not optional Every distinct SQL text gets hard-parsed: Oracle checks syntax, permissions, and builds a plan, while holding latches and mutexes in the shared pool. A query built by string concatenation produces a new SQL text for every value, so ten thousand POS terminals asking for ten thousand different invoice numbers means ten thousand hard parses. This finds statements that differ only in their literals: SELECT force_matching_signature, COUNT() AS versions, MIN(sql_id) AS sample_sql_id, MIN(SUBSTR(sql_text, 1, 80)) AS sample_text FROM v$sql WHERE force_matching_signature <> 0 GROUP BY force_matching_signature HAVING COUNT() > 50 ORDER BY versions DESC; If the top row shows hundreds of versions of the same query, you have found your shared pool problem. The fix belongs in the application: // node-oracledb: one SQL text, reused for every invoice const result = await conn.execute( SELECT total FROM invoices WHERE invoice_no = :inv, { inv: invoiceNo } ); Static SQL inside PL/SQL binds its variables automatically, which is one more reason to keep data logic in packages. Dynamic SQL does not: use EXECUTE IMMEDIATE ... USING, never || with a value. cursor_sharing = FORCE replaces literals with system binds and can stop the bleeding during an incident. Treat it as a tourniquet, not a fix: it can hide skew from the optimizer and it does nothing for SQL injection.
  2. Statistics: the optimizer is only as good as what it knows Oracle 19c gathers statistics automatically in the nightly maintenance window. That covers most tables most of the time. It fails in three predictable cases: a table loaded heavily during the day, a table truncated and reloaded by a batch job, and a window that gets skipped because the database was busy or down. Find tables Oracle itself considers stale: SELECT owner, table_name, num_rows, last_analyzed FROM dba_tab_statistics WHERE stale_stats = 'YES' AND owner NOT IN ('SYS', 'SYSTEM') ORDER BY last_analyzed NULLS FIRST; Check that the automatic job is actually running: SELECT client_name, status FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection'; When a batch job loads a large volume, gather right after the load, inside the job, instead of waiting for tonight: BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => 'POS', tabname => 'SALES_LINES', cascade => TRUE -- indexes too ); END; / For staging tables that swing between empty and millions of rows, gather once at a representative size and lock them with DBMS_STATS.LOCK_TABLE_STATS. Otherwise the nightly job may capture them empty, and tomorrow's plans will assume zero rows.
  3. Locks: find the head of the chain When sessions pile up on enq: TX - row lock contention, the waiting sessions are not the problem. The one holding the lock is. Often it is not even running anything. This shows who is blocking whom, as a tree: SELECT LPAD(' ', 2 * (LEVEL - 1)) || s.sid AS sid_tree, s.serial#, s.username, s.status, s.event, s.sql_id, s.prev_sql_id, s.machine, s.program FROM v$session s WHERE s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL) OR s.blocking_session IS NOT NULL START WITH s.blocking_session IS NULL CONNECT BY PRIOR s.sid = s.blocking_session; The root of the tree is your blocker. If its status is INACTIVE, you are looking at the most common lock incident in business applications: a session that updated a row and never committed. A form left open, an exception handler that swallowed the error without a rollback, a connection pool that handed back a connection mid-transaction. prev_sql_id shows the last statement it ran, which usually points straight at the code. To see how long that transaction has been open: SELECT s.sid, s.username, t.start_time, t.used_urec AS undo_records FROM v$session s JOIN v$transaction t ON t.addr = s.taddr ORDER BY t.start_time; Killing the session is sometimes the right call: ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;. Know what it costs first. Oracle has to roll back everything that session did, and undo_records gives you a sense of how long that will take. Then fix the code path, or it will happen again next week.
  4. Space: the outage you can see coming Running out of space is the most avoidable outage there is, and still one of the most common. Two places matter most. Tablespaces. Don't calculate free space from dba_free_space alone; it ignores autoextend. This view accounts for it, measuring usage against the maximum each tablespace can grow to: SELECT m.tablespace_name, ROUND(m.used_space * t.block_size / 1024 / 1024 / 1024, 1) AS used_gb, ROUND(m.tablespace_size * t.block_size / 1024 / 1024 / 1024, 1) AS max_gb, ROUND(m.used_percent, 1) AS used_pct FROM dba_tablespace_usage_metrics m JOIN dba_tablespaces t ON t.tablespace_name = m.tablespace_name ORDER BY m.used_percent DESC; Also check that the filesystem or ASM disk group underneath actually has that much room. A datafile set to autoextend to 32 GB on a disk with 4 GB free is a promise nobody can keep. The Fast Recovery Area. In ARCHIVELOG mode, if the FRA fills up and the archiver cannot write, the database stops accepting changes and clients get ORA-00257. This is how a quiet weekend turns into a phone call. SELECT file_type, percent_space_used, percent_space_reclaimable FROM v$recovery_area_usage ORDER BY percent_space_used DESC; If archived logs dominate and little is reclaimable, your backups are not running or not deleting what they have already backed up. Section 8 covers that.
  5. Undo and ORA-01555 ORA-01555: snapshot too old means a query needed an older version of a block to stay read-consistent, and the undo holding that version had already been overwritten. The month-end report that runs for two hours is the usual victim. V$UNDOSTAT keeps ten-minute buckets of undo activity and tells you whether your undo is keeping up: SELECT begin_time, maxquerylen AS longest_query_sec, tuned_undoretention AS retention_sec, ssolderrcnt AS ora_1555_count, nospaceerrcnt FROM v$undostat ORDER BY begin_time DESC FETCH FIRST 24 ROWS ONLY; If longest_query_sec regularly exceeds retention_sec, the undo tablespace is too small for your workload. Grow it, or let its datafiles autoextend so Oracle can tune retention upward. There is also a self-inflicted version, and I still find it in code reviews: opening a cursor over a big table, then updating and committing inside the loop that fetches from it. Each commit frees undo that the open cursor still needs, so the loop eventually reads a block it can no longer reconstruct. Committing every N rows feels safe. In this pattern it is what causes the error. Use a set-based UPDATE, or BULK COLLECT ... LIMIT with FORALL, and commit at the end of the logical unit of work.
  6. Backups: a backup you have never restored is a hope I have met more than one team with nightly backups that turned out to be unusable on the day they needed them. The backup ran. Nobody had ever tried to bring it back. A baseline RMAN setup for a single-instance 19c database: CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

-- the nightly job
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE NOPROMPT OBSOLETE;
The deletion policy plus DELETE OBSOLETE is what keeps the FRA from filling up in section 6. Copy backups off the database server too; a backup on the same disk as the database protects you from a dropped table, not from a dead server.
Confirm the jobs actually succeeded, from SQL, without opening logs:
SELECT start_time, end_time, input_type, status,
ROUND(output_bytes / 1024 / 1024 / 1024, 1) AS output_gb
FROM v$rman_backup_job_details
ORDER BY start_time DESC
FETCH FIRST 14 ROWS ONLY;
Then prove you can restore, at two levels:

  1. Weekly, cheap: RESTORE DATABASE VALIDATE; reads every backup piece RMAN would need and checks it for corruption, without writing anything.
  2. Quarterly, real: restore onto a separate server, open the database, and run a query your business cares about, like yesterday's sales total. Time it. That number is your actual recovery time, and it is usually longer than anyone assumed.
  3. The five-minute morning check None of this helps if you only look during an incident. This is the list I run every morning, ideally as a scheduled job that emails only when something crosses a line. Check Where Act when Last night's backup v$rman_backup_job_details Status is not COMPLETED Tablespace usage dba_tablespace_usage_metrics Any tablespace above 85% FRA usage v$recovery_area_usage Above 80% and little reclaimable New errors alert log (query below) Any ORA- since yesterday Long-open transactions v$transaction Open longer than 30 minutes Stale statistics dba_tab_statistics Hot tables marked stale Undo pressure v$undostat ssolderrcnt above 0 The alert log is queryable from SQL in 19c, so you do not need a shell on the server to read it: SELECT originating_timestamp, message_text FROM v$diag_alert_ext WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY AND message_text LIKE '%ORA-%' ORDER BY originating_timestamp DESC; The database rarely fails without warning. It usually tells you days ahead, in a view nobody was querying. Most of administration is building the habit of asking. What is on your morning checklist that is not on mine? I would like to hear what has saved you, especially from people running Oracle behind ERP and POS systems.