[20150428]11G SPM与cardinality feedback问题.txt

1.问题说明:
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SELECT    f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule
    FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p
   WHERE f.owner = 'SCOTT'
     AND f.table_name = 'EMP'
     AND f.constraint_type = 'R'
     AND SYS.all_cons_columns.constraint_name = f.constraint_name
     AND SYS.all_cons_columns.table_name = 'EMP'
     AND SYS.all_cons_columns.owner = 'SCOTT'
     AND p.owner = f.r_owner
     AND p.constraint_name = f.r_constraint_name
ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION;

--为了测试方便,我建立文件a.sql,便于多次执行.第1次执行很慢,我的测试机器开始2次需要将近4秒.在第二次执行计划出现 cardinality feedback.
--再一次硬分析,依旧分析时间很长,执行也很慢.第3次看执行计划使用快了许多.

SCOTT@test> set timing on
SCOTT@test> @a.sql

CONSTRAINT_NAME       OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
--------------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO             SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:04.04
SCOTT@test> @a.sql
CONSTRAINT_NAME       OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
--------------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO             SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:03.82
SCOTT@test> @a.sql
CONSTRAINT_NAME       OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
--------------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO             SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION
Elapsed: 00:00:00.01

--我前面写过一篇blog,链接:
http://blog.itpub.net/267265/viewspace-1298186/

--这个问题在于如果sql语句很复杂的情况下,oracle的分析时间过长,导致执行缓慢.最近正好遇到一个项目也出现类似的情况,分析过长,能否通过spm来稳定呢?
--但是我遇到cardinality feedback的问题.
--自己做一个测试.

2.建立sql_plan_baseline:

SCOTT@test> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.

SCOTT@test> @a.sql
CONSTRAINT_NAME     OWNER  R_OWNER   TABLE_NAME COLUMN_NAME          DELETE_RU
------------------- ------ --------- ---------- -------------------- ---------
FK_DEPTNO           SCOTT  SCOTT     DEPT       DEPTNO               NO ACTION

SCOTT@test> @a.sql
CONSTRAINT_NAME     OWNER  R_OWNER   TABLE_NAME COLUMN_NAME          DELETE_RU
------------------- ------ --------- ---------- -------------------- ---------
FK_DEPTNO           SCOTT  SCOTT     DEPT       DEPTNO               NO ACTION

--执行多次(6次).我担心cardinality feedback影响.

SCOTT@test> alter session set optimizer_capture_sql_plan_baselines=false;
Session altered.

SCOTT@test> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN                       SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SQL_7f4b11a38e3e8b09           SQL_PLAN_7yksjnf73x2s93753d4dd YES NO  NO  AUTO-CAPTURE       9172444460233952009
SQL_7f4b11a38e3e8b09           SQL_PLAN_7yksjnf73x2s9f002201c YES YES NO  AUTO-CAPTURE       9172444460233952009

--可以看到sql baseline已经生效.

3.测试:
SCOTT@test> set timing on
SCOTT@test> @a
CONSTRAINT_NAME       OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
--------------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO             SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:03.82
SCOTT@test> @a
CONSTRAINT_NAME       OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
--------------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO             SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:03.81
SCOTT@test> @a
CONSTRAINT_NAME       OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
--------------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO             SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:04.82
SCOTT@test> @a
CONSTRAINT_NAME       OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
--------------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO             SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:03.81

--无论我执行多少次,时间都在4秒上下.而且我每次执行完成查看执行计划,都是如下:

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gmzkkrbp9s3zb, child number 9
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: gmzkkrbp9s3zb, CHILD_NUMBER: 9
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

8 rows selected.
--可以发现子游标=9.这样可以推测每次都生成新的子游标.

SCOTT@test> select child_number,is_shareable,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
CHILD_NUMBER I SQL_PLAN_BASELINE
------------ - ------------------------------
           0 N
           1 N
           2 N
           3 N
           4 Y
           5 N
           6 N SQL_PLAN_7yksjnf73x2s9f002201c
           7 N SQL_PLAN_7yksjnf73x2s9f002201c
           8 N SQL_PLAN_7yksjnf73x2s9f002201c
10 rows selected.
Elapsed: 00:00:00.00

--并没有子游标9.why?消失了.....^_^.看下面就更加清晰了.

select * from table(dbms_xplan.display_cursor('gmzkkrbp9s3zb',8,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline '));

...
Note
-----
   - SQL plan baseline SQL_PLAN_7yksjnf73x2s9f002201c used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--还是可以发现使用SPM.

SCOTT@test> @share gmzkkrbp9s3zb
...
SQL_TEXT                       = SELECT    f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule     FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p    WHERE f.owner = 'SCOTT'      AND f.table_name = 'EMP'      AND
f.constraint_type = 'R'      AND SYS.all_cons_columns.constraint_name = f.constraint_name      AND SYS.all_cons_columns.table_name = 'EMP'      AND SYS.all_cons_columns.owner = 'SCOTT'      AND p.owner = f.r_owner      AND p.constraint_name = f.r_constraint_name ORDER BY f.constraint_name,
SYS.all_cons_columns.POSITION
SQL_ID                         = gmzkkrbp9s3zb
ADDRESS                        = 00000000BE232F58
CHILD_ADDRESS                  = 00000000BE1736E0
CHILD_NUMBER                   = 8
USE_FEEDBACK_STATS             = Y
REASON                         = 83Optimizer mismatch(13)3x432419456026215283Optimizer
mismatch(13)3x4324194560262152

--可以发现不能共享的原因是USE_FEEDBACK_STATS = Y

4.刷新共享池再看看:

--先退出,再登录:
SCOTT@test> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.16

SCOTT@test> set timing on
SCOTT@test> @a
CONSTRAINT_NAME  OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO        SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:03.84
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
CHILD_NUMBER I EXECUTIONS SQL_PLAN_BASELINE
------------ - ---------- ------------------------------
           0 Y          1 SQL_PLAN_7yksjnf73x2s9f002201c

Elapsed: 00:00:00.02
SCOTT@test> @a
CONSTRAINT_NAME  OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO        SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:04.87
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
CHILD_NUMBER I EXECUTIONS SQL_PLAN_BASELINE
------------ - ---------- ------------------------------
           0 N          1 SQL_PLAN_7yksjnf73x2s9f002201c

Elapsed: 00:00:00.00
SCOTT@test> @a
CONSTRAINT_NAME  OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO        SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:03.86
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
CHILD_NUMBER I EXECUTIONS SQL_PLAN_BASELINE
------------ - ---------- ------------------------------
           0 N          1 SQL_PLAN_7yksjnf73x2s9f002201c
Elapsed: 00:00:00.00

SCOTT@test> select executions,sql_plan_baseline from v$sqlarea where sql_id='gmzkkrbp9s3zb';
EXECUTIONS SQL_PLAN_BASELINE
---------- ------------------------------
         1 SQL_PLAN_7yksjnf73x2s9f002201c

--虽然没有生成新的子游标,但是注意一个细节,执行时间都是接近4秒.而且查询v$sql以及v$sqlarea视图,发现一个奇怪的现象,就是执行次数=1.不再变化.
--is_shareable='N',意味这个游标不能共享.

SCOTT@test> @share gmzkkrbp9s3zb
old  15:           and q.sql_id like ''&1''',
new  15:           and q.sql_id like ''gmzkkrbp9s3zb''',
SQL_TEXT                       = SELECT    f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule     FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p    WHERE f.owner = 'SCOTT'      AND f.table_name = 'EMP'      AND
f.constraint_type = 'R'      AND SYS.all_cons_columns.constraint_name = f.constraint_name      AND SYS.all_cons_columns.table_name = 'EMP'      AND SYS.all_cons_columns.owner = 'SCOTT'      AND p.owner = f.r_owner      AND p.constraint_name = f.r_constraint_name ORDER BY f.constraint_name,
SYS.all_cons_columns.POSITION
SQL_ID                         = gmzkkrbp9s3zb
ADDRESS                        = 00000000B077E318
CHILD_ADDRESS                  = 00000000B077E1B8
CHILD_NUMBER                   = 0
USE_FEEDBACK_STATS             = Y
REASON                         = 03Optimizer mismatch(13)3x4324194560262144
--------------------------------------------------
PL/SQL procedure successfully completed.

5.既然cardinality feedback存在影响,我在会话端关闭看看.

SCOTT@test> alter session set "_optimizer_use_feedback"=false;
Session altered.

Elapsed: 00:00:00.00
SCOTT@test> @a
CONSTRAINT_NAME  OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO        SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:03.82
SCOTT@test> @a
CONSTRAINT_NAME  OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
---------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO        SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:00.02
--很明显第2次加快了.

SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
CHILD_NUMBER I EXECUTIONS SQL_PLAN_BASELINE
------------ - ---------- ------------------------------
           0 N          1 SQL_PLAN_7yksjnf73x2s9f002201c
           1 Y          2 SQL_PLAN_7yksjnf73x2s9f002201c
Elapsed: 00:00:00.01

--这样就好理解is_shareable='N',CHILD_NUMBER=0根本就不共享,这样每次执行这条语句都是1次硬分析,而且奇怪的是在v$sql视图中并没有记录.
--而我在关闭"_optimizer_use_feedback"=false的情况下,cardinality feedback不再起作用,这样第1次执行是一次硬分析,而第2次软分
--析由于已经生成了执行计划,执行很快.

6.总结:
很明显SPM与cardinality feedback存在问题,这个问题导致一个奇怪的现象,在spm存在的情况下,每次都是1次硬分析,而由于这条语句的
特殊性,每次分析时间太长,导致执行时间也延长.