• oracle 11g 自己主动调整


    --:自己主动调教计划
    begin
      dbms_workload_repository.create_snapshot();
    end;


    select /*+ result_cache */ count(*) from (select * from  HJ.dbtan where object_type='TABLE' ORDER BY 1 DESC ,2 ASC ,3 DESC ,4 ASC);


    begin
      dbms_workload_repository.create_snapshot();
    end;


    --ft8s1pfmz9ph0
    SET SERVEROUTPUT ON
    DECLARE
      l_sql_tune_task_id  VARCHAR2(100);
    BEGIN
      l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                              begin_snap  => 21,
                              end_snap    => 22,
                              sql_id      => 'ft8s1pfmz9ph0',
                              scope       => DBMS_SQLTUNE.scope_comprehensive,
                              time_limit  => 300,
                              task_name   => 'ft8s1pfmz9ph0d_AWR_tuning_task',
                              description => 'Tuning task for statement f3hc7r4trnn1d in AWR.');
      DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
    END;


    begin DBMS_SQLTUNE.execute_tuning_task(task_name => 'ft8s1pfmz9ph0d_AWR_tuning_task');end;
    SELECT DBMS_SQLTUNE.report_tuning_task('ft8s1pfmz9ph0d_AWR_tuning_task') AS recommendations FROM dual;


    GENERAL INFORMATION SECTION
    -------------------------------------------------------------------------------
    Tuning Task Name   : ft8s1pfmz9ph0d_AWR_tuning_task
    Tuning Task Owner  : SYS
    Workload Type      : Single SQL Statement
    Scope              : COMPREHENSIVE
    Time Limit(seconds): 300
    Completion Status  : COMPLETED
    Started at         : 09/01/2014 14:55:32
    Completed at       : 09/01/2014 14:55:44


    -------------------------------------------------------------------------------
    Schema Name: SYS
    SQL ID     : ft8s1pfmz9ph0
    SQL Text   : select /*+ result_cache */ count(*) from (select * from 
                 HJ.dbtan where object_type='TABLE' ORDER BY 1 DESC ,2 ASC ,3
                 DESC ,4 ASC)


    -------------------------------------------------------------------------------
    FINDINGS SECTION (2 findings)
    -------------------------------------------------------------------------------


    1- Statistics Finding
    ---------------------
      尚未分析表 "HJ"."DBTAN"。


      Recommendation
      --------------
      - 考虑收集此表的优化程序统计信息。


        execute dbms_stats.gather_table_stats(ownname => 'HJ', tabname =>
                'DBTAN', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                method_opt => 'FOR ALL COLUMNS SIZE AUTO');
      Rationale
      ---------
        为了选择好的运行计划, 优化程序须要此表的最新统计信息。
    2- Index Finding (see explain plans section below)
    --------------------------------------------------
      通过创建一个或多个索引能够改进此语句的运行计划。
      Recommendation (estimated benefit: 95.98%)
      ------------------------------------------
      - 考虑执行能够改进物理方案设计的訪问指导或者创建推荐的索引。
        create index HJ.IDX$$_00360001 on HJ.DBTAN("OBJECT_TYPE");
      Rationale
      ---------
        创建推荐的索引能够显著地改进此语句的执行计划。

    可是, 使用典型的 SQL 工作量执行 "訪问指导"
        可能比单个语句更可取。通过这样的方法能够获得全面的索引建议案, 包含计算索引维护的开销和附加的空间消耗。
    -------------------------------------------------------------------------------
    EXPLAIN PLANS SECTION
    -------------------------------------------------------------------------------
    1- Original
    -----------
    Plan hash value: 1782547706
    --------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                            |     1 |    11 |  4706   (1)| 00:00:57 |
    |   1 |  RESULT CACHE       | 81z3k6zbauk9s2c83c03s270ja |       |       |            |          |
    |   2 |   SORT AGGREGATE    |                            |     1 |    11 |            |          |
    |*  3 |    TABLE ACCESS FULL| DBTAN                      | 40006 |   429K|  4706   (1)| 00:00:57 |
    --------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       3 - filter("OBJECT_TYPE"='TABLE')
    Result Cache Information (identified by operation id):
    ------------------------------------------------------
       1 - column-count=1; dependencies=(HJ.DBTAN); attributes=(single-row); parameters=(nls); name="select /*+ result_cache */ count(*) from (select * from  HJ.dbtan where object_type='TABLE' ORDER BY 1 DESC ,2 ASC ,3 DESC ,4 AS
    2- Using New Indices
    --------------------
    Plan hash value: 2810514733
    -------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                            |     1 |    11 |   189   (1)| 00:00:03 |
    |   1 |  RESULT CACHE      | 27yjysxpdun18b2utun82bynny |       |       |            |          |
    |   2 |   SORT AGGREGATE   |                            |     1 |    11 |            |          |
    |*  3 |    INDEX RANGE SCAN| IDX$$_00360001             | 40006 |   429K|   189   (1)| 00:00:03 |
    -------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       3 - access("OBJECT_TYPE"='TABLE')
    Result Cache Information (identified by operation id):
    ------------------------------------------------------
       1 - column-count=1; dependencies=(HJ.DBTAN); attributes=(single-row, ordered); parameters=(nls); name="select /*+ result_cache */ count(*) from (select * from  HJ.dbtan where object_type='TABLE' ORDER BY 1 DESC ,2 ASC ,3 DESC ,4 AS"
    -------------------------------------------------------------------------------


    begin DBMS_SQLTUNE.drop_tuning_task (task_name => 'ft8s1pfmz9ph0d_AWR_tuning_task');end;

    版权声明:本文博主原创文章。博客,未经同意不得转载。

  • 相关阅读:
    双色球的概率和收益
    伽玛函数
    概率统计13——二项分布与多项分布
    贝叶斯决策理论(1)
    线性代数笔记34——左右逆和伪逆
    线性代数笔记33——基变换和图像压缩
    线性代数笔记32——线性变换及对应矩阵
    线性代数笔记31——奇异值分解
    线性代数笔记30——相似矩阵和诺尔当型
    线性代数笔记29——正定矩阵和最小值
  • 原文地址:https://www.cnblogs.com/bhlsheji/p/4822109.html
Copyright © 2020-2023  润新知