Oracle Live

25/08/2016

RAT – SQL Performance Analyzer

Filed under: Database — mogukiller @ 12:14 pm
Tags: ,

SQL Performance Analyzer es parte de la funcionabilidad de RAT, que junto con Database Replay permite minimizar los problemas de perdida de performance ante cualquier cambio en el entorno. Oracle permite utilizar SQL Performance Analyzer en los siguientes situaciones:

– Upgrades de la base de datos.
– Cambios en el sistema operativo.
– Cambios en esquemas.
– Cambios de parametrización.
– Cambio de estadísticas.

/*TEORIA:
	SQL Tuning Sets store SQL statements along with
		The execution context, such as the parsing schema name and bind values
		Execution statistics such as average elapsed time and execution count
		Execution plans
		Row source statistics such as the number of rows processed for each operation executed within the plan
	
	SQL Tuning Sets can be created by filtering or ranking SQL statements from several sources:
		The cursor cache using the SELECT_CURSOR_CACHE Function
		Top SQL statements from the Automatic Workload Repository using the SELECT_WORKLOAD_REPOSITORY Functions
		Other SQL Tuning Sets using the SELECT_SQLSET Function
		SQL Trace files using the SELECT_SQL_TRACE Function
		A user-defined workload
	Otros procedimientos:
		CREATE_SQLSET: 	Crea el task para el STS
		LOAD_SQLSET:	Carga el STS con las queries utilizadas
		
	Posibles filtros:
	nota: posibles valores para 
	sql_id						Unique SQL ID
	forcing_matching_signature	Signature with literals, case, and whitespace removed
	sql_text					Full text for the statement
	parsing_schema_name			Schema where the SQL is parsed
	module						Last application module for the SQL
	action						Last application action for the SQL
	elapsed_time				Sum total elapsed time for this SQL statement
	cpu_time					Sum total CPU time for this SQL statement
	buffer_gets					Sum total number of buffer gets
	disk_reads					Sum total number of disk reads
	direct_writes				Sum total number of direct writes
	rows_processed				Sum total number of rows processed by this SQL
	fetches						Sum total number of fetches
	executions					Total executions of this SQL
	end_of_fetch_count			Number of times the statement was fully executed with all of its rows fetched
	optimizer_cost				Optimizer cost for this SQL
	command_type				Statement type, such as INSERT or SELECT.
	first_load_time				Load time of parent cursor
	stat_period					Period of time (seconds) when the statistics of this SQL statement were collected
	active_stat_period			Effective period of time (in seconds) during which the SQL statement was active		
*/

--======================================
1.- Capturamos el workload
--======================================

------------------------------------------
1.1 - Creamos el STS que va a contener nuestro workload 
------------------------------------------

exec dbms_sqltune.create_sqlset(sqlset_name => 'test_tpcc', description => 'Prueba STS');

------------------------------------------
1.2 - Capturamos la carga desde un AWR
------------------------------------------
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR SELECT VALUE(p) FROM TABLE (
	DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
					998, --begin_snap
					999, --end_snap
					NULL, --basic_filter (The SQL predicate to filter the SQL from the workload repository defined on attributes of the SQLSET_ROW)
					NULL, --object_filter (The objects to selected from the SWRF)
					NULL, --ranking_measure1(An order by clause on the selected SQL)
					NULL, --ranking_measure2
					NULL, --ranking_measure3
					NULL, --result percentage ((A filter which picks the top N% as per ranking measure given.it applies only if one ranking measure is given)
					10,   --result_limit (The top L(imit) SQL from the (filtered) source ranked by the ranking measure)
					NULL -- attribute_list (List of SQL statement attributes to return in the result. The possible values are:Basic,Typical,ALL)
	) p;

DBMS_SQLTUNE.load_sqlset( sqlset_name => 'SPM_TEST_1', populate_cursor=>l_cursor);

END;
------------------------------------------
1.3 Capturamos la carga desde la shared cursor
------------------------------------------
-- Ejemplo 1
declare
mycur dbms_sqltune.sqlset_cursor;
begin
open mycur for
select value (P) from table (dbms_sqltune.select_cursor_cache( 'parsing_schema_name <> ''SYS'' AND elapsed_time >2500000',null,null,null,null,1,null,'ALL')) P;
dbms_sqltune.load_sqlset(sqlset_name => 'test_set',populate_cursor => mycur);
end;
/
-- Ejemplo 2
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
     SELECT VALUE(a)     FROM   TABLE(
              DBMS_SQLTUNE.select_cursor_cache(
                basic_filter   => 'parsing_schema_name = ''TPCC''',
                attribute_list => 'ALL')
            ) a;                                             
 
  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'test_tpcc', populate_cursor => l_cursor);
END;
/

SELECT sql_text FROM   dba_sqlset_statements WHERE  sqlset_name = 'test_tpcc';

------------------------------------------
1.4 Capturamos durante un intervalo de tiempo
------------------------------------------
-- nota: Captura las sql durante 10 horas y cada 5 minutos.

BEGIN dbms_sqltune.capture_cursor_cache_sqlset(
sqlset_name =>'10GSTS',
time_limit => '3600',
repeat_interval=>'300',
sqlset_owner =>'SYS');
END;

declare
mycur dbms_sqltune.sqlset_cursor;
begin
open mycur for
select value (P) from table (dbms_sqltune.select_cursor_cache( 'parsing_schema_name <> ''SYS'' AND elapsed_time >2500000',null,null,null,null,1,null,'ALL')) P;
dbms_sqltune.load_sqlset(sqlset_name => 'test_set',populate_cursor => mycur);
end;
/

------------------------------------------
1.5 Capturamos un determinado sql_id
------------------------------------------

DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_cursor_cache('sql_id = ''fgtq4z4vb0xx5''')) p;
dbms_sqltune.load_sqlset('mysts', cur);
close cur;
END;
/

-- combinacion de sql_text and buffer_gest

DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_cursor_cache('sql_text like ''%querystring%'' and buffer_gets > 1000')) p;
dbms_sqltune.load_sqlset('mysts', cur);
close cur;
END;
/


--========================================
2.- Exportamos los STS a otro servidor
--========================================

------------------------------------------
2.1.- Creamos la tabla donde almacenar los STS
------------------------------------------
SQL> exec DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_TAB', schema_name => 'TPCC', tablespace_name => 'DATOS');

------------------------------------------
2.2- Almacenamos los STS dentro de la tabla
------------------------------------------

BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'test_tpcc',
sqlset_owner => 'SYS',
staging_table_name => 'SQLSET_TAB',
staging_schema_owner => 'TPCC');
END;
/
------------------------------------------
2.3- Exportamos la tabla con los STS
------------------------------------------

expdp userid=tpcc/temporal dumpfile=export_table_sqlset_tab directory=DIR_EXPORT tables=SQLSET_TAB

/*SALIDA:
	. . "TPCC"."SQLSET_TAB"                         113.8 KB     112 filas exportadas
*/
------------------------------------------
2.4.- Enviamos e importamos
------------------------------------------

scp /recovery_area/dir_exports/export_table_sqlset_tab.dmp mogubedb11n2:$PWD
impdp userid=tpcc/temporal dumpfile=export_table_sqlset_tab directory=DIR_EXPORT tables=SQLSET_TAB

------------------------------------------
2.5.- Desempaquetamos los STS
------------------------------------------
SQL> EXEC DBMS_SQLTUNE.unpack_stgtab_sqlset( sqlset_name=> '%', sqlset_owner=> 'SYS', replace => TRUE, staging_table_name=> 'SQLSET_TAB', staging_schema_owner=> 'TPCC');

------------------------------------------
2.6.- Creamos el Task correspondiente
------------------------------------------
  DECLARE
   V_TASK VARCHAR2(100);
  BEGIN
	V_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK(SQLSET_NAME => 'test_tpcc', TASK_NAME => 'TASK_TPCC');
 END;
/

------------------------------------------
2.7.- Ejecutamos el STS
------------------------------------------
/* nota: el parametro execution_type puede ser =>
			text execute:			Ejecuta la parte DML de las sentencias del STS
			compare performance		Compara performance entre dos ejecuciones.
			explain plan:			Obtenemos los explain plan sin ejecutar las queries.
*/

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => 'TASK_TPCC',
    execution_type  => 'test execute',
    execution_name  => 'after_change');
END;
/

-- nota: Para ejecutar 'compare performance' seria necesario dos ejecuciones con 'test execute' antes y despues del cambio

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name        => 'TASK_TPCC',
    execution_type   => 'compare performance', 
dbms_advisor.arglist('execution_name1','before_change',
					'execution_name2','after_change',
					'comparision_metric','disk_reads'); -- compresion_metric puede recibir estos parametros: DISK_READ|OPTIMIZER_COST|BUFFER_GETS
    );
END;
/

--------------------------------------------
2.8.- Obtenemos un report de lo ejecutado
--------------------------------------------
SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET TRIMSPOOL ON
SET TRIM ON

SPOOL execute_comparison_report.htm

SELECT DBMS_SQLPA.report_analysis_task('TASK_TPCC', 'TEXT', 'ALL') FROM   dual;

SPOOL OFF

/*SALIDA 
SPOOL OFFGENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : TASK_TPCC
Tuning Task Owner                 : SYS
Tuning Task ID                    : 1827
Workload Type                     : SQL Tuning Set
Execution Count                   : 1
Current Execution                 : after_change
Execution Type                    : TEST EXECUTE
Scope                             : COMPREHENSIVE
Completion Status                 : COMPLETED
Started at                        : 02/13/2014 23:32:50
Completed at                      : 02/13/2014 23:32:52
SQL Tuning Set (STS) Name         : test_tpcc
SQL Tuning Set Owner              : SYS
SQL Tuning Set ID                 : 1
Number of Statements in the STS   : 42
Number of SQLs Analyzed           : 42
Number of SQLs in the Report      : 42
Number of SQLs with Findings      : 10

-------------------------------------------------------------------------------
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
---------- ------------- -------- -------- -------- -------- ---------
        34 7jyw5gy3d1t1b .000946  .000724  .000777        8         6
        26 4wg725nwpxb1z .000494  .000278  .000222       16         7
        28 5mz8u3b34u9gw .000958  .000257  .000222        5         2
        42 89k9fqaq5b5sy .002612  .000241  .000333       13         7
        41 88fgqncchy6wg .000352  .000169  .000222        4         3
        35 7m5h0wf6stq0q .000844  .000164  .000111        4         3
        21 13dn4hkrzfpdy .001529  .000083        0        4        26
        11 d6vwqbw6r2ffk  .00009  .000069  .000222        0         2
        43 9nba80p5fn109 .002849  .000069  .000111        3         2
        29 5prvb5fkfjvfk .001112  .000065        0        1         2
        37 8yvup05pk06ca .000657  .000052  .000111        4         3
        30 5ps73nuy5f2vj .000427   .00005        0        3         3
        39 81cz3jgb5a65f  .00035  .000047        0        1         2
         6 csv0xdm9c394t .002877  .000038        0        4         5
         7 cuuun8cacj7f2 .001277  .000038        0        3         3
        12 f9rutjmvjm8j7 .000275  .000035        0        3         3
        19 1bn3cfn6n3xv5   .0002  .000034        0        3         2
        44 95yr4xtfr50hx .000575  .000031  .000111        2         2
        18 0k3640075wf84 .002335   .00003        0        3         2
        23 2k7k32av19mqz .000635   .00003        0        3         2
        36 8m96hr974yu7a .000196   .00003        0        0         1
         5 btxfquck8wsq0  .00008  .000028        0        2         1
        32 6f80tjwtrnnsq .000327  .000028  .000111        2         2
        25 4r83hzuhh3yuv .000607  .000026  .000111        2         1
        15 g1r3tpjj3b2gh .000606  .000023        0        2         1
        17 g5u7xuchhfu62 .000277  .000022        0        1         1
        24 2xf7pasj5qz0a .001042  .000022        0        2         2
         8 c90b8j61ndux8 .000727  .000021        0        2         2
        33 69gxtaqr1vk8j  .00039  .000018  .000111        1         1
        40 82tfppq8s0dc2 .000237  .000018        0        3         3
         4 bswc46zum45tj .001186  .000017        0        1         1
        20 1dju1fycmanwn .000091  .000014        0        0         2

-------------------------------------------------------------------------------

SPOOL OFFGENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : TASK_TPCC
Tuning Task Owner                 : SYS
Tuning Task ID                    : 1827
Workload Type                     : SQL Tuning Set
Execution Count                   : 1
Current Execution                 : after_change
Execution Type                    : TEST EXECUTE
Scope                             : COMPREHENSIVE
Completion Status                 : COMPLETED
Started at                        : 02/13/2014 23:32:50
Completed at                      : 02/13/2014 23:32:52
SQL Tuning Set (STS) Name         : test_tpcc
SQL Tuning Set Owner              : SYS
SQL Tuning Set ID                 : 1
Number of Statements in the STS   : 42
Number of SQLs Analyzed           : 42
Number of SQLs in the Report      : 42
Number of SQLs with Findings      : 10

-------------------------------------------------------------------------------
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
---------- ------------- -------- -------- -------- -------- ---------
        34 7jyw5gy3d1t1b .000946  .000724  .000777        8         6
        26 4wg725nwpxb1z .000494  .000278  .000222       16         7
        28 5mz8u3b34u9gw .000958  .000257  .000222        5         2
        42 89k9fqaq5b5sy .002612  .000241  .000333       13         7
        41 88fgqncchy6wg .000352  .000169  .000222        4         3
        35 7m5h0wf6stq0q .000844  .000164  .000111        4         3
        21 13dn4hkrzfpdy .001529  .000083        0        4        26
        11 d6vwqbw6r2ffk  .00009  .000069  .000222        0         2
        43 9nba80p5fn109 .002849  .000069  .000111        3         2
        29 5prvb5fkfjvfk .001112  .000065        0        1         2
        37 8yvup05pk06ca .000657  .000052  .000111        4         3
        30 5ps73nuy5f2vj .000427   .00005        0        3         3
        39 81cz3jgb5a65f  .00035  .000047        0        1         2
         6 csv0xdm9c394t .002877  .000038        0        4         5
         7 cuuun8cacj7f2 .001277  .000038        0        3         3
        12 f9rutjmvjm8j7 .000275  .000035        0        3         3
        19 1bn3cfn6n3xv5   .0002  .000034        0        3         2
        44 95yr4xtfr50hx .000575  .000031  .000111        2         2
        18 0k3640075wf84 .002335   .00003        0        3         2
        23 2k7k32av19mqz .000635   .00003        0        3         2
        36 8m96hr974yu7a .000196   .00003        0        0         1
         5 btxfquck8wsq0  .00008  .000028        0        2         1
        32 6f80tjwtrnnsq .000327  .000028  .000111        2         2
        25 4r83hzuhh3yuv .000607  .000026  .000111        2         1
        15 g1r3tpjj3b2gh .000606  .000023        0        2         1
        17 g5u7xuchhfu62 .000277  .000022        0        1         1
        24 2xf7pasj5qz0a .001042  .000022        0        2         2
         8 c90b8j61ndux8 .000727  .000021        0        2         2
        33 69gxtaqr1vk8j  .00039  .000018  .000111        1         1
        40 82tfppq8s0dc2 .000237  .000018        0        3         3
         4 bswc46zum45tj .001186  .000017        0        1         1
        20 1dju1fycmanwn .000091  .000014        0        0         2

-------------------------------------------------------------------------------
*/


Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: