oracle基线与优化（三）(一)

oracle 基线与优化:

生产库sts获取：

?

BEGIN
  dbms_sqltune.create_sqlset(sqlset_name => 'SPS5', sqlset_owner => 'SYS');
END;
begin
  DBMS_SCHEDULER.CREATE_JOB(job_name   => 'SPS5',
                            job_type   => 'PLSQL_BLOCK',
                            job_action => 'DECLARE bf VARCHAR2(98); 
                                           BEGIN
                                               bf := q''#UPPER(PARSING_SCHEMA_NAME) = ''SYS'' AND UPPER(SQL_TEXT) = ''SELECT COUNT(*) FROM SYS.SPS_TEST'' #''; 
                                               dbms_sqltune.capture_cursor_cache_sqlset( sqlset_name=>''SPS5'', 
                                                                                         time_limit=>''120'', 
                                                                                         repeat_interval=>''5'', 
                                                                                         basic_filter=>bf, 
                                                                                         sqlset_owner=>''SYS'');
                                           END;',
                            enabled    => TRUE);
end;

?

生产库sts信息查询：

select name,statement_count from dba_sqlset;

BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('PACK_SQLSET', 'SYSTEM');
END;
BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLSET('SP52', 'SYS', 'PACK_SQLSET', 'SYSTEM');
END;
SELECT * FROM SYSTEM.PACK_SQLSET

执行：

?

begin
   DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET('SPS5','SYS',TRUE,'PACK_SQLSET','SYSTEM');
end; 

?

测试库上执行语句：

?

alter system flush buffer_cache;
alter system flush shared_pool;
select count(*) from SYS.SPS_TEST;
SELECT COUNT(*) FROM SYS.SPS_TEST

declare
  sts_task varchar2(64);
begin
  sts_task := dbms_sqlpa.create_analysis_task(task_name   => 'SPS5',
                                              description => 'experiment11gR2 execute',
                                              sqlset_name => 'SPS5');
end;

declare
  exe_task varchar2(64);
begin
  exe_task := dbms_sqlpa.execute_analysis_task(task_name      => 'SPS5',
                                               execution_name => 'SPS51',
                                               execution_type => 'CONVERT SQLSET',  --sts获取
                                               execution_desc => '11g sql trail');
end;

declare
  exe_task varchar2(64);
begin
  exe_task := dbms_sqlpa.execute_analysis_task(task_name      => 'SPS5',
                                               execution_name => 'SPS52',
                                               execution_type => 'TEST EXECUTE',
                                               execution_desc => '11g sql trail2');
end;

?

?

begin
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name        => 'SPS5',
                                   execution_type   => 'COMPARE PERFORMANCE',
                                   execution_name   => 'Compare_elapsed_time2',
                                   execution_params => dbms_advisor.arglist('execution_name1',
                                                                            'SPS51',
                                                                            'execution_name2',
                                                                            'SPS52',
                                                                            'comparison_metric',
                                                                            'elapsed_time'));
end;

select xmltype(dbms_sqlpa.report_analysis_task('SPS5', 'html', 'typical', 'all', null, 100, 'Compare_elapsed_time')).getclobval(0, 0)
from dual;

?

General Information