Gather Statistics AUTO_INVALIDATE 减少db的 library cache lock

这个参数可以用于解决gather statistics 导致的library cache lockOracle 最高效hard parse的办法gather statistics 后不会标记失效不执行不会无效执行一次不会无效执行一次才开始计时计时结束也不会标记无效。再次执行才会再次触发无效hard parse执行。1. 第一次执行不会失效也不会立即再次parse2.第一次执行后开始计算过期过期时间不超过最大 a (pseudo-)random value up to _optimizer_invalidation_period sec from the time of this parse. 大于过期时间后也不会mark 成invlidate3.再次执行才会标记无效并再次hard parse.Applies ToAll UsersSummaryStarting with Oracle10g, the DBMS_STATS package offers the AUTO_INVALIDATE option for the NO_INVALIDATE parameter of its GATHER_xxx_STATS and DELETE_xxx_STATS procedures. This parameter allows the user to specify when to invalidate dependent cursors i.e. cursors cached in the library cache area of the shared pool which reference a table, index, column or fixed object whose statistics are modified by the procedure call.According to the documentation the values NO_INVALIDATE can take are:TRUE: does not invalidate the dependent cursorsFALSE: invalidates the dependent cursors immediatelyAUTO_INVALIDATE (default): have Oracle decide when to invalidate dependent cursorsThis document describes details of how AUTO_INVALIDATE works.This article may be of interest to DBAs interested in the behaviour of DBMS_STATS with respect to cursor invalidations.SolutionWhen statistics are modified by DBMS_STATS, new cursors not yet cached in the shared pool will use them to get execution plans. Existing cursors cached in the shared pool cannot update their execution plans however. Instead, such cursors are invalidated and new versions (children cursors) are created which get execution plans based on the updated statistics. This involves a hard-parse operation which is more expensive in resource consumption than a soft-parse which simply reuses a cached cursor.An important question to consider is: when does this invalidation occur in time after the statistics have been modified ?Cursor Invalidations on Gathering Statistics prior to Oracle10gIn releases prior to Oracle10g gathering statistics using DBMS_STATS resulted in immediate invalidations of dependent cached cursors, unless NO_INVALIDATE was set to TRUE. An invalidation of a cached cursor means it has to be hard-parsed the next time it is executed. If large numbers of such cursors had to be invalidated and immediately re-executed e.g. due to DBMS_STATS being used on a popular object during a time period of heavy user workload, this would result in a hard-parse spike which could have serious effects on performance including high CPU usage, heavy library cache and shared pool latch contention with subsequent slowdown in application user response times.Setting NO_INVALIDATE to TRUE would prevent such spikes but this meant that cursors would not notice the updated object statistics and would continue using existing execution plans until hard-parsed. Such a hard parse could happen on a cursor reload (i.e. on the next execution following the cursor being automatically flushed out of the shared pool due to inactivity and heavy usage of other cursors) or after a manual flushing of the shared pool (which could itself also result in hard-parse spikes as most of the flushed-out cursors would need to do a hard parse on their next execution.)Cursor Invalidations with Oracle10g and AUTO_INVALIDATEWith the AUTO_INVALIDATE option the goal is to spread out the cursor invalidations over a time period long enough for hard-parses not to cause noticeable spikes.In this way a cached cursor depending on an object whose statistics have been modified by DBMS_STATS will be invalidated as follows:when DBMS_STATS modifies statistics for an object, all current cached cursors depending on this object are marked for rolling invalidation. Lets call this time T0.the next time a session executes a cursor marked for rolling invalidation, it sets a timestamp. This timestamp can take a (pseudo-)random value up to _optimizer_invalidation_period sec from the time of this parse. The reason it does this is to randomly distribute the actual invalidation so as to avoid multiple parses occurring at the same time as much as possible.The default for this parameter is 18000 sec i.e. 5 hours. Lets call the time of this parse T1 and the timestamp value Tmax. On this (first) execution and parse we reuse the existing cursor i.e. we do not hard-parse and do not use the modified statistics to generate a new plan (it is a soft parse.)on every subsequent execution and parse of this cursor (which is now marked for rolling invalidation and timestamped) we check whether the current time T2 exceeds the timestamp Tmax. If not, we reuse the existing cursor again, as happened on the first (soft) parse at time T1. If Tmax has been exceeded, we invalidate the cached cursor and create a new version of it (a new child cursor) which uses the new statistics of the object to generate its execution plan. The new child is marked ROLL_INVALID_MISMATCH in V$SQL_SHARED_CURSOR to explain why we could not share the previous child.From the above descriptions, it follows that:a cursor which is never parsed again after being marked for rolling invalidation will not beinvalidated and may eventually be flushed out of the shared pool if memory becomes scarcea cursor which is only parsed once after being marked for rolling invalidation will not be invalidated(it will only be timestamped) and again may be eventually flushed out if memory in the shared pool becomes scarce ---这个标记后还是不会变为失效状态。cursors which are regularly reused will become invalidatedon the next parse that happens after the timestamp Tmax has been exceeded(只有invalidate 才会再次执行时hard parse)It should be clear that the above method is efficient in that it incurs the overhead of invalidations only for frequently reused cursors.Exception:parallel SQL are immediately invalidated in order to ensure consistency between execution plans of slaves and Query Coordinator across multiple RAC instances. This is not a problem as parallel SQL are usually heavy and therefore hard-parse resources are insignificant to their total resource usage.Testcase for Cursor Invalidations using AUTO_INVALIDATEHere is a short testcase demonstrating the above from a 10.2.0.3 database. It creates a simple table, runs a simple query on it, then gathers statistics and monitors what happens to the cached cursor for the query as time passes and the query is re-executed.First connect to the database as SYSDBA using SQL*Plus and set it up to show current time, detailed timestamps and reduce the invalidation parameter to 5 minutes from the default of 5 hours:SQL set time on pages 100015:48:41 SQL alter session set nls_date_formatdd/mm/yyyy hh24:mi:ss;Session altered.15:48:41 SQL alter system set _optimizer_invalidation_period300;System altered.Then create a small table and gather statistics on it:15:48:41 SQL create table X as select * from dba_tables;Table created.15:48:41 SQL exec dbms_stats.gather_table_stats(null,X);PL/SQL procedure successfully completed.15:48:42 SQL select last_analyzed from dba_tables where table_nameX;LAST_ANALYZED-------------------13/03/2008 15:48:42Now run our example query and check its cursor:15:48:42SQL select count(*) from X;COUNT(*)----------159015:48:42 SQL select sql_id from v$sql where sql_textselect count(*) from X;SQL_ID-------------95rckg79jgshh15:48:42 SQL select * from v$sql_shared_cursor where sql_id95rckg79jgshh;SQL_ID ADDRESS CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -95rckg79jgshh 3076145C 33EFD654 0 N N N N N N N N N N N N N N N N N NN N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N15:48:42 SQL select child_number,parse_calls,executions,first_load_time,last_load_time,last_active_time from v$sql where sql_id95rckg79jgshh;CHILD_NUMBER PARSE_CALLSEXECUTIONSFIRST_LOAD_TIME LAST_LOAD_TIME------------ ---------------------------------------- -------------------LAST_ACTIVE_TIME-------------------0 112008-03-13/15:12:47 2008-03-13/15:48:4213/03/200815:48:42We can see a single child cursor parsed executed once at 15:18:42. Lets execute it once more and compare:15:48:53SQL select count(*) from X;COUNT(*)----------159015:48:55 SQL select child_number,parse_calls,executions,first_load_time,last_load_time,last_active_time from v$sql where sql_id95rckg79jgshh;CHILD_NUMBER PARSE_CALLSEXECUTIONSFIRST_LOAD_TIME LAST_LOAD_TIME------------ ---------------------------------------- -------------------LAST_ACTIVE_TIME-------------------0 222008-03-13/15:12:47 2008-03-13/15:48:4213/03/200815:48:53We can see now it has 2 executions (and parses) and last execution time has been updated to 15:48:53.Let us gather statistics on the table using the default which will be AUTO_INVALIDATE:15:48:55 SQL exec dbms_stats.gather_table_stats(null,X);PL/SQL procedure successfully completed.15:49:08 SQL select last_analyzed from dba_tables where table_nameX;LAST_ANALYZED-------------------13/03/2008 15:49:0715:49:08 SQL select * from v$sql_shared_cursor where sql_id95rckg79jgshh;SQL_ID ADDRESS CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -95rckg79jgshh 3076145C 33EFD654 0 N N N N N N N N N N N N N N N N N NN N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N15:49:08 SQL select child_number,parse_calls,executions,first_load_time,last_load_time,last_active_time from v$sql where sql_id95rckg79jgshh;CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME------------ ----------- ---------- ------------------- -------------------LAST_ACTIVE_TIME-------------------0 2 2 2008-03-13/15:12:47 2008-03-13/15:48:4213/03/2008 15:48:53Nothing has changed on the cursor although we know it has been marked behind the scenes for rolling invalidation. Now let us wait for longer than the _optimizer_invalidation_period e.g. 6 minutes, then check the cursor again:15:55:24 SQL select * from v$sql_shared_cursor where sql_id95rckg79jgshh;SQL_ID ADDRESS CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -95rckg79jgshh 3076145C 33EFD654 0 N N N N N N N N N N N N N N N N N NN N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N15:55:24 SQL select child_number,parse_calls,executions,first_load_time,last_load_time,last_active_time from v$sql where sql_id95rckg79jgshh;CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME------------ ----------- ---------- ------------------- -------------------LAST_ACTIVE_TIME-------------------0 2 2 2008-03-13/15:12:47 2008-03-13/15:48:4213/03/2008 15:48:53Now let us see what happens when we execute it again. This will do the first parse after statistics were gathered and the cursor was marked for rolling invalidation:15:55:35SQL select count(*) from X;COUNT(*)----------159015:55:37 SQL select * from v$sql_shared_cursor where sql_id95rckg79jgshh;SQL_ID ADDRESS CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -95rckg79jgshh 3076145C 33EFD654 0 N N N N N N N N N N N N N N N N N NN N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N15:55:37 SQL select child_number,parse_calls,executions,first_load_time,last_load_time,last_active_time from v$sql where sql_id95rckg79jgshh;CHILD_NUMBER PARSE_CALLSEXECUTIONSFIRST_LOAD_TIME LAST_LOAD_TIME------------ ---------------------------------------- -------------------LAST_ACTIVE_TIME-------------------0 332008-03-13/15:12:47 2008-03-13/15:48:4213/03/200815:55:35We can see that the same cursor has been reused, even though we waited for longer than the invalidation period parameter. This shows that the time period where the cursor will be invalidated does not begin until this first parse after statistics are modified has happened. This parse is what sets the Tmax timestamp.Let us wait another 6 minutes, during this time we expect the timestamp Tmax to run out:16:01:00 SQL select * from v$sql_shared_cursor where sql_id95rckg79jgshh;SQL_ID ADDRESS CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -95rckg79jgshh 3076145C 33EFD654 0 N N N N N N N N N N N N N N N N N NN N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N16:01:01 SQL select child_number,parse_calls,executions,first_load_time,last_load_time,last_active_time from v$sql where sql_id95rckg79jgshh;CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME------------ ----------- ---------- ------------------- -------------------LAST_ACTIVE_TIME-------------------0 3 3 2008-03-13/15:12:47 2008-03-13/15:48:4213/03/2008 15:55:35Nothing happens to the cursor until we execute (and parse) it again after Tmax has passed:16:01:08SQL select count(*) from X;COUNT(*)----------159016:01:09 SQL select * from v$sql_shared_cursor where sql_id95rckg79jgshh;SQL_ID ADDRESS CHILD_ADCHILD_NUMBERU S O O S L S E B P I S T A B D L T------------- -------- --------------------- - - - - - - - - - - - - - - - - -R I I R L I O S M U T N F A I T D L D B P C S R P T M B MRO P M F L- - - - - - - - - - - - - - - - - - - - - - - - - - - - --- - - - -95rckg79jgshh 3076145C 33EFD6540N N N N N N N N N N N N N N N N N NN N N N N N N N N N N N N N N N N N N N N N N N N N N N NNN N N N N95rckg79jgshh 3076145C 3078DA101N N N N N N N N N N N N N N N N N NN N N N N N N N N N N N N N N N N N N N N N N N N N N N NYN N N N N16:01:10 SQL select child_number,parse_calls,executions,first_load_time,last_load_time,last_active_time from v$sql where sql_id95rckg79jgshh;CHILD_NUMBERPARSE_CALLSEXECUTIONSFIRST_LOAD_TIME LAST_LOAD_TIME---------------------------------------------------- -------------------LAST_ACTIVE_TIME-------------------0 3 3 2008-03-13/15:12:47 2008-03-13/15:48:4213/03/2008 15:55:351112008-03-13/15:12:47 2008-03-13/16:01:0913/03/200816:01:08Here we now see that a new child cursor has been created and has 1 execution while the old cursor remains with its previous 3 executions. This shows that the original cursor was invalidated and a hard parse done. In V$SQL_SHARED_CURSOR the new child has a column set to Y (marked in bold), this corresponds to ROLL_INVALID_MISMATCH and indicates that the child had to be created because the original cursor could not be reused due to having been invalidated with rolling invalidation.This completes the testcase.