Oracle SQL Profile Notes

1.创建SQL Profile:

1>

create table kin as select * from dba_objects;
create index kin_idx on kin(object_id);

2>

explain plan for select * from kin where object_id= :a;
select * from table(dbms_xplan.display(null,null,'outline'));

3>

explain plan for select /*+ full(kin) */* from kin where object_id= :a;

select * from table(dbms_xplan.display(null,null,'outline'));

4>
declare
  v_hints sys.sqlprof_attr;
begin
  v_hints := sys.sqlprof_attr('FULL(@"SEL$1" “kin”@"SEL$1"); 
  dbms_sqltune.import_sql_profile('select * from kin where object_id= :a', 

                                  v_hints,
                                  'kin_profile', 

                                  force_match => true);
end;
/

5>

explain plan for select * from kin where object_id= :a;
select * from table(dbms_xplan.display);

 

2.查看SQL Profile:

SELECT NAME, CATEGORY, signature, sql_text, created, status, force_matching
  FROM dba_sql_profiles;
 

3.删除SQL Profile:

SELECT      'execute DBMS_SQLTUNE.drop_sql_profile (name   =>'
         || ''''
         || NAME
         || ''''
         || ',ignore => TRUE);  '
    FROM dba_sql_profiles
ORDER BY created DESC;

 

SQL Profile置换:

1.sql_id,child_number查询.

SELECT sql_id, SUBSTR (sql_text, 1, 200)
  FROM v$sql
 WHERE sql_id IN ('', '');

SELECT sql_id, child_number, TIMESTAMP, object_name
  FROM v$sql_plan
 WHERE object_name = '';

 

2.SQL Profile生成:

create_sql_profile.sql