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
