Oracle SQL Query Tuning Examples, Part 1

This is the first post in a series of interesting SQL tuning real life cases. These are, hopefully, more interesting than the usual tuning steps: looking for full table scans, missing indexes, missing statistics, obvious wrong execution plan.

The first examples is querying table1, which is about 100 GB in size , and it has an index on sys_creation_date.



select * from TABLE1 cs
where actv_code = 'MCN'
and trunc(sys_creation_date)=to_date('20130331','YYYYMMDD')
/


Execution Plan
----------------------------------------------------------
Plan hash value: 1027680822

------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost  |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |   101K|    12M| 35815 |        |      |            |
|   1 |  PX COORDINATOR      |                  |       |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000         |   101K|    12M| 35815 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |                  |   101K|    12M| 35815 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| TABLE1 |   101K|    12M| 35815 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------

The optimizer goes with a full table scan, since we have a function on the sys_creation_date and we don't have a function based index defined on trunc(sys_creation_date).

The interesting thing here is that we are looking for a date equal to March 31st 2013, and this can be accomplished by moving the trunc function to the right side and now the optimizer will choose the index.


  SQL> l
  1  select * from TABLE1 cs
  2  where actv_code = 'MCN'
  3* and sys_creation_date >= to_date('21030331','YYYYMMDD') and sys_creation_date < to_date('20130401','YYYYMMDD')
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3230845712

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |   132 |     0 |
|*  1 |  FILTER                      |                      |       |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TABLE1     |     1 |   132 |     3 |
|*  3 |    INDEX RANGE SCAN          | TABLE1_1IX |    15 |       |     1 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("ACTV_CODE"='MCN')
   3 - access("SYS_CREATION_DATE">=TO_DATE(' 2103-03-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "SYS_CREATION_DATE"<TO_DATE(' 2013-04-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - cpu costing is off (consider enabling it)


 Note that we cannot use the between operator, since it is inclusive, and we don't want dates of April 1st.
 So we've managed to avoid an expensive full table scan and to take advantage of the existing index, by re-writing the query; the two queries are logically identical.