DBA's Guide to Troubleshooting Database/Query Performance Problems

When DBAs receive a performance issue incident, the first step for DBA is to gather as much relevant information and background as possible. To do this, ask users or developers the following preliminary questions to collect initial data:

• How much time did it take previously?
• Is it slow for all parameters or just specific ones?
• What operations or programs were executed?
• Does the problem occur in both test and production environments?
• What is the frequency of job execution, and has it changed recently?
• Is the issue consistent, or does it occur at specific times of the day?
• Is it an Oracle-seeded program or a custom one?
• Were there any recent code changes or migrations?
• How much data is being processed? Is there any surge in data/volume ?
• Has the runtime increased gradually over time, or is this a sudden spike?

From here, your goal is to answer these three critical questions:

1. Where is the time spent?

Identify the major areas consuming time, such as code inefficiencies, network latency, disk I/O bottlenecks, or CPU/memory constraints.

  2. How is the time spent?

Break down the time spent across different layers—database, application, network, disk, etc.

 3. How can we reduce the time spent?

Based on your findings, pinpoint the primary culprits and figure out ways to optimize them.

Fundamental Steps for Resolving System-Level Performance Issues

CPU/Memory Consumption: Use tools like vmstat, top, or prstat to identify system-wide CPU and memory usage.

Disk Bottlenecks: Use iostat to check if disk I/O is causing delays.

Network Issues: Use netstat, tnsping, etc., to diagnose network-related problems.

Resource-Intensive Processes: Verify if other heavy processes are running on the server.

Filesystem Space: Ensure there is enough space available.

Logs and Traces: Check alert logs, application logs, and trace files for errors or warnings.

Database Locks: Check for locking issues within the database.

AWR Reports: Generate and analyze Automatic Workload Repository (AWR) reports to identify resource hogs.

Memory and Other Settings:

Evaluate if increasing application/database memory, redo/undo logs, temporary tablespace, or the System Global Area (SGA) will help.

Key Techniques to Address Session-Level Performance Challenges

Top SQLs:

Identify the top SQL statements executing under the session.

SQL Optimization:

Apply optimization techniques to the top SQLs.

Session-Level Locks:

Verify locking issues at the session level.

AWR/ASH Reports:

Generating AWR or Active Session History (ASH) reports for the duration of the issue can provide useful insights.

Effective Approach for SQL-Level Performance Issues

Trace and TKPROF: Apply a trace, generate a TKPROF file, and analyze it for resource-heavy SQL statements.

Avoid Full Table Scans: Avoid full table scans on large tables by using appropriate indexing.

Indexes: Consider creating indexes on columns used in the WHERE clause.

AWR/ASH Reports: Use these reports for comprehensive information.

SQLTRPT/SQLT: Utilize these tools for further analysis.

Statistics and Indexes: Verify that statistics are current and indexes are valid and adequate.

Parallelism and Materialized Views: Assess if parallelism, materialized views, baselines, or SQL profiles will help.

Utilize V$SESSION_LONGOPS: Monitor this view to detect long-running operations.

Hints: Decide if using hints in your SQL code improves performance.

Table Partitioning:

Consider table partitioning based on the kind and size of the tables to enhance performance.

By systematically addressing these aspects, you can effectively diagnose and resolve database performance issues, ensuring smooth and efficient operation.