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

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

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

 

解决方法：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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/241379/viewspace-693291/