[20130108]11GR2的SPM与schema问题.txt
11GR2的新特性SPM能够很好的稳定执行计划,但是如果不同的schema执行相同的语句,访问的表对象不一致情况会怎样呢?
自己做了一个测试:
1.测试环境搭建:
--在test,scott上建立表,名字都是t,但是里面存在不同的字段。
2.建立sql plan baselines:
--可以发现scott执行以上语句访问T表执行的全部扫描,使用基线。
3.以test用户登录看看:
--可以发现plan使用scott建立的基线,走的是全表扫描,而实际上test.t我建立了唯一索引。而实际上test的t与scott的t不一样。
--也就是讲SPM仅仅对比基线里面的sql文本是否符合,而不管什么schema。
--如果写成如下:
4.总结:
看来SPM设计时并没有考虑schema的问题,好在这种情况出现不会太多。我的感觉这个应该算BUG。
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。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-752269/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-752269/