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生成: