ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle SPA for create index

Oracle SPA for create index

原创 Oracle 作者：lucyne 时间：2015-08-07 15:13:45 0 删除 编辑
1.Setting Up the Test
CONN / AS SYSDBA
CREATE USER SPA_TUSER IDENTIFIED BY SPA_TUSER  QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO SPA_TUSER;
CONN SPA_TUSER/SPA_TUSER
drop table spa_objects purge;
CREATE TABLE spa_objects AS  SELECT * FROM all_objects;
EXEC DBMS_STATS.gather_table_stats(USER, 'spa_objects', cascade => TRUE);

测试sql
SELECT COUNT(*) FROM spa_objects WHERE object_id <= 100;
SELECT object_name FROM spa_objects WHERE object_id = 100;
SELECT COUNT(*) FROM spa_objects WHERE object_id <= 1000;
SELECT object_name FROM spa_objects WHERE object_id = 1000;
SELECT COUNT(*) FROM spa_objects WHERE object_id BETWEEN 100 AND 1000;
insert into spa_objects select * from spa_objects;
update spa_objects set object_id=object_id+1;
commit;

2.Creating SQL Tuning Sets using the DBMS_SQLTUNE Package
CONN / AS SYSDBA
BEGIN
  DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'spa_test_sqlset' );
  DBMS_SQLTUNE.create_sqlset(sqlset_name => 'spa_test_sqlset');
END;
/

DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
     SELECT VALUE(a)
     FROM   TABLE(
              DBMS_SQLTUNE.select_cursor_cache(
                basic_filter   => 'sql_text LIKE ''%spa_objects%'' and parsing_schema_name = ''SPA_TUSER''',
                attribute_list => 'ALL')
            ) a;
                                               
 
  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'spa_test_sqlset',
                           populate_cursor => l_cursor);
END;
/
SELECT sql_text
FROM   dba_sqlset_statements
WHERE  sqlset_name = 'spa_test_sqlset';

3.Running the SQL Performance Analyzer using the DBMS_SQLPA Package

CONN / AS SYSDBA
VARIABLE v_task VARCHAR2(64);
EXEC :v_task :=  DBMS_SQLPA.create_analysis_task(sqlset_name => 'spa_test_sqlset');
PRINT :v_task
BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :v_task,
    execution_type  => 'test execute',
    execution_name  => 'before_change');
END;
/

CONN spa_tuser/spa_tuser
CREATE INDEX spa_objects_index_01 ON spa_objects(object_id);
EXEC DBMS_STATS.gather_table_stats(USER, 'spa_objects', cascade => TRUE);
BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => 'TASK_8168',
    execution_type  => 'test execute',
    execution_name  => 'after_change');
END;
/

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name        => 'TASK_8168',
    execution_type   => 'compare performance', 
    execution_params => dbms_advisor.arglist(
                          'execution_name1', 
                          'before_change', 
                          'execution_name2', 
                          'after_change')
    );
END;
/

SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON

SPOOL /tmp/spa_report.htm

SELECT DBMS_SQLPA.report_analysis_task('TASK_8168', 'HTML', 'ALL','ALL')
FROM   dual;
SPOOL OFF

生成报告
SQL> SQL> SELECT DBMS_SQLPA.report_analysis_task('TASK_8358', 'HTML', 'ALL','ALL') 2 FROM dual; 

General Information

Task Information:
Workload Information:
Task Name : TASK_8358
Task Owner : SYS
Description :
SQL Tuning Set Name : spa_test_sqlset_s
SQL Tuning Set Owner : SYS
Total SQL Statement Count : 8

Execution Information:
Execution Name : EXEC_2917
Execution Type : COMPARE PERFORMANCE
Description :
Scope : COMPREHENSIVE
Status : COMPLETED
Number of Unsupported SQL : 1
Started : 07/17/2013 14:30:29
Last Updated : 07/17/2013 14:30:30
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0

Analysis Information:
Before Change Execution:
After Change Execution:
Execution Name : before_change
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 07/17/2013 14:27:19
Last Updated : 07/17/2013 14:27:21
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0
Execution Name : after_change
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 07/17/2013 14:29:24
Last Updated : 07/17/2013 14:29:24
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0
Comparison Metric: ELAPSED_TIME
Workload Impact Threshold: 1%
SQL Impact Threshold: 1%

Report Summary

Projected Workload Change Impact:
Overall Impact : 70.74%
Improvement Impact : 70.74%
Regression Impact : 0%

SQL Statement Count
SQL Category SQL Count Plan Change Count
Overall 8 6
Improved 6 6
Unchanged 1 0
Unsupported 1 0

Top 7 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
22 26yxj8yv7kwrm 38.31% 2 57177 19019 66.74% y
25 4wqafnq66w8wn 6.58% 1 13247 132 99% y
20 0gz3zzrng1vkx 6.54% 1 13065 38 99.71% y
21 1bwpy6c5txv6t 6.53% 1 13136 117 99.11% y
26 9rz6h3tsqn38s 6.44% 1 12854 28 99.78% y
24 388163c38ywm4 6.34% 1 12653 28 99.78% y
23 2av9ava9mgn4b .01% 1 19919 19906 .07% n
Note: time statistics are displayed in microseconds


Report Details

SQL Details:
Object ID : 22
Schema Name : SPA
SQL ID : 26yxj8yv7kwrm
Execution Frequency : 2
SQL Text : update spa_objects set object_id=object_id+1

Execution Statistics:

Stat Name 		Impact on 
Workload 		Value 
Before 		Value 
After 		Impact 
on SQL
elapsed_time 38.31% .057177 .019019 66.74%
parse_time 2.92% .000138 .000116 15.94%
cpu_time 38.39% .057102 .018885 66.93%
user_io_time 0% 0 0 0%
buffer_gets 21.19% 2160 329 84.77%
cost 14.61% 241 100 58.51%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 148242 148242
Note: time statistics are displayed in seconds 

Notes:
Before Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The performance of this SQL has improved.
  2. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Id : 7208
Plan Hash Value : 1236905229

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 74121 370605 241 00:00:03
1 . UPDATE SPA_OBJECTS
2 .. TABLE ACCESS FULL SPA_OBJECTS 74121 370605 241 00:00:03

Execution Plan After Change:
Plan Id : 7216
Plan Hash Value : 3391198381

Id Operation Name Rows Bytes Cost Time
0 UPDATE STATEMENT 148242 741210 100 00:00:02
1 . UPDATE SPA_OBJECTS
2 .. INDEX FULL SCAN SPA_OBJECTS_INDEX_01 148242 741210 100 00:00:02

SQL Details:
Object ID : 25
Schema Name : SPA
SQL ID : 4wqafnq66w8wn
Execution Frequency : 1
SQL Text : SELECT COUNT(*) FROM spa_objects WHERE object_id <= 1000

Execution Statistics:

Stat Name 		Impact on 
Workload 		Value 
Before 		Value 
After 		Impact 
on SQL
elapsed_time 6.58% .013247 .000132 99%
parse_time .86% .000207 .000194 6.28%
cpu_time 6.53% .013109 .000111 99.15%
user_io_time 0% 0 0 0%
buffer_gets 12.47% 2160 5 99.77%
cost 12.44% 241 1 99.59%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 1 1
Note: time statistics are displayed in seconds 

Notes:
Before Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The performance of this SQL has improved.
  2. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Id : 7211
Plan Hash Value : 2730836028

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 5 241 00:00:03
1 . SORT AGGREGATE 1 5
* 2 .. TABLE ACCESS FULL SPA_OBJECTS 654 3270 241 00:00:03

Predicate Information (identified by operation id):
  • 2 - filter("OBJECT_ID"<=1000)

Execution Plan After Change:
Plan Id : 7219
Plan Hash Value : 1744510740

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 5 1 00:00:01
1 . SORT AGGREGATE 1 5
* 2 .. INDEX RANGE SCAN SPA_OBJECTS_INDEX_01 1306 6530 1 00:00:01

Predicate Information (identified by operation id):
  • 2 - access("OBJECT_ID"<=1000)

SQL Details:
Object ID : 20
Schema Name : SPA
SQL ID : 0gz3zzrng1vkx
Execution Frequency : 1
SQL Text : SELECT COUNT(*) FROM spa_objects WHERE object_id <= 100

Execution Statistics:

Stat Name 		Impact on 
Workload 		Value 
Before 		Value 
After 		Impact 
on SQL
elapsed_time 6.54% .013065 .000038 99.71%
parse_time -9.3% .000114 .000254 -122.81%
cpu_time 6.58% .013109 0 100%
user_io_time 0% 0 0 0%
buffer_gets 12.49% 2160 2 99.91%
cost 12.44% 241 1 99.59%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 1 1
Note: time statistics are displayed in seconds 

Notes:
Before Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The performance of this SQL has improved.
  2. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Id : 7206
Plan Hash Value : 2730836028

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 5 241 00:00:03
1 . SORT AGGREGATE 1 5
* 2 .. TABLE ACCESS FULL SPA_OBJECTS 65 325 241 00:00:03

Predicate Information (identified by operation id):
  • 2 - filter("OBJECT_ID"<=100)

Execution Plan After Change:
Plan Id : 7214
Plan Hash Value : 1744510740

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 5 1 00:00:01
1 . SORT AGGREGATE 1 5
* 2 .. INDEX RANGE SCAN SPA_OBJECTS_INDEX_01 128 640 1 00:00:01

Predicate Information (identified by operation id):
  • 2 - access("OBJECT_ID"<=100)

SQL Details:
Object ID : 21
Schema Name : SPA
SQL ID : 1bwpy6c5txv6t
Execution Frequency : 1
SQL Text : SELECT COUNT(*) FROM spa_objects WHERE object_id BETWEEN 100 AND 1000

Execution Statistics:

Stat Name 		Impact on 
Workload 		Value 
Before 		Value 
After 		Impact 
on SQL
elapsed_time 6.53% .013136 .000117 99.11%
parse_time -1.59% .000215 .000239 -11.16%
cpu_time 6.58% .01322 .000111 99.16%
user_io_time 0% 0 0 0%
buffer_gets 12.47% 2160 5 99.77%
cost 12.44% 241 1 99.59%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 1 1
Note: time statistics are displayed in seconds 

Notes:
Before Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The performance of this SQL has improved.
  2. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Id : 7207
Plan Hash Value : 2730836028

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 5 241 00:00:03
1 . SORT AGGREGATE 1 5
* 2 .. TABLE ACCESS FULL SPA_OBJECTS 591 2955 241 00:00:03

Predicate Information (identified by operation id):
  • 2 - filter("OBJECT_ID"<=1000 AND "OBJECT_ID">=100)

Execution Plan After Change:
Plan Id : 7215
Plan Hash Value : 1744510740

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 5 1 00:00:01
1 . SORT AGGREGATE 1 5
* 2 .. INDEX RANGE SCAN SPA_OBJECTS_INDEX_01 1182 5910 1 00:00:01

Predicate Information (identified by operation id):
  • 2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=1000)

SQL Details:
Object ID : 26
Schema Name : SPA
SQL ID : 9rz6h3tsqn38s
Execution Frequency : 1
SQL Text : SELECT object_name FROM spa_objects WHERE object_id = 100

Execution Statistics:

Stat Name 		Impact on 
Workload 		Value 
Before 		Value 
After 		Impact 
on SQL
elapsed_time 6.44% .012854 .000028 99.78%
parse_time 2.59% .00021 .000171 18.57%
cpu_time 6.31% .012553 0 100%
user_io_time 0% 0 0 0%
buffer_gets 12.48% 2160 4 99.81%
cost 12.44% 241 1 99.59%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 2 2
Note: time statistics are displayed in seconds 

Notes:
Before Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The performance of this SQL has improved.
  2. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Id : 7212
Plan Hash Value : 4168003864

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 30 241 00:00:03
* 1 . TABLE ACCESS FULL SPA_OBJECTS 1 30 241 00:00:03

Predicate Information (identified by operation id):
  • 1 - filter("OBJECT_ID"=100)

Execution Plan After Change:
Plan Id : 7220
Plan Hash Value : 2904428770

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2 60 1 00:00:01
1 . TABLE ACCESS BY INDEX ROWID SPA_OBJECTS 2 60 1 00:00:01
* 2 .. INDEX RANGE SCAN SPA_OBJECTS_INDEX_01 2 1 00:00:01

Predicate Information (identified by operation id):
  • 2 - access("OBJECT_ID"=100)

SQL Details:
Object ID : 24
Schema Name : SPA
SQL ID : 388163c38ywm4
Execution Frequency : 1
SQL Text : SELECT object_name FROM spa_objects WHERE object_id = 1000

Execution Statistics:

Stat Name 		Impact on 
Workload 		Value 
Before 		Value 
After 		Impact 
on SQL
elapsed_time 6.34% .012653 .000028 99.78%
parse_time -1.79% .000232 .000259 -11.64%
cpu_time 6.42% .012775 0 100%
user_io_time 0% 0 0 0%
buffer_gets 12.48% 2160 4 99.81%
cost 12.44% 241 1 99.59%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 2 2
Note: time statistics are displayed in seconds 

Notes:
Before Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Findings (2):
  1. The performance of this SQL has improved.
  2. The structure of the SQL execution plan has changed.


Execution Plan Before Change:
Plan Id : 7210
Plan Hash Value : 4168003864

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 30 241 00:00:03
* 1 . TABLE ACCESS FULL SPA_OBJECTS 1 30 241 00:00:03

Predicate Information (identified by operation id):
  • 1 - filter("OBJECT_ID"=1000)

Execution Plan After Change:
Plan Id : 7218
Plan Hash Value : 2904428770

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2 60 1 00:00:01
1 . TABLE ACCESS BY INDEX ROWID SPA_OBJECTS 2 60 1 00:00:01
* 2 .. INDEX RANGE SCAN SPA_OBJECTS_INDEX_01 2 1 00:00:01

Predicate Information (identified by operation id):
  • 2 - access("OBJECT_ID"=1000)

SQL Details:
Object ID : 23
Schema Name : SPA
SQL ID : 2av9ava9mgn4b
Execution Frequency : 1
SQL Text : insert into spa_objects select * from spa_objects

Execution Statistics:

Stat Name 		Impact on 
Workload 		Value 
Before 		Value 
After 		Impact 
on SQL
elapsed_time .01% .019919 .019906 .07%
parse_time -1% .000251 .000266 -5.98%
cpu_time .22% .020108 .019663 2.21%
user_io_time 0% 0 0 0%
buffer_gets 0% 2160 2160 0%
cost -12.44% 243 483 -98.77%
reads 0% 0 0 0%
writes 0% 0 0 0%
io_interconnect_bytes 0% 0 0 0%
rows 148242 148242
Note: time statistics are displayed in seconds 

Notes:
Before Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.

After Change:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were averaged over next 9 executions.


Execution Plan Before Change:
Plan Id : 7209
Plan Hash Value : 4168003864

Id Operation Name Rows Bytes Cost Time
0 INSERT STATEMENT 74121 7189737 243 00:00:03
1 . LOAD TABLE CONVENTIONAL SPA_OBJECTS
2 .. TABLE ACCESS FULL SPA_OBJECTS 74121 7189737 243 00:00:03

Execution Plan After Change:
Plan Id : 7217
Plan Hash Value : 4168003864

Id Operation Name Rows Bytes Cost Time
0 INSERT STATEMENT 148242 14379474 483 00:00:06
1 . LOAD TABLE CONVENTIONAL SPA_OBJECTS
2 .. TABLE ACCESS FULL SPA_OBJECTS 148242 14379474 483 00:00:06
SQL> SQL> SPOOL OFF

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26390465/viewspace-1764775/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 没有了~
下一篇： 格式化DBMS_METADATA获得DDL
请登录后发表评论 登录
全部评论
lucyne
. . 一个 DBA. . .

注册时间：2012-01-06

  • 博文量
    84
  • 访问量
    783178

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593