Oracle SPA使用详解
这里的SPA不是说美容，而是Oracle的SQL性能优化分析器，SQL Performance Analyzer。说到SPA就不得不提Oracle关于SQL优化的几个常见的术语，SQL Tuning Advisor, SQL Tuning Set，SQL Performance Analyzer。
STS=SQL Tuning Set，SQL调优集，是一系列Oracle SQL的集合，这些SQL可以从AWR或者cursor cache中获得。可以使用DBMS_SQLTUNE包中SQL Tuning Set相关的函数和过程进行操作。
STA=SQL Tuning Advisor，SQL优化顾问，是针对SQL调优集或者SPA甚至单纯的SQL进行优化。可以使用DBMS_SQLTUNE包中SQL Tuning Advisor相关的函数和过程进行操作。
SPA=SQL Performance Analyzer，SQL性能分析器。针对数据库变更进行的性能分析。变更包括数据库升级，平台/版本/架构变更，参数调整，统计信息收集等等。SPA其实也是Oracle Real Application Testing的一个组件，另外一个组件就是前面介绍过的Database Replay。SPA的原理是通过变更前对收集的STS进行执行，获取基线数据，变更后再次进行执行，然后进行对比，从多个维度比如CPU时间，I/O，buffer get等生成详细的变更对比报告。
典型用法是捕捉STS，然后通过SPA生成对比报告，然后对变更后衰减的SQL调用STA进行优化。
下面是一个SPA的使用详解，其中的输入就是从AWR两个快照之间取的STS：
--Drop STS:
SQL> exec DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'my_sts');
--Create STS:
SQL> exec DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'my_sts', description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
--Load STS:
SQL> DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>257, end_snap=>258,basic_filter=>'sql_id = ''1bq7qgz7p6x46''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'my_sts', populate_cursor=>cur);
CLOSE cur;
END;
/
--Create SPA TASK
SQL> declare v_spa_name varchar2(100);
2 begin
3 v_spa_name:=dbms_sqlpa.create_analysis_task(
4 sqlset_name=>'my_sts',
5 task_name=>'my_spa_task');
6 dbms_output.put_line('spa_name='||v_spa_name);
7 end;
8 /
spa_name=my_spa_task
PL/SQL procedure successfully completed.
--Execute SPA TASK before change
SQL> exec dbms_sqlpa.execute_analysis_task(task_name=>'my_spa_task',execution_type=>'test execute', execution_name=>'my_exec_before_change');
PL/SQL procedure successfully completed.
--make change now....
SQL> select index_name from dba_indexes where table_name='MY_OBJECTS';
INDEX_NAME
------------------------------
MY_OBJECTS_INDEX_01
SQL> drop index spa_test_user.MY_OBJECTS_INDEX_01;
Index dropped.
--Execute SPA TASK after change
SQL> exec dbms_sqlpa.execute_analysis_task(task_name=>'my_spa_task',execution_type=>'test execute', execution_name=>'my_exec_after_change');
PL/SQL procedure successfully completed.
--Get report or compare report
SQL> var rep clob;
SQL> exec :rep:=dbms_sqlpa.report_analysis_task('my_spa_task','text','typical','summary');
PL/SQL procedure successfully completed.
SQL> set long 100000 longchunksize 100000 linesize 130
SQL> print :rep
REP
----------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_spa_task
Tuning Task Owner : SYS
Workload Type : SQL Tuning Set
Execution Count : 2
Current Execution : my_exec_after_change
Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE
Completion Status : COMPLETED
Started at : 03/14/2013 14:58:00
Completed at : 03/14/2013 14:58:08
SQL Tuning Set (STS) Name : my_sts
SQL Tuning Set Owner : SYS
Number of Statements in the STS : 79
Number of SQLs Analyzed : 79
Number of SQLs in the Report : 79
Number of SQLs with Findings : 1
Number of SQLs with Errors : 5
-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
SQL Statements Ordered by Elapsed Time
-------------------------------------------------------------------------------
Parse Elapsed CPU Buffer Optimizer
object ID SQL ID Time (s) Time (s) Time (s) Gets Cost
---------- ------------- -------- -------- -------- -------- ---------
86 1bx8mgs8by25x .005597 .157076 .108483 3214 144
147 dayq182sk41ks .000288 .143791 .089486 0 1
137 bm2pwrpcr8ru6 .000155 .143363 .093819 0 1
139 bunssq950snhf .000356 .142137 .091986 0 1
87 1cq3qr774cu45 .001988 .036542 .003221 0 5
107 5dfmd823r8dsp .002836 .030445 .019774 0 2
97 350myuyx0t1d6 .006113 .008463 .000999 12 3
152 f0s0bk5k713yb .000248 .005393 .003666 0 2
119 7sxujr2u5ddy4 .000271 .004132 .000666 0 1
91 1sd9s6z4gs4c5 .000135 .003598 .002444 816 229
96 32mk33ry1g665 .00201 .003385 .000333 8 2
133 ab9rc63zxa4bj .062683 .003121 .001222 205 13
116 6xpsr8v27pmy2 .001445 .002782 .001888 0 1
146 d0hs62mdytbk1 .004535 .002498 .001777 32 16
121 7xshr7maxf6rc .004096 .002443 .001777 242 13
141 by4un3fmx698z .010119 .002443 .001666 62 13
127 95jvkhg3t7rqj .163061 .00239 .001666 184 23
114 6m6b8kbv1dt4c .02843 .002303 .001666 188 50
157 gcpj8av4kx6j2 .011175 .002274 .001666 62 13
89 1nay5fd0d17km .009615 .002213 .001444 62 13
100 4002tnht9dwhw .012735 .002199 .001444 62 13
113 6d1jnnuhkt8wy .035774 .002051 .001333 214 29
104 4ku91umkgpmmr .027785 .001845 .00111 188 47
132 9vqb56yc6w899 .012859 .001827 .000777 7 13
123 86v8m3jqnpxmu .012963 .00152 .001222 12 14
135 b5576mqw15vum .023645 .001499 .001222 12 14
83 0n1919jrgz485 .036324 .001243 .000666 226 17
150 dnwpm0gdccrph .000486 .000846 .000444 0 1
149 ddnxm4tfc08nm .000813 .000633 .000333 10 8
90 1rbz9nrbs8wyg .031264 .000401 .000222 56 21
122 7yjzq240hcd2c .006697 .000209 .000222 6 703
134 aca4xvmz0rzup .00341 .000199 0 1 1
115 6wm3n4d7bnddg .019572 .00019 .000111 16 27
158 gdfb17u17m2k2 .016272 .000187 .000222 56 19
138 bq584rmaa85ua .00917 .000156 .000222 62 19
102 43wq1yur8h8u2 .000796 .000132 0 4 3
101 40mgxk6ty822a .001024 .000115 .000111 11 5
109 5km6gkvuh3n43 .000391 .000103 .000111 2 1
140 bwsz40d3hc8q7 .000498 .000103 0 0 0
128 971vpk0tp7ahm .000176 .000093 0 2 3
111 5n1fs4m2n2y0r .000199 .000089 .000111 3 2
84 12hhcr6ucw6mb .004583 .000076 .000111 8 7
118 7ntngwmf9bf4p .01232 .00007 .000111 16 12
129 9n8xc314xdm0t .000665 .000061 0 0 4
99 3ktacv9r56b51 .000519 .000052 0 7 10
148 dcstr36r0vz0d .00067 .000046 0 3 4
117 7ng34ruy5awxq .00687 .000044 0 7 5
142 c6awqs517jpj0 .000071 .000042 .000111 2 3
112 69k5bhm12sz98 .000619 .000039 0 6 3
126 8wutkrpf8j81q .000162 .000039 .000111 2 2
108 5j4c2v06qdhqq .000549 .000036 0 2 2
88 1gu8t96d0bdmu .000046 .000035 0 4 2
105 53saa2zkr6wc3 .000243 .000032 0 3 3
95 32hbap2vtmf53 .000445 .000031 0 4 3
124 87gaftwrm2h68 .000416 .000029 0 3 3
130 9q5jas50b88ud .006783 .000026 0 3 3
103 46zsasxjf8hb9 .000157 .000025 .000111 0 0
131 9tgj4g8y4rwy8 .000233 .000025 0 3 2
151 dzan2tgvtkzdw .009243 .000025 0 3 2
93 2xgubd6ayhyb1 .000061 .000024 0 2 2
125 8swypbbr0m372 .000319 .000024 0 2 3
154 fa0uzvspp236d .006965 .000024 0 1 27
159 gj6uc50hpnr1p .000297 .000023 0 0 1
156 ga9j9xk5cy9s0 .000461 .000021 0 2 3
144 cqqwzhmfk5acd .000064 .00002 0 1 0
145 cvn54b7yz0s8u .000282 .00002 0 2 3
98 39m4sx9k63ba2 .000262 .000019 0 2 3
136 bgjhtnqhr5u9h .000269 .000019 0 2 3
155 g3wrkmxkxzhf2 .000679 .000019 0 3 2
94 317v5hnvvd49h .013503 .000018 0 0 74
110 5ms1dhxbadq64 .001107 .000018 0 1 1
161 grwydz59pu6mc .000122 .000013 0 0 1
153 f0wj261bm8snd .000329 .000012 0 1 1
-------------------------------------------------------------------------------
Statements with Errors
-------------------------------------------------------------------------------
object ID SQL ID Error
---------- ------------- ------------------------------------------------------
92 2tv1pqg3v08aa ORA-00932: inconsistent datatypes: expected UDT got...
106 572fbaj0fdw2b ORA-20020: Database/Instance / does not exist in DB...
120 7u3jnmmaw8gg1 ORA-00932: inconsistent datatypes: expected UDT got...
143 cj86nch2p483y ORA-00932: inconsistent datatypes: expected UDT got...
160 gman4yn14jyfz ORA-00932: inconsistent datatypes: expected UDT got...
-------------------------------------..." - rest of line ignored.-------------
SQL>
SQL> exec dbms_sqlpa.execute_analysis_task(task_name=>'my_spa_task',execution_type=>'compare performance', execution_name=>'compare');
PL/SQL procedure successfully completed.
SQL> var rep clob;
SQL> exec :rep:=dbms_sqlpa.report_analysis_task('my_spa_task','text','typical','summary');
PL/SQL procedure successfully completed.
SQL> print :rep
REP
----------------------------------------------------------------------------------------------------------------------------------
General Information
---------------------------------------------------------------------------------------------
Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : my_spa_task SQL Tuning Set Name : my_sts
Task Owner : SYS SQL Tuning Set Owner : SYS
Description : Total SQL Statement Count : 79
Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : compare Started : 03/14/2013 15:00:59
Execution Type : COMPARE PERFORMANCE Last Updated : 03/14/2013 15:01:00
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 5
Number of Unsupported SQL : 1
Analysis Information:
---------------------------------------------------------------------------------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ---------------------------------------------
Execution Name : my_exec_before_change Execution Name : my_exec_after_change
Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLETED
Started : 03/14/2013 14:54:39 Started : 03/14/2013 14:58:00
Last Updated : 03/14/2013 14:54:47 Last Updated : 03/14/2013 14:58:08
Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED
Number of Errors : 5 Number of Errors : 5
---------------------------------------------
Comparison Metric: ELAPSED_TIME
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------
Report Summary
---------------------------------------------------------------------------------------------
Projected Workload Change Impact:
-------------------------------------------
Overall Impact : -1.49%
Improvement Impact : 0%
Regression Impact : -1.49%
SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall 79 1
Regressed 1 0
Unchanged 72 1
with Errors 5 0
Unsupported 1 0
Top 73 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
| | | Impact on | Execution | Metric | Metric | Impact | Plan |
| object_id | sql_id | Workload | Frequency | Before | After | on SQL | Change |
-------------------------------------------------------------------------------------------
| 166 | 1cq3qr774cu45 | -1.49% | 1 | 25755 | 36542 | -41.88% | n |
| 226 | dayq182sk41ks | -.71% | 1 | 138657 | 143791 | -3.7% | n |
| 204 | 8swypbbr0m372 | .65% | 72 | 89 | 24 | 73.03% | n |
| 176 | 350myuyx0t1d6 | .58% | 1 | 12668 | 8463 | 33.19% | n |
| 216 | bm2pwrpcr8ru6 | .41% | 1 | 146357 | 143363 | 2.05% | n |
| 165 | 1bx8mgs8by25x | -.36% | 1 | 154450 | 157076 | -1.7% | n |
| 178 | 3ktacv9r56b51 | .31% | 72 | 83 | 52 | 37.35% | n |
| 186 | 5dfmd823r8dsp | -.26% | 1 | 28546 | 30445 | -6.65% | n |
| 218 | bunssq950snhf | -.21% | 1 | 140601 | 142137 | -1.09% | n |
| 212 | ab9rc63zxa4bj | -.21% | 1 | 1613 | 3121 | -93.49% | n |
| 240 | grwydz59pu6mc | -.18% | 639 | 11 | 13 | -18.18% | n |
| 211 | 9vqb56yc6w899 | -.17% | 1 | 620 | 1827 | -194.68% | n |
| 198 | 7sxujr2u5ddy4 | -.1% | 1 | 3416 | 4132 | -20.96% | n |
| 174 | 32hbap2vtmf53 | -.09% | 66 | 21 | 31 | -47.62% | n |
| 170 | 1sd9s6z4gs4c5 | -.08% | 1 | 2995 | 3598 | -20.13% | y |
| 231 | f0s0bk5k713yb | -.06% | 1 | 4960 | 5393 | -8.73% | n |
| 184 | 53saa2zkr6wc3 | .06% | 36 | 44 | 32 | 27.27% | n |
| 190 | 5n1fs4m2n2y0r | -.05% | 6 | 23 | 89 | -286.96% | n |
| 229 | dnwpm0gdccrph | -.05% | 1 | 514 | 846 | -64.59% | n |
| 188 | 5km6gkvuh3n43 | -.04% | 4 | 23 | 103 | -347.83% | n |
| 207 | 971vpk0tp7ahm | -.04% | 4 | 18 | 93 | -416.67% | n |
| 213 | aca4xvmz0rzup | -.04% | 2 | 54 | 199 | -268.52% | n |
| 225 | d0hs62mdytbk1 | -.04% | 1 | 2229 | 2498 | -12.07% | n |
| 200 | 7xshr7maxf6rc | .03% | 1 | 2665 | 2443 | 8.33% | n |
| 192 | 6d1jnnuhkt8wy | .03% | 1 | 2240 | 2051 | 8.44% | n |
| 179 | 4002tnht9dwhw | -.02% | 1 | 2031 | 2199 | -8.27% | n |
| 214 | b5576mqw15vum | -.02% | 1 | 1346 | 1499 | -11.37% | n |
| 193 | 6m6b8kbv1dt4c | -.02% | 1 | 2163 | 2303 | -6.47% | n |
| 220 | by4un3fmx698z | .02% | 1 | 2571 | 2443 | 4.98% | n |
| 196 | 7ng34ruy5awxq | .02% | 6 | 64 | 44 | 31.25% | n |
| 201 | 7yjzq240hcd2c | -.02% | 1 | 92 | 209 | -127.17% | n |
| 169 | 1rbz9nrbs8wyg | -.02% | 1 | 287 | 401 | -39.72% | n |
| 162 | 0n1919jrgz485 | .01% | 1 | 1350 | 1243 | 7.93% | n |
| 221 | c6awqs517jpj0 | -.01% | 4 | 19 | 42 | -121.05% | n |
| 195 | 6xpsr8v27pmy2 | .01% | 1 | 2873 | 2782 | 3.17% | n |
| 181 | 43wq1yur8h8u2 | -.01% | 1 | 50 | 132 | -164% | n |
| 206 | 95jvkhg3t7rqj | .01% | 1 | 2465 | 2390 | 3.04% | n |
| 228 | ddnxm4tfc08nm | -.01% | 1 | 559 | 633 | -13.24% | n |
| 227 | dcstr36r0vz0d | .01% | 4 | 64 | 46 | 28.13% | n |
| 194 | 6wm3n4d7bnddg | -.01% | 1 | 121 | 190 | -57.02% | n |
| 219 | bwsz40d3hc8q7 | .01% | 1 | 172 | 103 | 40.12% | n |
| 168 | 1nay5fd0d17km | -.01% | 1 | 2146 | 2213 | -3.12% | n |
| 234 | g3wrkmxkxzhf2 | .01% | 67 | 20 | 19 | 5% | n |
| 175 | 32mk33ry1g665 | -.01% | 1 | 3321 | 3385 | -1.93% | n |
| 205 | 8wutkrpf8j81q | -.01% | 4 | 23 | 39 | -69.57% | n |
| 237 | gdfb17u17m2k2 | -.01% | 1 | 130 | 187 | -43.85% | n |
| 203 | 87gaftwrm2h68 | -.01% | 8 | 22 | 29 | -31.82% | n |
| 230 | dzan2tgvtkzdw | .01% | 1 | 81 | 25 | 69.14% | n |
| 167 | 1gu8t96d0bdmu | -.01% | 6 | 26 | 35 | -34.62% | n |
| 189 | 5ms1dhxbadq64 | .01% | 1 | 72 | 18 | 75% | n |
| 187 | 5j4c2v06qdhqq | .01% | 4 | 49 | 36 | 26.53% | n |
| 236 | gcpj8av4kx6j2 | .01% | 1 | 2322 | 2274 | 2.07% | n |
| 180 | 40mgxk6ty822a | .01% | 1 | 156 | 115 | 26.28% | n |
| 217 | bq584rmaa85ua | 0% | 1 | 121 | 156 | -28.93% | n |
| 209 | 9q5jas50b88ud | 0% | 1 | 50 | 26 | 48% | n |
| 197 | 7ntngwmf9bf4p | 0% | 1 | 93 | 70 | 24.73% | n |
| 173 | 317v5hnvvd49h | 0% | 1 | 39 | 18 | 53.85% | n |
| 191 | 69k5bhm12sz98 | 0% | 2 | 48 | 39 | 18.75% | n |
| 215 | bgjhtnqhr5u9h | 0% | 4 | 22 | 19 | 13.64% | n |
| 183 | 4ku91umkgpmmr | 0% | 1 | 1856 | 1845 | .59% | n |
| 235 | ga9j9xk5cy9s0 | 0% | 4 | 19 | 21 | -10.53% | n |
| 208 | 9n8xc314xdm0t | 0% | 1 | 67 | 61 | 8.96% | n |
| 210 | 9tgj4g8y4rwy8 | 0% | 6 | 24 | 25 | -4.17% | n |
| 232 | f0wj261bm8snd | 0% | 2 | 15 | 12 | 20% | n |
| 202 | 86v8m3jqnpxmu | 0% | 1 | 1515 | 1520 | -.33% | n |
| 163 | 12hhcr6ucw6mb | 0% | 1 | 72 | 76 | -5.56% | n |
| 172 | 2xgubd6ayhyb1 | 0% | 4 | 23 | 24 | -4.35% | n |
| 177 | 39m4sx9k63ba2 | 0% | 4 | 18 | 19 | -5.56% | n |
| 224 | cvn54b7yz0s8u | 0% | 4 | 19 | 20 | -5.26% | n |
| 182 | 46zsasxjf8hb9 | 0% | 1 | 22 | 25 | -13.64% | n |
| 223 | cqqwzhmfk5acd | 0% | 1 | 18 | 20 | -11.11% | n |
| 238 | gj6uc50hpnr1p | 0% | 1 | 24 | 23 | 4.17% | n |
| 233 | fa0uzvspp236d | 0% | 1 | 24 | 24 | 0% | n |
-------------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------
- See more at: http://www.rickyzhu.com/2429_spa%E4%BD%BF%E7%94%A8%E8%AF%A6%E8%A7%A3.html#sthash.8zGCbImp.dpuf
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28211342/viewspace-2071953/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
Oracle RAT介绍及最佳实践
请登录后发表评论
登录
全部评论
