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 EBS R12 General Ledger (GL) Module – Practical SQL Queries

Introduction Oracle E-Business Suite (EBS) R12 General Ledger (GL) is the core Financial module in Oracle EBS R12. It is used to manage journal entries, accounting periods, balances, and financial reporting. The GL module integrates with Payables (AP), Receivables (AR), Fixed Assets (FA), Purchasing (PO), and Inventory (INV). The main purpose of a general ledger system is to record financial activity of a company and to produce financial and management reports to help the organization make decisions. Below are real-world SQL queries commonly used.   1) View configured Ledgers      Purpose: Fetch Ledger Configuration Details SELECT       ledger_id,      name  AS   ledger_name ,    short_name ,   currency_code FROM     gl_ledgers ORDER BY     ledger_id ;   2) Ledger Information Query Purpose: Fetch Ledger Configuration Details SELECT     gl.name   ...
Post a Comment
Read more

Exploring AI in Oracle APEX 26.1

Artificial Intelligence (AI) is transforming the way organizations and developers work. Businesses and professionals are increasingly adopting AI tools to improve productivity, automate processes, and build smarter applications. Oracle APEX introduces a significant evolution in application development by integrating governed Generative AI capabilities directly into the low-code platform. This enables developers to build intelligent, conversational applications while maintaining enterprise-grade security and control. In this article we explore AI capabilities in Oracle APEX 26.1 What is AI Agents AI Agents can be created to reason over user requests and take actions through approved AI Tools. Each tool exposes a specific application capability the agent is allowed to invoke, such as retrieving data, running server-side PL/SQL, or executing client-side JavaScript. APEX manages the execution flow by preparing context, dispatching tool calls, executing tools, handling results, and...
Post a Comment
Read more

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