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

select * from dbms_xplan.display_awr('aq03p7muwgvq5','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

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,'sql_comp_ind',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

做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 statistics
Single Access Paths
Join Costs
Table Joins Considered
Join Methods Considered (NL/MS/HA)
 Level 1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：
Parameters used by the optimizer
Index statistics 
*/

ALTER SESSION SET EVENTS '10053 trace name context off';

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!