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: %E4%BD%BF%E7%94%A8%E8%AF%A6%E8%A7%A3.html#sthash.8zGCbImp.dpuf
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2071953/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Profile 使用詳解Oracle
- oracle rat spaOracle
- Oracle Profile 使用詳解(轉)Oracle
- SPA路由機制詳解（看不懂不要錢~~）路由
- oracle oradebug使用詳解Oracle
- Oracle中job的使用詳解Oracle
- Oracle使用者profile詳解Oracle
- oracle外部表詳解以及使用Oracle
- Oracle_oswbba工具使用詳解Oracle
- Oracle RAC LoadBalance 使用詳解Oracle
- oracle SPA 效能分析案例Oracle
- Oracle ErrorStack 使用和閱讀詳解OracleError
- 詳解oracle使用者建立(中)Oracle
- ORACLE中RECORD、VARRAY、TABLE的使用詳解Oracle
- Oracle中組合索引的使用詳解Oracle索引
- 瞭解前端中的SPA前端
- 詳解oracle使用者建立(create user)(下)Oracle
- 詳解oracle使用者建立(create user)(上)Oracle
- oracle之 Oracle LOB 詳解Oracle
- oracle dump詳解Oracle
- oracle INVENTORY 詳解Oracle
- oracle recyclebin詳解Oracle
- ORACLE -詳解SCNOracle
- Oracle SCN詳解Oracle
- Oracle checkpoint詳解Oracle
- Oracle Hints詳解Oracle
- oracle 序列 詳解Oracle
- oracle statspack詳解Oracle
- Oracle ASM 詳解OracleASM
- oracle 序列詳解Oracle
- oracle statspack 詳解Oracle
- Oracle bootstrap$ 詳解Oracleboot
- Oracle PGA詳解Oracle
- oracle Dataguard 詳解Oracle
- Oracle Hint 詳解Oracle
- 詳解Oracle使用者許可權檢視的使用Oracle
- Oracle資料庫AWR的使用例項詳解Oracle資料庫
- oracle使用者狀態（account_status）詳解 .Oracle