当前位置：首页 -> 基础 -> 数据库编程

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

2015-01-22 21:22:08 · 作者: · 浏览: 10
标签: oracle 优化
rted : 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_t