Troubleshoot Oracle performance issue

 Identifying performance issues in an Oracle Database 19c environment requires a structured approach to pinpoint bottlenecks and optimize the system. Below is a step-by-step guide to help you identify and resolve performance issues:

---

Step 1: Gather Baseline Information

1. Understand the Symptoms:

   • Is the database slow for all users or specific applications?

   • Are there specific queries or processes that are slow?

   • When did the performance degradation start?

2. Check the Alert Log:

   • Review the alert log (alert_<SID>.log) for errors or warnings.

   • Location: $ORACLE_BASE/diag/rdbms/<DB_NAME>/<SID>/trace/alert_<SID>.log.

3. Check Recent Changes:

   • Were there any recent changes (e.g., schema changes, parameter changes, patches)?

   • Query DBA_REGISTRY_HISTORY for recent updates.

---

Step 2: Monitor Database Activity

1. Check Active Sessions:

   • Identify active sessions and their wait events:

     sql

     Copy

     SELECT sid, serial#, username, sql_id, event, blocking_session
     FROM v$session
     WHERE status = 'ACTIVE';

2. Identify Top SQL:

   • Find high-load SQL statements:

     sql

     Copy

     SELECT sql_id, executions, elapsed_time, cpu_time
     FROM v$sql
     ORDER BY elapsed_time DESC;

3. Check Wait Events:

   • Identify bottlenecks using wait events:

     sql

     Copy

     SELECT event, total_waits, time_waited
     FROM v$system_event
     ORDER BY time_waited DESC;

---

Step 3: Analyze Resource Usage

1. Check CPU Usage:

   • Use OS tools (e.g., top, vmstat, sar) to monitor CPU usage.

   • Check Oracle-specific CPU metrics:

     sql

     Copy

     SELECT * FROM v$sysstat WHERE name LIKE '%CPU%';

2. Check Memory Usage:

   • Monitor SGA and PGA usage:

     sql

     Copy

     SELECT * FROM v$sgastat;
     SELECT * FROM v$pgastat;

3. Check I/O Performance:

   • Monitor I/O performance using v$filestat and v$tempstat:

     sql

     Copy

     SELECT file#, phyrds, phywrts, readtim, writetim
     FROM v$filestat;

---

Step 4: Review SQL Execution Plans

1. Identify Problematic SQL:

   • Use v$sql or AWR reports to find high-load SQL statements:

     sql

     Copy

     SELECT sql_id, executions, elapsed_time, cpu_time
     FROM v$sql
     ORDER BY elapsed_time DESC;

2. Analyze Execution Plans:

   • Use EXPLAIN PLAN or DBMS_XPLAN to analyze the execution plan of a query:

     sql

     Copy

     EXPLAIN PLAN FOR <SQL_STATEMENT>;
     SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

3. Optimize SQL:

   • Add missing indexes, rewrite queries, or use hints to improve performance.

---

Step 5: Check Space and Storage

1. Verify Tablespace Usage:

   • Check for free space in tablespaces:

     sql

     Copy

     SELECT tablespace_name, used_percent
     FROM dba_tablespace_usage_metrics;

2. Check Undo and Temp Space:

   • Monitor undo and temporary tablespace usage:

     sql

     Copy

     SELECT tablespace_name, bytes_used, bytes_free
     FROM v$temp_space_header;

3. Resolve Space Issues:

   • Add data files or resize existing files if space is low.

---

Step 6: Generate and Analyze AWR/ADDM Reports

1. Generate AWR Report:

   • Run the AWR report for a specific time period:

     sql

     Copy

     @$ORACLE_HOME/rdbms/admin/awrrpt.sql

2. Generate ADDM Report:

   • Run the ADDM report for performance analysis:

     sql

     Copy

     @$ORACLE_HOME/rdbms/admin/addmrpt.sql

3. Analyze Findings:

   • Look for top SQL, wait events, and resource bottlenecks in the reports.

---

Step 7: Review Database Parameters

1. Check Initialization Parameters:

   • Verify critical parameters (e.g., memory_target, processes, sessions):

     sql

     Copy

     SHOW PARAMETER;

2. Adjust Parameters if Necessary:

   • Modify parameters using ALTER SYSTEM (e.g., increase SGA_TARGET or PGA_AGGREGATE_TARGET).

---

Step 8: Check for Locking and Blocking

1. Identify Blocking Sessions:

   • Check for blocking sessions:

     sql

     Copy

     SELECT blocking_session, sid, serial#, wait_class, event
     FROM v$session
     WHERE blocking_session IS NOT NULL;

2. Resolve Blocking Issues:

   • Kill blocking sessions if necessary:

     sql

     Copy

     ALTER SYSTEM KILL SESSION '<sid>,<serial#>';

---

Step 9: Review Indexes and Statistics

1. Check Index Usage:

   • Identify unused or missing indexes:

     sql

     Copy

     SELECT * FROM v$object_usage WHERE used = 'NO';

2. Update Statistics:

   • Ensure statistics are up-to-date:

     sql

     Copy

     EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');

---

Step 10: Escalate to Oracle Support (if Needed)

1. Collect Diagnostic Data:

   • Use the Automatic Diagnostic Repository (ADR) to collect logs and traces:

     bash

     Copy

     adrci
     show alert

2. Open a Service Request (SR):

   • If the issue cannot be resolved, open a service request with Oracle Support and provide the necessary diagnostic data.

---

Step 11: Document and Prevent Recurrence

1. Document the Issue and Resolution:

   • Record the symptoms, root cause, and steps taken to resolve the issue.

2. Implement Preventive Measures:

   • Apply patches, update monitoring scripts, or adjust configurations to prevent recurrence.

---

By following this step-by-step guide, you can systematically identify and resolve performance issues in an Oracle Database 19c environment.