This discussion is archived
8 Replies Latest reply: Oct 7, 2012 4:39 AM by rahulras RSS

Does old plan stay after re-gathering stats ?

rahulras Explorer
Currently Being Moderated
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
  • 1. Re: Does old plan stay after re-gathering stats ?
    Iordan Iotzov Expert
    Currently Being Moderated
    >
    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?
    DBMS_STATS accepts NO_INVALIDATE parameter – whether or not the plan is flushed out of the SGA should be based on the value of that parameter.

    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?
    Assuming that the gather stats invalidated the old plan, the optimizer could have chosen exactly the same plan after that. That is a likely scenario, particularly if the stats before and after the gathering are not that different. What is the value of FIRST_LOAD_TIME and LAST_LOAD_TIME in V$SQL for the SQL in question?


    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 2. Re: Does old plan stay after re-gathering stats ?
    JohnWatson Guru
    Currently Being Moderated
    The default setting for the NO_INVALIDATE parameter of your GATHER_%_STATS call means that Oracle will not always invalidate the old plan immediately. The parameter optimizerinvalidation_period controls the max time before invalidation, and the default of 18000 seconds is a surprisingly long time.
  • 3. Re: Does old plan stay after re-gathering stats ?
    Mark D Powell Guru
    Currently Being Moderated
    One thing to note about the posted query is that it read the dba_hist_* views which with 5 exceptions require you to posses an extra cost EM Performance Pack license to query. So the sql is not suitable for use by many shops.

    With 11g you can configure dbms_stats to automatically 'publish' the statistics or to put them in 'pending' mode where the statistics do not take effect until you publish them. You can do this at the system, schema, or table level.

    Check to see what you have set. (Default out of the box should be publish).

    HTH -- Mark D Powell --
  • 4. Re: Does old plan stay after re-gathering stats ?
    rahulras Explorer
    Currently Being Moderated
    So many good points from you guys.

    I checked the "publish" preference, looks like stats will be automatically published
    SQL> select dbms_stats.get_prefs( pname => 'PUBLISH', ownname => USER, tabname => 'MASTER_TABLE') as PUBLISH_STATS from dual ;
    
    PUBLISH_STATS
    ---------------------------------------------------------------------------------------------------------------------------
    TRUE
    
    1 row selected.
    Also, I re-gathered stats for MASTER_TABLE with "no_invalidate => FALSE" and "cascade => TRUE". See the last analysed time below. However, same plan is used.
    TABLE_NAME                       NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
    ------------------------------ ---------- ----------- -------------------
    MASTER_TABLE                     50615338    50615338 01/10/2012 16:11:26
    DETAIL_TABLE                    353550440   353550440 01/10/2012 11:10:05
    
       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
         17475      1 01-OCT-12 16.00.04.750         1tcfvxjmwyqwc      2806693359           31         .335      144,733.5
         17476      1 01-OCT-12 16.30.06.053         1tcfvxjmwyqwc      2806693359           31         .334      144,733.5
    
    9 rows selected.
    One more thing to add here, the table(s) in question here are IOTs. Does that make any difference? I checked the stats on the primary key index, they are all done the same time I gathered stats.

    All I am looking for here is to clear my concept. I am expecting that, when I gathered stats on the table (now with no_invalidate=>FALSE and stats are published), I expect Oracle to re-create plan for SQLs involving that table. If that is correct, why it is not showing up in these SQLs?
    Any other way to verify this? am I missing anything here?
  • 5. Re: Does old plan stay after re-gathering stats ?
    JohnWatson Guru
    Currently Being Moderated
    Your problem could be the release of the database. 11.2.0.1 did not always invalidate when it should, is that the release you are running? If so, I would test with witth 11.2.0.3.
  • 6. Re: Does old plan stay after re-gathering stats ?
    rahulras Explorer
    Currently Being Moderated
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for Linux: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    
    5 rows selected.
  • 7. Re: Does old plan stay after re-gathering stats ?
    Mark D Powell Guru
    Currently Being Moderated
    Are you actually looking at the plan as shown by v$sql_plan or just using explain plan to determine the plan? The output of a regular explain plan may not be the plan actually used. Otherwise any update on what you have found?


    HTH -- Mark D Powell --

    PS - Do you have sql plan baselines in effect? An accepted baseline will stop plan changes from new statistics from taking effect for specific sql.

    Edited by: Mark D Powell on Oct 5, 2012 1:23 PM
  • 8. Re: Does old plan stay after re-gathering stats ?
    rahulras Explorer
    Currently Being Moderated
    Hi Mark D Powell,

    If you look at my query earlier in the post, I am looking into the DBA_HIST_* views. As per my understanding, these views are populated by automatic stats collection process (from v$ views including v$sql_plan). So, I assume these are actually being used by Oracle.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points