terça-feira, 6 de dezembro de 2016

Utilizando o SQL Tuning Advisor (DBMS_SQLTUNE)

Fala pessoal...

Hoje fazendo uns acertos aqui no meu ambiente, precisei utilizar o SQL Tuning Advisor.

Ele é bem simples, mas é sempre bom lembrar que precisamos ter o "Diagnostic+Tunig" devidamente licenciado e habilitado.

Sendo assim, abaixo segue um exemplo da utilização.


-- Executar o ADDM. 

@?/rdbms/admin/addmrpt.sql

Na verdade baseado no ADDM identifiquei as necessidades iniciais tendo em vista que é um ambiente que não administro a muito tempo. Sendo assim, não é obrigatório executar esse relatório.


-- Create task
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
         begin_snap  => 34932,           -- Snap inicial
        end_snap    => 35220,           -- Snap final
        sql_id      => 'cyb0csh3g3vxa',   -- SQL Id
        scope       => DBMS_SQLTUNE.scope_comprehensive,
        time_limit  => 3600,            -- Execução em seg
        task_name   => 'cyb0csh3g3vxa_AWR2_tuning_task', 
        description => 'Task: cyb0csh3g3vxa');
   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

No exemplo acima utilizei uma período do meu AWR para limitar a minha task. Se não quiser utilizar os snaps, basta excluir essas duas linhas.

-- Execute task
   EXEC DBMS_SQLTUNE.execute_tuning_task(
           task_name => 'cyb0csh3g3vxa_AWR2_tuning_task');


-- Verify status
   SELECT 
task_id, 
task_name, 
status
   FROM 
dba_advisor_log
   WHERE 
task_name like '%cyb0csh3g3vxa%'
   order by 
task_id;

  TASK_ID  TASK_NAME                      STATUS
---------- ------------------------------ -----------
    154921 cyb0csh3g3vxa_AWR2_tuning_task COMPLETED

-- Verify recomendations 
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
   SELECT 
      DBMS_SQLTUNE.report_tuning_task(
      'cyb0csh3g3vxa_AWR2_tuning_task'
      ) AS recommendations 
   FROM 
      dual;

Nesse momento será apresentado uma relatório com as recomendações de melhorias.

Example:

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : cyb0csh3g3vxa_AWR2_tuning_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1200
Completion Status  : COMPLETED
Started at         : 12/06/2016 06:24:20
Completed at       : 12/06/2016 06:24:22

-------------------------------------------------------------------------------
Schema Name: PRD1_GC_APP
SQL ID     : cyb0csh3g3vxa
SQL Text   : select tbatchjobs0_.SUB_ID as SUB1_34_, tbatchjobs0_.RUN_ID as
             RUN2_34_, tbatchjobs0_.PROCESS_TYPE as PROCESS3_34_,
             tbatchjobs0_.PIECE_ID as PIECE4_34_, tbatchjobs0_.CREATE_TIME as
             CREATE5_34_, tbatchjobs0_.START_TIME as START6_34_,
             tbatchjobs0_.FINISH_TIME as FINISH7_34_, tbatchjobs0_.AGENT_ID
             as AGENT8_34_, tbatchjobs0_.THREAD_ID as THREAD9_34_,
             tbatchjobs0_.STATUS as STATUS34_, tbatchjobs0_.RESULT_INFO as
             RESULT11_34_ from T_BATCH_JOB_SUB tbatchjobs0_ where
             tbatchjobs0_.AGENT_ID=1 and tbatchjobs0_.STATUS<>107 and
             tbatchjobs0_.STATUS<>105 and tbatchjobs0_.STATUS<>223 and
             tbatchjobs0_.STATUS<>106 order by tbatchjobs0_.SUB_ID

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  Predicate "TBATCHJOBS0_"."STATUS"<>107 used at line ID 2 of the execution
  plan is an inequality condition on indexed column "STATUS". This inequality
  condition prevents the optimizer from selecting indices  on table
  "PRD1_GC"."T_BATCH_JOB_SUB".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices.

2- Statistics Finding
---------------------
  Optimizer statistics for table "PRD1_GS"."T_CTS_BCP_GL_FEE" and its indices
  are stale.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(
        ownname => 'PRD1_GS', 
        tabname => 'T_CTS_BCP_GL_FEE', 
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
        method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 48.56%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(
task_name => 'cyb0csh3g3vxa_AWR2_tuning_task', 
task_owner => 'SYS', 
replace => TRUE);

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

1- Original
-----------
Plan hash value: 4019928224

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |  7983 |   350K|  2063   (3)| 00:00:21 |
|   1 |  SORT ORDER BY     |                 |  7983 |   350K|  2063   (3)| 00:00:21 |
|*  2 |   TABLE ACCESS FULL| T_BATCH_JOB_SUB |  7983 |   350K|  2061   (3)| 00:00:21 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TBATCHJOBS0_"."STATUS"<>107 AND "TBATCHJOBS0_"."STATUS"<>105
              AND "TBATCHJOBS0_"."STATUS"<>106 AND "TBATCHJOBS0_"."AGENT_ID"=1 AND
              "TBATCHJOBS0_"."STATUS"<>223)
-------------------------------------------------------------------------------

É isso ai pessoal, bem simples e objetivo. 

Agora é só analisar essas sugestões e lembrar-se que o advisor fornece sugestões de melhorias e não uma receita de bolo pronta e infalível. 

Portanto, o seu resultado e sua utilização devem ser analisados e testados com muito cuidado.

Abraço
Mario

Nenhum comentário:

Postar um comentário

Isso te ajudou? Comente...

Postagem em destaque

[ORACLE] Cleanup Repository Area - Oracle Database Appliance (ODA)

Hello everyone. How are you doing? Today, I'll share with you a simple tip about ODA. When I need to clean the repository area, I use th...