ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 走ORACLE后门cusor_sharing的问题

走ORACLE后门cusor_sharing的问题

原创 Linux操作系统 作者:westzq1984 时间:2009-04-06 18:08:55 0 删除 编辑

当cusor_sharing=similar时,在收集了直方图的列上进行非等查询时,进行硬解析

SQL> show parameter cursor_sh

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           SIMILAR

SQL> SELECT COUNT(*)
  2     FROM USER_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T1'
  4      AND COLUMN_NAME = 'HEADER_BLOCK';

  COUNT(*)
----------
        76

[oracle@SOURCE ~]$ cat parse.sql
SELECT B.VALUE  FROM V$STATNAME A, V$MYSTAT B WHERE A.STATISTIC# = B.STATISTIC#
AND A.NAME IN ('parse count (hard)');       


SQL> SELECT SUM(HEADER_BLOCK) FROM T1 WHERE SEGMENT_TYPE = 'INDEX' AND HEADER_BLOCK > 100;

SUM(HEADER_BLOCK)
-----------------
         59390560

SQL> @parse.sql

     VALUE
----------
         1

SQL> @parse.sql

     VALUE
----------
         1

SQL> SELECT SUM(HEADER_BLOCK) FROM T1 WHERE SEGMENT_TYPE = 'INDEX' AND HEADER_BLOCK > 101;

SUM(HEADER_BLOCK)
-----------------
         59390560

SQL> @parse.sql

     VALUE
----------
         2

SQL> SELECT SUM(HEADER_BLOCK) FROM T1 WHERE SEGMENT_TYPE = 'INDEX' AND HEADER_BLOCK > 103;

SUM(HEADER_BLOCK)
-----------------
         59390560

SQL> @parse.sql

     VALUE
----------
         3

上面修改HEADER_BLOCK的值,进行了硬解析,下面测试修改等条件的值

SQL> SELECT SUM(HEADER_BLOCK) FROM T1 WHERE HEADER_BLOCK > 103 and SEGMENT_TYPE = 'INDEX';

SUM(HEADER_BLOCK)
-----------------
         59390560

SQL> @parse.sql

     VALUE
----------
         1

SQL> SELECT SUM(HEADER_BLOCK) FROM T1 WHERE HEADER_BLOCK > 103 and SEGMENT_TYPE = 'TABLE';

SUM(HEADER_BLOCK)
-----------------
         33745222

SQL> @parse.sql

     VALUE
----------
         1
        
没有进行硬解析

SQL> DECLARE
  2     LN_COU   NUMBER;
  3     LN_VALUE NUMBER;
  4     LVC_SQL  VARCHAR2(200);
  5  BEGIN
  6     FOR I IN 1 .. 100 LOOP
  7             LVC_SQL := 'SELECT /*+FLAG1*/SUM(HEADER_BLOCK) FROM T1 WHERE SEGMENT_TYPE = ''INDEX'' AND HEADER_BLOCK > ' ||
  8                                                      TO_NUMBER(1000 + I);
  9             EXECUTE IMMEDIATE LVC_SQL
 10                     INTO LN_COU;
 11     END LOOP;
 12  END;
 13  /
 
SQL> SELECT SHARABLE_MEM, VERSION_COUNT, LOADS, INVALIDATIONS
  2     FROM V$SQLAREA
  3   WHERE SQL_TEXT LIKE '%FLAG1%'
  4      AND SQL_TEXT NOT LIKE 'DECLARE%';

SHARABLE_MEM VERSION_COUNT      LOADS INVALIDATIONS
------------ ------------- ---------- -------------
     1156725           100        100             0

SQL> DECLARE
  2     LN_COU   NUMBER;
  3     LN_VALUE NUMBER;
  4     LVC_SQL  VARCHAR2(200);
  5  BEGIN
  6     FOR I IN 1 .. 10000 LOOP
  7             LVC_SQL := 'SELECT /*+FLAG1*/SUM(HEADER_BLOCK) FROM T1 WHERE SEGMENT_TYPE = ''INDEX'' AND HEADER_BLOCK > ' ||
  8                                                      TO_NUMBER(1000 + I);
  9             EXECUTE IMMEDIATE LVC_SQL
 10                     INTO LN_COU;
 11     END LOOP;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> SELECT SHARABLE_MEM, VERSION_COUNT, LOADS, INVALIDATIONS
  2     FROM V$SQLAREA
  3   WHERE SQL_TEXT LIKE '%FLAG1%'
  4      AND SQL_TEXT NOT LIKE 'DECLARE%';

SHARABLE_MEM VERSION_COUNT      LOADS INVALIDATIONS
------------ ------------- ---------- -------------
    15035505          1496       1301             0

version_count在运行的时候是一直变化的,最高到达过2000多,然后又下降

在操作系统上做了个脚本,挂载后台同时10个一起跑,10分钟报04031

 

cursor_charing <> exact 时候,如果SQL中有不等条件,sessin_cursor_cache无作用

先来看下Session_Cached_Cursors关闭的情况

ALTER SESSION SET Session_Cached_Cursors=0;

BEGIN  
   FOR i IN 1..100 LOOP
    EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1';
    END LOOP;
END;

SQL> SELECT parse_calls,executions,sql_text
  2     FROM V$SQLAREA
  3   WHERE SQL_TEXT LIKE '%FLAG7%'
  4      AND SQL_TEXT NOT LIKE '%sqlarea%'
  5     AND sql_text NOT LIKE '%BEGIN%'
  6  /

PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- --------------------------------------------------
        100        100 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1

语句解析了100次

SQL> ALTER SESSION SET Session_Cached_Cursors=1;

Session altered.

SQL> BEGIN  
  2     FOR i IN 1..100 LOOP
  3      EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1';
  4      END LOOP;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> SELECT parse_calls,executions,sql_text
  2     FROM V$SQLAREA
  3   WHERE SQL_TEXT LIKE '%FLAG7%'
  4      AND SQL_TEXT NOT LIKE '%sqlarea%'
  5     AND sql_text NOT LIKE '%BEGIN%'
  6  / 

PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- --------------------------------------------------
        101        200 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1

设置了Session_Cached_Cursors=1后,语句只增加了1次解析

SQL> BEGIN  
  2     FOR i IN 1..100 LOOP
  3      EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1';
  4      EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1 where header_block>1000';
  5      END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT parse_calls,executions,sql_text
  2     FROM V$SQLAREA
  3   WHERE SQL_TEXT LIKE '%FLAG7%'
  4      AND SQL_TEXT NOT LIKE '%sqlarea%'
  5     AND sql_text NOT LIKE '%BEGIN%'
  6    
SQL> / 

PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- --------------------------------------------------
        201        300 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1
        100        100 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1 where h
                       eader_block>:"SYS_B_0"

如果增加了1个语句,由于只能使用1个Session_Cached_Cursors,所以又增加了100次

SQL>  ALTER SESSION SET Session_Cached_Cursors=2;

Session altered.

SQL> BEGIN  
  2     FOR i IN 1..100 LOOP
  3      EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1';
  4      EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1 where header_block>1000';
  5      END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT parse_calls,executions,sql_text
  2     FROM V$SQLAREA
  3   WHERE SQL_TEXT LIKE '%FLAG7%'
  4      AND SQL_TEXT NOT LIKE '%sqlarea%'
  5     AND sql_text NOT LIKE '%BEGIN%'
  6  /

PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- --------------------------------------------------
        202        400 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1
        200        200 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1 where h
                       eader_block>:"SYS_B_0"

第二个SQL不能使用Session_Cached_Cursors

SQL> BEGIN  
  2     FOR i IN 1..100 LOOP
  3      EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1';
  4      EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1 where header_block>1000';
  5      END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT parse_calls,executions,sql_text   
  2     FROM V$SQLAREA
  3   WHERE SQL_TEXT LIKE '%FLAG7%'
  4      AND SQL_TEXT NOT LIKE '%sqlarea%'
  5     AND sql_text NOT LIKE '%BEGIN%'
  6  /

PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- --------------------------------------------------
        203        500 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1
        300        300 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1 where h
                       eader_block>:"SYS_B_0"

尝试改为FORCE

SQL> ALTER SESSION SET CURSOR_SHARING=FORCE;

Session altered.

SQL> BEGIN  
  2     FOR i IN 1..100 LOOP
  3      EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1';
  4      EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1 where header_block>1000';
  5      END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT parse_calls,executions,sql_text
  2     FROM V$SQLAREA
  3   WHERE SQL_TEXT LIKE '%FLAG7%'
  4      AND SQL_TEXT NOT LIKE '%sqlarea%'
  5     AND sql_text NOT LIKE '%BEGIN%'
  6  /

PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- --------------------------------------------------
        204        600 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1
        400        400 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1 where h
                       eader_block>:"SYS_B_0"

设置成FORCE依然不行,那实验下为exact

SQL> ALTER SESSION SET CURSOR_SHARING=exact;

Session altered.

SQL> BEGIN  
  2     FOR i IN 1..100 LOOP
  3      EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1';
  4      EXECUTE IMMEDIATE 'SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1 where header_block>1000';
  5      END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT parse_calls,executions,sql_text
  2     FROM V$SQLAREA
  3   WHERE SQL_TEXT LIKE '%FLAG7%'
  4      AND SQL_TEXT NOT LIKE '%sqlarea%'
  5     AND sql_text NOT LIKE '%BEGIN%'
  6  /

PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- --------------------------------------------------
        205        700 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1
        400        400 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1 where h
                       eader_block>:"SYS_B_0"

          1        100 SELECT /*+FLAG7*/SUM(HEADER_BLOCK) FROM T1 where h
                       eader_block>1000

SQL> DECLARE
  2    ln_num NUMBER := 100;  
  3  BEGIN  
  4     FOR i IN 1..100 LOOP
  5      EXECUTE IMMEDIATE 'SELECT /*+FLAG8*/SUM(HEADER_BLOCK) FROM T1';
  6      EXECUTE IMMEDIATE 'SELECT /*+FLAG8*/SUM(HEADER_BLOCK) FROM T1 where header_block> :b' USING ln_num;
  7      END LOOP;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> SELECT parse_calls,executions,sql_text
  2     FROM V$SQLAREA
  3   WHERE SQL_TEXT LIKE '%FLAG8%'
  4      AND SQL_TEXT NOT LIKE '%sqlarea%'
  5     AND sql_text NOT LIKE '%BEGIN%'
  6  /

PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- --------------------------------------------------
          1        100 SELECT /*+FLAG8*/SUM(HEADER_BLOCK) FROM T1 where h
                       eader_block> :b

          1        100 SELECT /*+FLAG8*/SUM(HEADER_BLOCK) FROM T1

必须要使用绑定变量才能很好的使用sessin_cursor_cache


cursor_charing <> exact 时候,OUTLINE,STA不起作用

这个以前测试过,就不做测试了。不起作用的原因为,优化器根据没有进行自动绑定变量的SQL去找OUTLINE,STA中的执行计划,而OUTLINE,STA中保存的执行计划为进行绑定后的语句

总结一下如何使用这个后面 cusor_sharing

  1. 如果要使用similar,不要收集直方图,但是没直方图,感觉还不如用force
  2. similar很耗内存,对于我们的一个省级中的应用,单实例,SQL AREA基本需要2G以上,RAC的话,每个实例几乎都需要2G以上
  3. sessin_cursor_cache几乎没作用,可以不做设置。
  4. 调用工具OUTLINE,STA就别想用,唉,唯一的办法只有该SQL,其他和执行计划有关的参数最好别调整,副作用太大
  5. 再次看出绑定变量的重要性!!!

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    981461