走ORACLE後門cusor_sharing的問題

當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

如果要使用similar，不要收集直方圖，但是沒直方圖，感覺還不如用force
similar很耗記憶體，對於我們的一個省級中的應用，單例項，SQL AREA基本需要2G以上，RAC的話，每個例項幾乎都需要2G以上
sessin_cursor_cache幾乎沒作用，可以不做設定。
呼叫工具OUTLINE,STA就別想用，唉，唯一的辦法只有該SQL，其他和執行計劃有關的引數最好別調整，副作用太大
再次看出繫結變數的重要性！！！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-584883/，如需轉載，請註明出處，否則將追究法律責任。

走ORACLE後門cusor_sharing的問題

相關文章