oracle基線與優化（三）

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
                                              <span style="color:#ff6666;"> bf := q''#UPPER(PARSING_SCHEMA_NAME) = ''SYS'' AND UPPER(SQL_TEXT) = ''SELECT COUNT(*) FROM SYS.SPS_TEST'' #'</span>'; 
                                               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

創建執行SQL分析

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', <span style="color:#3333ff;"> --sts獲取</span>
                                               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

---

Task Information:

---

Workload Information:

---

Task Name : SPS5 Task Owner : SYS Description : SQL Tuning Set Name : SPS5 SQL Tuning Set Owner : SYS Total SQL Statement Count : 2
Execution Information:

---

Execution Name : Compare_elapsed_time Execution Type : COMPARE PERFORMANCE Description : Scope : COMPREHENSIVE Status : COMPLETED Started : 12/28/2014 17:30:05 Last Updated : 12/28/2014 17:30:05 Global Time Limit : UNLIMITED Per-SQL Time Limit : UNUSED Number of Errors : 0
Analysis Information:

---

Before Change Execution:

---

After Change Execution:

---

Execution Name : SPS51 Execution Type : CONVERT SQLSET Scope : COMPREHENSIVE Status : COMPLETED Started : 12/28/2014 17:24:52 Last Updated : 12/28/2014 17:24:52 Global Time Limit : UNLIMITED Per-SQL Time Limit : UNLIMITED Execution Name : SPS52 Execution Type : TEST EXECUTE Scope : COMPREHENSIVE Status : COMPLETED Started : 12/28/2014 17:27:32 Last Updated : 12/28/2014 17:27:33 Global Time Limit : UNLIMITED Per-SQL Time Limit : 60 Number of Errors : 0

---

Comparison Metric:ELAPSED_TIME

---

Workload Impact Threshold:1%

---

SQL Impact Threshold:1%

---

Report Summary

---

Projected Workload Change Impact:

---

Overall Impact : 99.5% Improvement Impact : 99.5% Regression Impact : 0%
SQL Statement Count

---

SQL Category SQL Count Plan Change Count Overall 2 0 Improved 2 0
Top 2 SQL Sorted by Absolute Value of Change Impact on the Workload

---

object_id
sql_id Impact on
Workload Execution
Frequency Metric
Before Metric
After Impact
on SQL Plan
Change 6 ff9p4xt2sqhd4 77.3% 63 10253.7301587302 56 99.45% n 5 ah0402tq401hh 22.2% 17 10892.1764705882 38 99.65% n

---

Report Details

---

SQL Details:

---

Object ID : 6 Schema Name : SYS SQL ID : ff9p4xt2sqhd4 Execution Frequency : 63 SQL Text : select count(*) from SYS.SPS_TEST
Execution Statistics:

---

Stat Name Impact on
Workload Value
Before Value
After Impact
on SQL elapsed_time 77.3% .010254 .000056 99.45% parse_time     .000121   cpu_time 76.6% .008914 0 100% user_io_time     0   buffer_gets 78.63% 2050 3 99.85% cost 3138.71% 558 2 99.64% reads 0% 0 0 0% writes 0% 0 0 0% io_interconnect_bytes     0   rows   1 1  
Notes:

---

After Change: 該語句已首先執行以預熱緩沖區高速緩存。顯示的統計信息是後面的 9 執行的平均值。

Findings (1):

---

此 SQL 的性能得到了改善。

Execution Plan Before Change:

---

Plan Hash Value : 3930752761
Id Operation Name Rows Bytes Cost Time 0 SELECT STATEMENT       558   1 . SORT AGGREGATE   1       2 .. TABLE ACCESS FULL SPS_TEST 143778   558 00:00:07
Execution Plan After Change:

---

Plan Id : 202 Plan Hash Value : 3930752761
Id Operation Name Rows Bytes Cost Time 0 SELECT STATEMENT   1   2 00:00:01 1 . SORT AGGREGATE   1       2 .. TABLE ACCESS FULL SPS_TEST 1   2 00:00:01

---

SQL Details:

---

Object ID : 5 Schema Name : SYS SQL ID : ah0402tq401hh Execution Frequency : 17 SQL Text : SELECT COUNT(*) FROM SYS.SPS_TEST
Execution Statistics:

---

Stat Name Impact on
Workload Value
Before Value
After Impact
on SQL elapsed_time 22.2% .010892 .000038 99.65% parse_time     .000125   cpu_time 23.4% .010094 0 100% user_io_time     0   buffer_gets 21.22% 2050 3 99.85% cost 846.95% 558 2 99.64% reads 0% 0 0 0% writes 0% 0 0 0% io_interconnect_bytes     0   rows   1 1  
Notes:

---

After Change: 該語句已首先執行以預熱緩沖區高速緩存。顯示的統計信息是後面的 9 執行的平均值。

Findings (1):

---

此 SQL 的性能得到了改善。

Execution Plan Before Change:

---

Plan Hash Value : 3930752761
Id Operation Name Rows Bytes Cost Time 0 SELECT STATEMENT       558   1 . SORT AGGREGATE   1       2 .. TABLE ACCESS FULL SPS_TEST 143778   558 00:00:07
Execution Plan After Change:

---

Plan Id : 201 Plan Hash Value : 3930752761

 

Id Operation Name Rows Bytes Cost Time 0 SELECT STATEMENT   1   2 00:00:01 1 . SORT AGGREGATE   1       2 .. TABLE ACCESS FULL SPS_TEST 1   2 00:00:01

 

其他：

 

--比較CPU_TIME
begin
  dbms_sqlpa.execute_analysis_task(task_name        => '11gsps2',
                                   execution_name   => 'comparecpu',
                                   execution_type   => 'COMPARE PERFORMANCE',
                                   execution_params => dbms_advisor.arglist('COMPARISON_METRIC',
                                                                             'CPU_TIME',
                                                                             'EXECUTION_NAME1',
                                                                             '11g_trail',
                                                                             'EXECUTION_NAME2',
                                                                             '11g_trail2'),
                                   execution_desc   => 'Compare 10g SQL Trace Performance to 11g Test-Execute for CPU_TIME');
end;
/

--比較BUFFER_GETS
begin
  dbms_sqlpa.execute_analysis_task(task_name        => '11gsps2',
                                   execution_name   => 'comparbuffergets',
                                   execution_type   => 'COMPARE PERFORMANCE',
                                   execution_params => dbms_advisor.arglist('COMPARISON_METRIC',
                                                                             'BUFFER_GETS',
                                                                             'EXECUTION_NAME1',
                                                                             '11g_trail',
                                                                             'EXECUTION_NAME2',
                                                                             '11g_trail2'),
                                   execution_desc   => 'Compare 10g SQL Trace Performance to 11g Test-Execute for BUFFER_GETS');
end;

--比較實際執行時長 

begin
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name        => '11gsps2',
                                   execution_type   => 'COMPARE PERFORMANCE',
                                   execution_name   => 'Compare_elapsed_time',
                                   execution_params => dbms_advisor.arglist('execution_name1',
                                                                            '11g_trail',
                                                                            'execution_name2',
                                                                            '11g_trail2',
                                                                            'comparison_metric',
                                                                            'elapsed_time'));
end;
/
--比較物理讀
begin
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name        => '11gsps2',
                                   execution_type   => 'COMPARE PERFORMANCE',
                                   execution_name   => 'Compare_physical_reads0',
                                   execution_params => dbms_advisor.arglist('execution_name1',
                                                                            '11g_trail',
                                                                            'execution_name2',
                                                                            '11g_trail2',
                                                                            'comparison_metric',
                                                                            'disk_reads'));
end;
/

set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off 
spool spa_report_elapsed_time.html 
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'ALL','ALL', execution_name=>'Compare_elapsed_time') FROM dual; 
spool off

 

---