[20120807]11G SPM的学习5.txt--第3方优化
继续上面的学习:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table t as select rownum id , cast(dbms_random.string('a',6 ) as varchar2(10)) name from dual connect by level
--分析表
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => user
,TabName => 'T'
,Estimate_Percent => NULL
,Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
,Degree => 4
,Cascade => TRUE
,No_Invalidate => TRUE);
END;
/
create index i_t_id on t(id);
1.假设程序中的代码写成如下:
select /*+ full(t) */ * from t where id=:1;
10g下可以使用sql profiles来改变与稳定计划,而使用11G SPM如何实现呢?
建立测试例子:
declare
begin
execute immediate 'select /*+ full(t) */ * from t where id=:1' using 100;
end;
/
--我这里使用绑定变量是:1,不能像sqlplus那样定义变量,必须写一个匿名过程。
SQL> select * from table(dbms_xplan.display_cursor('1nhrs4ytd2fvq',NULL,'ALLSTATS LAST PEEKED_BINDS cost'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID 1nhrs4ytd2fvq, child number 0
-------------------------------------
select /*+ full(t) */ * from t where id=:1
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 69 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 69 (2)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:1)
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
--可以发现这样的语句由于使用了提示,作为第3方无法修改sql代码?如何实现呢?
var v_basenum number;
exec :v_basenum:=dbms_spm.load_plans_from_cursor_cache(sql_id => '1nhrs4ytd2fvq',plan_hash_value =>1601196873 );
SQL> column signature format 9999999999999999999999
SQL> column FORCE_MATCHING_SIGNATURE like SIGNATURE
SQL> column EXACT_MATCHING_SIGNATURE like SIGNATURE
SQL> select sql_handle, plan_name, enabled, accepted,fixed,autopurge,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX AUT ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- --- -------------- -----------------------
SYS_SQL_2a9db9f54b3cfa0c SQL_PLAN_2p7dtyp5mtyhc94ecae5c YES YES NO YES MANUAL-LOAD 3070814984116959756
SQL> column sql_text format a60
SQL> SELECT sql_id, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature FROM v$sql WHERE sql_id = '1nhrs4ytd2fvq';
SQL_ID SQL_TEXT SQL_PLAN_BASELINE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- ------------------------------------------------------------ ------------------------------ ------------------------ ------------------------
1nhrs4ytd2fvq select /*+ full(t) */ * from t where id=:1 3070814984116959756 3070814984116959756
SQL> variable x number;
SQL> exec :x := 100;
PL/SQL procedure successfully completed.
SQL> select * from t where id=:x;
ID NAME
---------- ----------
100 lAGCjs
SQL> @dpc
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID 89fczrmrx25j6, child number 0
-------------------------------------
select * from t where id=:x
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | 1 (0)|
--------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:X)
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
SQL> SELECT sql_id, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature FROM v$sql WHERE sql_id in ('1nhrs4ytd2fvq','89fczrmrx25j6');
SQL_ID SQL_TEXT SQL_PLAN_BASELINE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- ------------------------------------------------------------ ------------------------------ ------------------------ ------------------------
89fczrmrx25j6 select * from t where id=:x 11842951964357158308 11842951964357158308
1nhrs4ytd2fvq select /*+ full(t) */ * from t where id=:1 3070814984116959756 3070814984116959756
exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '89fczrmrx25j6',plan_hash_value => 4153437776 ,sql_handle => 'SYS_SQL_2a9db9f54b3cfa0c');
SQL> exec :v_basenum:=dbms_spm.load_plans_from_cursor_cache(sql_id => '89fczrmrx25j6',plan_hash_value => 4153437776 ,sql_handle => 'SYS_SQL_2a9db9f54b3cfa0c');
PL/SQL procedure successfully completed.
SQL> select sql_handle, plan_name, enabled, accepted,fixed,autopurge,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX AUT ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- --- -------------- -----------------------
SYS_SQL_2a9db9f54b3cfa0c SQL_PLAN_2p7dtyp5mtyhc0893a4b2 YES YES NO YES MANUAL-LOAD 3070814984116959756
SYS_SQL_2a9db9f54b3cfa0c SQL_PLAN_2p7dtyp5mtyhc94ecae5c YES YES NO YES MANUAL-LOAD 3070814984116959756
SQL> alter system flush shared_pool;
declare
begin
execute immediate 'select /*+ full(t) */ * from t where id=:1' using 100;
end;
/
SQL> select * from table(dbms_xplan.display_cursor('1nhrs4ytd2fvq',NULL,'ALLSTATS LAST PEEKED_BINDS cost'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 1nhrs4ytd2fvq, child number 0
-------------------------------------
select /*+ full(t) */ * from t where id=:1
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 69 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 69 (2)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:1)
Note
-----
- SQL plan baseline SQL_PLAN_2p7dtyp5mtyhc94ecae5c used for this statement
--执行计划并没有选择索引!
SQL> exec :v_basenum := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_2a9db9f54b3cfa0c',plan_name=>'SQL_PLAN_2p7dtyp5mtyhc94ecae5c',attribute_name=>'enabled',attribute_value=>'NO');
PL/SQL procedure successfully completed.
SQL> select sql_handle, plan_name, enabled, accepted,fixed,autopurge,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX AUT ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- --- -------------- -----------------------
SYS_SQL_2a9db9f54b3cfa0c SQL_PLAN_2p7dtyp5mtyhc0893a4b2 YES YES NO YES MANUAL-LOAD 3070814984116959756
SYS_SQL_2a9db9f54b3cfa0c SQL_PLAN_2p7dtyp5mtyhc94ecae5c NO YES NO YES MANUAL-LOAD 3070814984116959756
alter system flush shared_pool;
declare
begin
execute immediate 'select /*+ full(t) */ * from t where id=:1' using 99;
end;
/
SQL> select * from table(dbms_xplan.display_cursor('1nhrs4ytd2fvq',NULL,'ALLSTATS LAST PEEKED_BINDS cost'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID 1nhrs4ytd2fvq, child number 0
-------------------------------------
select /*+ full(t) */ * from t where id=:1
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | 1 (0)|
--------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:1)
Note
-----
- SQL plan baseline SQL_PLAN_2p7dtyp5mtyhc0893a4b2 used for this statement
--可以发现执行计划采用索引范围扫描。
时间: 2024-09-20 21:22:25