Oracle SPA使用詳解

kunlunzhiying發表於2016-03-29

這裡的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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章