oracle 分析 dbms_stats 统计数据信息

1. 先说资料

这是oracle官方文档的解释说明，人家说的是最准确的

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1035814

网上中英文对照oracle 的Conceptes对照，编译这个才是真人才，大好人。

2, 说说自己的学习过程

1. oracle 10g 自身就有统计信息的特性，当数据超过10%或者经过一定长时间后会自动分析，但多数与系统产生冲突，不能起到相应的结果，好多生产系统将其关闭。

网上很多资料说这个：

scheduler job 查询

select job_name,program_name,schedule_name,job_class,state from dba_scheduler_jobs;

相应周期查询

SELECT a.job_name, a.enabled, c.window_name, c.repeat_interval

FROM   dba_scheduler_jobs a, dba_scheduler_wingroup_members b, dba_scheduler_windows c

WHERE  job_name='GATHER_STATS_JOB'

And    a.schedule_name=b.window_group_name

And    b.window_name=c.window_name;

执行情况查询：

SELECT log_id, job_name, status, to_char(log_date,'YYYYMMDD HH24:MI:SS') log_date

FROM   dba_scheduler_job_run_details

where  job_name = 'GATHER_STATS_JOB';

我发现我们的已经将其禁止。

3。 那应该就是手工运行分析，后来找到，前辈写了一个脚本，每个星期天0点执行（生产周日不营业）。

脚本处理过程：

select decode(mod(rn, 8),0,8,mod(rn, 8))||':'||stat_sql
from (
    select rownum rn,
      stat_sql
    from (
        select stat_sql1||stat_sql2 stat_sql
        from (
            select 'exec dbms_stats.gather_table_stats(ownname=>'||''''||a.owner||''''||',tabname=>'||'''
'||segment_name ||'''' stat_sql1,
                          case
                            when sum(bytes)/1024/1024 > 10240 then ',estimate_percent=>5,degree=>4'
                            when sum(bytes)/1024/1024 > 5120  then ',estimate_percent=>8,degree=>4'
                            when sum(bytes)/1024/1024 > 1024  then ',estimate_percent=>10,degree=>4'
                            when sum(bytes)/1024/1024 > 100   then ',estimate_percent=>10,degree=>2'
                            else ',estimate_percent=>10,degree=>1'
                          end ||');' stat_sql2
            from dba_segments a,
                 dba_tables b
            where a.owner = b.owner
              and a.segment_name = b.table_name
              and segment_type in ('TABLE','TABLE PARTITION')
              and segment_name not like '%\$%'
              and (b.owner in ( ‘user list
        or  b.owner like 'SW%')
            group by 'exec dbms_stats.gather_table_stats(ownname=>'||''''||a.owner||''''||',tabname=>'||'
'''||segment_name ||''''
            order by sum(bytes) desc)))

他生成8个sql 文件，并且两个实例均分， 每两个并行运行

 

4， 但是在检查系统的时候发现：

select owner, table_name, last_analyzed
from dba_tables
where owner in ( userlist
)
and last_analyzed IS NULL
    and table_name not like 'BIN%'
and table_name not like '%$%'
UNION ALL
select distinct table_owner, table_name, last_analyzed
from dba_tab_partitions
where table_owner in (userlist) and last_alyzed IS NULL
and table_name not like 'BIN%'
and table_name not like '%$%'
;

发现仍然有一些表没有被分析，原来这些表都是子分区表，前辈是脚本写的有问题，还是这些表本身就不能分析，怎么回事。

先看脚本为什么会把这些表漏掉：

and segment_type in ('TABLE','TABLE PARTITION')

而那些未被分析的表都是：

'TABLE SUBPARTITION'

5。 先要判断这些分区表用不用分析，能不能分析

1） 日终繁忙时间段做awr（里面太多的俺不懂，俺着重看看关于sql）

      说说关于awr 俺有前辈的一些文档，很好，人家把awr里的每项都写了sql

       比如： full scan table 比率

select to_char(begin_interval_time,'YYYYMMDD') snap_time,

   sum(decode(stat_name, 'table scan rows gotten', value, 0)) /

   (sum(decode(stat_name, 'table fetch by rowid', value, 0)) +

    sum(decode(stat_name, 'table scan rows gotten', value, 0))) * 100 tab_scan_ratio

   from dba_hist_sysstat m, dba_hist_snapshot s

   where s.snap_id between  440  and 490

   and s.snap_id = m.snap_id

   and m.instance_number = 1

   group by to_char(begin_interval_time,'YYYYMMDD')

Top 10 Wait Event 监测

select event, time
  from (select event, time
         from (select e.event_name                               event
                    , (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000  time
                from dba_hist_system_event b, dba_hist_system_event e
               where b.snap_id(+) = 470
                 and e.snap_id    = 471
                 and b.instance_number(+)  = 1
                 and e.instance_number     = 1
                 and b.event_id(+)            = e.event_id
              )
        order by time desc
      )
where rownum <= 10;

6， 查看分区表分析的资料

         分析应该慎重，尤其你要是在生产上玩的话。

         1。 备份

              CREATE_STAT_TABLE

           2.  导出

EXPORT_COLUMN_STATS Procedure EXPORT_DATABASE_STATS Procedure EXPORT_DICTIONARY_STATS Procedure EXPORT_FIXED_OBJECTS_STATS Procedure EXPORT_INDEX_STATS Procedure EXPORT_SCHEMA_STATS Procedure EXPORT_SYSTEM_STATS Procedure EXPORT_TABLE_STATS Procedure

         

7。 写了生成分区脚本

spool ${TMPDIR}/analyze_part.sh
prompt sqlplus -s userid/userpasswd@database <prompt set echo on
prompt spool ${workdir}analyze_part.log
prompt select current_timestamp from dual;
prompt /
select 'begin dbms_stats.gather_table_stats( wnname =>'''||table_owner || ''','||chr(13)||chr(10)||
'tabname =>'''||table_name||''','||chr(13)||chr(10)||
'block_sample => FALSE,'||chr(13)||chr(10)||
'method_opt  => ''FOR ALL INDEXED COLUMNS SIZE AUTO '','||chr(13)||chr(10)||
'degree => 5,'||chr(13)||chr(10)||
'granularity  => ''ALL'','||chr(13)||chr(10)||
'cascade => TRUE'||chr(13)||chr(10)||
');'||chr(13)||chr(10)||
 'end;'||chr(13)||chr(10)||
'/' as gether_part_table_sql
from
(
select distinct table_owner , table_name
  from dba_tab_partitions
--  where table_owner in 

  where table_owner in ( user list )
  and last_analyzed IS NULL
  and table_name not like 'BIN%'
  and table_name not like '%$%'
);
prompt select current_timestamp from dual;
prompt /
prompt spool off
prompt exit
spool off

/**************SQL 脚本***************

begin dbms_stats.gather_table_stats( wnname =>username,

tabname =>'TABLE_NAME',

block_sample => FALSE,

method_opt  => 'FOR ALL INDEXED COLUMNS SIZE AUTO ',

degree => 5,

granularity  => 'ALL',

cascade => TRUE

);

end;

/

*********************/

工作还没有完成，继续中

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21634752/viewspace-683487/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21634752/viewspace-683487/