Oracle 性能调优学习笔记(二)--Statpack(各种参数配置)

配置参数
       数据库参数配置:
        show parameter statistics;
     statistics_level:取值范围:BASIC,TYPICAL,ALL,默认TYPICAL
          如果设置为BASIC,受影响的功能如下:
          AWR,ADDM
       服务器生成的警告
       自动SGA内存管理
       自动性能优化收集
       对象级别统计
       服务级别的统计.
       应用的跟踪(v$client_stats)
       数据库时间分布统计(v$sess_time_model v$sys_time_model)
             Buffer cache advisory
       MTTR advisory
       共享池自动大小advisory
       Segment级别的统计
       PGA target advisory
       Timed statistics
       统计信息监控
     timed_os_statistics:
     timed_statistics:  
             默认情况下:
         当statistics_level设置为TYPICAL或者ALL时候为true,为BASIC为false.
       如果为false,跟时间相关的功能就开启.
    
    快照参数配置
        i_snapshot_level的不同级别的不同:
      0:
      5:
      6:
      7:
      10:
      查看快照参数默认表:
          desc stats$statspack_parameter;
       存储快照默认的参数信息
      
      修改快照级别
     
        1. exec statspack.snap(i_snap_level=>7,i_modify_parameter=>'true');
          备注:临时使用,不修改参数值可以使用如下 exec statspack.snap(i_snap_level=>7);
          
     
        2. exec statspack.modify_statspack_parameter(i_snap_level=>7,i_buffer_gets_th=>1000,i_disk_reads_th=>1000);
       可修改的参数如下:
      Input Parameters for the SNAP and MODIFY_STATSPACK_PARAMETERS procedures

         Parameters able to be passed in to the statspack.snap and
         statspack.modify_statspack_parameter procedures are as follows:

           Range of      Default
      Parameter Name      Valid Values  Value    Meaning
      ------------------  ------------  -------  -----------------------------------
      i_snap_level        0,5,6,7,10    5        Snapshot Level
      i_ucomment          Text          <blank>  Comment to be stored with Snapshot
      i_executions_th     Integer >=0   100      SQL Threshold: number of times
                   the statement was executed
      i_disk_reads_th     Integer >=0   1,000    SQL Threshold: number of disk reads
                   the statement made
      i_parse_calls_th    Integer >=0   1,000    SQL Threshold: number of parse
                   calls the statement made
      i_buffer_gets_th    Integer >=0   10,000   SQL Threshold: number of buffer
                   gets the statement made
      i_sharable_mem_th   Integer >=0   1048576  SQL Threshold: amount of sharable
                   memory
      i_version_count_th  Integer >=0   20       SQL Threshold: number of versions
                   of a SQL statement
      i_seg_phy_reads_th  Integer >=0   1,000    Segment statistic Threshold: number
                   of physical reads on a segment.
      i_seg_log_reads_th  Integer >=0   1,0000   Segment statistic Threshold: number
                   of logical reads on a segment.
      i_seg_buff_busy_th  Integer >=0   100      Segment statistic Threshold: number
                   of buffer busy waits for a segment.
      i_seg_rowlock_w_th  Integer >=0   100      Segment statistic Threshold: number
                   of row lock waits for a segment.
      i_seg_itl_waits_th  Integer >=0   100      Segment statistic Threshold: number
                   of ITL waits for a segment.
      i_seg_cr_bks_sd_th  Integer >=0   1000     Segment statistic Threshold: number
                   of Consistent Reads blocks served by
                   the instance for the segment*.
      i_seg_cu_bks_sd_th  Integer >=0   1000     Segment statistic Threshold: number
                   of CUrrent blocks served by the
                   instance for the segment*.
      i_session_id        Valid sid     0 (no    Session Id of the Oracle Session
           from          session) to capture session granular
           v$session              statistics for
      i_modify_parameter  True,False    False    Save the parameters specified for
                   future snapshots?    
     删除job
        exec dbms_job.remove(jobNo);
       
      
    实例参数配置
         在sprepcon.sql:
         define num_days=""
      define top_n_sql=65
         总的SQL显示信息为65行.
      define num_rows_per_hash=4
        每一个SQL最多显示4行数据.
     
      查看实例定义参数:
       define
        
    sql报告参数配置:
          在sprepcon.sql: