Before ORACLE 10G, object statistics gathering will immediately invalid all the underline execution plans in cache, plan re-generate will trigger when SQL be called again. After ORACLE 10G, underline parameter “_optimizer_invalidation_period” will control when the execution plan can be expired.
More frequency of object statistics gathering, more versions of SQL plan in cache. In 10G (tested in 10.2.0.3), Same plan may has diff version or child number of plan. Testing showed : For one SQL ID, any underline object statistics will trigger the optimizer re-gen the execution plan, and it was controlled by “_optimizer_invalidation_period” with default value = 5 hours.
After object statistics gathering with no_validation = AUTO (default), when underline (affected) SQL be called, Optimizer will pick latest valid execution plan and run. If the running time exceed the “_optimizer_invalidation_period” then after running the latest plan will be marked as “expired”. In 11G, this “expired” plan will not be used in future (except plan had dependency on bind variable) ; in 10G, it will not be expired and just marked as “switch plan/re-gen plan” in next calling.
In 10G, execution plans in cache will be used at least once and new children (new version of plan) will be generated in final. Switching or Re-gen will happen after “_optimizer_invalidation_period” from last calling time if plan parse timing is before underline objects statistics gathering.
In 11G, latest execution plans (need match to bind variable peeking value if have) will be call by default, and it will “marked” as “expired” if calling time exceed “_optimizer_invalidation_period” after object statistics gathering. New version of execution will be generated if re-run SQL after “_optimizer_invalidation_period” from “marked” “expired”.
If object statistics gathering with option no_validation = false, then underline execution plan(s) will flush out from cache. New plan will be re-gen in next time call.
Cases in below all tested on the columns with stewed values , and with histogram statistics. In 10G, after statistics gathering, new or best optimized plan may need re-run query more than once (depended how many children in the cache for this SQL), and some old bad plan in cache will be call at least once before right plan be used !! In 10G, it may need extra handle to retire the SQL plan in cache more frequently or config gathering statistics with no_validation = false (immediate invalidate plan after statistics be gathered).
In the tests, we changed the invalid period from 5 hours to 1 minute.
alter system set “_optimizer_invalidation_period” = 60 scope=memory
And gather table statistics
exec dbms_stats.gather_table_stats('SCOTT','PT1',cascade=>true);
Then, re-run the following SQL until new child of execution plan be generated.
SQL> r 1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20 MAX(OBJECT_ID) MIN(OBJECT_ID) -------------- -------------- 46479 20
During the each call, monitored the V$SQL for this SQL ID.
………………………………
In 10G, Repeated same SQL until new version generated, if re-call SQL with in 1 minute (“_optimizer_invalidation_period”), then plan would not switch. If the data changes to diff histogram, and optimized plan should be “3214516708”, but each “3347831050” with diff child_number cached in shared pool would be used at least once (depend on how many times called during “_optimizer_invalidation_period”). It would be worse off when bind variable peeking involved. See appendix A for detail in ORACLE 10.2.0.3
SQL> r 1 select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, LAST_LOAD_TIME, IS_OBSOLETE 2* from v$sql where sql_id='9fx3nzvp2scyn' SQL_ID EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME LAST_LOAD_TIME I ------------- ---------- ---------- --------------- ------------ - -------------------- ------------------ - 9fx3nzvp2scyn 7 3928765396 3347831050 0 N 04-FEB-2013:17:47:11 2013-02-01/17:42:04 N 9fx3nzvp2scyn 7 3928765396 3347831050 1 N 04-FEB-2013:17:46:08 2013-02-01/17:46:34 N 9fx3nzvp2scyn 4 3928765396 3347831050 2 N 04-FEB-2013:17:45:11 2013-02-01/17:54:35 N 9fx3nzvp2scyn 3 3928765396 3347831050 3 N 04-FEB-2013:17:44:17 2013-02-01/18:03:14 N 9fx3nzvp2scyn 4 3928765396 3214516708 4 N 04-FEB-2013:17:41:32 2013-02-01/18:17:45 N 9fx3nzvp2scyn 6 3928765396 3347831050 5 N 05-FEB-2013:09:26:46 2013-02-04/17:48:58 N 6 rows selected. == after 9 times calls , 3 times called with 1 minute == SQL> r 1 select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, LAST_LOAD_TIME, IS_OBSOLETE 2* from v$sql where sql_id='9fx3nzvp2scyn' SQL_ID EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME LAST_LOAD_TIME I ------------- ---------- ---------- --------------- ------------ - -------------------- ------------------ - 9fx3nzvp2scyn 8 3928765396 3347831050 0 N 05-FEB-2013:09:46:29 2013-02-01/17:42:04 N 9fx3nzvp2scyn 10 3928765396 3347831050 1 N 05-FEB-2013:09:40:41 2013-02-01/17:46:34 N 9fx3nzvp2scyn 5 3928765396 3347831050 2 N 05-FEB-2013:09:39:32 2013-02-01/17:54:35 N 9fx3nzvp2scyn 4 3928765396 3347831050 3 N 05-FEB-2013:09:37:04 2013-02-01/18:03:14 N 9fx3nzvp2scyn 5 3928765396 3214516708 4 N 05-FEB-2013:09:35:01 2013-02-01/18:17:45 N 9fx3nzvp2scyn 7 3928765396 3347831050 5 N 05-FEB-2013:09:48:47 2013-02-04/17:48:58 N 9fx3nzvp2scyn 1 3928765396 3214516708 6 N 05-FEB-2013:09:49:50 2013-02-05/09:49:51 N
7 rows selected.
In 11G, no loop on the older version of execution plan, and It had improved on the bind variable peeking too. The LAST_ACTIVE_TIME in 11G always happened before LAST_LOAD_TIME of next version.
SQL> r
1 select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, LAST_LOAD_TIME, IS_OBSOLETE 2* from v$sql where sql_id='1s1033aqhf1q4' SQL_ID EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME LAST_LOAD_TIME I ------------- ---------- ---------- --------------- ------------ - -------------------- ------------------- - 1s1033aqhf1q4 4 2902918852 599033881 0 N 05-FEB-2013:16:42:06 2013-02-05/16:39:47 N 1s1033aqhf1q4 3 2902918852 599033881 1 N 05-FEB-2013:17:08:16 2013-02-05/16:43:57 N 1s1033aqhf1q4 3 2902918852 3077296115 2 N 05-FEB-2013:17:20:05 2013-02-05/17:18:40 N
== after twice calls ==
SQL> r
1 select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, LAST_LOAD_TIME, IS_OBSOLETE
2* from v$sql where sql_id=’1s1033aqhf1q4′
SQL_ID EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME LAST_LOAD_TIME I ------------- ---------- ---------- --------------- ------------ - -------------------- ------------------- - 1s1033aqhf1q4 4 2902918852 599033881 0 N 05-FEB-2013:16:42:06 2013-02-05/16:39:47 N 1s1033aqhf1q4 3 2902918852 599033881 1 N 05-FEB-2013:17:08:16 2013-02-05/16:43:57 N 1s1033aqhf1q4 4 2902918852 3077296115 2 N 05-FEB-2013:17:22:09 2013-02-05/17:18:40 N 1s1033aqhf1q4 1 2902918852 3077296115 3 N 05-FEB-2013:17:24:27 2013-02-05/17:24:26 N
So, When find execution plan changed due to the statistics idel, and manual gathering the statistics, if you do not invalidate the execution immediate, the new plan or correct plan may not be generated or re-use. In 10G, it even need goes thru every version of execution plan in invalidation period which default value is 5 hours, before create the new version plan.
Fred's ORACLE DBA Works 