知识点 | oracle spa 单条sql优化流程

oracle spa属于real application test的内容，用来对比sql前后性能变化，比如我要比较一条sql前后的的性能变化：

–单条sql优化流程

alter session set optimizer\_features\_enable='10.2.0.4';  

variable g\_task varchar2(100);  

exec :g\_task:= dbms\_sqlpa.create\_analysis\_task(task\_name=>'TASK2',sql\_text=>'select count(\*) from employees where employee\_id>10')  
exec dbms\_sqlpa.execute\_analysis\_task(task\_name=>:g\_task,execution\_type=>'test execute',execution\_name=>'before\_change');  

alter table employees drop primary key drop index; 

alter session set optimizer\_features\_enable='11.2.0.4';  

\--EXEC DBMS\_SQLPA.SET\_ANALYSIS\_TASK\_PARAMETER( :g\_task,'comparison\_metric', 'buffer\_gets');  

exec dbms\_sqlpa.execute\_analysis\_task(task\_name=>:g\_task,execution\_type=>'test execute',execution\_name=>'after change');  

exec dbms\_sqlpa.execute\_analysis\_task(task\_name=>:g\_task,execution\_type=>'compare performance',execution\_name=>'compar');  

spool d:\\spa\_report\_elapsed\_time.html  
SELECT dbms\_sqlpa.report\_analysis\_task(:g\_task, 'HTML', 'ALL','ALL') FROM dual;  
spool off;  

exec DBMS\_SQLPA.DROP\_ANALYSIS\_TASK('TASK2');  

begin  
DBMS\_SQLPA.DROP\_ANALYSIS\_TASK('TASK2');  
end;

复制