Skip to main content

SQL Query Performance Tuning: A Practical Approach for Oracle DBAs

Inefficient queries in Oracle Database can significantly impact system performance, resulting in higher response times and decreased user productivity. By closely monitoring memory usage and CPU consumption, database administrators can identify performance bottlenecks and determine the root causes of inefficient query execution.

Oracle Query Performance Tuning Checklist

1.         Monitor Memory Usage: 

            Purpose: Ensure optimal memory allocation to prevent excessive disk I/O and performance degradation.

            Actions:

*Track memory consumption using Oracle Enterprise Manager

*Automatic Workload Repository (AWR) reports to review SGA and PGA utilization, and identify abnormal memory usage patterns.

Analyze AWR reports for:

a.      Buffer cache hit ratio

b.      Shared pool efficiency

c.       PGA cache hit percentage

·         Monitor memory advisory sections in AWR.

·         Validate automatic memory management parameters:

o   MEMORY_TARGET

o   SGA_TARGET

o   PGA_AGGREGATE_TARGET


2.         Check CPU Utilization: 

Purpose: Identify CPU bottlenecks and high-impact SQL statements affecting system throughput.

 Actions:

  • Review CPU usage trends in AWR reports.
  • Examine “Top SQL by CPU Time”.
  • Identify sessions consuming excessive CPU.
  • Correlate CPU spikes with batch jobs or peak workloads.

Helpful Tips

·         AWR Report (Top SQL by CPU)

Which will give ( CPU Time per SQL ,Elapsed Time ,Executions)

·         V$SYSSTAT 

V$SYSSTAT shows Oracle CPU usage for all sessions. The statistic "CPU used by this session" actually shows the aggregate CPU used by all sessions.

Query: Total CPU Usage

select value from v$sysstat s, v$statname n where s.statistic# = n.statistic#  and  n.name = 'CPU used by this session';

·         V$SESSTAT

V$SESSTAT shows Oracle CPU usage per session. You can use this view to see which particular session is using the most CPU.

Query: CPU Usage per Session

select sum(value) all_sess_cpu from v$sesstat s, v$statname n where s.statistic# = n.statistic#  and  n.name = 'CPU used by this session';

 

3.         Optimize SQL Queries: 

Purpose: Improve query efficiency and reduce resource consumption.

Action: Examine execution plans and implement recommendations from SQL Tuning Advisor to enhance query efficiency and minimize resource consumption.

Note:   For Execution Plan Points to look carefully

·         Full table scans

·         Nested loops on large tables

·         Missing index 

Slow Running Queries: You can find Slow-Running Active Sessions

 SELECT s.sid,

       s.serial#,

       s.username,

       q.sql_id,

       q.sql_text,

       s.event,

       s.seconds_in_wait

FROM v$session s

JOIN v$sql q ON s.sql_id = q.sql_id

WHERE s.status='ACTIVE'

AND s.username IS NOT NULL

ORDER BY s.seconds_in_wait DESC;

Comments

Post a Comment

Popular posts from this blog

Oracle R12 Inventory Configuration: Key points to Avoid Mistakes

Most Oracle ERP R12 inventory problems are not system failures they are process and configuration failures. The same categories of mistakes surface repeatedly across implementations: Focusing on master data control, disciplined transactions, proper OU setup, and strong internal controls prevents operational and financial discrepancies.   This article walks through the four most critical areas where Oracle R12 inventory implementations go wrong, explains why each mistake is costly, and provides recommendations to prevent them. Each section includes the relevant navigation path and configuration screenshots for hands-on reference   1.       Improper Item Master Configuration in Oracle R12   Oracle E‑Business Suite R12 , the Item Master Configuration serves as the foundation of the Inventory module. Two critical components under this configuration umbrella are: Define Master Item Setup Define Item Status Both setups control how items ...
Post a Comment
Read more

Monitoring and Troubleshooting Oracle APEX Performance with Real-World SQL Queries

    Introduction Oracle Application Express (APEX) is one of the most widely adopted low-code development platforms in the Oracle ecosystem. As organizations scale their APEX deployments, the need for robust monitoring and troubleshooting capabilities becomes critical. This article provides SQL queries that DBAs and developers can use to monitor, diagnose, and optimize their APEX environments.   What makes monitoring APEX unique is the three-tier architecture it operates on: the Oracle Database at the data layer, APEX at the application layer, and Oracle REST Data Services (ORDS) as the middleware. Understanding how sessions flow through these three layers is the key to effective monitoring.     Architecture Overview   Layer Component Version Role Database Oracle DB 19.28 Stores data, executes SQL, manages sessions Application Oracle AP...
Post a Comment
Read more