Tunning Advisor/Performance : Criando e executando Tunning tasks no SQLPLUS em background

 Este documento tem a finalidade de demostrar como criar um job para executar o Tunning advisor para um determinado sql_id em background enquanto analisa um determinado problema de performance e como mostrar o resultado da tunning task.

Se trata de uma atividade que otimiza o tempo de uma análise de performance onde temos vários pontos a cobrir como : Análise de locks, tempo de execução, troca de planos, indexes e etc. Criando a tunning task em background, enquanto a mesma executa, podemos seguir realizando as demais análises até que a task conclua e dessa forma temos uma visão mais clara e completa da performance de um determinado SQL_ID.

Identificando o SQL_ID lento:

Com a query abaixo, supondo que tenhamos uma sessão do user TESTE lenta, podemos identificar qual a máquina (Máquina da aplicação) ou até mesmo caso seja informado qual o SID da sessão lenta e pegar o SQL_ID para executar a Tunning Task. 

SQL> set lines 9999

set pages 9999

COL IDENTIFICADOR FOR a15;

COL USERNAME FOR A10;

COL MACHINE FOR A20 word_wrap trunc;

COL OUSER FOR a10;

COL STATUS FOR A8;

select sid||','||serial#||',@'||inst_id as IDENTIFICADOR, username, osuser, sql_id, machine, module, status from gv$session

where username = 'TESTE'

order by STATUS;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3  

Dessa forma abri uma nova sessão no terminal, com o user TESTE, criei a tabela TESTE.TABELA_TESTE com um index e populei a mesma com 5 mil registros:

SQL> CREATE TABLE "TESTE"."TABELA_TESTE" 

( "ID" NUMBER(5,0), 

 "NOME" VARCHAR2(2048 BYTE), 

 "ARQUIVO" CLOB, 

 "DATA" DATE DEFAULT sysdate);  2    3    4    5  

Table created.

SQL> CREATE UNIQUE INDEX TESTE.idx_teste01

  ON TESTE.TABELA_TESTE (ID) COMPUTE STATISTICS;   2  

Index created.

SQL> CREATE OR REPLACE PROCEDURE teste.popula( pinit in int, pfinal in int, parquivo in varchar2 )

AS

 BEGIN

      FOR v_Loop IN pinit..pfinal LOOP

      INSERT INTO teste.tabela_teste

        VALUES (v_Loop, NULL, parquivo, SYSDATE);

    END LOOP;

END;

/  2    3    4    5    6    7    8    9   10  

Procedure created.

SQL> EXEC teste.popula(1,5000, rpad('*','10101','*') );

PL/SQL procedure successfully completed.

SQL> SQL> select count(*) from TESTE.TABELA_TESTE;

  COUNT(*)

----------

      5000

SQL> 

Agora vamos executar uma consulta em uma sessão nesta tabela e pegar o SQL_ID em outra sessão:

Sessão 1 (Executando a query):

SQL> select * 

from TESTE.TABELA_TESTE 

where id in (select to_char(id) from TESTE.TABELA_TESTE where 1=1);

  2    3  

ID NOME     ARQUIVO     DATA

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

1     ******************************************************************************** 11-JUL-24

2  

Sessão 2 (Pegando o sql_id):

SQL> /

IDENTIFICADOR USERNAME   OSUSER   SQL_ID       MACHINE     MODULE STATUS

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

74,12549,@1 TESTE   oracle   0yqqna9k6vwmt        ol07db01.localdomain SQL*Plus ACTIVE

Com o sql_id em mãos vamos executar a Tunning task em background:

No meu diretório de scripts "/home/oracle/scripts" está o script "sta.sql".  Para invocar o script criado na pasta usaremos a sintax @sta <sql_id>. Após isso executaremos o @stalist para listar o job criado anteriormente e seu status indicando que o mesmo está em execução ou já executou:

SQL> @sta 0yqqna9k6vwmt

old   3: stmt_sqlid VARCHAR2(40) := '&1';

new   3: stmt_sqlid VARCHAR2(40) := '0yqqna9k6vwmt';

JOB STA_TASK_180_0yqqna9k6vwmt executing Task TASK_180

SQL> 

SQL> @stalist

Jobs Pendentes:

Jobs Finalizados:

JOB_NAME LOG_DATE     STATUS

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

STA_TASK_180_0YQQNA9K6VWMT 11/07/2024 12:59:05 SUCCEEDED

OBS: Como estamos simulando, a task executou em poucos segundos mas em ambientes produtivos é normal a task demorar bem mais devido a complexidade do sql, número de linhas e estatísticas.

Agora vamos mostrar oque a Tunning Task gerou como resultado com o comando @stsrpt <task_name>, a taks_name pode ser encontrada no campo JOB_NAME iniciando com "TASK_XXX", nesse caso "TASK_180":

SQL> @stsrpt TASK_180

old   1: select dbms_sqltune.report_tuning_task(task_name=> UPPER('&1'), OWNER_NAME=> UPPER(USER) ) as recomendacoes from dual

new   1: select dbms_sqltune.report_tuning_task(task_name=> UPPER('TASK_180'), OWNER_NAME=> UPPER(USER) ) as recomendacoes from dual

GENERAL INFORMATION SECTION

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

Tuning Task Name   : TASK_180

Tuning Task Owner  : SYS

Workload Type   : Single SQL Statement

Scope   : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status  : COMPLETED

Started at   : 07/11/2024 12:59:05

Completed at   : 07/11/2024 12:59:05

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

Schema Name: TESTE

SQL ID   : 0yqqna9k6vwmt

SQL Text   : select *

    from TESTE.TABELA_TESTE

    where id in (select to_char(id) from TESTE.TABELA_TESTE where

    1=1)

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

FINDINGS SECTION (1 finding)

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

1- Index Finding (see explain plans section below)

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

  The execution plan of this statement can be improved by creating one or more

  indices.

  Recommendation (estimated benefit: 85.72%)

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

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

    create index TESTE.IDX$$_00B40001 on TESTE.TABELA_TESTE(TO_NUMBER(TO_CHAR("

    ID")));

  Rationale

  ---------

    Creating the recommended indices significantly improves the execution plan

    of this statement. However, it might be preferable to run "Access Advisor"

    using a representative SQL workload as opposed to a single statement. This

    will allow to get comprehensive index recommendations which takes into

    account index maintenance overhead and additional space consumption.

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

EXPLAIN PLANS SECTION

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

1- Original

-----------

Plan hash value: 1812450745

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

| Id  | Operation     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |     |   1 |  3063 | 14   (8)| 00:00:01 |

|   1 |  NESTED LOOPS     |     |   1 |  3063 | 14   (8)| 00:00:01 |

|   2 |   NESTED LOOPS     |     |   1 |  3063 | 14   (8)| 00:00:01 |

|   3 |    SORT UNIQUE     |     |   1 | 13 | 13   (0)| 00:00:01 |

|   4 |     TABLE ACCESS FULL     | TABELA_TESTE |   1 | 13 | 13   (0)| 00:00:01 |

|*  5 |    INDEX UNIQUE SCAN     | IDX_TESTE01  |   1 |     |   0   (0)| 00:00:01 |

|   6 |   TABLE ACCESS BY INDEX ROWID| TABELA_TESTE |   1 |  3050 |   0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   5 - access("ID"=TO_NUMBER(TO_CHAR("ID")))

2- Using New Indices

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

Plan hash value: 2236255215

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

| Id  | Operation     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |       |     1 |  3063 |     2  (50)| 00:00:01 |

|   1 |  NESTED LOOPS     |       |     1 |  3063 |     2  (50)| 00:00:01 |

|   2 |   NESTED LOOPS     |       |     1 |  3063 |     2  (50)| 00:00:01 |

|   3 |    SORT UNIQUE     |       |     1 |    13 |     1 (0)| 00:00:01 |

|   4 |     INDEX FULL SCAN     | IDX$$_00B40001 |     1 |    13 |     1 (0)| 00:00:01 |

|*  5 |    INDEX UNIQUE SCAN     | IDX_TESTE01    |     1 |       |     0 (0)| 00:00:01 |

|   6 |   TABLE ACCESS BY INDEX ROWID| TABELA_TESTE   |     1 |  3050 |     0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   5 - access("ID"="TABELA_TESTE"."SYS_QSMMIX_VCOL_5001")

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

SQL> 

Aqui podemos ver que a task retornou como melhoria a criação de um novo index na tabela para corrigir a conversão do campo ID, no create da tabela o mesmo foi criado como number mas na consulta executada e analisada o mesmo foi convertido para char (texto) no subselect e foi passado no Where como char onde era esperado number, dessa forma a sugestão do otimizador foi criar um novo index convertendo para number, o campo "to_char(id)" ou então reescrever a consulta.

Concluindo: Aqui vimos como executar uma tunning task para consultas, sejam elas lentas ou passíveis de melhorias onde o Tunning advisor nos mostra desde a falta de coleta de estatísticas até erros de semântica, como códigos redundantes e também sugestão de indexes. O Tunning Advisor nos da uma visão do plano de acesso da consulta, oque ajuda na hora de identificar joins e custos antes e depois de aplicar as melhorias sugeridas, uma boa maneira de analisar queries muito grandes e complexas.

Scripts Usados:

• sta.sql

SET SERVEROUTPUT ON;

declare

stmt_task VARCHAR2(40);

stmt_sqlid VARCHAR2(40) := '&1';

vJobName VARCHAR2(100);

begin

stmt_task := dbms_sqltune.create_tuning_task(sql_id => stmt_sqlid);

vJobName := 'STA_' || stmt_task || '_' || stmt_sqlid;

dbms_scheduler.create_job

(

job_name => vJobName,

job_type => 'PLSQL_BLOCK',

job_action => 'begin dbms_sqltune.execute_tuning_task(task_name => '''|| stmt_task ||'''); end;',

start_date => systimestamp,

enabled => true,

auto_drop => true,

comments => 'Run SQL Tuning Advisor Task ' || stmt_task || ' for SQL ID ' || stmt_sqlid

);

dbms_output.put_line ('JOB ' || vJobName || ' executing Task ' || stmt_task);

end;

/

• stalist.sql

set feedback off

SET SQLFORMAT

SET PAGES 50

SET LINES 400

COL JOB_NAME FORMAT A40

COL START_TIME FORMAT A20

COL STATUS FORMAT A15

COL STATE FORMAT A15

COL SQL_ID FORMAT A20

COL COMMENTS FORMAT A70

PROMP Jobs Pendentes:

SELECT JOB_NAME,

STATE,

TO_CHAR(START_DATE,'DD/MM/YYYY HH24:MI:SS') AS START_DATE,

SUBSTR(COMMENTS,1,70) AS COMMENTS

FROM DBA_SCHEDULER_JOBS

WHERE JOB_NAME LIKE 'STA%';

PROMP

PROMP Jobs Finalizados:

SELECT JOB_NAME,

TO_CHAR(LOG_DATE,'DD/MM/YYYY HH24:MI:SS') AS LOG_DATE,

STATUS

FROM DBA_SCHEDULER_JOB_LOG

WHERE JOB_NAME LIKE 'STA%'

AND LOG_DATE >= SYSTIMESTAMP-1

ORDER BY LOG_ID DESC;

PROMP

• stsrpt.sql

set long 10000000;

set longchunksize 1000

set pagesize 10000

set lines 1000

set heading off

select dbms_sqltune.report_tuning_task(task_name=> UPPER('&1'), OWNER_NAME=> UPPER(USER) ) as recomendacoes from dual;

set heading on;

Espero ter ajudado e qualquer dúvida, deixe seu comentário.

Essa postagem foi baseada usando scripts disponibilizados no blog : Blog do Dibiei – DBA Maicon Carneiro, Oracle ACE Pro ♠