ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分区表的统计信息问题[转]

分区表的统计信息问题[转]

原创 Linux操作系统 作者:47328983 时间:2011-04-24 13:21:31 0 删除 编辑

一个分区表,按日分区,相当于每天一个分区,每个分区都与独立的信息,对于当天的分区的查询往往会很慢,

后来查找了原因,发现因为当天的分区的统计信息都是空的,因为当天的分区没有分析过,导致统计信息不准确,最后查询变慢。

 

解决方法:1、在一定的时间,比如说早上八点,分析分区表

          2、读取以前分区的统计信息,插入到入分区表当天的统计信息

 

第二种方法对系统的影响最小

第二种方法的实现方式:

CREATE OR REPLACE PROCEDURE p_insert_part_index_stats as

  m_numrows    number;
  m_numlblks    number;
  m_numdist    number;
  m_avglblk    number;
  m_avgdblk    number;
  m_clstfct    number;
  m_indlevel    number;
  m_guessq    number;
  m_numblks    number;
  m_avgrlen    number;
  srec      dbms_stats.statrec;
  m_distcnt    number;
  m_density    number;
  m_nullcnt    number;
  m_avgclen    number;
  m_CACHEDBLK number;
  m_CACHEHIT number;
  v_partname varchar (100);
  v_modify_today_pname varchar (100);
  v_modify_yes_pname varchar (100);
  cursor c_insert_stats is select index_name from tsy_insert_part_index_stats;
begin

 select 'TIME_'||TO_CHAR(SYSDATE-5,'YYMMDD') into  v_partname   FROM DUAL;
 select 'TIME_'||TO_CHAR(SYSDATE,'YYMMDD')   into  v_modify_today_pname   FROM DUAL;
 select 'TIME_'||TO_CHAR(SYSDATE 1,'YYMMDD') into  v_modify_yes_pname   FROM DUAL;
 for v_insert_stats in c_insert_stats loop
 dbms_stats.get_index_stats
 (ownname => SYS_CONTEXT('USERENV','CURRENT_USER'),
 indname => v_insert_stats.Index_Name,
 partname =>v_partname,
 numrows => m_numrows,
 numlblks =>m_numlblks,
 numdist => m_numdist,
 avglblk => m_avglblk,
 avgdblk => m_avgdblk,
 clstfct =>m_clstfct,
 indlevel => m_indlevel,
 guessq => m_guessq,
 cachedblk =>m_CACHEDBLK,
 cachehit =>m_CACHEHIT);
 
 dbms_stats.set_index_stats
 (ownname => SYS_CONTEXT('USERENV','CURRENT_USER'),
 indname => v_insert_stats.Index_Name,
 partname => v_modify_today_pname,
 numrows => m_numrows,
 numlblks => m_numlblks,
 numdist => m_numdist,
 avglblk => m_avglblk,
 avgdblk => m_avgdblk,
 clstfct =>m_clstfct,
 indlevel => m_indlevel,
 guessq => m_guessq,
 cachedblk =>m_CACHEDBLK,
 cachehit =>m_CACHEHIT);
  dbms_stats.set_index_stats
 (ownname => SYS_CONTEXT('USERENV','CURRENT_USER'),
 indname => v_insert_stats.Index_Name,
 partname => v_modify_yes_pname,
 numrows => m_numrows,
 numlblks => m_numlblks,
 numdist => m_numdist,
 avglblk => m_avglblk,
 avgdblk => m_avgdblk,
 clstfct =>m_clstfct,
 indlevel => m_indlevel,
 guessq => m_guessq,
 cachedblk =>m_CACHEDBLK,
 cachehit =>m_CACHEHIT);
 end loop;
 
end;


CREATE OR REPLACE PROCEDURE p_insert_part_table_stats as

  m_numrows    number;
  m_numlblks    number;
  m_numdist    number;
  m_avglblk    number;
  m_avgdblk    number;
  m_clstfct    number;
  m_indlevel    number;
  m_guessq    number;
  m_numblks    number;
  m_avgrlen    number;
  srec      dbms_stats.statrec;
  m_distcnt    number;
  m_density    number;
  m_nullcnt    number;
  m_avgclen    number;
  m_CACHEDBLK number;
  m_CACHEHIT number;
  v_partname varchar (100);
  v_modify_today_pname varchar (100);
  v_modify_yes_pname varchar (100);
  cursor c_insert_stats is select table_name from tsy_insert_part_table_stats;
begin
 

 select 'TIME_'||TO_CHAR(SYSDATE-2,'YYMMDD') into  v_partname   FROM DUAL;
 select 'TIME_'||TO_CHAR(SYSDATE,'YYMMDD')   into  v_modify_today_pname   FROM DUAL;
 select 'TIME_'||TO_CHAR(SYSDATE 1,'YYMMDD') into  v_modify_yes_pname   FROM DUAL;
 for v_insert_stats in c_insert_stats loop
 dbms_stats.get_table_stats(ownname => SYS_CONTEXT('USERENV','CURRENT_USER'),tabname => v_insert_stats.TABLE_NAME,partname =>v_partname,numrows =>m_numrows,numblks => m_numblks,avgrlen => m_avgrlen);
 dbms_stats.set_table_stats(ownname => SYS_CONTEXT('USERENV','CURRENT_USER'),tabname => v_insert_stats.TABLE_NAME,partname => v_modify_today_pname,numrows => m_numrows,numblks => m_numblks,avgrlen =>m_avgrlen);
end loop;

end;

 

这两个表里记录了需要预插入的表和索引

tsy_insert_part_table_stats

tsy_insert_part_index_stats

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

请登录后发表评论 登录
全部评论

注册时间:2009-03-07

  • 博文量
    111
  • 访问量
    333271