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_hintsfromxmltable('/*/outline_data/hint'passing (selectxmltype(other_xml) as xmlvalfromdba_hist_sql_planwheresql_id = '&sql_id'and plan_hash_value=&plan_hash_valueand 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 statementStatistics---------------------------------------------------------- 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
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 rownum<2; v_hints:=sys.sqlprof_attr( q'[INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("A"."OBJECT_ID" "A"."OBJECT_NAME"))]'); dbms_sqltune.import_sql_profile(v_sqltext,v_hints,'sql_comp_ind',force_match=>true,replace=>true);end;
建立更优的索引create index I_ind_object_id on a(object_Id) nologging;
再次查看执行计划,并没有走更优的索引
Execution Plan----------------------------------------------------------Plan hash value: 3075844428 ---------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------------| 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 id):-------------------------------------------------- 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 size 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<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;
做10053:
select value from v$parameter where name='sql_trace';alter session set sql_trace=true;alter session set tracefile_identifier='mytrace';
--single instance
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$Mystat where rownum<2));
--/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_22354_mytrace.trc
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
select * from a where object_id=3
/*Level 2:2级是1级的一个子集,它包含以下内容:Column statisticsSingle Access PathsJoin CostsTable Joins ConsideredJoin Methods Considered (NL/MS/HA) Level 1: 1级比2级更详细,它包含2级的所有内容,在加如下内容:Parameters used by the optimizerIndex statistics */
ALTER SESSION SET EVENTS '10053 trace name context off';
没有看出什么异常,和traceonly看到的一致,做sql_trace跟踪sql_profile写入的基表sys.sqlobj$data:
select to_char(comp_data),signature from sys.sqlobj$data where plan_Id=0
发现:
这一段里,
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'[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;
正常:
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---------------------------------------------------------- 1 recursive calls 0 db block gets 1248 consistent gets 1245 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