本文介绍了一种使用SQL性能分析器进行SQL性能比较的方法，包括从生产数据库捕获SQL、迁移SQL到测试数据库、创建和执行SQL性能分析任务等步骤，并最终生成详细的性能对比报告。
The SQL Performance Analyzer allows for the comparison of SQL performance statistics before and after changes and provide comparison report.
The Steps for SQL Replay is as below:
1 Capture SQLs on Production Database
2 Move SQLs to Test Database
3 Create a SQL Performance Analyzer task on the test system
4 Generate and store the query execution statistics before the change.
5 Make changes
6 Generate and store the query execution statistics after the change.
7 Compare Performance
8 Generate Report
1.Capture SQLs on Production Database
SQL> exec dbms_sqltune.create_sqlset('STS')
PL/SQL 过程已成功完成。
SQL> DECLARE
2 sts VARCHAR2(30) := 'STS';
3 BEGIN
4 dbms_sqltune.capture_cursor_cache_sqlset(sts,
5 30,
6 1,
7 'MERGE',
8 dbms_sqltune.MODE_ACCUMULATE_STATS
9 );
10 END;
11 /
PL/SQL 过程已成功完成。
SQL>
2.Move SQLs to Test Database
SQL> exec dbms_sqltune.create_stgtab_sqlset('SQLSET_TABLE');
PL/SQL 过程已成功完成。
SQL> exec dbms_sqltune.pack_stgtab_sqlset('STS','HENRY','SQLSET_TABLE','HENRY');
PL/SQL 过程已成功完成。
SQL> host exp henry/henry file=sqlset_table.dmp tables=sqlset_table
Export: Release 11.2.0.1.0 - Production on 星期二 12月 28 15:47:22 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径...
. . 正在导出表 SQLSET_TABLE导出了 160 行
成功终止导出, 没有出现警告。SQL> host imp bai/bai file=sqlset_table.dmp full=y
Import: Release 11.2.0.1.0 - Production on 星期二 12月 28 15:55:01 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
经由常规路径由 EXPORT:V11.02.00 创建的导出文件
警告: 这些对象由 HENRY 导出, 而不是当前用户
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 HENRY 的对象导入到 SYSTEM
. 正在将 HENRY 的对象导入到 SYSTEM
IMP-00015: 由于对象已存在, 下列语句失败:
"CREATE PUBLIC SYNONYM "ANYDATA" FOR "SYS"."ANYDATA""
. . 正在导入表 "SQLSET_TABLE"导入了 160 行
3 Create a SQL Performance Analyzer task on the test system
SQL> exec dbms_sqltune.UNPACK_STGTAB_SQLSET(SQLSET_NAME=>'STS', SQLSET_OWNER=>'HENRY',REPLACE=>true,STAGING_TABLE_NAME=>'SQLSET_
TABLE', STAGING_SCHEMA_OWNER=>'HENRY' );
PL/SQL 过程已成功完成。
SQL> var l_task_id char(30)
SQL> begin
2 :l_task_id:=dbms_sqlpa.create_analysis_task(
3 sqlset_name => 'STS',
4 task_name => 'MY_REPLAY_TASK'
5 );
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> exec dbms_sqltune.set_tuning_task_parameter('MY_REPLAY_TASK','LOCAL_TIME_LIMIT',60);
PL/SQL 过程已成功完成。
SQL> exec dbms_sqltune.set_tuning_task_parameter('MY_REPLAY_TASK','TIME_LIMIT',200000000000);
PL/SQL 过程已成功完成。
4 Generate and store the query execution statistics before the change.
SQL> begin
2 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
3 task_name => 'MY_REPLAY_TASK',
4 execution_type => 'TEST EXECUTE',
5 execution_name => 'MY_REPLAY_EXECUTE_BEFORE'
6 );
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> col TASK_NAME format a30
SQL> col EXECUTION_NAME for a30
SQL> select execution_name,
2 status,
3 execution_end
4 from DBA_ADVISOR_EXECUTIONS
5 where task_name='MY_REPLAY_TASK'
6 order by execution_end
7 /
EXECUTION_NAME STATUS EXECUTION_END
------------------------------ ----------- --------------
MY_REPLAY_EXECUTE_BEFORE COMPLETED 28-12月-10
MY_REPLAY_EXECUTE_after COMPLETED 28-12月-10
MY_COMPARE_EXECUTION COMPLETED 28-12月-10
MY_REPLAY_EXECUTE_BEFORE COMPLETED 28-12月-10
SQL> select TASK_ID,TASK_NAME,TASK_NAME , STATUS
2 FROM USER_ADVISOR_TASKS
3 WHERE task_name = 'MY_REPLAY_TASK';
TASK_ID TASK_NAME TASK_NAME STATUS
---------- ------------------------------ ------------------------------ -----------
412 MY_REPLAY_TASK MY_REPLAY_TASK COMPLETED
SQL> SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS
2 ;
SOFAR TOTALWORK
---------- ----------
487 487
487 487
487 487
0 0
SQL> select TASK_ID,TASK_NAME,TASK_NAME , STATUS FROM USER_ADVISOR_TASKS WHERE task_name = 'MY_REPLAY_TASK';
TASK_ID TASK_NAME TASK_NAME STATUS
---------- ------------------------------ ------------------------------ -----------
412 MY_REPLAY_TASK MY_REPLAY_TASK COMPLETED
SQL> show parameter target;
5 Make changes
SQL> show parameter target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 820M
memory_target big integer 800M
parallel_servers_target integer 8
pga_aggregate_target big integer 0
sga_target big integer 0
SQL> alter system set memory_target=500m;
系统已更改。
6 Generate and store the query execution statistics after the change.
SQL> begin
2 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
3 task_name => 'MY_REPLAY_TASK',
4 execution_type => 'TEST EXECUTE',
5 execution_name => 'MY_REPLAY_EXECUTE_after');
6 end;
7 /
PL/SQL 过程已成功完成。
7 Compare Performance
SQL> Begin
2
3 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
4 task_name => 'MY_REPLAY_TASK',
5 execution_type => 'COMPARE PERFORMANCE',
6 execution_name => 'MY_COMPARE_EXECUTION');
7
8 end;
9 /
PL/SQL 过程已成功完成。
8 Generate Report
SQL> set serveroutput on size 999999
SQL> set long 999999
SQL>
SQL> select DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK') from dual;
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
General Information
---------------------------------------------------------------------------------------------
Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : MY_REPLAY_TASK SQL Tuning Set Name : STS_TEST
Task Owner : HENRY SQL Tuning Set Owner : HE
NRY
Description : Total SQL Statement Count : 487
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : MY_COMPARE_EXECUTION Started : 12/28/2010 15:4
1:54
Execution Type : COMPARE PERFORMANCE Last Updated : 12/28/2010 15:42:00
Description : Global Time Limit : 200000000000
Scope : COMPREHENSIVE Per-SQL Time Limit : 60
Status : COMPLETED Number of Errors : 221
Number of Unsupported SQL : 11
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
Analysis Information:
---------------------------------------------------------------------------------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ------------------------------
---------------
Execution Name : MY_REPLAY_EXECUTE_BEFORE Execution Name : MY_REPLAY_EXECUTE_after
Execution Type : TEST EXECUTE Execution Type : TEST EX
ECUTE
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLET
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
ED
Started : 12/28/2010 15:38:18 Started : 12/28/2010 15:41:22
Last Updated : 12/28/2010 15:39:05 Last Updated : 12/28/2
010 15:41:47
Global Time Limit : 200000000000 Global Time Limit : 200000000000
Per-SQL Time Limit : 60 Per-SQL Time Limit : 60
Number of Errors : 221 Number of Errors : 221
---------------------------------------------
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
Comparison Metric: ELAPSED_TIME
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------
Report Summary
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Projected Workload Change Impact:
-------------------------------------------
Overall Impact : -8.81%
Improvement Impact : 1.52%
Regression Impact : -10.32%
SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
Overall 487 0
Improved 1 0
Regressed 4 0
Unchanged 250 0
with Errors 221 0
Unsupported 11 0
Top 100 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
----
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
| | | Impact on | Execution | Metric | Metric | Impact | Plan |
| object_id | sql_id | Workload | Frequency | Before | After | on SQL | Change |
-----------------------------------------------------------------------------------------
---
| 1171 | 5xpk50ma14qx0 | -3.61% | 600 | 329629 | 348143 | -5.62% | n |
| 1301 | a8j39qb13tqkr | -2.96% | 67 | 304100 | 440113 | -44.73% | n |
| 1085 | 35wbu3rr5q80d | -2.08% | 2928 | 1264 | 3452 | -173.1% | n
|
| 1140 | 51vw8qf5uprrv | -1.67% | 91 | 3092 | 59474 | -1823.48% | n |
| 1063 | 2ft0vv13g0xkd | 1.52% | 91 | 145457 | 94220 | 35.22% | n |
| 1360 | cm5vu20fhtnq1 | -.91% | 84541 | 50 | 83 | -66% | n
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
|
| 1272 | 9b7pdpgf2znkk | .75% | 100 | 126012 | 103010 | 18.25% | n |
| 1046 | 1spv5pysapqvc | .3% | 91 | 18908 | 8599 | 54.52% | n |
| 1150 | 59suf2bbv0ht2 | -.28% | 91 | 125721 | 135106 | -7.46% | n |
| 1462 | gwj1f651t001a | -.26% | 295 | 2242 | 4980 | -122.12% | n |
| 1397 | dtgc9kqnm4v5s | -.23% | 2928 | 642 | 881 | -37.23% | n |
| 1029 | 1d0yffmmwdnf3 | .22% | 2928 | 365 | 136 | 62.74% | n |
| 994 | 0drjkaxn1jfzh | -.21% | 1511 | 535 | 966 | -80.56
% | n |
| 1060 | 27v7m7f3zm07w | .16% | 272 | 3795 | 2027 | 46.59% | n |
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
| 1321 | ayh8rjn158m80 | -.13% | 6358 | 49 | 110 | -124.49% | n |
| 1425 | fsbqktj5vw6n9 | .11% | 2962 | 369 | 252 | 31.71%
| n |
| 1391 | dpxn9r818qm9r | .11% | 2929 | 218 | 100 | 54.13% | n |
| 1078 | 30hy59nc21g04 | .1% | 2933 | 322 | 220 | 31.68% | n |
| 1003 | 0k8522rmdzg4k | .07% | 2879 | 145 | 73 | 49.66%
| n |
| 1069 | 2n1hu6d1pmvtw | -.07% | 2933 | 67 | 137 | -104.48% | n |
| 1016 | 12kw3xcxv1qpg | .06% | 1779 | 190 | 80 | 57.89% | n |
| 1086 | 3626j8qpgf20s | -.06% | 53 | 4366 | 7934 | -81.72% |
n |
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
| 982 | 089dbukv1aanh | -.06% | 31334 | 63 | 69 | -9.52% | n |
| 1118 | 4a3ccstvk0ssw | .06% | 1779 | 185 | 80 | 56.76% | n |
| 1221 | 7ng34ruy5awxq | -.06% | 3166 | 90 | 145 | -61.11% |
n |
| 1324 | azr4arr8v288y | -.05% | 2928 | 46 | 99 | -115.22% | n |
| 1200 | 74jp9b3h4krhd | .05% | 2933 | 134 | 82 | 38.81% | n |
| 1189 | 6r75f7k88qr4a | .05% | 2933 | 134 | 83 | 38.06% | n
|
| 1132 | 4yb79gnqp1v90 | .05% | 2928 | 144 | 93 | 35.42% | n |
| 1111 | 3yqfw6yfwwmyk | .05% | 2928 | 90 | 40 | 55.56% | n |
| 1121 | 4fhzczsnd3zr2 | .05% | 2928 | 88 | 40 | 54.55% | n
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
|
| 1364 | ctyb7x3gfayw5 | -.04% | 2928 | 46 | 93 | -102.17% | n |
| 1407 | f6x4ksgvy5bkx | .04% | 2687 | 108 | 58 | 46.3% | n |
| 1002 | 0k54hbxdpz2a6 | .04% | 2928 | 88 | 44 | 50% | n
|
| 1454 | grwydz59pu6mc | -.04% | 6077 | 43 | 63 | -46.51% | n |
| 1463 | gwqrn0xb8btyt | -.04% | 2995 | 51 | 90 | -76.47% | n |
| 1337 | bqfx5q2jas08u | .04% | 89 | 5701 | 4392 | 22.96% | n
|
| 1326 | b2u9kspucpqwy | -.04% | 210 | 148 | 693 | -368.24% | n |
| 1109 | 3ybzn58dw0g6d | .03% | 2928 | 92 | 56 | 39.13% | n |
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
| 1106 | 3w4qs0tbpmxr6 | .03% | 2032 | 97 | 46 | 52.58% | n
|
| 1134 | 4z2at9d1natrv | .03% | 842 | 352 | 230 | 34.66% | n |
| 1438 | g41n1r60y3d71 | -.03% | 2929 | 64 | 99 | -54.69% | n |
| 984 | 08vznc16ycuag | .03% | 1497 | 200 | 133 | 33.5% | n
|
| 1164 | 5ms6rbzdnq16t | .03% | 16551 | 67 | 61 | 8.96% | n |
| 1129 | 4x2cynvsuuddu | .03% | 151 | 1945 | 1291 | 33.62% | n |
| 1116 | 459f3z9u4fb3u | .03% | 1359 | 125 | 53 | 57.6% | n
|
| 1395 | drvfnp22at00m | -.03% | 2928 | 41 | 74 | -80.49% | n |
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
| 1263 | 934ur8r7tqbjx | -.03% | 143 | 2338 | 2996 | -28.14% | n |
| 1185 | 6k5agh28pr3wp | -.03% | 210 | 450 | 898 | -99.56% | n |
| 1365 | cumjq42201t37 | -.03% | 1512 | 50 | 112 | -124% | n |
| 980 | 05xcf43d9psvm | .03% | 210 | 1080 | 674 | 37.59% | n |
| 1071 | 2q93zsrvbdw48 | .03% | 1807 | 173 | 126 | 27.17% | n |
| 1409 | f8y4ubckk4pfh | .03% | 91 | 1684 | 813 | 51.72
% | n |
| 1361 | crwtm662ycm4w | -.02% | 761 | 129 | 228 | -76.74% | n |
| 1355 | cb21bacyh3c7d | -.02% | 2970 | 44 | 69 | -56.82% | n |
| 986 | 09rmn1adfaqct | -.02% | 2928 | 45 | 70 | -55.56%
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
| n |
| 1371 | cxjqbfn0d3yqq | -.02% | 210 | 140 | 464 | -231.43% | n |
| 1236 | 828mzjuppndf4 | -.02% | 2928 | 251 | 274 | -9.16% | n |
| 1449 | gkm8bs08b4fqu | -.02% | 2928 | 42 | 65 | -54.76%
| n |
| 1080 | 32bhha21dkv0v | .02% | 535 | 309 | 187 | 39.48% | n |
| 1213 | 7gtztzv329wg0 | -.02% | 1544 | 63 | 105 | -66.67% | n |
| 1229 | 7wt7phk4xns75 | -.02% | 211 | 538 | 832 | -54.65% |
n |
| 1464 | gx4mv66pvj3xz | -.02% | 1991 | 56 | 86 | -53.57% | n |
| 997 | 0g5bcff16kw0p | -.02% | 5856 | 141 | 151 | -7.09% | n |
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
| 1187 | 6mvfay19q3v4n | .02% | 136 | 983 | 578 | 41.2% |
n |
| 1367 | cvn54b7yz0s8u | -.02% | 983 | 45 | 100 | -122.22% | n |
| 1210 | 7d92gmwphtza8 | -.02% | 132 | 1753 | 2135 | -21.79% | n |
| 1023 | 1a8n1zgb7m90w | .01% | 558 | 133 | 54 | 59.4% | n
|
| 1143 | 53saa2zkr6wc3 | -.01% | 6891 | 110 | 116 | -5.45% | n |
| 1404 | f3g84j69n0tjh | .01% | 508 | 135 | 55 | 59.26% | n |
| 1211 | 7dwyqv9sawkyb | -.01% | 729 | 86 | 140 | -62.79% | n
|
| 1402 | f0b7463xuzazk | .01% | 275 | 233 | 104 | 55.36% | n |
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
| 1191 | 6xvp6nxs4a9n4 | .01% | 981 | 94 | 58 | 38.3% | n |
| 1362 | csnp95dz2r8ss | -.01% | 981 | 49 | 83 | -69.39% | n
|
| 1237 | 83taa7kaw59c1 | .01% | 3680 | 133 | 124 | 6.77% | n |
| 1348 | c5sn73fw8w7qs | .01% | 544 | 172 | 114 | 33.72% | n |
| 1325 | b1wc53ddd6h3p | -.01% | 537 | 48 | 105 | -118.75% | n
|
| 1417 | fhk9r0qb2n3t4 | -.01% | 2928 | 41 | 51 | -24.39% | n |
| 1422 | fr31mzzknv0wh | .01% | 162 | 245 | 68 | 72.24% | n |
| 1356 | cca61npxvbudn | -.01% | 275 | 143 | 241 | -68.53% | n
|
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
| 1316 | aukfj0ur6962z | .01% | 276 | 156 | 59 | 62.18% | n |
| 1339 | bsa0wjtftg3uw | .01% | 781 | 90 | 58 | 35.56% | n |
| 1022 | 19x1189chq3xd | .01% | 216 | 171 | 57 | 66.67% | n
|
| 1375 | d3apnz76qwmrs | -.01% | 2933 | 89 | 97 | -8.99% | n |
| 1242 | 87gaftwrm2h68 | -.01% | 661 | 76 | 110 | -44.74% | n |
| 1235 | 81ky0n97v4zsg | .01% | 5480 | 1033 | 1029 | .39% | n
|
| 1259 | 90sssrprs2xu2 | .01% | 275 | 130 | 54 | 58.46% | n |
| 1017 | 14fy467qw1j7t | .01% | 373 | 101 | 46 | 54.46% | n |
| 1212 | 7gmpnpyyq71ux | -.01% | 272 | 159 | 234 | -47.17% | n |
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
| 1222 | 7nuw4xwrnuwxq | -.01% | 535 | 53 | 91 | -71.7% | n |
| 1393 | dqbhc9r7gz0a5 | -.01% | 136 | 554 | 703 | -26.9% | n |
| 1280 | 9k43zr1uannr6 | .01% | 691 | 176 | 150 | 14.77% | n |
| 1271 | 98wy11096mth0 | -.01% | 2928 | 232 | 238 | -2.59
% | n |
| 1188 | 6qz82dptj0qr7 | .01% | 511 | 83 | 51 | 38.55% | n |
| 1055 | 22zy9092puhcu | -.01% | 99 | 8686 | 8846 | -1.84% | n |
| 1141 | 52f02yu4nk0as | 0% | 91 | 5643 | 5475 | 2.98%
| n |
| 1323 | azbp445n3v9s1 | 0% | 71 | 139 | 350 | -151.8% | n |
DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')
--------------------------------------------------------------------------------
| 1040 | 1mjd9xp80vuqa | 0% | 250 | 108 | 49 | 54.63% | n |
| 1327 | b6y9wkhxv1puz | 0% | 2933 | 74 | 79 | -6.76%
| n |
| 1419 | fp0agx1ukw7wu | 0% | 2928 | 46 | 51 | -10.87% | n |
--------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
------------
扫一扫
1680
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
