Oracle DBA

1. Clarify the Problem Scope

First establish what exactly is slow.

Key questions:

• Which application or module is affected?
• Which SQL statements are slow?
• Is the issue new or always present?
• Does it occur for all users or specific users?
• When did the problem start?

Useful checks:

• Identify the schema name
• Identify SQL IDs involved
• Determine time window of the issue

Example query:

SELECT sql_id, sql_text
FROM v$sql
WHERE parsing_schema_name = 'SCHEMA_NAME'
ORDER BY elapsed_time DESC;

This helps identify top SQL consuming resources.

---

2. Check Current Database Activity

Verify if the issue is currently happening.

Key views:

• V$SESSION
• V$SESSION_WAIT
• V$ACTIVE_SESSION_HISTORY

Example:

SELECT sid, serial#, username, sql_id, event, wait_class
FROM v$session
WHERE username = 'SCHEMA_NAME';

Things to check:

• Waiting events
• Blocking sessions
• Long running SQL

---

3. Identify Top Resource Consuming SQL

Focus on high impact SQL statements.

SELECT sql_id,
       executions,
       elapsed_time/1000000 elapsed_sec,
       cpu_time/1000000 cpu_sec,
       buffer_gets,
       disk_reads
FROM v$sql
WHERE parsing_schema_name='SCHEMA_NAME'
ORDER BY elapsed_time DESC;

Look for:

• High buffer gets
• High elapsed time
• High disk reads

These usually reveal inefficient SQL.

---

4. Examine Execution Plan

Once problematic SQL is identified, analyze the execution plan.

SELECT * 
FROM table(DBMS_XPLAN.DISPLAY_CURSOR('sql_id',NULL,'ALLSTATS LAST'));

Check for:

• Full table scans
• Cartesian joins
• Incorrect join methods
• Large cardinality misestimates
• Missing indexes

---

5. Check for Blocking or Locks

Sometimes performance issues are caused by blocking sessions.

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

Also review:

• DBA_BLOCKERS
• DBA_WAITERS

---

6. Check Object Statistics

Outdated statistics can cause bad execution plans.

SELECT table_name, last_analyzed
FROM dba_tables
WHERE owner='SCHEMA_NAME';

If required:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');

---

7. Check Index Usage

Confirm whether indexes exist and are used.

SELECT index_name, table_name, status
FROM dba_indexes
WHERE owner='SCHEMA_NAME';

Potential issues:

• Missing indexes
• Unused indexes
• Index fragmentation

---

8. Investigate Wait Events

Look at database wait events to identify the bottleneck.

Common waits:

• db file sequential read → index I/O
• db file scattered read → full table scan
• enq: TX - row lock contention → locking
• log file sync → commit issues

Query:

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

---

9. Check Historical Performance (AWR / ASH)

If the issue is intermittent, analyze history.

Use:

• AWR report
• ASH report

Look for:

• Top SQL
• Top wait events
• Load spikes

---

10. Check System-Level Bottlenecks

Sometimes the schema is not the real issue.

Check:

• CPU utilization
• I/O latency
• Memory pressure
• Tablespace contention

Views:

• V$SYSMETRIC
• V$IOSTAT_FILE
• V$RESOURCE_LIMIT

---

11. Possible Solutions

Depending on findings:

• SQL tuning
• Adding indexes
• Updating statistics
• Resolving locks
• Partitioning large tables
• SQL plan baseline
• Application query rewrite

---