En esta entrada vamos a ver como crear un SQL Tuning Set (STS) que posteriormente podemos utilizarlo de diversas formas.

Un SQL Tuning Set (STS), es un conjunto de operaciones sobre la BD que puede ser creado desde el caché, desde snapshost, SQLID…

Una vez tenemos definido el SQL Tuning Set (STS), podemos utilizarlo con:

• SQL Tuning Advisor
  Obtiene recomendaciones para mejora el rendimiento del entorno, indices nuevos planes, etc..  de un sqlset (entre otras posibles entradas)
• SQL Performance Analyzer
  Obtiene los planes de ejecución y tiempos, etc… de un sqlset (entre otras posibles entradas), se pueden comparar diferentes tasks, habiendo realizado cambios de parametrización de la BD, estructuras, etc…
• SQL Access Advisor
  Obtiene recomendaciones de vistas materializadas o índices para mejorar el rendimiento.
• Transportarlo a otra base de datos, para utlizarlo con los 3 paquetes anteriores en un entorno diferente.

La documentación oficial es esta aquí.

Un STS se puede crear de diferentes formas, yo personalmente prefiero crearlo a partir de los snapshots AWR filtrando solo lo que nos interesa.

Si es necesario podemos crear una carga de trabajo ficticia en la BD con la entrada Generar carga de trabajo en Oracle Database

Vamos a crear un STS a partir de los snapshots de AWR.

1. Crear STS
   

   Source

   exec DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name  => 'sh_workload', description  => 'Prueba sh' );
   --podemos ver el estado del STS, como lo acabamos de crear está vacío SQLCNT=0.
   COLUMN NAME FORMAT a20
   COLUMN COUNT FORMAT 99999
   COLUMN DESCRIPTION FORMAT a30
   SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM   USER_SQLSET;
   NAME                     SQLCNT DESCRIPTION
   -------------------- ---------- ------------------------------
   sh_workload                   0 Prueba sh

2. Cargar con la actividad del usuario SH
   

   Source

   --Se carga el STS a partir de lo que hay en los snapshots 42 y 44 solo del esquema SH
   DECLARE
   cur DBMS_SQLTUNE.SQLSET_CURSOR;
   BEGIN
   OPEN cur FOR
   SELECT VALUE(P)
   FROM TABLE(
   DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(42,44,
   'parsing_schema_name = ''SH''',
   NULL, NULL,NULL,NULL,
   1,
   NULL,
   'ALL')) P;
   DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'sh_workload',
   populate_cursor => cur,
   load_option => 'MERGE',
   update_option => 'ACCUMULATE');
   END;
   /

3. Revisamos que se ha cargado
   

   Source

   --podemos ver el estado del STS, con la actidad cargada SQLCNT=69
   COLUMN NAME FORMAT a20
   COLUMN COUNT FORMAT 99999
   COLUMN DESCRIPTION FORMAT a30
   SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM   USER_SQLSET;
   NAME                     SQLCNT DESCRIPTION
   -------------------- ---------- ------------------------------
   sh_workload                  69 Prueba sh
   --podemos ver el contenido con:
   COLUMN SQL_TEXT FORMAT a30
   COLUMN SCH FORMAT a3
   COLUMN ELAPSED FORMAT 999999999
   SELECT SQL_ID, PARSING_SCHEMA_NAME, SQL_TEXT,
   ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
   FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'sh_workload' ) );

Hasta aquí la creación de un STS.

Ahora vamos a ver como transportar el STS a otro sistema:

1. [Máquina origen.domain] Exportar el STS sobre una tabla.
   

   Source

   --crear tabla
   exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ( table_name  => 'SH_STAGING_TABLE',schema_name => 'SH');
   --llenamos la tabla
   exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET (sqlset_name => 'sh_workload', staging_table_name  => 'SH_STAGING_TABLE',   staging_schema_owner => 'SH');
   --verificamos llenado
   SELECT count(*) FROM SH.SH_STAGING_TABLE;

2. [Máquina origen.domain] Dump de la tabla creada y copiado a la máquina destino
   

   Source

   #hacemos un dump de la tabla
   expdp system/sys DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_SH_STAGING_TABLE.dmp LOGFILE=expdp_SH_STAGING_TABLE.log TABLES=SH.SH_STAGING_TABLE
   #copiamos expdp_SH_STAGING_TABLE.dmp a la máquina destino.domain
   scp /u01/app/oracle/product/10.1.0/db_1/rdbms/log/expdp_SH_STAGING_TABLE.dmp destino.domain:/u01/app/oracle/admin/prueba/dpdump/

3. [Máquina destino.domain] importar dump tabla
   

   Source

   #importar tabla
   impdp system/sys DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_SH_STAGING_TABLE.dmp LOGFILE=impdp_SH_STAGING_TABLE.log TABLES=SH.SH_STAGING_TABLE

4. [Máquina destino.domain] importar el STS contenido en la tabla importada
   

   Source

   exec  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (sqlset_name => '%', REPLACE => true, staging_table_name => 'SH_STAGING_TABLE', staging_schema_owner => 'SH');
   --ver el contenido del sqlset
   COLUMN NAME FORMAT a20
   COLUMN COUNT FORMAT 99999
   COLUMN DESCRIPTION FORMAT a30
   SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM   USER_SQLSET;
   NAME                     SQLCNT DESCRIPTION
   -------------------- ---------- ------------------------------
   sh_workload                  69 Prueba sh

En posteriores entradas veremos como utilizar el STS para trabajos de tuning.