走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博客 ” ，链接：https://blog.itpub.net/8242091/viewspace-584883/ ，如需转载，请注明出处，否则将追究法律责任。
