How I Tuned an Oracle SQL Query from 240+ Seconds to 0.14 Seconds

Performance tuning is a fascinating aspect of database management, and I recently encountered an interesting challenge. A query that initially took over 240 seconds to execute was optimized to run in just 0.14 seconds. Here's a detailed breakdown of the journey, the problems identified, and the solutions implemented.

The Query Scenario

The query aimed to fetch specific columns from a combination of multiple joins between views. These views were built on subqueries to calculate arithmetic values like MIN, MAX, and SUM. While the query used indexed columns in its join and filter criteria, two significant issues slowed it down.

The Problems Identified

Problem 1: Overuse of Nested Queries

The query had several nested subqueries in the FROM clause using the ANY operator. A simplified structure of the query is shown below:

SELECT
    A.column4 AS "Min",
    A.column5 AS "Max",
    A.column6 AS "Sum"
FROM (
    (SELECT MIN("A13"."EXPIRE_DATE") FROM TableC "A13" WHERE "A13"."Test_ID" = ANY (
        SELECT "A14"."Test_ID" FROM TableD "A14" WHERE "A14"."Another_ID" = "A2"."Col00001"
    )) AS "EARLIEST_EXPIRE_DATE",

    (SELECT MAX("A13"."NEW_DATE") FROM TableC "A13" WHERE "A13"."Test_ID" = ANY (
        SELECT "A14"."Test_ID" FROM TableD "A14" WHERE "A14"."Another_ID" = "A2"."Col00001"
    )) AS "LATEST_EXPIRE_DATE",

    (SELECT SUM(CASE WHEN "A7"."Payment_STATUS" = 'VOID' THEN 0 ELSE "A7"."Payment_AMOUNT" END)
     FROM TableE "A7"
     WHERE "A7"."Test_ID" = ANY (
         SELECT "A20"."Test_ID" FROM TableC "A20" WHERE "A20"."Test_ID" = ANY (
             SELECT "A21"."Test_ID" FROM TableD "A21" WHERE "A21"."Another_ID" = "A2"."Col00001"
         )
     )) AS "TOTAL_Payment_Amount"
FROM (
    SELECT "A4"."XYX_ID" AS "Col00001"
    FROM TableF "A4", TableG "A3"
    WHERE "A3"."XYX_ID" = "A4"."XYX_ID"
) AS "A2"
) AS "A1"
WHERE "A1".Test_ID = :BindValue;        

Problem 2: Missing Index on Key Column

• TableD, a heavily used table, lacked an index on the Another_ID column. This led to frequent full table scans, significantly increasing query execution time.

Steps Taken to Solve the Performance Bottleneck

Step 1: Generate SQLHC Report

Using Oracle's SQLHC (SQL Health Check) report, I analyzed the query's execution plan. Key findings included:

• Full Table Scans: Detected on TableD and TableE.
• High Buffer Gets: Observed excessive buffer reads and a high query cost.

Step 2: Apply Index on TableD

To address the full table scan on TableD, I created an index on the Another_ID column:

CREATE INDEX IDX_ANOTHER_ID ON TableD (Another_ID);        

This reduced the query execution time from 240 seconds to 9 seconds by enabling index scans.

Step 3: Rewrite the Query

The = ANY() clause in the subqueries was replaced with equivalent joins that leveraged indexed columns. The optimized query snippet is shown below:

SELECT
    SUM(CASE WHEN "A7"."Payment_STATUS" = 'VOID' THEN 0 ELSE "A7"."Payment_AMOUNT" END) AS "TOTAL_Payment_Amount"
FROM
    TableE "A7",
    TableC "A20",
    TableD "A21"
WHERE
    "A7"."Test_ID" = "A20"."Test_ID"
    AND "A21"."Test_ID" = "A20"."Test_ID"
    AND "A21"."Another_ID" = "A2"."Col00001";        

This modification:

• Removed expensive full table scans on TableE.
• Allowed all ID columns with indexes to create access predicates.

Results

• Execution time dropped from 240+ seconds to 9 seconds after applying the index.
• Further optimization reduced the time to 0.14 seconds by rewriting the query to eliminate unnecessary subqueries and leverage joins effectively.

Key Learnings

1. Use `` Clause Carefully: Reserve the ANY operator for < ANY and > ANY operations. Replace = ANY() with joins wherever possible.
2. Indexes are Crucial: Ensure frequently used columns in filters or joins are indexed to prevent full table scans.
3. Analyze Execution Plans: Tools like SQLHC are invaluable for identifying bottlenecks and understanding execution plans.
4. Avoid Over-Nesting: Excessive nested queries can cause performance degradation. Simplify query structure by leveraging joins.

Feel free to share your thoughts or experiences with Oracle SQL optimization in the comments!