首页 > 代码库 > 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: | ||||||||||||
|
|
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
oracle 基线与优化(三)
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。