一 建立測試表 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
