HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

SQL Rolling Invalidation

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2010-04-19 07:06:26

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

    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代码
  1. HELLODBA.COM>alter system flush shared_pool;   
  2.   
  3. System altered.   
  4.   
  5. HELLODBA.COM>select count(*) from demo.t_test2;   
  6.   
  7.   COUNT(*)   
  8. ----------   
  9.       2693   
  10.   
  11. HELLODBA.COM>select kglobflg from sys.X$KGLCURSOR_CHILD t where kglnaobj like 'select count(*) from demo.t_test2%';   
  12.   
  13.   KGLOBFLG   
  14. ----------   
  15.          0   
  16.   
  17. HELLODBA.COM>begin  
  18.   2    dbms_stats.gather_table_stats('DEMO','T_TEST2',no_invalidate => dbms_stats.AUTO_INVALIDATE);   
  19.   3  end;   
  20.   4  /   
  21.   
  22. PL/SQL procedure successfully completed.   
  23.   
  24. HELLODBA.COM>select kglobflg from sys.X$KGLCURSOR_CHILD t where kglnaobj like 'select count(*) from demo.t_test2%';   
  25.   
  26.   KGLOBFLG   
  27. ----------   
  28.        513   
  29.   
  30. HELLODBA.COM>select name,value from all_parameters where name = '_optimizer_invalidation_period';   
  31.   
  32. NAME                                        VALUE   
  33. ------------------------------------------- ---------------   
  34. _optimizer_invalidation_period              30   
  35.   
  36. HELLODBA.COM>select count(*) from demo.t_test2;   
  37.   
  38.   COUNT(*)   
  39. ----------   
  40.       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代码
  1. 220D50A0 00000000 00000000 00000000 4BCBC80E  [...............K]   

    Or peeked by oradebug: 

SQL代码
  1. HELLODBA.COM> oradebug PEEK 0x220D50AC 4
  2.   
  3. [220D50AC, 220D50B0) = 4BCBC80E

    Convert 4BC818E2 it as timestamp data: 

SQL代码
  1. 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')/   
  2. (24*3600) tmax from dual;   
  3.   
  4.   KGLOBITM TMAX   
  5. ---------- -------------------   
  6. 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代码
  1. HELLODBA.COM>select sysdate from dual;   
  2.   
  3. SYSDATE   
  4. -------------------   
  5. 2010-04-19 11:03:56   
  6.   
  7. HELLODBA.COM>select count(*) from demo.t_test2;   
  8.   
  9.   COUNT(*)   
  10. ----------   
  11.       2693   
  12.   
  13. HELLODBA.COM>select sql_id, child_address, child_number, ROLL_INVALID_MISMATCH  from v$sql_shared_cursor where sql_id = 'c9ghnbx097ta7';   
  14.   
  15. SQL_ID        CHILD_AD CHILD_NUMBER R   
  16. ------------- -------- ------------ -   
  17. c9ghnbx097ta7 494D5C04            0 N   
  18. c9ghnbx097ta7 495B5D1C            1 Y   

    Therefore, the burden of the reparsing sql plans after objects statistics data updating are distributed randomly.  

--- Fuyuncat ---

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat