SQL Tuning Process

Existe dois advisors que nos ajuda a fazer o tunning de sql. O SQL Tunning advisor, que analisa como a instrução é executada, e o SQL Access Advisor, que analisa de forma mais profunda, olhando os segmentos no qual a instrução esta sendo executada. Ambos advisors são parte do Tunnig Pack, uma option, do enterprise edtion.

Quando o Oracle executa uma instrução, ele tem diversas opções de como executar. Irá utilizar o index ou fazer um full table scan? Que tipo de join fazer?

O método de executar a instrução é conhecido como execution plan, e o plano de execução que o Oracle escolher será vital para a performance. Por isso devemos ter as estatísticas atualizadas dos nosso objetos.

Para verificar as estatísticas das tabelas, pode consultar a view DBA_TABLES em que informa quando foi a última coleta de estatísticas, quantidade de linhas na tabela, quantidade de oracle blocks sendo utilizados, tamanho médio das linhas e etc. Fora a view DBA_TABLES, podemos consultar a view DBA_TAB_COLUMNS , que inclui as estatísticas referente a cada coluna da tabela, como quantidade de valores distintos, quantidade de nulls e etc.

Essas informações, e outras, dão informações vitais ao optimizer de como executar melhor a instrução SQL. Se essas informações estiverem erradas ou desatualizadas o optimizer irá escolher o plano de execução errado e a performance da execução será desastrosa.

Se durante a execução da instrução, o CBO(Cost Based Optimizer) verificar que esta retornando mais linhas do que ele achou que iria retornar, e se a diferença for suficientemente grande, então o CBO irá gerar um SQL Plan Directive, para uso futuro. SQL Plan Directive é uma instrução para o optimizer para coletar mais informações sobre os objetos em questão, usando um mecanismos conhecido como dynamic sampling. Essas diretivas são salvas no AWR e associadas com a tabela relacionada. Então qualquer query que vá nessa tabela pode se beneficiar do SQL Plan Directive.

Outro método que o Oracle utiliza para melhorar a execução das querys é o Adaptive Execution Plan. Isso ocorre no momento da execução. De maneira simples, o Oracle altera o seu plano de execução no meio da execução, caso perceba que o plano inicial baseado nas estatísticas não foi bom.

O Oracle coleta automaticamente as estatísticas automaticamente durante a janela de manutenção. Mas podemos coletar manualmente através da package DBMS_STATS. Veja um exemplo simples

DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HR', tabname => 'EMPLOYEES', estimate_percent => 100);

No exemplo acima foi coletada as estatísticas da tabela HR.EMPLOYEES e o estimate_percent de 100 informa que se deve analisar TODA a tabela. Devemos ter cuidado com isso, pois em uma tabela muito grande isso pode levar várias horas estressando o sistema. Em tabelas grandes pode-se fazer somente um sample e não a tabela inteira.

Existe muitos mais argumentos na procedure e várias outras procedures para colectar estatísticas de índices, schemas, e etc. Recomendo a leitura da documentação oficial.

O SQL Tunning Advisor analisa uma ou mais instruções SQL e recomenda coletar estatísticas atualizadas, criar um SQL profile, índices ou revisar a instrução. Ele é executado automaticamente durante a janela de manutenção do banco, escolhendo as instruções que utilizou mais recursos do banco.

Claro, podemos executar o advisor manualmente também com a package DBMS_SQLTUNE. Para isso precisamos criar uma task, executar a task e assim verificar as recomendações obtidas.

SQL> CREATE TABLE teste_sql_advisor
  2  AS
  3  SELECT * FROM dba_objects;

Table created.

SQL> VARIABLE vtask VARCHAR2(64);
SQL> EXECUTE :vtask := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'SELECT MAX(object_id) FROM teste_sql_advisor');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_SQLTUNE.execute_TUNING_TASK(:vtask);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:vtask) FROM dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(:VTASK)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_289
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 09/13/2019 13:54:17
Completed at       : 09/13/2019 13:54:21

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : gpfs6f9cgj7r2
SQL Text   : SELECT MAX(object_id) FROM teste_sql_advisor

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  A tabela "SYS"."TESTE_SQL_ADVISOR" não foi analisada.

  Recommendation
  --------------
  - Considere a coleta de estatísticas do otimizador para esta tabela
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
            'TESTE_SQL_ADVISOR', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
            AUTO');

  Rationale
  ---------
    O otimizador requer estatísticas atualizadas para a tabela ao selecionar
    um plano de execução adequado.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 319195040

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 |    13 |   360   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                   |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| TESTE_SQL_ADVISOR | 66419 |   843K|   360   (1)| 00:00:01 |
----------------------------------------------------------------------------------------

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

Perceba que no exemplo acima a recomendação foi coletar as estatísticas da tabela recém criada. Outras recomendações pode ser criar um índice, que deve ser analisado, pois ele pode ajudar somente nessa consulta, mas pode atrapalhar operações de DML por exemplo.

Além do SQL Tuning Advisor, temos o SQL Access Advisor que faz uma analise mais profunda, recomendando até mesmo views materializadas e particionamento.

O SQL Access advisor não tem uma package exclusiva como tem para o SQL Tuning Advisor. Para executar o SQL Access Advisor temos que usar a package DBMS_ADVISOR, que é como se fosse uma package genérica para todos os advisors.

Existe alguns passos recomendados para executar o advisor SQL Access, que seria:

  1. Criar o SQL Tunnig set(STS) – STS é um objeto do banco que armazena a instrução SQL junto com o contexto de execução(schema, valores de bind e etc).
  2. Carregar o SQL Tuning Set com as informações necessárias.
  3. Criar e configurar a TASK
  4. Executar a TASK
  5. Ver as recomendações
  6. Implementar as sugestões

Então, vamos criar o nosso STS agora.

SQL> VARIABLE workload_name VARCHAR2(100);
SQL> EXECUTE :workload_name := 'WORKLOAD_TESTE';

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'TESTE ACCESS');

PL/SQL procedure successfully completed.

Agora vamos popular o SQL Tuning Set com o workload. Para isso vamos criar uma tabela na qual vamos armazenar informações de 3 querys e depois popular o STS com essas informações.

CREATE TABLE user_workload
(
  username             varchar2(128),   /* User who executes statement */
  module               varchar2(64),        /* Application module name */
  action               varchar2(64),        /* Application action name */
  elapsed_time         number,               /* Elapsed time for query */
  cpu_time             number,                   /* CPU time for query */
  buffer_gets          number,        /* Buffer gets consumed by query */
  disk_reads           number,         /* Disk reads consumed by query */
  rows_processed       number,         /* # of rows processed by query */
  executions           number,            /* # of times query executed */
  optimizer_cost       number,             /* Optimizer cost for query */
  priority             number,             /* User-priority (1,2 or 3) */
  last_execution_date  date,               /* Last time query executed */
  stat_period          number,          /* Window exec time in seconds */
  sql_text             clob                           /* Full SQL Text */
);

-- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT   t.week_ending_day, p.prod_subcategory, 
          SUM(s.amount_sold) AS dollars, s.channel_id, s.promo_id
 FROM     sales s, times t, products p 
 WHERE    s.time_id = t.time_id
 AND      s.prod_id = p.prod_id 
 AND      s.prod_id > 10 
 AND      s.prod_id < 50
 GROUP BY t.week_ending_day, p.prod_subcategory, s.channel_id, s.promo_id')
/
 
-- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
 'SELECT   t.calendar_month_desc, SUM(s.amount_sold) AS dollars
  FROM     sales s , times t
  WHERE    s.time_id = t.time_id
  AND      s.time_id BETWEEN TO_DATE(''01-JAN-2000'', ''DD-MON-YYYY'')
  AND      TO_DATE(''01-JUL-2000'', ''DD-MON-YYYY'')
  GROUP BY t.calendar_month_desc')
/
 
-- order by
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
 'SELECT   c.country_id, c.cust_city, c.cust_last_name
  FROM     customers c
  WHERE    c.country_id IN (52790, 52789)
  ORDER BY c.country_id, c.cust_city, c.cust_last_name')
/
COMMIT;

DECLARE
  sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN sqlset_cur FOR
    SELECT SQLSET_ROW(null,null, SQL_TEXT, null, null, 'SH', module,
                     'Action', 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, null, 2, 3,
                     sysdate, 0, 0, null, 0, null, null)
    FROM USER_WORKLOAD;
  DBMS_SQLTUNE.LOAD_SQLSET('WORKLOAD_TESTE', sqlset_cur);
END;
/

Agora, vamos criar a task, configurar alguns parâmetros e linkar com o workload. Depois disso, podemos executar a mesma.

VARIABLE task_name VARCHAR2(50);
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name);

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'TIME_LIMIT', 30);
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'ANALYSIS_SCOPE', 'ALL');

EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, 'SH', :workload_name);

EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

E para finalizar, vamos verificar as recomendações. Para ver essas informações, podemos consultar uma série de views, as DBA_ADVISOR_*. Ou então podemos user o DBMS_ADVISOR.GET_TASK_SCRIPT, que gera um arquivo, ou seja, ele é salvo em um diretório do SO, contendo todas as recomendações e os scripts para implementar.

Uma outra opção seria executar o dbms_output.quick_tune que aceita uma única instrução SQL. Essa procedure cria a task, o workload e executa a task. Veja como serie para executar.

VARIABLE t_name VARCHAR2(255);
VARIABLE sq VARCHAR2(4000);
EXEC :sq := 'SELECT COUNT(*) FROM customers WHERE cust_state_province =''CA''';
EXECUTE :t_name := 'MY_QUICKTUNE_TASK';

EXEC DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,:t_name,:sq);

Para verificar os resultados seria do mesmo jeito.

Anúncios

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Marcado com: , , , , , , , , , , , ,
Publicado em Administração, Performance

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Esse Blog é reconhecido pela
Sou um
Certificações
Sou articulista

Clique para seguir este blog e receber notificações via email de novos posts.

Tércio Costa

Tércio Costa

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Links Pessoais

Serviços verificados

Visualizar Perfil Completo →

Total de Visualizações da Página
  • 142.605 Visualizações
%d blogueiros gostam disto: