Oracle Database Discussions

Hi All,

I am on 11.2 on Linux.

I am looking into a performance issue. The issue is around 1 particular SQL, involving about 5 tables.
I re-gathered statistics on 2 main tables in the query (out of 5 tables).
When I say re-gathered, I first did DBMS_STATS.DELETE_TABLE_STATS and then did DBMS_STATS.GATHER_TABLE_STATS.
Earlier, we had histograms on these tables, which I removed and gathered stats without generating histograms.

SQL> select table_name, num_rows, sample_size, last_analyzed from user_tables where
  2  table_name in ( 'DETAIL_TABLE','MASTER_TABLE');

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ---------- ----------- -------------------
MASTER_TABLE                     50615338    50615338 01/10/2012 11:09:27
DETAIL_TABLE                    353550440   353550440 01/10/2012 11:10:05

2 rows selected.

Then ran the SQL again couple of times (actually, that SQL is in a stored procedure, which I ran couple of times).

I found this wonderfull SQL on internet, which tells me when the SQL ran and which plan (identified by its hash value) it used. Using this SQL I tried to check if my SQL was run using any different plan, but it used exactly same plan it used before I re-gathered the stats. See the last analyzed time above and begin_interval_time below, same SQL has run before and after stats collection, with same plan_hash_value.

SQL> select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
  2  nvl(executions_delta,0) execs,
  3  (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
  4  (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
  5  from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
  6  where sql_id = nvl('&sql_id','4dqs2k5tynk61')
  7  and ss.snap_id = S.snap_id
  8  and ss.instance_number = S.instance_number
  9  and executions_delta > 0
 10  order by 1, 2, 3;
Enter value for sql_id: 1tcfvxjmwyqwc
old   6: where sql_id = nvl('&sql_id','4dqs2k5tynk61')
new   6: where sql_id = nvl('1tcfvxjmwyqwc','4dqs2k5tynk61')

   SNAP_ID   NODE BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME        AVG_LIO
---------- ------ ------------------------------ ------------- --------------- ------------ ------------ --------------
     17329      1 28-SEP-12 15.00.46.907         1tcfvxjmwyqwc      2806693359           93        1.227      144,237.8
     17333      1 28-SEP-12 17.00.51.988         1tcfvxjmwyqwc      2806693359           31         .339      144,719.2
     17357      1 29-SEP-12 05.00.24.926         1tcfvxjmwyqwc      2806693359            3        4.010      146,604.7
     17358      1 29-SEP-12 05.30.26.258         1tcfvxjmwyqwc      2806693359           28        1.720      140,573.9
     17371      1 29-SEP-12 12.00.43.268         1tcfvxjmwyqwc      2806693359           31         .335      144,725.1
     17467      1 01-OCT-12 12.00.54.396         1tcfvxjmwyqwc      2806693359           31         .336      144,726.4
     17468      1 01-OCT-12 12.30.55.678         1tcfvxjmwyqwc      2806693359           62         .330      144,726.1

7 rows selected.

My question is, when I re-gathered stats on 2 tables out of 5 tables in a given SQL, are the plans not flushed out of SGA?
I was expecting that, at least a new plan hash value would show up front of my SQL, before and after stats collection.
The new plan is good or bad, I was expecting a new plan being generated.
Should a new plan be created every time table stats is re-gathered?

Thanks in advance