ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120206]Cursor Invalidation与分析表.txt

[20120206]Cursor Invalidation与分析表.txt

原创 Linux操作系统 作者:lfree 时间:2012-02-06 11:25:35 0 删除 编辑
在分析表的是否有一个参数no_invalidate:缺省值是DBMS_STATS.AUTO_INVALIDATE.AUTO_INVALIDATE。

    10g中默认是AUTO_INVALIDATE,就是说分析表后,游标不会马上invalidate,已经存在的SQL的执行计划不会受新的统计信息影响。可以手工DDL
invalidate游标。又或者等待隐藏参数_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒后,
Oracle自动invalidate游标并使SQL能够读取新的统计信息产生新的执行计划。

    如果想要dbms_stats分析立马见效,需要使用no_invalidate=false option或者DBA自己手工invalidate游标。

--说明一下,我个人感觉这个参数理解起来很烦,validate表示有效,no_invalidate反了2次,也是表示有效的意思。

dbms_stats收集统计信息时候no_invalidate参数
用于是否与收集相关object的cursor失效,defalut(9i false, 10g dbms_stats.auto_invalidate(既null))
true:当收集完统计信息后,收集对象的cursor不会失效(不会产生新的执行计划,子游标)
false:当收集完统计信息后,收集对象的cursor会立即失效(新的执行计划,新的子游标)
no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,分析表后,游标不会马上invalidate,已经存在的SQL的执行计划不会受新的统计信息影响。可以手工
DDL invalidate游标。又或者等待隐藏参数_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒后,
Oracle自动invalidate游标并使SQL能够读取新的统计信息产生新的执行计划。


1.建立测试环境:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t as select rownum id , 'test' name from dual connect by level<=64;
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => DBMS_STATS.AUTO_INVALIDATE);

SQL> select count(*) from t;
  COUNT(*)
----------
        64

--获取sql_id
SQL> @dpc
PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID  cyzznbykb509s, child number 0
-------------------------------------
select count(*) from t

Plan hash value: 2966233522

---------------------------------------------------------
| Id  | Operation          | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |     3 (100)|
|   1 |  SORT AGGREGATE    |      |      1 |            |
|   2 |   TABLE ACCESS FULL| T    |     64 |     3   (0)|
---------------------------------------------------------

sqlid='cyzznbykb509s'

2.测试1(no_invalidate => false):
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';

SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s            0          1           1          1             0


SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => false);
PL/SQL procedure successfully completed.

SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s            0          1           1          1             1
--分析后no_invalidate => false,v$sql 的INVALIDATIONS=1.光标失效。

SQL> select count(*) from t;
  COUNT(*)
----------
        64

SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s            0          1           1          2             1

3.测试2(no_invalidate => true):

SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => true);

PL/SQL procedure successfully completed.

SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s            0          1           1          2             1

--分析后no_invalidate => true,v$sql 的INVALIDATIONS=1(没有变化与上次一样).说明光标没有失效。

SQL> select count(*) from t;
  COUNT(*)
----------
        64

SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s            0          2           2          2             1

--再次执行查询,发现PARSE_CALLS增加了1次,loads没有变化。

4.测试3(no_invalidate => DBMS_STATS.AUTO_INVALIDATE):
缺省隐藏参数_optimizer_invalidation_period设置的时间太长=18000(5个小时),我缩短一些。

SQL> alter system set "_optimizer_invalidation_period" = 300 scope=memory;
System altered.

SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s            0          2           2          2             1

--马上执行,select count(*) from t;
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => DBMS_STATS.AUTO_INVALIDATE);
PL/SQL procedure successfully completed.

SQL> select count(*) from t;
  COUNT(*)
----------
        64

SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s            0          3           3          2             1

--可以发现v$sql 的INVALIDATIONS=1(没有变化与上次).说明光标没有失效。执行计划以及使用原来的光标。
--等一段时间300秒,再测试:

SQL> host sleep 300

SQL> select count(*) from t;
  COUNT(*)
----------
        64

SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s            0          3           3          2             1
cyzznbykb509s            1          1           1          1             0


--可以发现原来的光标无效,生成新的子光标。看看为什么不能共享?

SQL> @share cyzznbykb509s
old  15:           and q.sql_id like ''&1''',
new  15:           and q.sql_id like ''cyzznbykb509s''',
SQL_TEXT                       = select count(*) from t
SQL_ID                         = cyzznbykb509s
ADDRESS                        = 000000009353C428
CHILD_ADDRESS                  = 0000000093623B88
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select count(*) from t
SQL_ID                         = cyzznbykb509s
ADDRESS                        = 000000009353C428
CHILD_ADDRESS                  = 000000009362C7E0
CHILD_NUMBER                   = 1
ROLL_INVALID_MISMATCH          = Y
--------------------------------------------------
PL/SQL procedure successfully completed.

--原来的光标无效,是由于ROLL_INVALID_MISMATCH。最后修改隐含参数回来。
SQL> alter system set "_optimizer_invalidation_period" = 18000 scope=memory;
System altered.

总结:
    缺省分析DBMS_STATS.AUTO_INVALIDATE,如果处理不好,一些性能问题会延迟出现,在优化时注意。
    
share脚本如下:
SET  serveroutput on size 100000;

DECLARE
   c           NUMBER;
   col_cnt     NUMBER;
   col_rec     DBMS_SQL.desc_tab;
   col_value   VARCHAR2 (4000);
   ret_val     NUMBER;
BEGIN
   c := DBMS_SQL.open_cursor;
   DBMS_SQL.parse
      (c,
       'select q.sql_text, s.*
      from v$sql_shared_cursor s, v$sql q
      where s.sql_id = q.sql_id
          and s.child_number = q.child_number
          and q.sql_id like ''&1''',
       DBMS_SQL.native
      );
   DBMS_SQL.describe_columns (c, col_cnt, col_rec);

FOR idx IN 1 .. col_cnt
   LOOP
      DBMS_SQL.define_column (c, idx, col_value, 4000);
   END LOOP;

   ret_val := DBMS_SQL.EXECUTE (c);

   WHILE (DBMS_SQL.fetch_rows (c) > 0)
   LOOP
      FOR idx IN 1 .. col_cnt
      LOOP
         DBMS_SQL.COLUMN_VALUE (c, idx, col_value);

         IF col_rec (idx).col_name IN
               ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER',
                'SQL_TEXT')
         THEN
            DBMS_OUTPUT.put_line (   RPAD (col_rec (idx).col_name, 30)
                                  || ' = '
                                  || col_value
                                 );
         ELSIF col_value = 'Y'
         THEN
            DBMS_OUTPUT.put_line (   RPAD (col_rec (idx).col_name, 30)
                                  || ' = '
                                  || col_value
                                 );
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line
                         ('--------------------------------------------------');
   END LOOP;

   DBMS_SQL.close_cursor (c);
END;
/

SET serveroutput off;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-715663/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2317
  • 访问量
    6045579