[ORACLE] Useful scripts for the day-to-day life of a DBA (Part 2) - Execution Plan

Hello all 

 

Hope you're doing well!

As I said here, I've created a repository on GITHUB to share some scripts that I like to make and that help me in my day-to-day life as a DBA.

 

 

Today's script is showplan.sql and is in the SQLPLAN directory.     
 
The script serves to help monitor and check active execution plans and try to create an advisor for sql plans with problems. 

 

It's the first version, so I'm sure the Advisor needs some 'tuning' 😆. The attempt here is to flag potentially problematic plans, just a starting point for further analysis.

You can use this script in combination with Carlos Sierra's coe_xfr_sql_profile.sql to define a better plan, for example.

Remember that these are scripts that you can use and change at your own risk. They help me a lot, but they may not be as functional for you.

My script, my rules for advice and tuning, ok?

 

The repository is https://github.com/MarioBarduchi/Scripts2DBA.

 

The script is: https://github.com/MarioBarduchi/Scripts2DBA/blob/main/SQLPLAN/showplan.sql

 

And always remember:

1. Some scripts are a compilation of several other scripts I've created.
   
2. Some scripts are based on and adapted from existing scripts. Those that I found reference to the author I obviously identify in the script.
3. So, if there's a script that you're the author of and I've adapted to my reality and it's not mentioned, please let me know and I'll be happy to reference it.
4. Because they're my scripts, they have my logic and help me in my day-to-day work, it doesn't mean that they're the best or that they're always the most refined and concerned with good practices. They can help you, but that's all.
5.  Feel free to make changes, but please keep the reference to the authors.
6.  If you find any mistakes, please let me know.

Feel free to download and use.

Regards

Mario Barduchi

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