Oracle Query Tuning – Example 1

In this post I am going to share one oracle query tuning example.

Query:

----------------------------------------------------
 SELECT MIN (BATCH_DATE), MAX (BATCH_DATE)
 FROM SA.BATCH_DTLS
 WHERE ONLINE_BATCH_IND = 'O';
----------------------------------------------------

Environment Details:
--------------------
Database Version    :   9.2.0.8
Operating System        :   Solaris

Table Statistics:
-----------------
LAST_ANALYZED       :   23-AUG-15   
NUM_ROWS        :   5296999
TABLE SIZE      :   1300 MB
          
INDEXES:
--------
Index is present on ONLINE_BATCH_IND column.          

Column Statistics:
------------------        
COLUMN_NAME                          NUM_DISTINCT  NUM_NULLS
------------------------------------ ------------ ----------
ONLINE_BATCH_IND                                2          0
BATCH_DATE                                   2605          0

Now I am executing this query and will try to understand the problem with the query.

SQL>
SQL> SELECT MIN (BATCH_DATE), MAX (BATCH_DATE)
  FROM SA.BATCH_DTLS
 WHERE ONLINE_BATCH_IND = 'O';

MIN(BATCH_DATE) MAX(BATCH_DATE)
--------------- ---------------
02-APR-05       28-AUG-15

Elapsed: 00:00:10.21

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14568 Card=1 Bytes=10)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'BATCH_DTLS' (Cost=14568 Card=2648500 Bytes=26485000)

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
     152102  consistent gets
       1097  physical reads
          0  redo size
        609  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

The above statistics shows that the query is taking 10.21 seconds for execution. In the production environment this query was running concurrently from 10-15 sessions continuously and causing latch free and buffer busy waits which results in increased in the elapsed time up to 40-45 seconds per execution.
This query is going for FULL TABLE SCAN on BATCH_DTLS which was performing 1097 physical reads and 152102 consistent gets. Physical Read is highly expensive operation in any system.

Why optimizer has not used an INDEX ?

I have mentioned that there is already an Index present on ONLINE_BATCH_IND column but that Index is not used by optimizer. There are 52,96,999 rows present in the table and ONLINE_BATCH_IND column has only 2 unique values in the table. No null value present for any row. Data distribution is equal for these two values. Optimizer thinks that it has to query around 26 lacs rows for this query that is almost 50% of data. For queering such huge amount of data its always better to go for scattered it that is FULL TABLE SCAN as it is multi-block read. If we enforce oracle to to sequential read using the present index then it will be much more time consuming.

Oracle is not using the present index on the table and there are only two columns used in the query so I have decided to drop this index and create the composite index on ONLINE_BATCH_IND and BATCH_DATE column so that required data will be accessed from Index only. After creation of this Index I am again executing this query. Post creation of this index query started using the created index.

SQL>
SQL> SELECT MIN (BATCH_DATE), MAX (BATCH_DATE)
  FROM SA.BATCH_DTLS a
 WHERE ONLINE_BATCH_IND = 'O';

MIN(BATCH_DATE) MAX(BATCH_DATE)
--------------- ---------------
02-APR-05       28-AUG-15

Elapsed: 00:00:02.38

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=10)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'IDX_BATCH_DTLS_COMP' (NON-UNIQUE) (Cost=4 Card=2648500 Bytes=26485000)

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
      15703  consistent gets
          0  physical reads
          0  redo size
        609  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Above statistics shows us that now query is taking 02.38 seconds. We have reduced the execution time from 10 seconds to 2 seconds. Statistics shows that query has performed 0 physical reads as index blocks cached in the memory. Also consistent gets reduced from 152102 to 15703. Query is using index but it is going for INDEX FAST FULL SCAN operation. This is again the scattered read operation. This is because we are queering MIN and MAX of BATCH_DATE.

Can we optimize this query further?

After creating an index problem with this query resolved but still query is taking 2 seconds to execute. Now I am checking possibilities rewriting this query. I have rewrite the query as shown below. Now I am executing this rewritten query.

SQL>
SQL> SELECT *
  FROM (SELECT MIN (batch_date)
          FROM SA.BATCH_DTLS
         WHERE online_batch_ind = 'O'),
       (SELECT MAX (batch_date)
          FROM SA.BATCH_DTLS
         WHERE online_batch_ind = 'O');

MIN(BATCH_DATE) MAX(BATCH_DATE)
--------------- ---------------
02-APR-05       28-AUG-15

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=18)
   1    0   MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=18)
   2    1     VIEW (Cost=2 Card=1 Bytes=9)
   3    2       SORT (AGGREGATE)
   4    3         FIRST ROW (Cost=2 Card=2648500 Bytes=26485000)
   5    4           INDEX (RANGE SCAN (MIN/MAX)) OF 'IDX_BATCH_DTLS_COMP' (NON-UNIQUE) (Cost=2 Card=2)
   6    1     FIRST ROW
   7    6       VIEW (Cost=2 Card=1 Bytes=9)
   8    7         SORT (AGGREGATE)
   9    8           FIRST ROW (Cost=2 Card=2648500 Bytes=26485000)
  10    9             INDEX (RANGE SCAN (MIN/MAX)) OF 'IDX_BATCH_DTLS_COMP' (NON-UNIQUE) (Cost=2 Card=2)

Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        609  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

From the above statistics we can see that query gets executed in microseconds. Also consistent gets reduced from 15703 to 10. Execution plan shows that it is performing MERGE JOIN CARTESIAN but we know that MIN and MAX always return only one value so that it is not costlier operation in this scenario.

• 100% Practical Training

• Hands
  On
  Training

• 100%
  Job
  Gurantee

Interested to be a trainer?

jobs@greenstech.in

For corporate Training

corp@greenstech.in

Copyright © 2018 Greens Technologys. All Rights Reserved. Privacy Policy