Oracle Performance Tuning & Monitoring

This post covers essential Oracle Performance Tuning and Monitoring questions with clear, easy-to-understand answers. Whether you’re a junior or experienced DBA, these Q&As will help you prepare for interviews by explaining key concepts, tools, and techniques to identify and solve performance issues in Oracle databases.

1. What is Oracle Performance Tuning and why is it important?
Oracle Performance Tuning is the process of optimizing the database to run efficiently, ensuring fast query response and better resource usage. It’s important to maintain application performance and user satisfaction.

2. What are the common causes of poor database performance?
Common causes include inefficient SQL queries, lack of proper indexing, contention and locking issues, insufficient memory, slow I/O, and outdated statistics.

3. What are wait events in Oracle? Explain their role in performance tuning.
Wait events are points where a session waits for a resource, such as I/O or locks. Monitoring wait events helps identify bottlenecks impacting performance.

4. How do you identify performance bottlenecks in an Oracle database?
By analyzing AWR reports, ASH data, wait events, SQL execution plans, and monitoring system resource usage like CPU and I/O.

5. What tools and views does Oracle provide for performance monitoring?
Tools include AWR, ASH, SQL Trace, TKPROF, Oracle Enterprise Manager; views include V$SESSION, V$SQL, V$SYSTEM_EVENT, and V$WAITSTAT.

6. Explain Automatic Workload Repository (AWR) and its importance.
AWR collects and stores performance data over time, enabling DBAs to analyze trends and diagnose issues.

7. What is the Active Session History (ASH) and how is it used?
ASH samples active sessions every second, helping identify what sessions are doing during performance issues.

8. How do you use the SQL Trace and TKPROF utilities for tuning?
SQL Trace records detailed SQL execution info; TKPROF formats the trace file to analyze SQL performance and resource usage.

9. What is Explain Plan and how does it help in performance tuning?
Explain Plan shows how Oracle executes a SQL query, helping identify inefficient operations and improve query design.

10. How do you analyze and tune a slow-running SQL query?
Review execution plan, check indexes, optimize joins, use bind variables, gather fresh statistics, and reduce unnecessary data retrieval.

11. What is the difference between cost-based and rule-based optimizer?
Cost-based optimizer uses statistics to choose the best execution plan; rule-based relies on fixed rules (obsolete now).

12. How does Oracle manage memory for performance? Explain SGA and PGA tuning.
SGA (System Global Area) is shared memory for caching data and SQL; PGA (Program Global Area) is private memory for session operations. Proper sizing avoids excessive I/O and CPU overhead.

13. What is a latch and how does latch contention affect performance?
Latches are lightweight locks to protect shared memory structures. Contention causes waits, slowing performance.

14. How do you monitor and tune Oracle wait events?
Use V$ views and AWR reports to identify top wait events; tune by resolving resource bottlenecks causing waits.

15. What is buffer cache hit ratio? Is it a good performance indicator?
It’s the percentage of logical reads served from memory. Useful but not the only indicator; focus also on response time and wait events.

16. How do you identify and handle locking and blocking issues?
Monitor V$LOCK and V$SESSION views; kill or resolve blocking sessions; tune application logic to minimize locks.

17. What is the role of optimizer statistics and histograms?
Statistics help optimizer choose efficient plans; histograms improve estimation for skewed data distributions.

18. Explain bind variables and their effect on performance.
Bind variables allow query reuse by replacing literals with placeholders, reducing parsing and improving performance.

19. How do you monitor and tune I/O performance in Oracle?
Use AWR I/O stats, OS tools, and tune with proper tablespace layout, indexing, and buffer cache sizing.

20. What is the difference between hard parse and soft parse?
Hard parse creates a new execution plan; soft parse reuses an existing plan, saving resources.

21. How do you monitor and tune redo log performance?
Monitor LGWR wait events; use appropriate redo log size and placement; avoid frequent switches.

22. What is SQL plan baseline and how does it help with SQL stability?
It stores trusted execution plans to prevent performance regressions when SQL changes.

23. Explain the use of Oracle Enterprise Manager for performance monitoring.
OEM provides graphical tools and reports for monitoring, diagnostics, and tuning recommendations.

24. How do you perform instance tuning in Oracle?
Tune memory structures, background processes, I/O parameters, and optimizer settings based on workload.

25. What is the role of the Segment Advisor and how does it help performance?
It identifies segment space wastage and recommends segment shrink or reorganization.

26. How do you detect and resolve high CPU usage in Oracle?
Check SQL queries consuming CPU, tune them; monitor OS CPU stats; review application logic.

27. What is the impact of statistics gathering on performance?
Accurate statistics enable better execution plans; outdated stats cause inefficient queries.

28. How do you monitor and manage Oracle background processes for performance?
Use V$PROCESS and V$SESSION views; check process waits and CPU usage; tune initialization parameters.

29. How do you tune network performance related to Oracle database?
Optimize SQL data transfer, use connection pooling, monitor network latency, and tune SQL*Net parameters.

30. What is performance tuning in RAC environments? What extra considerations are there?
Tune interconnect latency, cache fusion, and load balancing; monitor global waits and coordination overhead.