SQL Rolling Invalidation
[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2010-04-19 07:06:26
Share to
In CBO, we need gather objects statistics data to help optimizer choose the best plan. However, if the schema's statistics data be updated, all of the dependent execution plans may be affected. Before 10g, you just have 2 choices, none or all of them be affected. The inputing parameter is NO_INVALIDATE, the choices are true or false. If you set true, none of the plans will adapt the new statistics to reparse the sql, unless it's swapped out from library cache, or be flushed mannually. For those 24*7 databases, some sql may never be improved as we expected. And if you choose false, all of the related plans will be flushed out from library cache immediately, and all of them will be reparsed at next time execution, which may consume much of CPU. That will be a nightmare if it happens at the time that resource is tight.
To avoid such issues, another choice is introduced in 10g, AUTO_INVALIDATE, which means oracle will decide when the reparse the plan to avoid a parsing storm. Let's look into how does oracle do.
First, once the object's statistics data be upadated with AUTO_INVALIDATE, the related plans in library cache will not be changed or flushed immediately, just be marked a flag in the SQL cursor.
SQL代码
- HELLODBA.COM>alter system flush shared_pool;
- System altered.
- HELLODBA.COM>select count(*) from demo.t_test2;
- COUNT(*)
- ----------
- 2693
- HELLODBA.COM>select kglobflg from sys.X$KGLCURSOR_CHILD t where kglnaobj like 'select count(*) from demo.t_test2%';
- KGLOBFLG
- ----------
- 0
- HELLODBA.COM>begin
- 2 dbms_stats.gather_table_stats('DEMO','T_TEST2',no_invalidate => dbms_stats.AUTO_INVALIDATE);
- 3 end;
- 4 /
- PL/SQL procedure successfully completed.
- HELLODBA.COM>select kglobflg from sys.X$KGLCURSOR_CHILD t where kglnaobj like 'select count(*) from demo.t_test2%';
- KGLOBFLG
- ----------
- 513
- HELLODBA.COM>select name,value from all_parameters where name = '_optimizer_invalidation_period';
- NAME VALUE
- ------------------------------------------- ---------------
- _optimizer_invalidation_period 30
- HELLODBA.COM>select count(*) from demo.t_test2;
- COUNT(*)
- ----------
- 2693
At the first execution right after the gathering statistics data, optimizer will check the flag and allocate 16 bytes in sub heap 0, generate a pseudo random value between 0 and the _optimizer_invalidation_period (default is 18000, 5 hours) as the rolling invalidation windows, whose start point is current time of this execution. And it will put the timestamp in the chunk that holds such data as enviroments and statistic data of the child cursor in subheap0. With a subheap dump, we can get the timestamp:
SQL代码
- 220D50A0 00000000 00000000 00000000 4BCBC80E [...............K]
Or peeked by oradebug:
SQL代码
- HELLODBA.COM> oradebug PEEK 0x220D50AC 4
- [220D50AC, 220D50B0) = 4BCBC80E
Convert 4BC818E2 it as timestamp data:
SQL代码
- HELLODBA.COM>select to_number('4BCBC80E','XXXXXXXX') kglobitm,to_date('1970-01-01 08:00:00','YYYY-MM-DD HH24:MI:SS')+to_number('4BCBC80E','XXXXXXXX')/
- (24*3600) tmax from dual;
- KGLOBITM TMAX
- ---------- -------------------
- 1271646222 2010-04-19 11:03:42
If the timestamp of next exectuion beyond this windows, it will re-parse another child cursor because rolling invalidation. And from the view v$sql_shared_course, you can find it be marked ROLL_INVALID_MISMATCHED.
SQL代码
- HELLODBA.COM>select sysdate from dual;
- SYSDATE
- -------------------
- 2010-04-19 11:03:56
- HELLODBA.COM>select count(*) from demo.t_test2;
- COUNT(*)
- ----------
- 2693
- HELLODBA.COM>select sql_id, child_address, child_number, ROLL_INVALID_MISMATCH from v$sql_shared_cursor where sql_id = 'c9ghnbx097ta7';
- SQL_ID CHILD_AD CHILD_NUMBER R
- ------------- -------- ------------ -
- c9ghnbx097ta7 494D5C04 0 N
- c9ghnbx097ta7 495B5D1C 1 Y
Therefore, the burden of the reparsing sql plans after objects statistics data updating are distributed randomly.
--- Fuyuncat ---