Top 30 Oracle Performance Issues and Fixes for Banking Experts

Top 30 Oracle Performance Issues and Fixes for Banking Experts

Category 1: Transaction and Locking Issues

1. Long-Running Transactions

Short Description: A batch update on a customer table takes hours, blocking users.

Impact: Delays and timeouts for other users.

Solution: Optimize the transaction and reduce locking.

Steps:

Check V$SESSION_LONGOPS: SELECT SID, OPNAME, SOFAR, TOTALWORK FROM V$SESSION_LONGOPS WHERE OPNAME = 'Transaction';

Find blocking sessions: SELECT SID, BLOCKING_SESSION FROM V$SESSION WHERE STATUS = 'ACTIVE';

Analyze SQL: EXPLAIN PLAN FOR <your_sql>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Add index: CREATE INDEX idx_cust_id ON customer(cust_id);

Use batches: UPDATE customer SET balance = balance + 100 WHERE cust_id BETWEEN 1 AND 1000; COMMIT;

Monitor: V$SESSION_LONGOPS.

2. Enqueue Waits

Short Description: TX enqueue waits from row-level lock contention.

Impact: Delays in committing transactions.

Solution: Resolve locking conflicts.

Steps:

Identify waits: SELECT EVENT, P1, P2 FROM V$SESSION_WAIT WHERE EVENT LIKE 'enq: TX%';

Find blockers: SELECT SID, BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;

Review SQL: SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT SQL_ID FROM V$SESSION WHERE SID = <blocking_sid>);

Kill session if safe: ALTER SYSTEM KILL SESSION '<sid>,<serial#>' IMMEDIATE;

Optimize app: Commit more often.

Monitor: V$ENQUEUE_STAT.

3. Deadlock Situations

Short Description: Deadlocks during concurrent updates.

Impact: Transaction failures for users.

Solution: Resolve deadlocks.

Steps:

Check alert log: SELECT MESSAGE_TEXT FROM V$DIAG_ALERT_EXT WHERE MESSAGE_TEXT LIKE '%deadlock%';

Trace session: ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Identify SQL: SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT SQL_ID FROM V$SESSION WHERE SID = <sid>);

Redesign app: Update in consistent order.

Add retry logic in code.

Monitor: V$SESSION.

Category 2: Resource Contention Issues

4. High CPU Utilization

Short Description: CPU spikes to 95% from inefficient SQL.

Impact: Slows all operations.

Solution: Tune high-CPU SQL.

Steps:

Find top users: SELECT SID, USERNAME, VALUE/100 AS CPU_SECONDS FROM V$SESSTAT WHERE STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME WHERE NAME = 'CPU used by this session') ORDER BY VALUE DESC;

Get SQL: SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT SQL_ID FROM V$SESSION WHERE SID = <sid>);

Check plan: EXPLAIN PLAN FOR <sql>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Add index or rewrite SQL.

Use Resource Manager: BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE('PLAN1', 'GROUP1', 'Limit CPU', CPU_P1 => 50); END;

Monitor: V$SYSSTAT.

5. I/O Bottlenecks

Short Description: Slow db file sequential read due to disk delays.

Impact: Slow query responses.

Solution: Optimize I/O distribution.

Steps:

Check waits: SELECT EVENT, TOTAL_WAITS, TIME_WAITED FROM V$SYSTEM_EVENT WHERE EVENT LIKE 'db file%read';

Find hot files: SELECT FILE#, READS, WRITES FROM V$FILESTAT;

Move to SSD: ALTER TABLESPACE data MOVE DATAFILE 'old_path' TO '/ssd_path/data01.dbf';

Increase cache: ALTER SYSTEM SET DB_CACHE_SIZE = 1G;

Rebuild indexes: ALTER INDEX idx_name REBUILD;

Monitor: V$FILESTAT.

6. Latch Contention

Short Description: library cache latch contention from high parses.

Impact: Slows SQL execution.

Solution: Reduce parsing.

Steps:

Check waits: SELECT NAME, GETS, MISSES FROM V$LATCH WHERE NAME = 'library cache';

Find sessions: SELECT SID, EVENT FROM V$SESSION_WAIT WHERE EVENT LIKE 'latch%';

Review SQL: SELECT SQL_TEXT FROM V$SQL WHERE PARSE_CALLS > 100;

Use bind variables.

Increase pool: ALTER SYSTEM SET SHARED_POOL_SIZE = 500M;

Verify: V$LATCH.

7. Buffer Cache Contention

Short Description: Contention on cache buffers chains from hot blocks.

Impact: Slows data access.

Solution: Distribute load.

Steps:

Check contention: SELECT NAME, WAITS FROM V$LATCH WHERE NAME = 'cache buffers chains';

Find hot blocks: SELECT OBJECT_NAME, BLOCK# FROM V$SEGMENT_STATISTICS WHERE STATISTIC_NAME = 'physical reads' ORDER BY VALUE DESC;

Partition table: ALTER TABLE accounts PARTITION BY RANGE (account_id) (PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (MAXVALUE));

Rebuild indexes: ALTER INDEX idx_accounts REBUILD;

Increase cache: ALTER SYSTEM SET DB_CACHE_SIZE = 2G;

Verify: V$LATCH.

Category 3: Memory and Storage Issues

8. Library Cache Misses

Short Description: High hard parses from unshared SQL.

Impact: Increases CPU usage.

Solution: Minimize hard parses.

Steps:

Check ratio: SELECT NAMESPACE, GETS, GETHITRATIO FROM V$LIBRARYCACHE;

Find SQL: SELECT SQL_TEXT, PARSE_CALLS FROM V$SQL ORDER BY PARSE_CALLS DESC;

Use bind variables: SELECT * FROM accounts WHERE id = :1;

Set sharing: ALTER SYSTEM SET CURSOR_SHARING = FORCE;

Flush pool: ALTER SYSTEM FLUSH SHARED_POOL;

Monitor: V$LIBRARYCACHE.

9. Log Buffer Space Waits

Short Description: log buffer space waits from slow redo log writes.

Impact: Slows commits.

Solution: Optimize redo handling.

Steps:

Check waits: SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE 'redo%';

Increase buffer: ALTER SYSTEM SET LOG_BUFFER = 10M;

Add log group: ALTER DATABASE ADD LOGFILE GROUP 3 ('/path/redo03.log') SIZE 100M;

Check switches: SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

Adjust checkpoint: ALTER SYSTEM SET FAST_START_MTTR_TARGET = 300;

Monitor: V$SYSSTAT.

10. Undo Tablespace Issues

Short Description: snapshot too old errors from insufficient undo space.

Impact: Transaction failures.

Solution: Optimize undo management.

Steps:

Check usage: SELECT TABLESPACE_NAME, BYTES_USED FROM V$UNDOSTAT;

Resize space: ALTER DATABASE DATAFILE '/path/undo01.dbf' RESIZE 500M;

Set retention: ALTER SYSTEM SET UNDO_RETENTION = 3600;

Add datafile: ALTER TABLESPACE UNDO_TBS ADD DATAFILE '/path/undo02.dbf' SIZE 200M;

Find queries: SELECT SID, SQL_TEXT FROM V$SESSION JOIN V$SQL ON V$SESSION.SQL_ID = V$SQL.SQL_ID WHERE STATUS = 'ACTIVE';

Monitor: V$UNDOSTAT.

11. Memory Allocation Issues

Short Description: SGA/PGA misconfiguration causes swapping.

Impact: Slows database performance.

Solution: Tune memory settings.

Steps:

Check SGA: SELECT POOL, NAME, BYTES FROM V$SGASTAT;

Review PGA: SELECT NAME, VALUE FROM V$PGASTAT WHERE NAME LIKE 'total PGA%';

Increase SGA: ALTER SYSTEM SET SGA_TARGET = 4G;

Adjust PGA: ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 1G;

Check OS swapping (e.g., vmstat on Unix).

Monitor: V$SGASTAT and V$PGASTAT.

Category 4: Query and Execution Issues

12. SQL Plan Regression

Short Description: Query slows after statistics refresh.

Impact: Degraded report performance.

Solution: Stabilize the plan.

Steps:

Find SQL: SELECT SQL_ID, EXECUTIONS, ELAPSED_TIME FROM V$SQL WHERE ELAPSED_TIME > 1000000;

Compare plans: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>'));

Create baseline: DECLARE l_plan PLS_INTEGER; BEGIN l_plan := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE('<sql_id>'); END;

Pin plan: ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;

Recompute stats: EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE', CASCADE => TRUE);

Monitor: V$SQL.

13. Parallel Execution Overhead

Short Description: Excessive parallel queries consume CPU.

Impact: Slows other operations.

Solution: Control parallel execution.

Steps:

Check usage: SELECT SQL_ID, PX_SERVERS_EXECUTED FROM V$SQL WHERE PX_SERVERS_EXECUTED > 0;

Limit servers: ALTER SYSTEM SET PARALLEL_MAX_SERVERS = 16;

Adjust percent: ALTER SESSION SET PARALLEL_MIN_PERCENT = 50;

Rewrite SQL: Remove PARALLEL hint.

Monitor CPU: V$SYSSTAT.

Verify: V$PX_PROCESS.

14. Index Contention

Short Description: enq: TX - index contention during inserts.

Impact: Delays in DML operations.

Solution: Reduce contention.

Steps:

Identify waits: SELECT EVENT, P1, P2 FROM V$SESSION_WAIT WHERE EVENT LIKE 'enq: TX - index%';

Find index: SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID = <P2_value>;

Partition index: ALTER INDEX idx_trans PARTITION BY RANGE (trans_date) (PARTITION p1 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')), PARTITION p2 VALUES LESS THAN (MAXVALUE));

Rebuild index: ALTER INDEX idx_trans REBUILD;

Monitor: V$SESSION_WAIT.

Adjust app: Reduce insert frequency.

Category 5: Logging and Archiving Issues

15. Checkpoint Inefficiency

Short Description: Frequent checkpoints cause I/O spikes.

Impact: Performance dips during peaks.

Solution: Optimize checkpoint frequency.

Steps:

Check waits: SELECT EVENT, TOTAL_WAITS FROM V$SYSTEM_EVENT WHERE EVENT LIKE 'log file switch%';

Increase log size: ALTER DATABASE DROP LOGFILE GROUP 1; ALTER DATABASE ADD LOGFILE GROUP 1 ('/path/redo01.log') SIZE 200M;

Adjust target: ALTER SYSTEM SET FAST_START_MTTR_TARGET = 600;

Add log group: ALTER DATABASE ADD LOGFILE GROUP 4 ('/path/redo04.log') SIZE 200M;

Monitor switches: SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

Verify: V$SYSTEM_EVENT.

16. Archive Log Generation Lag

Short Description: Archiving lags, risking downtime.

Impact: Database hangs due to full destinations.

Solution: Improve archive management.

Steps:

Check status: SELECT DEST_ID, STATUS, DESTINATION FROM V$ARCHIVE_DEST_STATUS;

Increase space: Add disk to /archivelog_path.

Add destination: ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'LOCATION=/new_path';

Force archive: ALTER SYSTEM ARCHIVE LOG CURRENT;

Adjust processes: ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 8;

Monitor: V$ARCHIVE_DEST_STATUS.

Category 6: Temporary and Cluster Issues

17. Temp Tablespace Contention

Short Description: High direct path read/write waits from temp overload.

Impact: Slows sorts and joins.

Solution: Optimize temp usage.

Steps:

Check usage: SELECT TABLESPACE_NAME, BYTES_USED FROM V$TEMPSTAT;

Add files: ALTER TABLESPACE TEMP ADD TEMPFILE '/path/temp02.dbf' SIZE 500M;

Increase PGA: ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 2G;

Optimize SQL: Add indexes.

Monitor: V$TEMPSTAT.

Verify: V$SESSION_WAIT.

18. RAC Interconnect Issues

Short Description: gc buffer busy waits in RAC from slow interconnect.

Impact: Degrades cluster performance.

Solution: Optimize interconnect traffic.

Steps:

Check waits: SELECT EVENT, TOTAL_WAITS FROM GV$SYSTEM_EVENT WHERE EVENT LIKE 'gc buffer busy%';

Verify performance: SELECT INSTANCE_NAME, VALUE FROM GV$SYSSTAT WHERE NAME = 'gc blocks lost';

Increase bandwidth: Work with network team.

Adjust fusion: ALTER SYSTEM SET _GC_AFFINITY_LIMIT = 50;

Partition tables: Distribute block sharing.

Monitor: GV$SYSTEM_EVENT.

Category 7: Application and Sequence Issues

19. Application-Level Block Contention

Short Description: Sequence generator contention slows inserts.

Impact: Delays in transaction processing.

Solution: Optimize sequence usage.

Steps:

Find hot blocks: SELECT OBJECT_NAME, VALUE FROM V$SEGMENT_STATISTICS WHERE STATISTIC_NAME = 'physical reads' ORDER BY VALUE DESC;

Check settings: SELECT SEQUENCE_NAME, CACHE_SIZE FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_NAME';

Increase cache: ALTER SEQUENCE SEQ_NAME CACHE 1000;

Consider NOCACHE if needed.

Partition table: Distribute inserts.

Monitor: V$SEGMENT_STATISTICS.

20. High Network Latency

Short Description: High SQL*Net message from client waits from network delays.

Impact: Slow responses for remote users.

Solution: Reduce round-trips.

Steps:

Identify waits: SELECT EVENT, TOTAL_WAITS FROM V$SESSION_EVENT WHERE EVENT LIKE 'SQL*Net%';

Trace session: EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID => <sid>, SERIAL_NUM => <serial#>, WAITS => TRUE);

Review trace file (in udump directory).

Optimize SQL: Use FORALL in PL/SQL.

Improve bandwidth: Work with network team.

Monitor: V$SESSION_EVENT.

21. Unexpected Query Delay

Short Description: A daily query that finishes in 30 seconds is now running over 2 hours.

Impact: Delays critical reports and transactions.

Solution: Diagnose and optimize the query.

Steps:

Identify the query: SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%<keyword>%' ORDER BY LAST_ACTIVE_TIME DESC;

Check execution time: SELECT SID, ELAPSED_TIME FROM V$SESSION WHERE SQL_ID = '<sql_id>';

Compare plans: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>'));

Check for locks or waits: SELECT EVENT FROM V$SESSION_WAIT WHERE SID = <sid>;

Add index or hint: CREATE INDEX idx_col ON table(col); or SELECT /*+ INDEX(table idx_col) */ * FROM table;

Monitor: V$SESSION and re-run to confirm.

22. Post-Upgrade Memory Inefficiency

Short Description: After upgrading to Oracle 19c, SGA settings cause high memory usage.

Impact: Slow performance due to excessive paging.

Solution: Reconfigure memory parameters.

Steps:

Check current SGA: SELECT POOL, NAME, BYTES FROM V$SGASTAT;

Review AWR for memory waits: SELECT * FROM DBA_HIST_SYSTEM_EVENT WHERE EVENT_NAME LIKE '%memory%';

Reset SGA: ALTER SYSTEM SET SGA_TARGET = 3G;

Adjust PGA: ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 800M;

Validate with OS tools (e.g., top or vmstat).

Monitor: V$SGASTAT post-adjustment.

23. Post-Migration Query Performance Drop

Short Description: After migrating to a new server, a key query runs 50% slower.

Impact: Delays in critical banking reports.

Solution: Optimize post-migration configuration.

Steps:

Compare plans: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>')); on old and new.

Check stats: SELECT TABLE_NAME, LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME = '<table>';

Gather stats: EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', '<table>', CASCADE => TRUE);

Adjust I/O: ALTER TABLESPACE data MOVE DATAFILE 'old_path' TO '/new_path';

Test query: Run with SQL_TRACE enabled.

Monitor: V$SQL for elapsed time.

24. Post-Upgrade Archive Lag

Short Description: After upgrading to 19c, archive log generation slows.

Impact: Increased risk of database stalls.

Solution: Tune archiving post-upgrade.

Steps:

Check status: SELECT DEST_ID, STATUS FROM V$ARCHIVE_DEST_STATUS;

Review redo log size: SELECT GROUP#, BYTES FROM V$LOG;

Increase log size: ALTER DATABASE ADD LOGFILE GROUP 5 ('/path/redo05.log') SIZE 300M;

Adjust processes: ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 10;

Clear old logs: ALTER SYSTEM SWITCH LOGFILE;

Monitor: V$ARCHIVE_DEST_STATUS.

25. Post-Migration Cluster Latency

Short Description: After migrating to RAC, interconnect latency increases.

Impact: Slows cluster-wide operations.

Solution: Optimize RAC configuration.

Steps:

Check waits: SELECT EVENT, TOTAL_WAITS FROM GV$SYSTEM_EVENT WHERE EVENT LIKE 'gc%';

Review interconnect: SELECT INST_ID, NAME, VALUE FROM GV$SYSSTAT WHERE NAME LIKE 'gc%lost';

Adjust network: Work with team to reduce latency.

Tune parameters: ALTER SYSTEM SET _GC_POLICY_TIME = 100;

Redistribute data: ALTER TABLE accounts REORGANIZE PARTITION;

Monitor: GV$SYSTEM_EVENT.

26. Excessive Cursor Caching

Short Description: Post-upgrade, too many cursors are cached, slowing session performance.

Impact: Increased memory usage and session delays.

Solution: Adjust cursor settings.

Steps:

Check cursor usage: SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'session cursor cache hits';

Review parameter: SHOW PARAMETER open_cursors;

Increase limit: ALTER SYSTEM SET OPEN_CURSORS = 1000;

Clear cache: ALTER SYSTEM FLUSH SHARED_POOL;

Test application performance.

Monitor: V$SESSTAT for cursor hits.

27. Post-Migration Data Skew

Short Description: After migration, data distribution causes uneven query performance.

Impact: Slows down specific queries on skewed tables.

Solution: Rebalance data distribution.

Steps:

Identify skewed tables: SELECT TABLE_NAME, NUM_ROWS FROM USER_TABLES WHERE NUM_ROWS > 1000000 ORDER BY NUM_ROWS DESC;

Check histograms: SELECT COLUMN_NAME, HISTOGRAM FROM USER_TAB_COLS WHERE TABLE_NAME = '<table>';

Gather stats with histograms: EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', '<table>', METHOD_OPT => 'FOR ALL COLUMNS SIZE 254');

Partition table if needed: ALTER TABLE accounts PARTITION BY HASH (account_id) PARTITIONS 4;

Test query: Run with EXPLAIN PLAN.

Monitor: V$SQL for performance.

28. Post-Upgrade Index Fragmentation

Short Description: After upgrading, indexes are fragmented, slowing DML.

Impact: Increased I/O and slower updates.

Solution: Rebuild fragmented indexes.

Steps:

Check fragmentation: SELECT INDEX_NAME, DEL_LF_ROWS, LF_ROWS FROM INDEX_STATS;

Analyze index: ANALYZE INDEX idx_name VALIDATE STRUCTURE;

Rebuild index: ALTER INDEX idx_name REBUILD;

Verify space: SELECT INDEX_NAME, BLEVEL FROM USER_INDEXES;

Test DML performance.

Monitor: V$SEGMENT_STATISTICS.

29. Post-Migration Network Overhead

Short Description: After migration, network latency increases query times.

Impact: Slows remote client operations.

Solution: Optimize network settings.

Steps:

Check network waits: SELECT EVENT, TOTAL_WAITS FROM V$SESSION_EVENT WHERE EVENT LIKE 'SQL*Net%';

Trace session: EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID => <sid>, WAITS => TRUE);

Review trace (in udump directory).

Adjust SQL for batching: Use FORALL in PL/SQL.

Collaborate with network team to reduce latency.

Monitor: V$SESSION_EVENT.

30. Post-Upgrade Session Wait Surge

Short Description: After upgrading, session waits spike due to new defaults.

Impact: Degrades overall system responsiveness.

Solution: Tune wait-related parameters.

Steps:

Check waits: SELECT EVENT, TOTAL_WAITS FROM V$SYSTEM_EVENT ORDER BY TOTAL_WAITS DESC;

Review AWR: SELECT * FROM DBA_HIST_SYSTEM_EVENT WHERE EVENT_NAME LIKE '%wait%';

Adjust parameters: ALTER SYSTEM SET _SMALL_TABLE_THRESHOLD = 100;

Increase resources: ALTER SYSTEM SET PROCESSES = 1000;

Test with load: Run typical workload.

Monitor: V$SYSTEM_EVENT.