SQL Profiles-STA使用方法

秒速五厘米 2022-08-08 05:26 134阅读 0赞

1、创建优化任务并执行  
使用SQL Profile还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限  
  
  
set timing off  
DECLARE  
  my\_task\_name VARCHAR2(30);  
  my\_sqltext   CLOB;  
BEGIN  
  my\_sqltext := 'select count(\*) from bigtab a, smalltab b where a.object\_name=b.table\_name';  
  my\_task\_name := DBMS\_SQLTUNE.CREATE\_TUNING\_TASK(  
          sql\_text    => my\_sqltext,  
          user\_name   => 'DEMO',  
          scope       => 'COMPREHENSIVE',  
         time\_limit  => 60,  
         task\_name   => 'tuning\_sql\_test',  
         description => 'Task to tune a query on a specified table');  
  
  
 DBMS\_SQLTUNE.EXECUTE\_TUNING\_TASK( task\_name => 'tuning\_sql\_test');  
END;  
/  
也可以使用sql\_id  
var tuning\_task varchar2(100);    
DECLARE    
  l\_sql\_id v$session.prev\_sql\_id%TYPE;    
  l\_tuning\_task VARCHAR2(30);    
BEGIN    
  l\_sql\_id:='4zbqykx89yc8v';    
  l\_tuning\_task := dbms\_sqltune.create\_tuning\_task(sql\_id => l\_sql\_id);    
  :tuning\_task:=l\_tuning\_task;    
  dbms\_sqltune.execute\_tuning\_task(l\_tuning\_task);    
  dbms\_output.put\_line(l\_tuning\_task);    
END;        
/  
  
  
select task\_name, status from USER\_ADVISOR\_LOG where task\_name='tuning\_%';  
\--可以通过视图USER\_ADVISOR\_LOG查看优化任务。  
  
  
2、查看优化结果  
set long 10000  
set longchunksize 1000  
set linesize 100  
print tuning\_task  
SELECT dbms\_sqltune.report\_tuning\_task(:tuning\_task) FROM dual;  
  
  
3、按照优化建议进行优化  
execute dbms\_sqltune.accept\_sql\_profile(task\_name => 'TASK\_241',task\_owner => 'YALI', replace => TRUE,force\_match => TRUE);  
 Description是profile的描述信息；task\_owner是优化建议任务的所有者；replace为TRUE时，如果这个profile已经存在，就代替它；force\_match为TURE时表示与语句强制匹配，即强制使用绑定变量，和系统参数cursor\_sharing设置为FORCE时类似，为FALSE时，与cursor\_sharing设置为EXACT时类似，即完全匹配。  
这里要特别提到的是category这个参数，你可以通过设置这个参数，制定特定会话使用这个profile。在10g中，每个会话都有一个新参数SQLTUNE\_CATEGORY，他的默认值是DEFAULT。而我们在调用这个函数时，如果没有指定这个参数，那它的值也是DEFAULT，而如果我们给这个profile指定了一个其它的CATEGORY值，如FOR\_TUNING，那么只有会话参数SQLTUNE\_CATEGORY也为FOR\_TUNING时，才会使用这个porfile。为什么说这个参数很有用呢？试想一个这样的环境：你在一个生产系统上利用STA调优一条语句，STA已经给出了优化建议，但是你又不敢贸然实施它给出的建议（毕竟它只是机器嘛，不能完全信任），你就可以创建一个有特殊CATEGORY的profile，然后在你自己的会话中制定SQLTUNE\_CATEGORY为这个特殊的CATEGORY，那就既可以看优化建议的实际效果又不影响生产环境。  
  
  
4、控制sql profile  
  
  
exec DBMS\_SQLTUNE.ALTER\_SQL\_PROFILE(name => 'my\_sql\_profile',attribute\_name  => 'STATUS', value => 'DISABLED');  --失效  
exec DBMS\_SQLTUNE.DROP\_SQL\_PROFILE(name => 'my\_sql\_profile');  --删除  
  
  
5、profile的转储与移植  
DBMS\_SQLTUNE包提供了另外几个函数用于将profile的数据导出到表中，然后可以再将表中的数据移植到其它环境中  
  
  
1\}、创建存储表  
begin  
DBMS\_SQLTUNE.CREATE\_STGTAB\_SQLPROF (  
   table\_name            => 'PROFILE\_STGTAB',  
   schema\_name           => 'DEMO',  
   tablespace\_name       => 'EDGARDEMO');  
end;  
/  
  
  
2）、将profile中数据存入存储表中  
BEGIN  
DBMS\_SQLTUNE.PACK\_STGTAB\_SQLPROF (  
   profile\_name          => '%',  
   profile\_category      => 'DEFAULT',  
   staging\_table\_name    => 'PROFILE\_STGTAB',  
   staging\_schema\_owner  => 'DEMO');  
END;  
/  
另外，可以通过存储过程来修改存储表中的信息：  
begin  
DBMS\_SQLTUNE.REMAP\_STGTAB\_SQLPROF (  
  old\_profile\_name      => 'my\_sql\_profile',  
  new\_profile\_name      => 'new\_sql\_profile',  
  new\_profile\_category  => 'DEV',  
  staging\_table\_name    => 'PROFILE\_STGTAB',  
  staging\_schema\_owner  => 'DEMO');  
end;  
/  
  
  
3）、从存储表中导入profile数据  
create table STGTAB as select \* from PROFILE\_STGTAB@EDGAR;  
然后将数据导出为profile  
begin  
DBMS\_SQLTUNE.UNPACK\_STGTAB\_SQLPROF (  
   profile\_name          => 'new\_sql\_profile',  
   profile\_category      => 'DEV',  
   replace               => FALSE,  
   staging\_table\_name    => 'STGTAB',  
   staging\_schema\_owner  => 'DEMO');  
end;  
/  
  
  
select name, category, sql\_text from DBA\_SQL\_PROFILES;  ---查看导入情况