SQL Access Advisor Oracle Database

Una vez más cuando tenemos problemas de rendimiento cualquier ayuda es poca, Oracle Database ofrece el SQL Access Advisor que nos ayudará ofreciendo recomendaciones para mejora el rendimiento del entorno, indices nuevos, planes de ejecución, etc..  de un SQL Tuning Set (entre otras posibles entradas)

En esta entrada vamos a ver como utilizar el SQL Access Advisor (SAA), que ofrecerá recomendaciones del tipo:

  • Particionado de tablas
  • Creación de índices
  • Creación de vistas materializadas
  • Generación de estadisticas.

Que podemos aplicar para mejorar el rendimiento de la BD.

La documentación oficial está aqui.

Partimos de un SQL Tuning Set (STS) creado en la entrada SQL Tuning Set Oracle Database.

  1. Lo primero que hacemos es crear una access task
    Source   
    SET SERVEROUTPUT ON
    DECLARE
    task_id NUMBER;
    task_name VARCHAR2(30);
    BEGIN
    task_name := 'SH_SAA';
    DBMS_ADVISOR.CREATE_TASK(advisor_name=>DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id => task_id, task_name => task_name);
    DBMS_OUTPUT.PUT_LINE(task_id);
    END;
    /
    El task_id=192 después de la ejecución
  2. Configuramos la task
    Source   
    EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(task_name =>'SH_SAA', parameter=>'TIME_LIMIT', value=>30);
    EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(task_name =>'SH_SAA', parameter=>'ANALYSIS_SCOPE', value=>'ALL');
  3. Añadimos un SQL Tuning Set “sh_workload” previo a la task, en la “entrada” se explica como crear un SQL Tuning Set
    Source   
    EXECUTE DBMS_ADVISOR.ADD_STS_REF(task_name=>'SH_SAA', sts_owner=>'SYS', workload_name=>'sh_workload');
  4. Ejecutamos la task
    Source   
    exec DBMS_ADVISOR.EXECUTE_TASK (task_name=> 'SH_SAA');
    Podemos ver el estado de la ejecución en:
    Source   
    COL TASK_ID FORMAT 999
    COL TASK_NAME FORMAT a25
    COL STATUS_MESSAGE FORMAT a25
    SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGEFROM   USER_ADVISOR_LOG WHERE TASK_NAME='SH_SAA';TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
    ------- ------------------------- ----------- -------------------------
    192 SH_SAA                    COMPLETED   Access advisor execution completed
  5. Obtención de las recomendaciones
    Hay diferentes vistas que no muestran información:DBA_ADVISOR_TASKS, para ver información general de las tasks (documentación):
    Source   
    SET pagesize 1000
    SET linesize 200
    SELECT TASK_ID,TASK_NAME,STATUS,PCT_COMPLETION_TIME,STATUS_MESSAGE,RECOMMENDATION_COUNT FROM DBA_ADVISOR_TASKS WHERE ADVISOR_NAME = 'SQL Access Advisor' AND TASK_NAME='SH_SAA';TASK_ID TASK_NAME                 STATUS      PCT_COMPLETION_TIME STATUS_MESSAGE            RECOMMENDATION_COUNT
    ------- ------------------------- ----------- ------------------- ------------------------- --------------------
    192 SH_SAA                    COMPLETED                   100 Access advisor execution                     2
    completed
    DBA_ADVISOR_RECOMMENDATIONS, recomendaciones ofrecidas por las tareas (documentación):
    Source   
    SET linesize 200
    SELECT REC_ID,RANK,TYPE,BENEFIT FROM DBA_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME='SH_SAA' ORDER BY RANK;
    REC_ID       RANK TYPE                              BENEFIT
    ---------- ---------- ------------------------------ ----------
    1          1 ACTIONS                           1639905
    2          2 ACTIONS                             33770
    El campo BENEFIT mostrado en la consulta anterior se refiere a coste del optimizador.
    Podemos ver el impacto en las consultas incluidas en el SQL Tuning Set con:
    Source   
    SELECT SQL_ID, REC_ID, PRECOST, POSTCOST,
    (PRECOST-POSTCOST)*100/PRECOST AS PERCENT_BENEFIT
    FROM   USER_ADVISOR_SQLA_WK_STMTS
    WHERE  TASK_NAME = 'SH_SAA'
    AND    WORKLOAD_NAME = 'sh_workload'
    ORDER BY percent_benefit DESC;
    SQL_ID            REC_ID    PRECOST   POSTCOST PERCENT_BENEFIT
    ------------- ---------- ---------- ---------- ---------------
    03ghr9b5m3x43          2      48490      14720      69.6432254
    0bg8rg9srv8rm          1     271544     221984      18.2511858
    1p5p7drt7atxd          1     247299     202164      18.2511858
    1qr4ps69f36jy          1     271544     221984      18.2511858
    1xmq1jkcnp2mf          1     252148     206128      18.2511858
    1zm0sn3qcx0hu          1     218205     178380      18.2511858
    222q8cuf6ugt9          1     227903     186308      18.2511858
    28da8t4nc8uk9          1     300638     245768      18.2511858
    370m5yqjjcr8z          1     290940     237840      18.2511858
    3fgwfcsa2k3bz          1     261846     214056      18.2511858
    3zdr12jv606xs          1     256997     210092      18.2511858
    416hz7t3mx918          1     203658     166488      18.2511858
    4kwqmbh8cs1gp          1     237601     194236      18.2511858
    4wazxyxv5cpt0          1     227903     186308      18.2511858
    56nj9ptzakm56          1     213356     174416      18.2511858
    5hppq6bp5wmnm          1     295789     241804      18.2511858
    5vbwrcz3vw3s0          1     295789     241804      18.2511858
    6n5rbcc9c9tvs          1     247299     202164      18.2511858
    709uggnu40a9a          1     223054     182344      18.2511858
    7f6kr3z5p8qxa          1      38792      31712      18.2511858
    83wufms0kwjb0          1      38792      31712      18.2511858
    8g3y654ptjzrz          1     281242     229912      18.2511858
    8hytq9ybsh5xu          1      29094      23784      18.2511858
    8k0bufga1dutt          1      43641      35676      18.2511858
    8wskn4vvvkd93          1     232752     190272      18.2511858
    954q4b1vkp8m4          1     276393     225948      18.2511858
    9aft7htmzpd1d          1     324883     265588      18.2511858
    9b723qjgkmy9z          1     237601     194236      18.2511858
    9fsjrrksxp4gj          1     232752     190272      18.2511858
    9h2u2z9y900yk          1      48490      39640      18.2511858
    9tkfxkjugywqp          1      53339      43604      18.2511858
    9w0c53r5um1xy          1     252148     206128      18.2511858
    a75y9uq84au4p          1     184262     150632      18.2511858
    a9nt3xdwup082          1     227903     186308      18.2511858
    aktx221b6sqc4          1     271544     221984      18.2511858
    au7x78c2uju0p          1     300638     245768      18.2511858
    c6axjhw2bv23t          1     203658     166488      18.2511858
    cmkygw8kypnpb          1     232752     190272      18.2511858
    dfdg44tjr89uf          1     232752     190272      18.2511858
    fd5j4mmztdpmm          1      38792      31712      18.2511858
    fwsw6473mw961          1     227903     186308      18.2511858
    fx2c47hcyfz9f          1      38792      31712      18.2511858
    gppx8hpcwsb5g          1      33943      27748      18.2511858
    gpxukn3ft9qgw          1      53339      43604      18.2511858
    gx7ymx5myrd2t          1     261846     214056      18.2511858
    08kysb4nyt58n          1      43641      35676      18.2511858
    46 rows selected.

    DBA_ADVISOR_ACTIONS, cada recomendacion incluye una o más acciones (documentación).

    Source   
    SET linesize 200
    COL COMMAND FORMAT a30
    SELECT REC_ID,ACTION_ID, COMMAND FROM DBA_ADVISOR_ACTIONS WHERE TASK_NAME='SH_SAA' ORDER BY REC_ID,ACTION_ID;
    REC_ID  ACTION_ID COMMAND
    ---------- ---------- ------------------------------
    1          1 PARTITION TABLE
    1          2 PARTITION TABLE
    1          3 PARTITION TABLE
    1          4 CREATE MATERIALIZED VIEW LOG
    1          6 CREATE MATERIALIZED VIEW LOG
    1          8 CREATE MATERIALIZED VIEW LOG
    1         10 CREATE MATERIALIZED VIEW
    1         11 GATHER TABLE STATISTICS
    2          1 PARTITION TABLE
    2          2 PARTITION TABLE
    2          3 PARTITION TABLE
    2          4 CREATE MATERIALIZED VIEW LOG
    2          6 CREATE MATERIALIZED VIEW LOG
    2          8 CREATE MATERIALIZED VIEW LOG
    2         10 CREATE MATERIALIZED VIEW
    2         11 GATHER TABLE STATISTICS
    2         12 CREATE INDEX
    17 rows selected.

    Creación de un PL/SQL, esta es la mejor forma de obtener información sobre las recomentaciones ya que facilita la lectura.

    Source   
    CREATE OR REPLACE PROCEDURE show_recm (in_task_name IN VARCHAR2) IS
    CURSOR curs IS
    SELECT DISTINCT action_id, command, attr1, attr2, attr3, attr4
    FROM user_advisor_actions
    WHERE task_name = in_task_name
    ORDER BY action_id;
    v_action        number;
    v_command     VARCHAR2(32);
    v_attr1       VARCHAR2(4000);
    v_attr2       VARCHAR2(4000);
    v_attr3       VARCHAR2(4000);
    v_attr4       VARCHAR2(4000);
    v_attr5       VARCHAR2(4000);
    BEGIN
    OPEN curs;
    DBMS_OUTPUT.PUT_LINE('=========================================');
    DBMS_OUTPUT.PUT_LINE('Task_name = ' || in_task_name);
    LOOP
    FETCH curs INTO
    v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
    EXIT when curs%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Action ID: ' || v_action);
    DBMS_OUTPUT.PUT_LINE('Command : ' || v_command);
    DBMS_OUTPUT.PUT_LINE('Attr1 (name)      : ' || SUBSTR(v_attr1,1,30));
    DBMS_OUTPUT.PUT_LINE('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
    DBMS_OUTPUT.PUT_LINE('Attr3             : ' || SUBSTR(v_attr3,1,30));
    DBMS_OUTPUT.PUT_LINE('Attr4             : ' || v_attr4);
    DBMS_OUTPUT.PUT_LINE('Attr5             : ' || v_attr5);
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');
    END LOOP;
    CLOSE curs;
    DBMS_OUTPUT.PUT_LINE('=========END RECOMMENDATIONS============');
    END show_recm;
    /

    Llamamos al PL/SQL para ver las recomendaciones:

    Source   
    SET SERVEROUTPUT ON SIZE 99999
    exec show_recm('SH_SAA');
    =========================================
    Task_name = SH_SAA
    Action ID: 1
    Command : PARTITION TABLE
    Attr1 (name)      : "SH"."SALES"
    Attr2 (tablespace):
    Attr3             : ("TIME_ID")
    Attr4             : HASH
    Attr5             :
    ----------------------------------------
    Action ID: 2
    Command : PARTITION TABLE
    Attr1 (name)      : "SH"."CUSTOMERS"
    Attr2 (tablespace):
    Attr3             : ("CUST_ID")
    Attr4             : INTERVAL
    Attr5             :
    ----------------------------------------
    Action ID: 3
    Command : PARTITION TABLE
    Attr1 (name)      : "SH"."TIMES"
    Attr2 (tablespace):
    Attr3             : ("TIME_ID")
    Attr4             : HASH
    Attr5             :
    ----------------------------------------
    Action ID: 4
    Command : CREATE MATERIALIZED VIEW LOG
    Attr1 (name)      : "SH"."TIMES"
    Attr2 (tablespace):
    Attr3             : ROWID, SEQUENCE
    Attr4             :  INCLUDING NEW VALUES
    Attr5             :
    ----------------------------------------
    Action ID: 6
    Command : CREATE MATERIALIZED VIEW LOG
    Attr1 (name)      : "SH"."SALES"
    Attr2 (tablespace):
    Attr3             : ROWID, SEQUENCE
    Attr4             :  INCLUDING NEW VALUES
    Attr5             :
    ----------------------------------------
    Action ID: 8
    Command : CREATE MATERIALIZED VIEW LOG
    Attr1 (name)      : "SH"."CUSTOMERS"
    Attr2 (tablespace):
    Attr3             : ROWID, SEQUENCE
    Attr4             :  INCLUDING NEW VALUES
    Attr5             :
    ----------------------------------------
    Action ID: 10
    Command : CREATE MATERIALIZED VIEW
    Attr1 (name)      : "SYS"."MV$$_00C00000"
    Attr2 (tablespace):
    Attr3             : REFRESH FAST WITH ROWID
    Attr4             : ENABLE QUERY REWRITE
    Attr5             :
    ----------------------------------------
    Action ID: 11
    Command : GATHER TABLE STATISTICS
    Attr1 (name)      : "SYS"."MV$$_00C00000"
    Attr2 (tablespace):
    Attr3             : -1
    Attr4             :
    Attr5             :
    ----------------------------------------
    Action ID: 12
    Command : CREATE INDEX
    Attr1 (name)      : "SYS"."MV$$_00C00000_IDX$$_00C
    Attr2 (tablespace):
    Attr3             : "SYS"."MV$$_00C00000"
    Attr4             : BITMAP
    Attr5             :
    ----------------------------------------
    =========END RECOMMENDATIONS============
    PL/SQL procedure successfully completed.

    Podemos crear un script que implemente las recomentaciones con:

    Source   
    CREATE DIRECTORY ADVISOR_RESULTS AS '/tmp';
    GRANT READ ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;
    GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;
    EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('SH_SAA'),'ADVISOR_RESULTS', 'sql_access_advisor_script.sql');

    Os dejo el resultado del sql_access_advisor_script.sql.

Suerte…

Deja un comentario