一个分区表,按日分区,相当于每天一个分区,每个分区都与独立的信息,对于当天的分区的查询往往会很慢,
后来查找了原因,发现因为当天的分区的统计信息都是空的,因为当天的分区没有分析过,导致统计信息不准确,最后查询变慢。
解决方法: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/