Oracle Performance Tuning

Today we had a request from customer stating that a query is running more than 5 mins as opposed to run in less than 2 minutes.

Query Performance Slow and taking too long:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3491363858

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    22 | 48742   (1)| 00:09:45 |
|   1 |  SORT AGGREGATE    |      |     1 |    22 |            |          |
|*  2 |   TABLE ACCESS FULL| T217 |    74 |  1628 | 48742   (1)| 00:09:45 |  --> Even though table and the column C536900006 are having indexes these are not used.
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("T217"."C536900006">=1370041200 AND
              "T217"."C60000001"='Access - GCC' AND "T217"."C536900006"<=1372633
140

              AND "T217"."C536870916"=0)

16 rows selected.


From DBA_IND_COLUMNS  --> View which gives the list of Indexes created a cloumns 
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
I217_536870919_1               T217                           C536870919
I217_536870919_1               T217                           C4
I217_536871159_1               T217                           C536871159
I217_536870913_1               T217                           C536870913
I217_536870913_1               T217                           C60000001
I217_60000001_1                T217                           C60000001
I217_60000001_1                T217                           C536870919
I217_7_1                       T217                           C7
I217_2_1                       T217                           C2
I217_536871222_1               T217                           C536871222
IT217                          T217                           C1




SQL> select last_analyzed from dba_tables where table_name='T217';

LAST_ANAL
---------
09-JUL-13


Thought of analysing the table which may fix and issued:

analyze table <tablename> compute statistics;

explani plan for <select statement>;

select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 597076386

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |   130 | 10150   (1)| 00:02:02 |
|   1 |  SORT ORDER BY               |                 |     1 |   130 | 10150   (1)| 00:02:02 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T217            |     1 |   130 | 10149   (1)| 00:02:02 | --> Index is being used
|*  3 |    INDEX RANGE SCAN          | I217_60000001_1 |  8851 |       |   120   (0)| 00:00:02 |
------------------------------------------------------------------------------------------------


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

   2 - filter("T217"."C536900006">=1370041200 AND "T217"."C536900006"<=1372633140 AND
              "T217"."C536870916"=0)
   3 - access("T217"."C60000001"='Access - GCC')


17 rows selected.