SQL Access Advisor Oracle Database

Posted on by

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…

Related posts:

  1. SQL Tuning Advisor Oracle Database
  2. Error ORA-13605 al ejecutar SQL Access Advisor en Oracle 10.2.0.5
  3. SQL Tuning Set Oracle Database
  4. Buscar una cadena de texto en un esquema completo de Oracle

Deja un comentario