手工设置列的直方图信息

从dba_source中摘取的相关内容信息:

-- types for minimum/maximum values and histogram endpoints

type numarray is varray(256) of number;

type datearray is varray(256) of date;

type chararray is varray(256) of varchar2(4000);

type rawarray is varray(256) of raw(2000);

type fltarray is varray(256) of binary_float;

type dblarray is varray(256) of binary_double;

type StatRec is record (

epc number,

minval raw(2000),

maxval raw(2000),

bkvals numarray,

novals numarray,

chvals chararray,

eavs number);

--这些函数是重载的

procedure prepare_column_values(srec in out StatRec, charvals chararray);

procedure prepare_column_values(srec in out StatRec, datevals datearray);

procedure prepare_column_values(srec in out StatRec, numvals numarray);

procedure prepare_column_values(srec in out StatRec, fltvals fltarray);

procedure prepare_column_values(srec in out StatRec, dblvals dblarray);

procedure prepare_column_values(srec in out StatRec, rawvals rawarray);

procedure prepare_column_values_nvarchar(srec in out StatRec, nvmin nvarchar2, nvmax nvarchar2);

procedure prepare_column_values_rowid(srec in out StatRec, rwmin rowid, rwmax rowid);

--

-- Convert user-specified minimum, maximum, and histogram endpoint

-- datatype-specific values into Oracle's internal representation

-- for future storage via set_column_stats.

--

-- Generic input arguments:

-- srec.epc - The number of values specified in charvals, datevals,

-- numvals, or rawvals. This value must be between 2 and 256 inclusive.

-- Should be set to 2 for procedures which don't allow histogram

-- information (nvarchar and rowid).

-- The first corresponding array entry should hold the minimum

-- value for the column and the last entry should hold the maximum.

-- If there are more than two entries, then all the others hold the

-- remaining height-balanced or frequency histogram endpoint values

-- (with in-between values ordered from next-smallest to next-largest).

-- This value may be adjusted to account for compression, so the

-- returned value should be left as is for a call to set_column_stats.

-- srec.bkvals - If a frequency distribution is desired, this array contains

-- the number of occurrences of each distinct value specified in

-- charvals, datevals, numvals, or rawvals. Otherwise, it is merely an

-- ouput argument and must be set to null when this procedure is

-- called.

--

-- Datatype specific input arguments (one of these):

-- charvals - The array of values when the column type is character-based.

-- Up to the first 32 bytes of each string should be provided.

-- Arrays must have between 2 and 256 entries, inclusive.

-- If the datatype is fixed char, the strings must be space padded

-- to 15 characters for correct normalization.

-- datevals - The array of values when the column type is date-based.

-- numvals - The array of values when the column type is numeric-based.

-- rawvals - The array of values when the column type is raw.

-- Up to the first 32 bytes of each strings should be provided.

-- nvmin,nvmax - The minimum and maximum values when the column type

-- is national character set based (NLS). No histogram information

-- can be provided for a column of this type.

-- If the datatype is fixed char, the strings must be space padded

-- to 15 characters for correct normalization.

-- rwmin,rwmax - The minimum and maximum values when the column type

-- is rowid. No histogram information can be provided for a column

-- of this type.

--

-- Output arguments:

-- srec.minval - Internal representation of the minimum which is

-- suitable for use in a call to set_column_stats.

-- srec.maxval - Internal representation of the maximum which is

-- suitable for use in a call to set_column_stats.

-- srec.bkvals - array suitable for use in a call to set_column_stats.

-- srec.novals - array suitable for use in a call to set_column_stats.

--

-- Exceptions:

-- ORA-20001: Invalid or inconsistent input values

一个例子,手工设置owner列上的频度直方图信息:

declare

v_statrec dbms_stats.StatRec;

v_val_array dbms_stats.chararray;

--v_val_array dbms_stats.numarray;

--v_val_array dbms_stats.datearray;

--v_val_array dbms_stats.rawarray;

--这里的数据类型根据要设置的列的类型选定

v_density NUMBER;

begin

select owner,count(1) cnt

bulk collect into v_val_array,v_statrec.bkvals

from zsj_objs

group by owner

order by 1;

v_statrec.epc:=v_val_array.count; --endpoint count

--最大值最小值在频度直方图信息中已经包含了,所以不用设置

SELECT 1/(2*COUNT(1)) INTO v_density FROM ZSJ_OBJS;

--频度直方图的density=1/(2*num_rows)

dbms_stats.prepare_column_values(v_statrec,v_val_array);

--这个函数是重载的

dbms_stats.set_column_stats(

ownname => USER,

tabname => 'ZSJ_OBJS',

colname => 'OWNER',

distcnt => 18,

density => v_density,

nullcnt => 0,

srec => v_statrec,

avgclen => 6);

end;

/

这里设置优化器统计信息时还是查询了相关对象,如果你对数据情况非常了解的话,你完全可以像下面这样手工设置

 declare

v_statrec dbms_stats.StatRec;

v_val_array dbms_stats.chararray;

--v_val_array dbms_stats.numarray;

--v_val_array dbms_stats.datearray;

--v_val_array dbms_stats.rawarray;

--这里的数据类型根据要设置的列的类型选定

begin

v_val_array:=dbms_stats.chararray('MYTEST','PUBLIC','SYS','WMSYS'); --列值

v_statrec.bkvals:=dbms_stats.numarray(991,19000,20000,10000); --列值数量

--如果这里的总的数量和优化器统计信息中的num_rows-num_nulls不匹配的话,是按比例计算card的

--如果这里设置的值比(num_rows-num_nulls)*density还要小的话,取后者的值

v_statrec.epc:=4; --endpoint count

dbms_stats.prepare_column_values(v_statrec,v_val_array);

--这个函数是重载的

dbms_stats.set_column_stats(

ownname => USER,

tabname => 'ZSJ_OBJS',

colname => 'OWNER',

distcnt => 4,

density => 0.00001,

nullcnt => 0,

srec => v_statrec,

avgclen => 5);

end;

    如果因为直方图信息导致了性能问题,而数据库主机负载又太高了,导致根本没有办法重新收集直方图信息,而你对数据分布情况又比较了解的话,可以使用这种方法手工设置直方图信息,调用 dbms_stats.set_column_stats这个过程的时候一般需要no_invalidate => false,使得相关游标立刻invalidate,下次执行时重新硬分析,应用新的直方图信息.