Oracle OCP笔记(21)统计信息,顾问程序,警报与阀值

qq489498494

Oracle OCP笔记(21)统计信息,顾问程序,警报与阀值


一.统计信息
1.关于统计信息
    统计信息对SQL语句的性能影响巨大，优化器将使用统计信息来设计有效的执行计划。可以采用手工或自动方式来收集统计信息。如果在收集了统计信息后，性能问题依然存在，那么可以使用各种指标来探究问题的根源。
    统计与PL/SQL无关，只与SQL有关。不过，大多数PL/SQL代码都包含对SQL语句的调用。对于这些语句来说，统计信息同样非常重要。


    1.对象统计信息
    表的统计信息(显示在视图dba_tables中)
    ·表中的行数
    ·为表分配的块数(无论是否曾经被使用过)
    ·正在使用的块内的空闲空间数
    ·每行的平均长度
    ·链接行数(分割在多个块内的行，可能是行过长或存储设置不当)
    列的统计信息(显示在视图dba_tab_columns中)
    ·不同值的数目
    ·最大值和最小值
    ·NULL值的数目
    ·平均列长度
    索引的统计信息(显示在视图dba_indexes中)
    ·索引树的深度
    ·不同键值的数目
    ·聚合因子(行的自然顺序与索引顺序之间的接近程度)
    索引相关的统计信息(显示在视图index_stats中)
    ·引用现有行的索引数
    ·引用被删除行的索引数
    就维护索引的方式而言，上面这些信息很有价值。维护索引的方式为: 行被删除时，保留索引键。经过一段时间后，由于对被删除行的引用占用了大量空间，因此索引会变得低效。
   
2.手动收集统计信息
    analyze命令
    analyze table tablename compute statistics;


    dbms_stats程序包
    dbms_stats.gather_table_stats   
    dbms_stats.gather_schema_stats


    锁住统计信息
    dbms_stats.lock_table_stats
    dbms_stats.lock_schema_stats
    解锁统计信息
    dbms_stats.unlock_table_stats
    dbms_stats.unlock_schema_stats


3.控制统计信息的收集和可见性(参数statistics_level)
    select value from v$parameter where name like 'statistics_level';
    ·BASIC    -- 禁用AWR(自动工作负荷仓库)统计信息的计算，并禁用日常分析，将无法使用各种性能和调整顾问，警报系统将不起作用。
    ·TYPICAL  -- 默认值,收集数据库的自我管理和调整功能需要的所有统计信息，并将启用每天在维护窗口中运行的自动对象分析任务。
    ·ALL      -- 收集所有可能的统计信息。包括操作系统活动统计信息，以及有关SQL语句执行的非常详细的统计信息。可能对性能产生负面影响。


    自动统计信息收集任务每天在维护窗口执行此任务，维护窗口在平时晚上运行4个小时(22点开始),周六日运行20个小时(6点开始)。
    由自动任务收集的统计信息足够使用，没有必要再手工收集统计信息。




二.自动工作负荷仓库
1.关于自动工作负荷仓库(AWR)
    Oracle收集大量有关活动和性能的信息，这些统计信息收集到内存中，并由MMON后台进程定期转储到自动工作负荷仓库(AWR)。
    AWR是位于SYSAUX表空间内的一组表，这些表不能被重新定位，并且存在立SYSMAN模式中.


2.收集AWR统计信息
    只有将参数statistics_level参数设置为TYPICAL或ALL，才能启用统计信息收集。
    ·BASIC    -- 禁用AWR(自动工作负荷仓库)统计信息的计算，并禁用日常分析，将无法使用各种性能和调整顾问，警报系统将不起作用。
    ·TYPICAL  -- 默认值,收集数据库的自我管理和调整功能需要的所有统计信息，并将启用每天在维护窗口中运行的自动对象分析任务。
    ·ALL      -- 收集所有可能的统计信息。包括操作系统活动统计信息，以及有关SQL语句执行的非常详细的统计信息。可能对性能产生负面影响。


    MMON进程直接访问构成SGA的内存结构，从而也可以访问这些内存结构中的统计信息。这个进程可以在不需要通过会话的情况下从SGA内抽取数据。此时唯一的系统开销是将数据的快照实际写入AWR。任何第三方工具都不能像MMON那样对实例进行直接内存访问。
    MMON进程将AWR统计信息作为快照保存到AWR中，默认方式是每小时保存一次，在被重写前，快照会保存8天。


    Enterprise Manager Database Control和Enterprise Manager Grid Control使用配置文件中加密形式的口令，以SYSMAN的身份登录到数据库。更改SYSMAN口令:
    alter user sysman identified by ...
    emctl sepasswd dbconsole


3.管理AWR
    AWR在SYSAUX表空间内可能需要200MB~300MB的空间。
    Database Control/Server/Statistics Management/AWR
   
4.统计信息、指标和基准
    统计信息: AWR快照包含统计信息。
    指标(metrics): 统计信息本身并无意义，要使其发挥作用，必须将统计信息转换成指标。如: 每秒读磁盘次数，每个事务读磁盘的次数等。
    基准(baseline): 基准是一组已经存储的统计信息和指标，可用于跨越时间进行比较.
    当MMON进程保存AWR快照时，会根据统计信息生成大量的指标，这些都自动发生。而基准的生成必须由DBA完成。过一段时间会清除快照，默认是8天后清除。基准是快照的一个或多个对，将一直保存到专门删除为止。
    需要为特定事件和普通运行创建基准。例如，如果数据库会定期执行月末处理，则最好将每月末收集的AWR快照存储为基准,这样，可以观察整年中月末处理活动的变化情况，并确定是否存在任何问题，另外，可以存储令人满意的一般运行期间的基准，这样，如果后来性能下降，将可以获得信息，来帮助了解改变的内容。
    Database Control/Baselines打开AWR Baselines窗口，可以定义基准的存储周期。


5.使用dbms_workload_repository程序包
    可以调整快照的频率和持久性，生成临时快照，创建和操纵基准，并生成任何两个快照之间的活动报告。
    -- 强制收集快照，作为普通预定快照的补充.
    execute dbms_workload_repository.create_snapshot;
    -- 设置MMON半小时收集一次快照，快照保留30天.
    execute dbms_workload_repository.modify_snapshot_settings(retention => 43200, interval => 30);
    -- 存储快照为基准.
    execute dbms_workload_repository.create_baseline(start_snap_id => 487, end_snap_id => 488, baseline_name => 'FridayPM');


    -- 查询AWR的所有快照
    select snap_id,begin_interval_time from dba_hist_snapshot order by begin_interval_time desc;


    -- SYSAUX表空间的所有组件
    select * from v$sysaux_occupants;
    select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name = 'SM/AWR'; -- AWR的空间大小


三.使用顾问程序
    所有的顾问程序: select * from dba_advisor_definitions;


1.数据库自动诊断监视程序(Automatic Database Diagnostic Monitor,ADDM)
    ADDM是分析性能和解决问题的出发点，也可能给出所有需要的建议:
    ·更改硬件(如添加CPU)
    ·配置数据库(如实例参数设置)
    ·更改模式(如使用表和索引分区)
    ·更改应用程序(如使用绑定变量)
    ·使用其他顾问程序以边获得更详细的分析和建议.


    缺省情况下,ADDM已经被自动启用,通过初始化参数文件中的statistics_level来控制.
    alter session set statistics_level=typical;
    execute dbms_advisor.set_default_parameter('ADDM','DBIO_EXPECTED',10000);


2.内存顾问程序
    v$pga_target_advice
    v$sga_target_advice
    v$shared_pool_advice
    v$db_cache_advice
    v$java_pool_advice
    v$streams_pool_advice
    v$memory_target_advice
    gv$pga_target_advice
    gv$sga_target_advice
    gv$shared_pool_advice
    gv$db_cache_advice
    gv$java_pool_advice
    gv$streams_pool_advice
    gv$memory_target_advice


3.SQL顾问程序
    (1)SQL访问顾问程序(SQL Access Advisor)
       dbms_stats.gather_table_stats  -- 收集统计信息
       dbms_advisor.quick_tune        -- 执行调整分析: AdvisorName:DBMS_ADVISOR.SQLACCESS_ADVISOR
       dbms_advisor.get_task_script   -- 得到调整建议
       --dbms_advisor.reset_task      -- 重置任务
       --dbms_advisor.create_file     -- 创建文件
       --dbms_advisor.delete_task     -- 删除任务
      
       select dbms_advisor.get_task_script('taskname') from dual;


       -- select * from dba_advisor_actions where task_name = 'taskname';
       -- select * from user_advisor_sqla_wk_stmts where task_name = 'taskname';


       相关视图
       dba_advisor_tasks
       dba_advisor_log
       dba_advisor_findings
       dba_advisor_recommendations


    (2)SQL调整顾问程序(SQL Tuning Advisor)
       dbms_sqltune.create_tuning_task         -- 创建SQL调优任务，需指定SQL语句，返回任务名称
       dbms_sqltune.set_tuning_task_parameter  -- 设定任务参数，如时间限制
       dbms_sqltune.execute_tuning_task        -- 执行调优任务
       dbms_sqltune.report_tuning_task         -- 报告调整建议 select dbms_sqltune.report_tuning_task('taskname') from dual;
       dba_advisor_tasks, v$advisor_progress   -- 查看任务状态.
       dba_advisor_actions                     -- 顾问信息


       select task_name,status,sofar,totalwork
         from dba_advisor_tasks
         join v$advisor_progress using(task_id)
        where task_name = 'taskname';


       select * from dba_advisor_actions where task_name = 'taskname';


    (3)SQL修复顾问程序




4.自动撤销顾问程序
    撤销顾问程序将观察生成撤销数据的频率，以及运行的查询的长度，并建议撤销表空间的最小大小，这将保证查询不会因快照过旧错误而失败。


    DECLARE
       v_task_id    NUMBER;
       v_task_name  VARCHAR2(30) := 'undo_advisor_task_001';
       v_object_id  NUMBER;
    BEGIN
       dbms_advisor.create_task('Undo Advisor', v_task_id, v_task_name, 'Undo Advisor Task');
       dbms_advisor.create_object(v_task_name, 'UNDO_TBS', null, null, null, 'null', v_object_id);
       dbms_advisor.set_task_parameter(v_task_name, 'TARGET_OBJECTS', v_object_id);
       dbms_advisor.set_task_parameter(v_task_name, 'START_SNAPSHOT', 1);
       dbms_advisor.set_task_parameter(v_task_name, 'END_SNAPSHOT', 2);
       dbms_advisor.set_task_parameter(v_task_name, 'INSTANCE', 1);
       dbms_advisor.execute_task(v_task_name);
    END;


    相关视图
    dba_advisor_tasks
    dba_advisor_log
    dba_advisor_findings
    dba_advisor_recommendations


5.实例平均恢复时间顾问程序
    参数fast_start_mttr_target
    ·设置为0，发生实例故障后恢复时间成本较高，但实现了性能的最大化，DBWn尽量减少写入量，恢复将为数据文件应用大量的重做。
    ·设置为非0，设置了恢复目标，启用了检查点自动调整。


    v$instance_recovery  -- 顾问程序评估的当前恢复时间.
    v$mttr_target_advice
    gv$mttr_target_advice


6.数据恢复顾问程序(Data Recovery Advisor)
    rman命令
    ·list failure
    ·advise failure
    ·repair failure
    ·change failure


7.段顾问程序
    dbms_advisor.create_task          -- 创建顾问任务
    dbms_advisor.create_object        -- 创建顾问对象:表或其它
    dbms_advisor.set_task_parameter   -- 设置参数
    dbms_advisor.execute_task         -- 执行任务


    -- 段顾问发现的内容
    select owner,task_id,task_name,type,message,more_info from dba_advisor_findings where task_id=6;
    -- 推荐的收缩操作及潜在的节省空间(byte)
    select owner,task_id,task_name,benefit_type from dba_advisor_recommendations where task_id=6;
    -- 执行收缩所需要的SQL语句
    select owner,task_id,task_name,command,attr1 from dba_advisor_actions where task_id=6;


四.自动维护作业(autotask)
    要使数据库在良好状态下运行，就必须达到以下几项要求: 优化器必须访问正确的对象统计信息; 表和索引必须能有效运行，而没有大量浪费的空间和碎片; 而且已经调整了负载高的SQL语句。
    有三个自动维护的任务: 收集优化器统计信息，段顾问程序和SQL调整顾问程序。
    这些任务在维护窗口中运行，维护窗口默认方式下从工作日的22点开始运行4个小时，在周六日从早上6点开始运行20个小时。




五.管理警报与阀值
    必须使用阀值配置有状态警报
    如果引发了有状态警报，那么，在清除此情形前，它将一直保留。无状态警报会予以报告，但不需要清理。
    阀值存储在AWR中，MMON进程通常引发警报，Enterprise Manager报告警报，可以通过Enterprise Manager设置发送邮件。


    设置阀值使用程序包dbms_server_alert.


    与警报有关视图:
    dba_outstanding_alerts;
    dba_alert_history         版权声明：本文为博主原创文章，未经博主允许不得转载。