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;
Task Information:
| Workload Information:
| ||||||||||||
|
|
Execution Information:
|
|
Analysis Information:
Before Change Execution:
| After Change Execution:
| ||||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||
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:
|
Findings (1):
|
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:
|
Findings (1):
|
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