oracle sql_profile绑定异常处置

发布时间：2022-06-28 12:53 所属栏目：115 来源：互联网

导读：oracle sql_profile绑定异常处置：一建立测试表 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_

  oracle sql_profile绑定异常处置：

　　一　建立测试表
　　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 rownum<2;
　　 v_hints:=sys.sqlprof_attr(q'[FULL(@"SEL$1" "A"@"SEL$1")]');
　　 dbms_sqltune.import_sql_profile(v_sqltext,v_hints,'sql_full',force_match=>true,replace=>true);
　　end;
　　建立索引
　　create index I_ind_object_id_com on a(object_id,object_name) nologging;
　　查看执行计划，并没有走索引：

　　 Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 2248738933
　　--------------------------------------------------------------------------
　　| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
　　--------------------------------------------------------------------------
　　|   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 id):
　　---------------------------------------------------
　　   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 size
　　       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

　　当然你也可以用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<2;
　　 v_hints:=sys.sqlprof_attr(
　　q'[BEGIN_OUTLINE_DATA]',
　　q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
　　q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
　　q'[DB_VERSION('11.2.0.4')],
　　q'[ALL_ROWS]',
　　q'[OUTLINE_LEAF(@"SEL$1")]',
　　q'[FULL(@"SEL$1" "A"@"SEL$1")]',
　　q'[END_OUTLINE_DATA]');
　　 dbms_sqltune.import_sql_profile(v_sqltext,v_hints,'sql_full',force_match=>true,replace=>true);
　　end;

（编辑：ASP站长网）