72 ) Performance tuning - for oracle

Sample Slow Running Query (Oracle Dialect)

SQL

SELECT 
    r.RegionName,
    s.StoreName,
    EXTRACT(YEAR FROM o.OrderDate) AS OrderYear,
    EXTRACT(MONTH FROM o.OrderDate) AS OrderMonth,
    COUNT(DISTINCT o.OrderID) AS TotalOrders,
    SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue,
    AVG(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS AverageOrderValue
FROM 
    Sales.Orders o
INNER JOIN 
    Sales.OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN 
    Sales.Stores s ON o.StoreID = s.StoreID
INNER JOIN 
    Sales.Regions r ON s.RegionID = r.RegionID
WHERE 
    r.RegionName = 'North America'
    AND o.OrderDate >= DATE '2025-01-01' 
    AND o.OrderDate < DATE '2025-02-01'
GROUP BY 
    r.RegionName,
    s.StoreName,
    EXTRACT(YEAR FROM o.OrderDate),
    EXTRACT(MONTH FROM o.OrderDate);

Steps to Resolve

Step 1: Enable timing and set autotrace to capture baseline performance

• Action: Enable SQL*Plus timing commands or trace settings to measure the exact execution elapsed time and resource consumption before applying optimizations.

• SQL Command:

  SQL

  SET TIMING ON;
  SET AUTOTRACE ON STATISTICS;
  
  -- Execute the slow running query above
  SELECT 
      r.RegionName,
      s.StoreName,
      EXTRACT(YEAR FROM o.OrderDate) AS OrderYear,
      EXTRACT(MONTH FROM o.OrderDate) AS OrderMonth,
      COUNT(DISTINCT o.OrderID) AS TotalOrders,
      SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue,
      AVG(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS AverageOrderValue
  FROM 
      Sales.Orders o
  INNER JOIN 
      Sales.OrderDetails od ON o.OrderID = od.OrderID
  INNER JOIN 
      Sales.Stores s ON o.StoreID = s.StoreID
  INNER JOIN 
      Sales.Regions r ON s.RegionID = r.RegionID
  WHERE 
      r.RegionName = 'North America'
      AND o.OrderDate >= DATE '2025-01-01' 
      AND o.OrderDate < DATE '2025-02-01'
  GROUP BY 
      r.RegionName,
      s.StoreName,
      EXTRACT(YEAR FROM o.OrderDate),
      EXTRACT(MONTH FROM o.OrderDate);
  

• Sample Output (Autotrace Statistics Report):

  Plaintext

          0  recursive calls
          0  db block gets
     485200  consistent gets
      98400  physical reads
          0  redo size
     Elapsed: 00:00:04.92
  

• Explanation of Output: The baseline execution shows an elapsed time of 4.92 seconds with heavy disk reads (98,400 physical reads) and buffer accesses (485,200 consistent gets), indicating full table scans across the unindexed fact tables.

Step 2: Run and analyze the execution plan using EXPLAIN PLAN

• Action: Generate an execution plan using Oracle's EXPLAIN PLAN utility to see how the Cost-Based Optimizer (CBO) handles table access paths.

• SQL Command:

  SQL

  EXPLAIN PLAN FOR
  SELECT 
      r.RegionName,
      s.StoreName,
      EXTRACT(YEAR FROM o.OrderDate) AS OrderYear,
      EXTRACT(MONTH FROM o.OrderDate) AS OrderMonth,
      COUNT(DISTINCT o.OrderID) AS TotalOrders,
      SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue,
      AVG(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS AverageOrderValue
  FROM 
      Sales.Orders o
  INNER JOIN 
      Sales.OrderDetails od ON o.OrderID = od.OrderID
  INNER JOIN 
      Sales.Stores s ON o.StoreID = s.StoreID
  INNER JOIN 
      Sales.Regions r ON s.RegionID = r.RegionID
  WHERE 
      r.RegionName = 'North America'
      AND o.OrderDate >= DATE '2025-01-01' 
      AND o.OrderDate < DATE '2025-02-01'
  GROUP BY 
      r.RegionName,
      s.StoreName,
      EXTRACT(YEAR FROM o.OrderDate),
      EXTRACT(MONTH FROM o.OrderDate);
  
  -- Display the plan
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  

• Sample Output (DBMS_XPLAN.DISPLAY):

  Plaintext

  Plan hash value: 3412095811
  --------------------------------------------------------------------------------------------
  | Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |               |     1 |   120 | 14205  (15)| 00:01:00 |
  |   1 |  HASH GROUP BY     |               |     1 |   120 | 14205  (15)| 00:01:00 |
  |*  2 |   HASH JOIN        |               | 45200 |  529K | 12400  (12)| 00:00:52 |
  |*  3 |    HASH JOIN       |               |  1500 | 25500 |  3100   (8)| 00:00:14 |
  |*  4 |     TABLE ACCESS FULL| REGIONS     |     1 |    13 |     2   (0)| 00:00:01 |
  |   5 |     TABLE ACCESS FULL| STORES      |   150 |  1350 |     3   (0)| 00:00:01 |
  |*  6 |    TABLE ACCESS FULL | ORDERS      | 45200 |  678K |  4100  (10)| 00:00:18 |
  |   7 |   TABLE ACCESS FULL  | ORDERDETAILS  |  1.25M|    25M|  5100  (10)| 00:00:22 |
  --------------------------------------------------------------------------------------------
  

• Explanation of Output: The execution plan displays multiple TABLE ACCESS FULL operations on ORDERS, ORDERDETAILS, STORES, and REGIONS, showing that Oracle is sequentially scanning all data blocks from disk because no indexes are mapped.

Step 3: Create targeted B-Tree indexes

• Action: Build structural B-Tree indexes on foreign keys and filter columns to replace table scans with fast index lookups or range scans.

• SQL Command:

  SQL

  CREATE INDEX Sales.idx_regions_name ON Sales.Regions(RegionName);
  CREATE INDEX Sales.idx_stores_reg ON Sales.Stores(RegionID, StoreID);
  CREATE INDEX Sales.idx_orders_store_date ON Sales.Orders(StoreID, OrderDate);
  CREATE INDEX Sales.idx_orderdetails_ord ON Sales.OrderDetails(OrderID);
  

• Sample Output (User Indexes Verification Query):

  SQL

  SELECT index_name, table_name, status FROM user_indexes WHERE table_owner = 'SALES';
  

  Plaintext

  INDEX_NAME              TABLE_NAME    STATUS
  IDX_REGIONS_NAME        REGIONS       VALID
  IDX_STORES_REG          STORES        VALID
  IDX_ORDERS_STORE_DATE   ORDERS        VALID
  IDX_ORDERDETAILS_ORD    ORDERDETAILS  VALID
  

• Explanation of Output: Confirms that all indexes have been built successfully and hold a VALID state within the schema.

Step 4: Gather fresh table statistics using DBMS_STATS

• Action: Force Oracle's Cost-Based Optimizer to re-evaluate row counts, block distributions, and cluster factors using high sample sizes.

• SQL Command:

  SQL

  BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(
          ownname => 'SALES', 
          tabname => 'ORDERS', 
          estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
          cascade => TRUE
      );
      DBMS_STATS.GATHER_TABLE_STATS(
          ownname => 'SALES', 
          tabname => 'ORDERDETAILS', 
          estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
          cascade => TRUE
      );
      DBMS_STATS.GATHER_TABLE_STATS(
          ownname => 'SALES', 
          tabname => 'STORES', 
          estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
          cascade => TRUE
      );
      DBMS_STATS.GATHER_TABLE_STATS(
          ownname => 'SALES', 
          tabname => 'REGIONS', 
          estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
          cascade => TRUE
      );
  END;
  /
  

• Sample Output (SQL*Plus confirmation block):

  Plaintext

  PL/SQL procedure successfully completed.
  

• Explanation of Output: Confirms that statistics have been refreshed, ensuring the optimizer has accurate cardinality inputs for path calculations.

Step 5: Verify PGA Memory Allocation

• Action: Ensure Automatic PGA Memory Management is configured correctly to prevent hash joins and sorting operations from writing data out to temporary tablespaces on disk.

• SQL Command:

  SQL

  SELECT name, value FROM v$parameter WHERE name IN ('pga_aggregate_target', 'workarea_size_policy');
  

• Sample Output:

  Plaintext

  NAME                     VALUE
  workarea_size_policy     AUTO
  pga_aggregate_target     4294967296
  

• Explanation of Output: Confirms automatic policy configuration with a target PGA memory pool of 4 GB (4294967296 bytes) to keep hash aggregation steps fully in memory.

Step 6: Rewrite the query using Subquery Factoring (WITH clause / CTEs)

• Action: Restructure the query using subquery factoring to isolate filter criteria and shrink working data volumes before execution.

• Tuned Oracle Query:

  SQL

  WITH FilteredOrders AS (
      SELECT 
          o.OrderID,
          o.StoreID,
          EXTRACT(YEAR FROM o.OrderDate) AS OrderYear,
          EXTRACT(MONTH FROM o.OrderDate) AS OrderMonth
      FROM Sales.Orders o
      INNER JOIN Sales.Stores s ON o.StoreID = s.StoreID
      INNER JOIN Sales.Regions r ON s.RegionID = r.RegionID
      WHERE r.RegionName = 'North America'
        AND o.OrderDate >= DATE '2025-01-01' 
        AND o.OrderDate < DATE '2025-02-01'
  ),
  AggregatedSales AS (
      SELECT 
          fo.OrderYear,
          fo.OrderMonth,
          fo.StoreID,
          COUNT(DISTINCT fo.OrderID) AS TotalOrders,
          SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue,
          AVG(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS AverageOrderValue
      FROM FilteredOrders fo
      INNER JOIN Sales.OrderDetails od ON fo.OrderID = od.OrderID
      GROUP BY 
          fo.OrderYear,
          fo.OrderMonth,
          fo.StoreID
  )
  SELECT 
      r.RegionName,
      s.StoreName,
      a.OrderYear,
      a.OrderMonth,
      a.TotalOrders,
      a.TotalRevenue,
      a.AverageOrderValue
  FROM AggregatedSales a
  INNER JOIN Sales.Stores s ON a.StoreID = s.StoreID
  INNER JOIN Sales.Regions r ON s.RegionID = r.RegionID;
  

• Sample Output Execution Metrics (Autotrace Verification):

  SQL

  SET TIMING ON;
  SET AUTOTRACE ON STATISTICS;
  -- [Run Fully Tuned Query]
  

  Plaintext

          0  recursive calls
          0  db block gets
       2400  consistent gets
          0  physical reads
          0  redo size
     Elapsed: 00:00:00.16
  

• Explanation of Output: Physical disk reads drop to 0 and consistent gets shrink from hundreds of thousands down to 2,400. The elapsed execution time drops from 4.92 seconds down to 0.16 seconds, proving a massive 30x performance improvement.