Task Name : cyb0csh3g3vxa_AWR2_tuning_taskTuning Task Owner : SYSWorkload Type : Single SQL StatementScope : COMPREHENSIVETime Limit(seconds): 1200Completion Status : COMPLETEDStarted at : 12/06/2016 06:24:20Completed at : 12/06/2016 06:24:22-------------------------------------------------------------------------------Schema Name: PRD1_GC_APPSQL ID : cyb0csh3g3vxaSQL 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)-------------------------------------------------------------------------------E isso ai pessoal, bem simples e objetivo. Agora e so analisar essas sugestoes e lembrar-se que o advisor fornece sugestoes de melhorias e nao uma receita de bolo pronta e infalivel. Portanto, o seu resultado e sua utilizacao devem ser analisados e testados com muito cuidado.AbracoMario