sql profile的创建脚本和权限

最新推荐文章于 2023-03-22 21:01:32 发布

congmo2314

最新推荐文章于 2023-03-22 21:01:32 发布

阅读量140

点赞数

一、创建profile需要如下权限
grant CREATE ANY SQL PROFILE to sgpm;
grant DROP ANY SQL PROFILE to sgpm;
grant ALTER ANY SQL PROFILE to sgpm;

二、生成profile的2种方式

1.1.数据库中存在正确的执行计划
--查询v$sql_plan或者是dba_hist_sql_plan确认正确的执行计划：
--记录正确执行计划的child_number及sql_id；
select child_number,
       operation,
       options,
       object#,
       object_owner,
       object_name,
       object_type,
       cost,
       cardinality,
       other_xml
from v$sql_plan
where sql_id = '5czwj3q9sp5v1'
order by child_number, id；
---已有sqlplan
declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text      clob;
l_profile_name   varchar2(30) := '&profile_name'; ---定义profile名字建议带上SQLID
sqlid            Varchar2(30) := '&sql_id'; --sql_id
childno          Number(1) := &child_number; --正确的执行计划的child_number
begin
--获取正确的执行计划可以从当前内存视图v$sql_plan获取或者从历史视图dba_hist_sql_plan获取
select extractvalue(value(d), '/hint') as outline_hints
    bulk collect
    into ar_profile_hints
    from xmltable('/*/outline_data/hint' passing
                  (select xmltype(other_xml) as xmlval
                     from v$sql_plan
                    where sql_id = sqlid
                      and child_number = childno
                      and other_xml is not null)) d;
--查找文本信息可以从当前内存视图获取，或者从历史视图获取dba_hist_sqltext
select sql_fulltext
    into cl_sql_text
    from v$sql
   where sql_id = sqlid
     and child_number = childno;

dbms_sqltune.import_sql_profile(sql_text => cl_sql_text,
                                  profile => ar_profile_hints,
                                  --category => 'DEFAULT',
                                  name        => l_profile_name,
                                  force_match => true,--该参数为true对于未绑定变量的SQL可以使用相同的profile
                                  replace     => true);
end;
/

1.2、手动生存正确的执行计划
--生成想要的执行计划
Delete From plan_table; --先删除老的
commit;
explain plan set statement_id = 'myprofile~' for
select /*+ proftest1 */
   t1.*, t2.owner
    from t1, t2
   where t1.object_name like '%T1%'
     and t1.object_id = t2.object_id;

--查看执行计划及 hint
Select * From Table(dbms_xplan.display(Null, Null, 'outline', Null));

Select plan_id, statement_id
From plan_table
where statement_id = 'myprofile~'
and rownum=1;

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text      clob;
l_profile_name   varchar2(30) := '&profile_name';
sqlid            Varchar2(30) := '&sql_id';
planid           Number(38, 0) := &planid;
begin
select extractvalue(value(d), '/hint') as outline_hints
    bulk collect
    into ar_profile_hints
    from xmltable('/*/outline_data/hint' passing
                  (select xmltype(other_xml) as xmlval
                     from plan_table
                    where plan_id = planid
                      and other_xml is not null)) d;
--查找文本信息可以从当前内存视图获取，或者从历史视图获取dba_hist_sqltext
select sql_fulltext
    into cl_sql_text
    from v$sql
   where sql_id = sqlid
     and Rownum = 1;
dbms_sqltune.import_sql_profile(sql_text => cl_sql_text,
                                  profile => ar_profile_hints,
                                  --category => 'DEFAULT',
                                  name        => l_profile_name,
                                  force_match => true,
                                  replace     => true);