[20150430]11G SPM与cardinality feedback2

[20150430]11G SPM与cardinality feedback问题2.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次看执行计划使用快了许多.

--继续昨天的测试:
http://blog.itpub.net/267265/viewspace-1611161/

--昨天的测试提到SPM与cardinality feedback存在问题,这个问题导致一个奇怪的现象,在spm存在的情况下,每次都是1次硬分析,而由于
--这条语句的特殊性,每次分析时间太长,导致执行时间也延长.

--但是昨天SPM相关视图dba_sql_plan_baselines记录如下,是否是另外一个accepted=NO导致的呢?
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

2.激活看看:

--激活是这个命令,看了文档,好像使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE不行.至少我这个版本是这样.

variable v_basenum number;
variable v_text varchar2(1000);
variable v_sql_handle  varchar2(30);
variable v_plan_name_1 varchar2(30);

exec :v_sql_handle      := 'SQL_7f4b11a38e3e8b09';
exec :v_plan_name_1     := 'SQL_PLAN_7yksjnf73x2s93753d4dd';

exec :v_text := dbms_spm.evolve_sql_plan_baseline(:v_sql_handle,:v_plan_name_1,verify=> 'NO', commit=>'YES');

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 YES NO  AUTO-CAPTURE       9172444460233952009
SQL_7f4b11a38e3e8b09           SQL_PLAN_7yksjnf73x2s9f002201c YES YES NO  AUTO-CAPTURE       9172444460233952009

--继续测试看看.
SCOTT@test> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.44

SCOTT@test> @hide _optimizer_use_feedback
NAME                     DESCRIPTION             DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE
------------------------ ----------------------- ---------------------- ---------------------- ----------------------
_optimizer_use_feedback  optimizer use feedback  TRUE                   TRUE                   TRUE

SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';
no rows selected

SCOTT@test> set timing on
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';

no rows selected

Elapsed: 00:00:00.03
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.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.73

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          1 SQL_PLAN_7yksjnf73x2s93753d4dd
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:00.01

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_7yksjnf73x2s93753d4dd
Elapsed: 00:00:00.00
--第1次3.84秒,第2次3.73秒,第3次执行0.01秒,可以发现最后执行选择的计划的SQL_PLAN_BASELINE='SQL_PLAN_7yksjnf73x2s93753d4dd'.

COTT@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                        = 00000000BE842A50
CHILD_ADDRESS                  = 00000000BE7F77F0
CHILD_NUMBER                   = 0
USE_FEEDBACK_STATS             = Y
REASON                         = 03Optimizer mismatch(13)3x4324194560262144
--------------------------------------------------
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                        = 00000000BE842A50
CHILD_ADDRESS                  = 00000000B0F3D370
CHILD_NUMBER                   = 1
REASON                         =
--------------------------------------------------

PL/SQL procedure successfully completed.

3.关闭SQL_PLAN_BASELINE='SQL_PLAN_7yksjnf73x2s9f002201c'看看:

variable v_plan_name_2 varchar2(30);
exec :v_plan_name_2     := 'SQL_PLAN_7yksjnf73x2s9f002201c';
exec :v_basenum:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name => :v_plan_name_2,attribute_name => 'ENABLED',   attribute_value => 'NO');

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 YES NO  AUTO-CAPTURE       9172444460233952009
SQL_7f4b11a38e3e8b09           SQL_PLAN_7yksjnf73x2s9f002201c NO  YES NO  AUTO-CAPTURE       9172444460233952009

SCOTT@test> alter system flush shared_pool;
System altered.

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:04.89
SCOTT@test> select child_number,is_shareable,executions,sql_plan_baseline from v$sql where sql_id='gmzkkrbp9s3zb';

no rows selected

Elapsed: 00:00:00.03
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.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

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.74
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

--问题再次再现,is_shareable='N',而EXECUTIONS=1.

4.删除SQL_PLAN_BASELINE='SQL_PLAN_7yksjnf73x2s9f002201c'看看:

variable v_plan_name_2 varchar2(30);
exec :v_plan_name_2     := 'SQL_PLAN_7yksjnf73x2s9f002201c';
exec :v_basenum:=DBMS_SPM.drop_sql_plan_baseline (sql_handle =>:v_sql_handle,plan_name => :v_plan_name_2);

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 YES NO  AUTO-CAPTURE       9172444460233952009

--问题依旧(不贴出来了,结果一样的),看来使用SPM要注意这个问题,估计是bug.

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

Elapsed: 00:00:00.01
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.83
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.01

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
           1 Y          2
Elapsed: 00:00:00.00

--注意这时没有使用SPM.