oracle sql_profile绑定异常处理

marty001

　　一　建立测试表
　　create table a nologging as select * from all_objects;
　　二　准备工作
　　找到sql_Id='aq03p7muwgvq5'
　　select  * from V$sql where sql_text like '% from a where object_id=3%';
　　找到全表的outline:
　　方法一：dba_hist_sql_plan/v$sql_plan都可以
　　

select extractvalue(value(d), '/hint') as outline_hints　　
from
　　
xmltable('/*/outline_data/hint'
　　
passing (
　　
select
　　
xmltype(other_xml) as xmlval
　　
from
　　
dba_hist_sql_plan
　　
where
　　
sql_id = '&sql_id'
　　
and plan_hash_value=&plan_hash_value
　　
and other_xml is not null)) d
　　

　　方法二：
　　

select * from dbms_xplan.display_awr('aq03p7muwgvq5',0,'outline');　　
/*+
　　BEGIN_OUTLINE_DATA
　　IGNORE_OPTIM_EMBEDDED_HINTS
　　OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
　　DB_VERSION('11.2.0.4')
　　ALL_ROWS
　　OUTLINE_LEAF(@"SEL$1")
　　FULL(@"SEL$1" "A"@"SEL$1")
　　END_OUTLINE_DATA
　　
*/
　　

declare　　v_hints sys.sqlprof_attr;
　　v_sqltext clob;
　　
begin
　　select sql_fulltext into v_sqltext from v$sql where sql_id='aq03p7muwgvq5' and rownumtrue,replace=>true);
　　
end;
　　

　　建立索引
　　create index I_ind_object_id_com on a(object_id,object_name) nologging;
　　查看执行计划，并没有走索引：
　　

Execution Plan　　
----------------------------------------------------------
　　
Plan hash value: 2248738933
　　
--------------------------------------------------------------------------

　　
|>　　
--------------------------------------------------------------------------
　　
|   0 | SELECT STATEMENT  |      |     1 |    98 |   177   (1)| 00:00:01 |
　　
|*  1 |  TABLE ACCESS FULL| A    |     1 |    98 |   177   (1)| 00:00:01 |
　　
--------------------------------------------------------------------------

　　
Predicate Information (identified by operation>　　
---------------------------------------------------
　　1 - filter("OBJECT_ID"=3)
　　
Note
　　
-----
　　- SQL profile "sql_full" used for this statement
　　
Statistics
　　
----------------------------------------------------------
　　7  recursive calls
　　0  db block gets
　　1254  consistent gets
　　1246  physical reads

　　0  redo>　　1606  bytes sent via SQL*Net to client
　　519  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　

　　删除profile
　　

begin　　dbms_sqltune.drop_sql_profile('sql_full');
　　end;
　　

　　再次执行sql,找到走索引的outline
　　

select * from dbms_xplan.display_awr('aq03p7muwgvq5',1,'outline');　　/*+
　　BEGIN_OUTLINE_DATA
　　IGNORE_OPTIM_EMBEDDED_HINTS
　　OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
　　DB_VERSION('11.2.0.4')
　　ALL_ROWS
　　OUTLINE_LEAF(@"SEL$1")
　　INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("A"."OBJECT_ID" "A"."OBJECT_NAME"))
　　END_OUTLINE_DATA
　　*/
　　

declare　　v_hints sys.sqlprof_attr;
　　v_sqltext clob;
　　
begin
　　select sql_fulltext into v_sqltext from v$sql where sql_id='aq03p7muwgvq5' and rownumtrue,replace=>true);
　　
end;
　　

　　建立更优的索引
　　create index I_ind_object_id on a(object_Id) nologging;
　　再次查看执行计划，并没有走更优的索引
　　

Execution Plan　　
----------------------------------------------------------
　　
Plan hash value: 3075844428
　　
---------------------------------------------------------------------------------------------------

　　
|>　　
---------------------------------------------------------------------------------------------------
　　
|   0 | SELECT STATEMENT            |                     |     1 |    98 |     3   (0)| 00:00:01 |
　　
|   1 |  TABLE ACCESS BY INDEX ROWID| A                   |     1 |    98 |     3   (0)| 00:00:01 |
　　
|*  2 |   INDEX RANGE SCAN          | I_IND_OBJECT_ID_COM |     1 |       |     2   (0)| 00:00:01 |
　　
------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　
--------------------------------------------------
　　2 - access("OBJECT_ID"=3)
　　Note
　　
-----
　　- SQL profile "sql_comp_ind" used for this statement
　　Statistics
　　

　　

      7  recursive calls　　0  db block gets
　　10  consistent gets
　　1  physical reads

　　0  redo>　　1609  bytes sent via SQL*Net to client
　　519  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　

　　当然你也可以用SQLT里的coe_xfr_sql_profile.sql或者create_sql_profile.sql生成sql_profile;
　　这里有一点比较扯的是用完整的outline，写进去不报错，但执行计划不走sql_profile里约定的内容；
　　

declare　　v_hints sys.sqlprof_attr;
　　v_sqltext clob;
　　
begin
　　select sql_fulltext into v_sqltext from v$sql where sql_id='aq03p7muwgvq5' and rownum
　　

　　

　　

　　

　　

　　这一段里，