[20130108]11GR2的SPM与schema问题.txt

11GR2的新特性SPM能够很好的稳定执行计划，但是如果不同的schema执行相同的语句，访问的表对象不一致情况会怎样呢？
自己做了一个测试：

1.测试环境搭建：

SQL> connect test/xxxx

Connected.

SQL> create table t as select rownum id,lpad('x',10,'x') namex from dual connect by level<=10000;

Table created.

SQL> create unique index i_t_id on t(id);

Index created.

SQL>  exec dbms_stats.gather_table_stats(USER,'T',cascade => true);

PL/SQL procedure successfully completed.

SQL> connect scott/xxxx

Connected.

SQL> create table t as select rownum id,lpad('x',10,'x') name from dual connect by level<=10000;

Table created.

SQL>  exec dbms_stats.gather_table_stats(USER,'T',cascade => true);

PL/SQL procedure successfully completed.

--在test,scott上建立表，名字都是t,但是里面存在不同的字段。

2.建立sql plan baselines：

SQL> scott/xxxx

SQL> select sql_handle, plan_name, sql_text,enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;

no rows selected

SQL> alter session set optimizer_capture_sql_plan_baselines=true ;

Session altered.

SQL> variable a number ;

SQL> exec :a := 42;

PL/SQL procedure successfully completed.

SQL> alter session set optimizer_capture_sql_plan_baselines=true ;

Session altered.

SQL> variable a number ;

SQL> exec :a := 42;

PL/SQL procedure successfully completed.

SQL> select * from t where id=:a;

--执行3次以上。

SQL> alter session set optimizer_capture_sql_plan_baselines=false ;

Session altered.

SQL> select sql_handle, plan_name, sql_text,enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                     ENA ACC FIX ORIGIN                       SIGNATURE

------------------------------ ------------------------------ ---------------------------- --- --- --- -------------- -----------------------

SYS_SQL_304e765a300e4066       SQL_PLAN_30mmqb8s0wh3694ecae5c select * from t where id=:a  YES YES NO  AUTO-CAPTURE       3480849691729346662

SQL> select * from t where id=:a;

        ID NAME

---------- --------------------------------------------------

        42 xxxxxxxxxx

SQL> @dpc ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  85j1d9crk28ba, child number 2

-------------------------------------

select * from t where id=:a

Plan hash value: 1601196873

--------------------------------------------------------

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

--------------------------------------------------------

|   0 | SELECT STATEMENT  |      |        |    10 (100)|

|*  1 |  TABLE ACCESS FULL| T    |      1 |    10   (0)|

--------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 42

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ID"=:A)

Note

-----

   - SQL plan baseline SQL_PLAN_30mmqb8s0wh3694ecae5c 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

--可以发现scott执行以上语句访问T表执行的全部扫描，使用基线。

3.以test用户登录看看:

SQL> connect test/btbtms

Connected.

SQL> select * from t where id=:a;

        ID NAMEX

---------- ----------

        42 xxxxxxxxxx

SQL> @dpc ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  85j1d9crk28ba, child number 1

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 85j1d9crk28ba, CHILD_NUMBER: 1

      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)

--第1次报错?      

SQL> select * from t where id=:a;

        ID NAMEX

---------- ----------

        42 xxxxxxxxxx

SQL> @dpc ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  85j1d9crk28ba, child number 0

-------------------------------------

select * from t where id=:a

Plan hash value: 1601196873

--------------------------------------------------------

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

--------------------------------------------------------

|   0 | SELECT STATEMENT  |      |        |    10 (100)|

|*  1 |  TABLE ACCESS FULL| T    |     26 |    10   (0)|

--------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ID"=:A)

Note

-----

   - SQL plan baseline SQL_PLAN_30mmqb8s0wh3694ecae5c 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

--可以发现plan使用scott建立的基线，走的是全表扫描，而实际上test.t我建立了唯一索引。而实际上test的t与scott的t不一样。
--也就是讲SPM仅仅对比基线里面的sql文本是否符合，而不管什么schema。

--如果写成如下：

SQL> variable b number ;

SQL> exec :b :=100;

PL/SQL procedure successfully completed.

SQL> select * from t where id=:b;

        ID NAMEX

---------- ----------

       100 xxxxxxxxxx

SQL> @dpc ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  1qkn5c6gf11tw, child number 0

-------------------------------------

select * from t where id=:b

Plan hash value: 1149237570

--------------------------------------------------------------------

| Id  | Operation                   | Name   | E-Rows | Cost (%CPU)|

--------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |        |     2 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |     2   (0)|

|*  2 |   INDEX UNIQUE SCAN         | I_T_ID |      1 |     1   (0)|

--------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID"=:B)

Note

-----

   - 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

25 rows selected.

4.总结：
看来SPM设计时并没有考虑schema的问题，好在这种情况出现不会太多。我的感觉这个应该算BUG。