Execution Plan

If the query is running slower, then it is probably using the bad plan. This can be checked by running below query and observing the plan number for the latest plan.

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select plan_hash_value, child_number, TIMESTAMP from v$sql_plan where sql_id ='49fhsvm5z337y' order by 3;

This can usually be resolved by flushing the query from the cache by using the below steps:

select address, hash_value from v$sqlarea where sql_id='49fhsvm5z337y';

Use the values from above query in the below purge statement:

exec sys.dbms_shared_pool.purge('C000000EF6D74030,3421605118','C');

Following this, the query should use the correct plan:

SQL> select plan_hash_value, child_number, TIMESTAMP from v$sql_plan where sql_id ='49fhsvm5z337y' order by 3;

PLAN_HASH_VALUE CHILD_NUMBER TIMESTAMP
--------- ------- ------------
     1784340712 0 01-DEC-2012 04:55
     1784340712 0 01-DEC-2012 04:55
     1784340712 0 01-DEC-2012 04:55
     1784340712 0 01-DEC-2012 04:55
If the above flush does not work, then another possible option could be to  analyze the table with a 100% estimate:

Begin
 dbms_stats.gather_table_stats(ownname=>'EPUSER1',
tabname=>'LISTING_CUR_DIM',estimate_percent=>100,
method_opt=>' FOR ALL INDEXED COLUMNS SIZE AUTO', degree=>2,granularity=>'DEFAULT',cascade=>TRUE,no_invalidate=>FALSE);
END;
Also, here's a very helpful query to check the execution history of the SQL along with the execution plan:

set pages 100 lines 204 wrap off
column "Parsing Schema" format a15
column "ms Per Exec" format 9999999.99
column "Total Execs" format 999999999
column "Gets Per Exec" format 99999999
column "CPU %" format 99999999999
column "Rows Per Exec" format 99999999
column "Disk Reads Per Exec" format 99999999
Column "Start Time" format a18
Column "End Time" format a18
column " TPS" format 99999
select  a.snap_id "Snap Id", parsing_schema_name "Parsing Schema", plan_hash_value,
        to_char(min(b.begin_interval_time),'MM/DD/YYYY HH24:MI') "Start Time", to_char(max(b.end_interval_time),'MM/DD/YYYY HH24:MI') "End Time",
        a.sql_id, round(sum(elapsed_time_delta)/1000/sum(executions_delta),2) "ms Per Exec", sum(a.executions_delta) "Total Execs",
        round(sum(buffer_gets_delta)/sum(executions_delta)) "Gets Per Exec",  
    round(sum(rows_processed_delta)/sum(executions_delta)) "Rows Per Exec"
from ( select snap_id, sql_id, instance_number,parsing_schema_name, plan_hash_value, executions_delta, elapsed_time_delta,
    buffer_gets_delta, cpu_time_delta, disk_Reads_delta,rows_processed_delta
from dba_hist_sqlstat x
where sql_id in ('49fhsvm5z337y')
and   x.snap_id between  1 and 100000000) a, dba_hist_snapshot b
where executions_delta >0
and   a.snap_id = b.snap_id
and   a.instance_number=b.instance_number
group by a.snap_id, a.sql_id, parsing_schema_name, plan_hash_Value
order by 2,1;

The output of the above query will be as below. As can be seen from the execution history, when the plan_hash_value is 1784340712, the query gets executed in < 10 ms per exec and performs <=6 block reads per execution ("Gets Per Exec"). Because of the low elapsed time for the query, there are also more executions of the query. With the bad plan, the execution time is very high and "gets per exec" also indicates there are more data blocks being read.


set pages 100 lines 204 wrap off
column "Parsing Schema" format a15
column "ms Per Exec" format 9999999.99
column "Total Execs" format 999999999
column "Gets Per Exec" format 999999999
column "CPU %" format 99999999999
column "Rows Per Exec" format 99999999
column "Disk Reads Per Exec" format 99999999
Column "Start Time" format a18
Column "End Time" format a18
column " TPS" format 99999
select  a.snap_id "Snap Id", parsing_schema_name "Parsing Schema", plan_hash_value,
        to_char(min(b.begin_interval_time),'MM/DD/YYYY HH24:MI') "Start Time", to_char(max(b.end_interval_time),'MM/DD/YYYY HH24:MI') "End Time",
        a.sql_id, round(sum(elapsed_time_delta)/1000/sum(executions_delta),2) "ms Per Exec", sum(a.executions_delta) "Total Execs",
        round(sum(buffer_gets_delta)/sum(executions_delta)) "Gets Per Exec",
    round(sum(rows_processed_delta)/sum(executions_delta)) "Rows Per Exec"
from ( select snap_id, sql_id, instance_number,parsing_schema_name, plan_hash_value, executions_delta, elapsed_time_delta,
    buffer_gets_delta, cpu_time_delta, disk_Reads_delta,rows_processed_delta
from dba_hist_sqlstat x
where sql_id in ('&sid')
and   x.snap_id between  1 and 100000000) a, dba_hist_snapshot b
where executions_delta >0
and   a.snap_id = b.snap_id
and   a.instance_number=b.instance_number
group by a.snap_id, a.sql_id, parsing_schema_name, plan_hash_Value
order by 2,1;