Simplifying PostgreSQL Restarts: How to Choose the Right Mode for Your Needs

Three Restart Modes in PostgreSQL

1. smart mode (-m smart) - Gentle & Safe

Waits patiently for all clients to disconnect on their own
Completes all transactions normally before shutting down
No data loss - everything commits properly
Best for production during active hours
Slowest method but most graceful

2. fast mode (-m fast) - Quick & Controlled

Rolls back active transactions immediately
Disconnects all clients abruptly but safely
Restarts quickly without waiting
No data corruption - maintains database integrity
Perfect for maintenance windows
Medium risk - some transactions get aborted

3. immediate mode (-m immediate) - Emergency Only

Forceful shutdown like a crash
No waiting - kills everything immediately
May require recovery on next startup
Risk of temporary inconsistencies
Only for emergencies when nothing else works
PostgreSQL will automatically recover using WAL logs

💡 Key Points to Remember:

All three modes are safe for the database itself
Data integrity is maintained through WAL (Write-Ahead Logging)
Choose based on your situation: gentle vs fast vs emergency
Fast mode is completely normal for planned maintenance
Always warn users before using fast or immediate modes

🚀 When to Use Each:

Use smart mode when:
Database is in production use
You can afford to wait
No transactions should be interrupted

Use fast mode when:
You need to restart quickly
During maintenance windows
Some transaction rollback is acceptable

Use immediate mode when:
Database is unresponsive
Emergency situations only
You have no other choice

All modes will bring your database back safely - they just differ in how gently they treat connected clients and active transactions!

Step-by-Step: Converting Postgre Sync Replication to Async in Production

Simple Steps

📋 Prerequisites

Postgre 9.1 or higher.
Superuser access to the primary database.
Existing replication setup.

🚀 Quick Change Steps (No Restart Required)

Step 1: Connect to Primary Database
p -U postgres -h your-primary-server

Step 2: Check Current Sync Status
SELECT name, setting FROM pg_settings WHERE name IN ('synchronous_commit', 'synchronous_standby_names');

Step 3: Change to Asynchronous Mode
-- Disable synchronous commit
ALTER SYSTEM SET synchronous_commit = 'off';

-- Clear synchronous standby names
ALTER SYSTEM SET synchronous_standby_names = '';

-- Reload configuration (no restart needed)

SELECT pg_reload_conf();

Step 4: Verify the Change
-- Confirm settings changed

SELECT name, setting FROM pg_settings WHERE name IN ('synchronous_commit', 'synchronous_standby_names');

-- Check replication status (should show 'async')

SELECT application_name, sync_state, state FROM pg_stat_replication;

📝 Configuration File Method (Alternative)

Edit postgre.conf on Primary:
sudo nano /etc/postgre/14/main/postgre.conf
Change these lines:
conf
synchronous_commit = off
synchronous_standby_names = ''

Reload Configuration:
sudo systemctl reload postgres
# or
sudo pg_ctl reload

🧪 Testing the Change

Test 1: Performance Check
-- Time an insert operation
\timing on
INSERT INTO test_table (data) VALUES ('async test');
\timing off

Test 2: Verify Async Operation
-- Check replication lag
SELECT application_name, sync_state, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_lag FROM pg_stat_replication;

Test 3: Data Consistency Check

# On primary:
p -c "SELECT COUNT(*) FROM your_table;"

# On standby:
p -h standby-server -c "SELECT COUNT(*) FROM your_table;"

⚠️ Important Considerations

Before Changing:

Understand the risks: Async replication may cause data loss during failover
Check business requirements: Ensure async meets your data consistency needs
Monitor performance: Async should improve write performance but may increase replication lag

After Changing:

Monitor replication lag regularly
Set up alerts for significant lag increases
Test failover procedures to understand potential data loss scenarios
Review application behavior - some apps may need adjustments

🔄 Reverting to Synchronous Mode

If you need to switch back:

ALTER SYSTEM SET synchronous_commit = 'on';
ALTER SYSTEM SET synchronous_standby_names = 'your_standby_name';

SELECT pg_reload_conf();

🎯 When to Use This Change

Good candidates for Async:

Read-heavy workloads where write performance matters
Non-critical data that can tolerate minor data loss
High-volume logging or analytics data
Geographically distributed systems with high latency

Keep Synchronous For:

Financial transactions.
User account data.
Critical business operations.
Systems requiring zero data loss.

💡 Pro Tips

Use a hybrid approach: Some systems support both sync and async simultaneously.
Monitor continuously: Use tools like pg_stat_replication to watch lag times.
Test thoroughly: Always test replication changes in a staging environment first.
Document the change: Keep records of why and when you changed replication modes.
The change from sync to async takes effect immediately and requires no downtime, making it a relatively low-risk operation that can significantly improve write performance for appropriate use cases.

The Real-Time Database Dilemma: When to Use Synchronous vs Asynchronous Replication in PostgreSQL

Synchronous Replication

✅ Benefits:

Zero Data Loss: Guarantees that data is safely written to both primary and standby before confirming success.
Strong Consistency: All servers have exactly the same data at all times.
Automatic Failover: Standby servers are always up-to-date and ready to take over instantly.
Perfect for Critical Data: Ideal for financial transactions, user accounts, and mission-critical information.

❌ Drawbacks:

Slower Performance: Write operations wait for standby confirmation, increasing latency.
Availability Risk: If standby goes down, primary may become unavailable or slow.
Higher Resource Usage: Requires more network bandwidth and standby server resources.
Complex Setup: More configuration and maintenance required.

⚡ Asynchronous Replication

✅ Benefits:

Faster Performance: Write operations complete immediately without waiting for standby.
Better Availability: Primary continues working even if standby servers disconnect.
Long-Distance Friendly: Works well across geographical distances with higher latency.
Simpler Management: Easier to configure and maintain.

❌ Drawbacks:

Potential Data Loss: Recent transactions may be lost if primary fails before replication completes.
Data Lag: Standby servers might be slightly behind the primary.
Weaker Consistency: Temporary mismatches between primary and standby possible.
Failover Complexity: May require manual intervention to ensure data consistency.

🏭 Production Environment Preferences

📊 For Most Production Systems:

Mixed Approach: Use synchronous for critical data, asynchronous for less critical data.
Tiered Architecture: Synchronous for primary standby, asynchronous for additional read replicas.
Monitoring Essential: Track replication lag and performance metrics continuously.

⚡ For Real-Time Systems:

Synchronous Preferred: When data consistency is more important than raw speed.
Financial Systems: Banking, payments, and transactions require synchronous replication.
E-commerce: Order processing and inventory management often need synchronous guarantees.

🚀 When to Choose Async:

High-Write Systems: Applications with heavy write workloads (logging, analytics).
Read-Heavy Applications: When you need multiple read replicas for performance.
Geographic Distribution: Systems spanning multiple regions with higher latency.
Non-Critical Data: Caching, session data, or temporary information.

🎯 Key Recommendations

Start with Async for most applications, then move to sync for critical components.
Monitor Replication Lag constantly - anything beyond few seconds needs attention.
Test Failover Regularly - ensure your standby can take over when needed.
Use Connection Pooling to help manage the performance impact of synchronous replication.
Consider Hybrid Approaches - some databases support both modes simultaneously.

🔍 Real-World Example

Social Media Platform:

Synchronous: User accounts, direct messages, financial transactions.
Asynchronous: Activity feeds, notifications, analytics, likes.

E-commerce Site:

Synchronous: Orders, payments, inventory updates.
Asynchronous: Product recommendations, user reviews, search indexing.

Step-by-Step Guide to PostgreSQL Streaming Replication (Primary-Standby Setup)

Here's the properly organized step-by-step guide for setting up PostgreSQL 12/15 streaming replication between Primary (172.19.0.20) and Standby (172.19.0.30):


✅ Pre-Requisites

Two servers with PostgreSQL 12/15.3 installed
Database cluster initialized on both using initdb (service not started yet)

IP configuration:

Primary: 172.19.0.20
Standby: 172.19.0.30

🔧 Primary Server Configuration

Backup config file:

bash

cd /u01/postgres/pgdata_15/data
cp postgresql.conf postgresql.conf_bkp_$(date +%b%d_%Y)


Edit postgresql.conf:

bash

vi postgresql.conf

Set these parameters:

listen_addresses = '*'
port = 5432
wal_level = replica
archive_mode = on
archive_command = 'cp %p /u01/postgres/pgdata_15/data/archive_logs/%f'
max_wal_senders = 10
wal_keep_segments = 50

Start PostgreSQL:

bash

/u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Create replication user:

sql

CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'strongpassword';

Configure pg_hba.conf:

bash

cp pg_hba.conf pg_hba.conf_bkp_$(date +%b%d_%Y)

vi pg_hba.conf

Add:
host replication repl_user 172.19.0.30/32 md5

Reload config:

bash

/u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile reload

🔄 Standby Server Configuration

Clear data directory:

bash

rm -rf /u01/postgres/pgdata_15/data/*

Run pg_basebackup:

bash

pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Start PostgreSQL:

bash

pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Verify logs:

bash

tail -f /u01/postgres/pgdata_15/data/logfile

Look for:

text

started streaming WAL from primary at 0/3000000 on timeline 1

🔍 Verification Steps

On Primary:

sql

CREATE DATABASE reptest;

\c reptest

CREATE TABLE test_table (id int);

INSERT INTO test_table VALUES (1);

On Standby:

sql

\c reptest

SELECT * FROM test_table; -- Should return 1 row

Check processes:

Primary:

bash

ps -ef | grep postgres

Look for walsender process

Standby:

Look for walreceiver process

Network test:

bash

nc -zv 172.19.0.20 5432


📌 Key Notes

-Replace paths/versions according to your setup
-Ensure firewall allows port 5432 between servers
For Oracle Linux 6.8, adjust service commands if needed
-Monitor logs (logfile) on both servers for errors


Detailed steps:

✅ Pre-Requisites

Two servers with PostgreSQL 12 or PostgreSQL 15.3 installed.
Database cluster initialized on both using initdb (but do not start the service yet).

IPs configured:

Primary: 172.19.0.20
Standby: 172.19.0.30

Make some necessary changes in postgresql.conf file on the primary server.

[postgres@HOST01 data]$ pwd

/u01/postgres/pgdata_15/data

[postgres@HOST01 data]$ cp postgresql.conf postgresql.conf_bkp_Aug18_2025

[postgres@HOST01 data]$ vi postgresql.conf


listen_addresses = '*'
port = 5432
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /u01/postgres/pgdata_15/data/archive_logs/%f'
max_wal_senders = 10

After making necessary changes in the postgresql.conf file. Let’s start the database server on primary server using below command.

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

Once the database server is started, invoke psql as super user i.e. postgres and create a role for replication.

CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'strongpassword';

Allow connection to created user from Standby Server by seting pg_hba.conf

[postgres@HOST01 data]$ pwd

/u01/postgres/pgdata_15/data

[postgres@HOST01 data]$ ls

archive_logs pg_commit_ts pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf

base pg_dynshmem pg_logical pg_serial pg_subtrans pg_wal postgresql.conf_old

global pg_hba.conf pg_multixact pg_snapshots pg_tblspc pg_xact postmaster.opts

logfile pg_hba.conf_bkp_Aug18_2025 pg_notify pg_stat pg_twophase postgresql.auto.conf postmaster.pid


[postgres@HOST01 data]$ cp pg_hba.conf pg_hba.conf_bkp_Aug18_2025

[postgres@HOST01 data]$ vi pg_hba.conf

host replication repl_user 172.19.0.30/32 md5

Reload the setting of the cluster.

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile stop

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

(OR)

[postgres@HOST01 data]$ /u01/postgres/pgdata_15/bin/pg_ctl -D /u01/postgres/pgdata_15/data -l logfile reload

We made changes to the configuration of the primary server. Now we’ll use pg_basebackup to get the data directory i.e. /u01/postgres/pgdata_15/data to Standby Server.

Standby Server Configurations.

[postgres@HOST02 ~]$ pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Password:

pg_basebackup: error: directory "/u01/postgres/pgdata_15/data" exists but is not empty

Navigate to data directory and empty it.

cd /u01/postgres/pgdata_15/data

rm -rf *

[postgres@HOST02 ~]$ pg_basebackup -h 172.19.0.20 -U repl_user -p 5432 -D /u01/postgres/pgdata_15/data -Fp -Xs -P -R

Password:

38761/38761 kB (100%), 1/1 tablespace

Now start the database server at standby side using below command.


[postgres@HOST02 ~]$ pg_ctl -D /u01/postgres/pgdata_15/data -l logfile start

waiting for server to start.... done

server started

Check the logfile to gather some more information.

[postgres@HOST02 data]$ tail -f logfile

2025-08-18 21:12:09.487 UTC [11349] LOG: listening on IPv4 address "0.0.0.0", port 5432
2025-08-18 21:12:09.487 UTC [11349] LOG: listening on IPv6 address "::", port 5432
2025-08-18 21:12:09.491 UTC [11349] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-08-18 21:12:09.498 UTC [11352] LOG: database system was interrupted; last known up at 2025-08-18 21:11:13 UTC
2025-08-18 21:12:09.513 UTC [11352] LOG: entering standby mode
2025-08-18 21:12:09.518 UTC [11352] LOG: redo starts at 0/2000028
2025-08-18 21:12:09.519 UTC [11352] LOG: consistent recovery state reached at 0/2000100
2025-08-18 21:12:09.520 UTC [11349] LOG: database system is ready to accept read-only connections
2025-08-18 21:12:09.557 UTC [11353] LOG: started streaming WAL from primary at 0/3000000 on timeline 1

Let’s now check the replication. Invoke psql and perform some operations.

Primary:

create database qadar_reptest;
\c qadar_reptest;
create table mqm_reptst_table (serial int);
insert into mqm_reptst_table values (1);

Standby:

\c qadar_reptest;
select count(*) from mqm_reptst_table;

Check the background process involved in streaming replication:

Primary:

[postgres@HOST01 data]$ ps -ef|grep postgres

root 11329 160 0 19:40 pts/1 00:00:00 su - postgres
postgres 11330 11329 0 19:40 pts/1 00:00:04 -bash
root 11608 11496 0 21:07 pts/2 00:00:00 su - postgres
postgres 11609 11608 0 21:07 pts/2 00:00:00 -bash
postgres 11635 11609 0 21:07 pts/2 00:00:00 /usr/bin/coreutils --coreutils-prog-shebang=tail /usr/bin/tail -f logfile
postgres 11639 1 0 21:08 ? 00:00:00 /u01/postgres/pgdata_15/bin/postgres -D /u01/postgres/pgdata_15/data
postgres 11640 11639 0 21:08 ? 00:00:00 postgres: checkpointer
postgres 11641 11639 0 21:08 ? 00:00:00 postgres: background writer
postgres 11643 11639 0 21:08 ? 00:00:00 postgres: walwriter
postgres 11644 11639 0 21:08 ? 00:00:00 postgres: autovacuum launcher
postgres 11645 11639 0 21:08 ? 00:00:00 postgres: archiver last was 000000010000000000000002.00000028.backup
postgres 11646 11639 0 21:08 ? 00:00:00 postgres: logical replication launcher
postgres 11663 11639 0 21:12 ? 00:00:00 postgres: walsender repl_user 172.19.0.30(38248) streaming 0/3000060
postgres 11671 11330 0 21:15 pts/1 00:00:00 ps -ef
postgres 11672 11330 0 21:15 pts/1 00:00:00 grep --color=auto postgres

Standby:

[postgres@HOST02 ~]$ ps -ef|grep postgres

root 11300 119 0 20:59 pts/1 00:00:00 su - postgres
postgres 11301 11300 0 20:59 pts/1 00:00:00 -bash
postgres 11349 1 0 21:12 ? 00:00:00 /u01/postgres/pgdata_15/bin/postgres -D /u01/postgres/pgdata_15/data
postgres 11350 11349 0 21:12 ? 00:00:00 postgres: checkpointer
postgres 11351 11349 0 21:12 ? 00:00:00 postgres: background writer
postgres 11352 11349 0 21:12 ? 00:00:00 postgres: startup recovering 000000010000000000000003
postgres 11353 11349 0 21:12 ? 00:00:00 postgres: walreceiver streaming 0/3000148
postgres 11356 11301 0 21:16 pts/1 00:00:00 ps -ef
postgres 11357 11301 0 21:16 pts/1 00:00:00 grep --color=auto postgres


Check the network connectivity betweeen the servers.

Standby:

[postgres@HOST02 ~] yum install nc
[postgres@HOST02 ~]$ nc -zv 172.19.0.20 5432
Ncat: Version 7.70 ( https://nmap.org/ncat )
Ncat: Connected to 172.19.0.20:5432.
Ncat: 0 bytes sent, 0 bytes received in 0.02 seconds.

ERROR with "Failed to set locale, defaulting to C" on Centos at the docker environment (yum install)

Fix:

As root user, then do steps:

sed -i 's/mirrorlist/#mirrorlist/g' /etc/yum.repos.d/CentOS-*
sed -i 's|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g' /etc/yum.repos.d/CentOS-*

yum update -y

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.