SQL Tuning Set Oracle Database

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.

2 thoughts on “SQL Tuning Set Oracle Database

  1. Pingback: SQL Tuning Advisor Oracle Database - Administrando Sistemas

  2. Pingback: SQL Access Advisor Oracle Database - Administrando Sistemas

Deja un comentario