Package Body Just Stats

create or replace

package body just_stats

as

-- Definitions of custom types.

------

type colcounts

is

table of number index by binary_integer;

type coldistinct

is

table of number index by binary_integer;

type colmin

is

table of varchar2(32000) index by binary_integer;

type colmax

is

table of varchar2(32000)index by binary_integer;

type colhist

is

table of varchar2(32000) index by binary_integer;

type colhiststats

is

table of dbms_stats.statrec index by binary_integer;

type colhiststatsnum

is

table of dbms_stats.numarray index by binary_integer;

type colhiststatsvar

is

table of dbms_stats.chararray index by binary_integer;

type colhiststatsdate

is

table of dbms_stats.datearray index by binary_integer;

------

-- Definiotion of package variables that would hold manually computed statistics

------

-- Column statistics

l_colcnt colcounts;

l_coldis coldistinct;

l_colmin colmin;

l_colmax colmax ;

l_colhist colhist;

l_colhiststats colhiststats;

l_colhiststatsn colhiststatsnum ;

l_colhiststatsv colhiststatsvar ;

l_colhiststatsd colhiststatsdate ;

-- Table statistics

l_tab_cnt number ;

-- Index statistics

l_ind_cnt number;

l_ind_leaf_blocks number;

l_ind_distinct number;

l_ind_clustering_factor number;

-- Saving index statistics in the DD using an autonomous transaction

procedure save_index_stats(

p_owner varchar2 ,

p_index varchar2)

is

pragma autonomous_transaction;

begin

dbms_stats.set_index_stats(ownname => p_owner,

indname => p_index,

numrows => l_ind_cnt,

numlblks => l_ind_leaf_blocks,

numdist => l_ind_distinct,

clstfct => l_ind_clustering_factor ,

no_invalidate => FALSE);

end;

-- Saving table or table partition statistics in the DD using an autonomous transaction

procedure save_table_stats(

p_owner varchar2 ,

p_table varchar ,

p_scope varchar2 ,

p_partname varchar2 ,

p_cnt number)

is

pragma autonomous_transaction;

begin

if p_scope = 'GLOBAL' then

dbms_stats.set_table_stats( ownname => p_owner ,

tabname => p_table ,

numrows => p_cnt ,

no_invalidate => FALSE);

else

dbms_stats.set_table_stats( ownname => p_owner ,

tabname => p_table ,

partname => p_partname ,

numrows => p_cnt,

no_invalidate => FALSE);

end if;

end;

-- Saving column statistics in the DD using an autonomous transaction

procedure save_col_stats(

p_owner varchar2 ,

p_table varchar ,

p_scope varchar2 ,

p_partname varchar2 )

is

pragma autonomous_transaction;

type colhist

is

table of varchar2(32000) index by binary_integer;

l_srec dbms_stats.statrec ;

l_novals dbms_stats.numarray;

l_chvals dbms_stats.chararray;

l_dtvals dbms_stats.datearray;

l_nulls number;

l_min_num number;

l_max_num number;

l_min_char varchar2(100);

l_max_char varchar2(100);

l_min_date date;

l_max_date date;

m_distcnt number;

m_density number;

m_nullcnt number;

m_avgclen number;

begin

-- Loops through all columns in the table

for i in (

select

column_name ,

column_id ,

data_type

from

dba_tab_columns

where

table_name = p_table

and owner = p_owner

order by

column_id

)

loop

-- Checks the data type.

if

i.data_type = 'NUMBER'

or i.data_type = 'FLOAT'

or i.data_type = 'VARCHAR2'

or i.data_type = 'DATE'

or substr(i.data_type,1,9) = 'TIMESTAMP' then

l_nulls :=l_tab_cnt - l_colcnt ( i.column_id);

l_srec.epc := 2;

l_srec.eavs := null;

-- Histograms are supported for tables only

if p_scope = 'GLOBAL' then

-- Checking for frequency histogram on that column

if l_colhist(i.column_id) = 'FREQUENCY' then

if i.data_type = 'NUMBER' then

-- Populate l_srec structure for NUMBER from the package variables

l_srec.epc:=l_colhiststats(i.column_id).bkvals.count;

l_srec.bkvals:=l_colhiststats(i.column_id).bkvals;

dbms_stats.prepare_column_values (l_srec, l_colhiststatsn(i.column_id));

elsif i.data_type = 'VARCHAR2' then

-- Populate l_srec structure for VARCHAR2 from the package variables

l_srec.epc :=l_colhiststats(i.column_id).bkvals.count;

l_srec.bkvals:=l_colhiststats(i.column_id).bkvals;

dbms_stats.prepare_column_values (l_srec, l_colhiststatsv(i.column_id));

elsif i.data_type = 'DATE' then

-- Populate l_srec structure for DATE from the package variables

l_srec.epc :=l_colhiststats(i.column_id).bkvals.count;

l_srec.bkvals:=l_colhiststats(i.column_id).bkvals;

dbms_stats.prepare_column_values (l_srec, l_colhiststatsd(i.column_id));

end if;

-- Checking for height balanced histogram on that column

elsif l_colhist(i.column_id) ='HEIGHT BALANCED' then

if i.data_type = 'NUMBER' then

-- Populate l_srec structure for NUMBER from the package variables

l_srec.epc :=l_colhiststatsn(i.column_id).count;

l_srec.bkvals:=null;

dbms_stats.prepare_column_values (l_srec, l_colhiststatsn(i.column_id));

elsif i.data_type = 'VARCHAR2' then

-- Populate l_srec structure for VARCHAR2 from the package variables

l_srec.epc :=l_colhiststatsv(i.column_id).count;

l_srec.bkvals:=null;

dbms_stats.prepare_column_values (l_srec, l_colhiststatsv(i.column_id));

elsif i.data_type = 'DATE' then

-- Populate l_srec structure for DATE from the package variables

l_srec.epc :=l_colhiststatsd(i.column_id).count;

l_srec.bkvals:=null;

dbms_stats.prepare_column_values (l_srec, l_colhiststatsd(i.column_id));

end if;

end if;

end if;

if ( i.data_type = 'NUMBER' or i.data_type = 'FLOAT' ) then

if l_colhist(i.column_id) is null then

-- Populate l_srec structure for NUMBER from the package variables

l_srec.bkvals :=null;

l_min_num:=to_number(l_colmin(i.column_id));

l_max_num:=to_number(l_colmax(i.column_id));

l_novals := dbms_stats.numarray (l_min_num,l_max_num) ;

dbms_stats.prepare_column_values (l_srec, l_novals);

end if;

elsif ( i.data_type = 'VARCHAR2' ) then

if l_colhist(i.column_id) is null then

-- Populate l_srec structure for VARCHAR2 from the package variables

l_srec.bkvals :=null;

l_min_char := substr(l_colmin(i.column_id),1,20);

l_max_char := substr( l_colmax(i.column_id), 1, 20) ;

l_chvals := dbms_stats.chararray( l_min_char , l_max_char);

dbms_stats.prepare_column_values (l_srec, l_chvals);

end if;

elsif ( i.data_type = 'DATE' or substr(i.data_type,1,9) = 'TIMESTAMP' ) then

if l_colhist(i.column_id) is null then

-- Populate l_srec structure for DATE from the package variables

l_srec.bkvals :=null;

l_min_date := to_date(l_colmin(i.column_id),'MM-DD-YYYY:HH24:MI:SS');

l_max_date := to_date( l_colmax(i.column_id), 'MM-DD-YYYY:HH24:MI:SS');

l_dtvals := dbms_stats.datearray( l_min_date , l_max_date);

dbms_stats.prepare_column_values (l_srec, l_dtvals);

end if ;

end if;

-- Write the statistics in into the data dictionary

if p_scope = 'GLOBAL' then

-- Table

dbms_stats.set_column_stats (ownname => p_owner , tabname => p_table, colname => i.column_name, distcnt => l_coldis(i.column_id) , nullcnt => l_nulls , srec => l_srec , no_invalidate => FALSE);

else

-- Partition

dbms_stats.set_column_stats (ownname => p_owner , tabname => p_table, partname => p_partname , colname => i.column_name, distcnt => l_coldis(i.column_id) , nullcnt => l_nulls , srec => l_srec , no_invalidate => FALSE);

end if;

end if;

end loop;

end;

-- Gather statistics for tables and partitions by issuing dynamically constructed SQLs

procedure gather_table_part_stats(

ownname varchar2 ,

tabname varchar2 ,

p_scope varchar2 ,

partname varchar2 default null ,

estimate_percent number default null ,

method_opt varchar2 default null ,

p_degree number default null)

as

--type l_numnum is table of number_number;

type l_numnum

is

table of number index by binary_integer;

l_colswithhistograms l_numnum ;

l_colswithhistogramssize l_numnum ;

l_table_q varchar2(32000);

l_out varchar2(32000);

l_column_q varchar2(32000);

l_rest varchar2(32000);

l_int_cnt number ;

l_dash_cnt number;

l_t varchar2(1000);

l_cnt number;

l_res_num number;

l_res_varchar2 varchar2(32000);

l_res_date date;

l_res_timestamp timestamp(9);

l_c_name number ;

l_d number;

col_cnt integer;

rec_tab dbms_sql.desc_tab2;

r number;

l_method_opt_parsing varchar2(32000);

l_column varchar2(32000);

l_col_id number;

l_col_sz varchar2(100);

begin

-- Parsing method_opt to get which columns should get histogrms

-- and how many buckets are to be used

-- I am sure there is a better way....

for i in (select column_id from dba_tab_columns where owner = ownname and table_name = tabname) loop

l_colswithhistograms(i.column_id):=0;

l_colswithhistogramssize(i.column_id):=0;

end loop;

l_method_opt_parsing:=ltrim(method_opt);

if upper(substr(l_method_opt_parsing,1,3)) = 'FOR' then

l_method_opt_parsing:=ltrim(substr(l_method_opt_parsing,4));

if upper(substr(l_method_opt_parsing,1,7)) = 'COLUMNS' then

l_method_opt_parsing:=ltrim(substr(l_method_opt_parsing,8));

while (length (l_method_opt_parsing) > 0) loop

if instr(l_method_opt_parsing,' ') = 0 and instr(l_method_opt_parsing,',') = 0 then

l_column:=upper(ltrim(l_method_opt_parsing));

else

if (instr(l_method_opt_parsing,' ') > 0 and instr(l_method_opt_parsing,',') = 0)

or (instr(l_method_opt_parsing,' ') > 0 and instr(l_method_opt_parsing,' ') < instr(l_method_opt_parsing,',') and instr(l_method_opt_parsing,',') > 0) then

l_column:=upper(ltrim(substr(l_method_opt_parsing,1,instr(l_method_opt_parsing,' '))));

else

l_column:=upper(ltrim(substr(l_method_opt_parsing,1,instr(l_method_opt_parsing,',')-1)));

end if;

end if;

select column_id

into l_col_id

from dba_tab_columns

where owner = ownname

and table_name = tabname

and column_name = ltrim(rtrim(l_column));

l_colswithhistograms(l_col_id):=1;

if instr(l_method_opt_parsing,' ') = 0 and instr(l_method_opt_parsing,',') = 0 then

l_method_opt_parsing:=null;

else

if (instr(l_method_opt_parsing,' ') > 0 and instr(l_method_opt_parsing,' ') < instr(l_method_opt_parsing,',')) or (instr(l_method_opt_parsing,',') = 0) then

l_method_opt_parsing:=ltrim(substr(l_method_opt_parsing,instr(l_method_opt_parsing,' ')));

else

l_method_opt_parsing:=ltrim(substr(l_method_opt_parsing,instr(l_method_opt_parsing,',')-1));

end if;

end if;

if upper(substr(l_method_opt_parsing,1,4)) = 'SIZE' then

l_method_opt_parsing:=ltrim(substr(l_method_opt_parsing,5));

if instr(l_method_opt_parsing,',') > 0 then

l_col_sz:=upper(ltrim(substr(l_method_opt_parsing,1,instr(l_method_opt_parsing,',')-1)));

else

l_col_sz:=upper(ltrim(l_method_opt_parsing));

end if;

l_colswithhistogramssize(l_col_id):=to_number(l_col_sz);

l_method_opt_parsing:=ltrim(substr(l_method_opt_parsing,instr(l_method_opt_parsing,',')));

end if ;

if instr(l_method_opt_parsing,',') = 0 then

l_method_opt_parsing:=null;

else

l_method_opt_parsing:=ltrim(substr(l_method_opt_parsing,instr(l_method_opt_parsing,',')+1));

end if;

end loop;

end if;

end if;

-- Building the query text in l_table_q

-- Startinng with count

if p_degree is null then

-- No parallelism

l_table_q:=' select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*) ';

else

-- With parallelism

l_table_q:=' select /*+ parallel('||p_degree||') dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*) ';

end if;

-- For each column in the table

for i in

(select

column_name ,

data_type

from

dba_tab_columns

where

owner = ownname

and table_name = tabname

order by

column_id

)

loop

-- Checking if the column type is among the supported types

if

i.data_type = 'NUMBER'

or i.data_type = 'FLOAT'

or i.data_type = 'VARCHAR2'

or i.data_type = 'DATE'

or substr(i.data_type,1,9) = 'TIMESTAMP' then

-- Add entries for number of not null records, number of distinct records, min and max values

l_table_q :=l_table_q||',count(t.'||i.column_name||')';

l_table_q :=l_table_q||',count(distinct(t.'||i.column_name||'))';

l_table_q :=l_table_q||',min(t.'||i.column_name||')';

l_table_q :=l_table_q||',max(t.'||i.column_name||')';

end if;

end loop;

if p_scope = 'GLOBAL' then

-- Add the "from" clause for a table

if estimate_percent is not null then

-- Add "sample" clause

l_table_q :=l_table_q||' from '||ownname||'.'||tabname||' sample ('||estimate_percent||' ) t' ;

else

-- No "sample" clause - full table scan

l_table_q:=l_table_q||' from '||ownname||'.'||tabname||' t' ;

end if;

else

-- Add the "from" clause for a partition

if estimate_percent is not null then

-- Add "sample" calause

l_table_q :=l_table_q||' from '||ownname||'.'||tabname||' partition ( '||partname||' ) sample ( '||estimate_percent||' ) t ' ;

else

-- No "sample" clause - full table scan

l_table_q:=l_table_q||' from '||ownname||'.'||tabname||' partition ( '||partname||' ) t ' ;

end if;

end if;

-- Create a cursor

l_c_name := dbms_sql.open_cursor;

-- Parse the SQL query in l_table_q

dbms_sql.parse(l_c_name, l_table_q, dbms_sql.native);

-- l_cnt would be used to go through all columns in the dynamically created SQL statement (l_table_q)

l_cnt:=1;

-- Defining the first column - the number of records in the table

dbms_sql.define_column (l_c_name, l_cnt ,l_res_num );

-- For each column in the table, in the same order as when l_table_q was generated

for i in

(select

column_name ,

data_type ,

data_length

from

dba_tab_columns

where

owner = ownname

and table_name = tabname

order by

column_id

) loop

-- Checking if the column type is among the supported types.

-- The check should be identical to the one used when generating the query

if

i.data_type = 'NUMBER'

or i.data_type = 'FLOAT'

or i.data_type = 'VARCHAR2'

or i.data_type = 'DATE'

or substr(i.data_type,1,9) = 'TIMESTAMP'

then

-- Defining the first of each column group - the number of non-null values

l_cnt :=l_cnt+1;

dbms_sql.define_column (l_c_name, l_cnt ,l_res_num );

-- Defining the second of each column group - the number of distinct values

l_cnt:=l_cnt+1;

dbms_sql.define_column (l_c_name, l_cnt ,l_res_num );

-- Since the next two columns are not necessary numbers

-- we have to check their type and supply the correct arguments to dbms_sql.define_column

if ( i.data_type = 'NUMBER'

or i.data_type = 'FLOAT') then

-- Define the the third and forth column as a number

l_cnt :=l_cnt+1;

dbms_sql.define_column (l_c_name, l_cnt ,l_res_num );

l_cnt:=l_cnt+1;

dbms_sql.define_column (l_c_name, l_cnt ,l_res_num );

elsif i.data_type = 'VARCHAR2' then

-- Define the the third and forth column as a varchar

l_cnt :=l_cnt+1;

dbms_sql.define_column (l_c_name, l_cnt ,l_res_varchar2 ,i.data_length);

l_cnt:=l_cnt+1;

dbms_sql.define_column (l_c_name, l_cnt ,l_res_varchar2 ,i.data_length);

elsif i.data_type = 'DATE' then

-- Define the the third and forth column as a date

l_cnt :=l_cnt+1;

dbms_sql.define_column (l_c_name, l_cnt ,l_res_date );

l_cnt:=l_cnt+1;

dbms_sql.define_column (l_c_name, l_cnt ,l_res_date );

elsif substr(i.data_type,1,9) = 'TIMESTAMP' then

-- Define the the third and forth column as a timestamp

l_cnt :=l_cnt+1;

dbms_sql.define_column (l_c_name, l_cnt ,l_res_timestamp );

l_cnt:=l_cnt+1;

dbms_sql.define_column (l_c_name, l_cnt ,l_res_timestamp );

end if;

end if;

end loop;

-- Execute the query

l_d := dbms_sql.execute(l_c_name);

-- Describe the returned columns

dbms_sql.describe_columns2(l_c_name ,col_cnt , rec_tab);

r:= dbms_sql.fetch_rows (l_c_name);

-- Fetch the first row. The query will return only one row

l_dash_cnt:=1;

-- Get the value of the first column - number of records into l_res_num

l_cnt :=1;

dbms_sql.column_value (l_c_name, l_cnt ,l_res_num );

-- Set the package variable

l_tab_cnt:=l_res_num;

-- For each column in the table, in the same order as when l_table_q was generated

for i in

(select

column_id,

column_name ,

data_type

from

dba_tab_columns

where

owner = ownname

and table_name = tabname

order by

column_id

)

loop

-- Checking if the column type is among the supported types.

-- The check should be identical to the one used when generating the query

if

i.data_type = 'NUMBER'

or i.data_type = 'FLOAT'

or i.data_type = 'VARCHAR2'

or i.data_type = 'DATE'

or substr(i.data_type,1,9) = 'TIMESTAMP'

then

-- Get the second column, number of non-null values, into l_res_num

l_cnt :=l_cnt+1;

dbms_sql.column_value (l_c_name, l_cnt ,l_res_num );

-- Save that number into package variable l_colcnt

-- l_colcnt is an array that would store the values for all columns

l_colcnt(i.column_id):=l_res_num;

-- Get the third column, number of distinct values, into l_res_num

l_cnt :=l_cnt+1;

dbms_sql.column_value (l_c_name, l_cnt ,l_res_num );

-- Save that number into package variable l_coldis

-- l_coldis is an array that would store the values for all columns

l_coldis(i.column_id):=l_res_num;

-- Since the next two columns are not necessary numbers

-- We have to convert them to CHAR and stored them in an array

if i.data_type = 'NUMBER' or i.data_type = 'FLOAT' then

-- Save the min number into l_res_num

-- convert l_res_num to CHAR save it into l_colmin, a package array that stores min values for all columns

l_cnt :=l_cnt+1;

dbms_sql.column_value (l_c_name, l_cnt ,l_res_num );

l_colmin(i.column_id):=to_char(l_res_num);

-- Save the max number into l_res_num

-- convert l_res_num to CHAR save it into l_colmax, a package array that stores min values for all columns

l_cnt :=l_cnt+1;

dbms_sql.column_value (l_c_name, l_cnt ,l_res_num );

l_colmax(i.column_id):=to_char(l_res_num);

elsif i.data_type = 'VARCHAR2' then

-- Save the min value into l_res_varchar2

-- convert l_res_varchar2 to CHAR save it into l_colmin, a package array that stores min values for all columns

l_cnt :=l_cnt+1;

dbms_sql.column_value (l_c_name, l_cnt ,l_res_varchar2 );

l_colmin(i.column_id):=l_res_varchar2 ;

-- Save the max value into l_res_varchar2

-- convert l_res_varchar2 to CHAR save it into l_colmax, a package array that stores max values for all columns

l_cnt :=l_cnt+1;

dbms_sql.column_value (l_c_name, l_cnt ,l_res_varchar2 );

l_colmax(i.column_id):=l_res_varchar2 ;

elsif (i.data_type = 'DATE' ) then

-- Save the min date into l_res_date

-- convert l_res_date to CHAR save it into l_colmin, a package array that stores min values for all columns

l_cnt :=l_cnt+1;

dbms_sql.column_value (l_c_name, l_cnt ,l_res_date);

l_colmin(i.column_id):=to_char(l_res_date,'MM-DD-YYYY:HH24:MI:SS');

-- Save the max date into l_res_date

-- convert l_res_date to CHAR save it into l_colmax, a package array that stores max values for all columns

l_cnt :=l_cnt+1;

dbms_sql.column_value (l_c_name, l_cnt ,l_res_date );

l_colmax(i.column_id) :=to_char(l_res_date,'MM-DD-YYYY:HH24:MI:SS');

elsif substr(i.data_type,1,9) = 'TIMESTAMP' then

-- Save the min timestamp into l_res_timestamp (Note - loss of precision may occur)

-- convert l_res_timestam to CHAR and save it into l_colmin, a package array that stores min values for all columns

l_cnt :=l_cnt+1;

dbms_sql.column_value (l_c_name, l_cnt ,l_res_timestamp);

l_colmin(i.column_id):=to_char(l_res_timestamp,'MM-DD-YYYY:HH24:MI:SS');

-- Save the max timestamp into l_res_timestamp (Note - loss of precision may occur)

-- convert l_res_timestam to CHAR and save it into l_colmax, a package array that stores max values for all columns

l_cnt :=l_cnt+1;

dbms_sql.column_value (l_c_name, l_cnt ,l_res_timestamp );

l_colmax(i.column_id):=to_char(l_res_timestamp,'MM-DD-YYYY:HH24:MI:SS');

end if;

-- Check if table or partition. Only tables can have histograms

if p_scope = 'GLOBAL' then

-- Check if a histogrms has to be created on the column

-- The package does not have any "AUTO" functionality, so the invoker must specifically request a histogram

if l_colswithhistograms (i.column_id) = 1 then

-- Deciding what type of histogram to create

if (l_colswithhistogramssize(i.column_id) > 1 and l_colswithhistogramssize(i.column_id) < 254 ) or (l_coldis(i.column_id) < 254) then

-- Creating frequency histogram

l_colhist(i.column_id) :='FREQUENCY';

if l_colswithhistogramssize(i.column_id) > 1 and l_colswithhistogramssize(i.column_id) < 254 then

if i.data_type = 'VARCHAR2' then

l_column_q :='SELECT * FROM ( SELECT * FROM (SELECT * FROM (SELECT substr('||i.column_name||',1,32) col , count(*) cnt from '||ownname||'.'||tabname||' group by SUBSTR('||i.column_name||',1,32) ) order by cnt desc ) where rownum <='||l_colswithhistogramssize(i.column_id)||') order by col';

else

l_column_q :='SELECT * FROM ( SELECT * FROM (SELECT * FROM (SELECT '||i.column_name||' col , count(*) cnt from '||ownname||'.'||tabname||' group by '||i.column_name||' ) order by cnt desc ) where rownum <='||l_colswithhistogramssize(i.column_id)||') order by col';

end if;

else

-- Specify the SQL text of the query that would get the frequency data

if i.data_type = 'VARCHAR2' then

l_column_q :='SELECT substr('||i.column_name||',1,32) , count(*) from '||ownname||'.'||tabname||' group by substr('||i.column_name||',1,32) order by '||i.column_name;

else

l_column_q :='SELECT '||i.column_name||' , count(*) from '||ownname||'.'||tabname||' group by '||i.column_name||' order by '||i.column_name;

end if;

end if;

-- Since the data type of the first column of the query would depend on the column type

-- we need to have different statement for each supported data type

if i.data_type = 'NUMBER' then

-- Store the frequency data, which it is itself an array , into a package variables l_colhiststatsn and l_colhiststats

execute immediate l_column_q bulk collect into l_colhiststatsn(i.column_id) ,l_colhiststats (i.column_id).bkvals ;

elsif i.data_type = 'VARCHAR2' then

-- Store the frequency data, which it is itself an array , into a package variables l_colhiststatsv and l_colhiststats

execute immediate l_column_q bulk collect into l_colhiststatsv(i.column_id) ,l_colhiststats (i.column_id).bkvals ;

elsif i.data_type = 'DATE' then

-- Store the frequency data, which it is itself an array , into a package variables l_colhiststatsv and l_colhiststats

execute immediate l_column_q bulk collect into l_colhiststatsd(i.column_id) ,l_colhiststats (i.column_id).bkvals ;

end if;

else

-- Creating hieght balanced histograms

l_colhist(i.column_id):='HEIGHT BALANCED';

if i.data_type = 'VARCHAR2' then

l_column_q :='SELECT VAL FROM (select val , bkt , lag(bkt,1) over (order by val) pr from ( select substr('||i.column_name||',1,32) val , ntile(256) over (order by '||i.column_name||') bkt

from '||ownname||'.'||tabname||' )) where bkt > pr order by bkt';

else

l_column_q :='SELECT VAL FROM (select val , bkt , lag(bkt,1) over (order by val) pr from ( select '||i.column_name||' val , ntile(256) over (order by '||i.column_name||') bkt

from '||ownname||'.'||tabname||' )) where bkt > pr order by bkt';

end if;

if i.data_type = 'NUMBER' then

execute immediate l_column_q bulk collect into l_colhiststatsn(i.column_id);

elsif i.data_type = 'VARCHAR2' then

execute immediate l_column_q bulk collect into l_colhiststatsv(i.column_id);

elsif i.data_type = 'DATE' then

execute immediate l_column_q bulk collect into l_colhiststatsd(i.column_id);

end if;

end if;

else

-- Set the l_colhist flag to indicate there would be no histogram for that column

l_colhist(i.column_id):=null;

end if;

else

l_colhist(i.column_id):=null;

end if;

end if;

end loop;

-- Save the collected table statistics into the data dictionaty using an autonomous transaction

save_table_stats (ownname , tabname , p_scope , partname , l_tab_cnt);

-- Save the collected column statistics into the data dictionaty using an autonomous transaction

save_col_stats (ownname , tabname , p_scope , partname );

-- Check is table or partition. If table will gather index statistics

if p_scope = 'GLOBAL' then

-- Loop though all indexes that belong to the table

for inds in

(select

index_name

from

dba_indexes

where

owner = ownname

and table_name = tabname

and index_type > 'LOB'

)

loop

-- Call the procedure that gathers index stats

gather_index_stats(ownname , inds.index_name);

null;

end loop;

end if;

end gather_table_part_stats;

procedure gather_table_stats(

ownname varchar2 ,

tabname varchar2 ,

partname varchar2 default null ,

estimate_percent number default null,

method_opt varchar2 default null ,

degree number default null ,

granularity varchar2 default 'AUTO' )

as

l_part varchar2(100);

begin

-- Is the table partitioned?

select

partitioned

into

l_part

from

dba_tables

where

owner = ownname

and table_name = tabname ;

if l_part = 'NO' then

-- If table not partitioned then gather stats for the whole table

gather_table_part_stats ( ownname , tabname , 'GLOBAL', partname ,estimate_percent ,method_opt , degree );

else

-- If table is partitioned then we have look at granurality

if granularity = 'GLOBAL' then

-- gather stats on the whole table

gather_table_part_stats ( ownname , tabname ,'GLOBAL' , partname ,estimate_percent ,method_opt , degree );

elsif granularity = 'PARTITION' then

-- gather stats on the spacified partition

gather_table_part_stats ( ownname , tabname ,'PARTITION' , partname ,estimate_percent ,method_opt , degree );

elsif granularity = 'ALL' or granularity = 'GLOBAL AND PARTITION' then

-- gather stats on the whole table

gather_table_part_stats ( ownname , tabname ,'GLOBAL' , partname ,estimate_percent ,method_opt , degree );

-- and gather stats on every partition

for part in

(select partition_name

from dba_tab_partitions

where table_owner = ownname

and table_name = tabname

)

loop

gather_table_part_stats ( ownname , tabname ,'PARTITION' , part.partition_name ,method_opt , degree );

end loop;

else

-- if no granurality specified all stats (table + all partitions) are gathered

gather_table_part_stats ( ownname , tabname ,'GLOBAL' , partname , estimate_percent ,method_opt , degree );

for part in

(select partition_name

from dba_tab_partitions

where table_owner = ownname

and table_name = tabname

)

loop

gather_table_part_stats ( ownname , tabname ,'PARTITION' , part.partition_name , estimate_percent ,method_opt , degree );

end loop;

end if;

end if;

end;

procedure gather_index_stats(

p_owner varchar2 ,

p_index varchar2)

is

l_index_q varchar2(32000);

l_obj_id number;

l_num_ind_columns number ;

l_table_name varchar2(30);

l_inv_columns number;

begin

-- This procedure is able to gather index stats only on

-- indexes that are on NUMBER','VARCHAR2','DATE','FLOAT and TIMESTAMP columns

select

count(*)

into

l_inv_columns

from

dba_ind_columns a ,

dba_tab_columns b

where

a.index_name = p_index

and a.index_owner = p_owner

and a.index_owner = b.owner

and a.column_name = b.column_name

and ( b.data_type not in ('NUMBER','VARCHAR2','DATE','FLOAT'))

and substr(b.data_type,1,9) > 'TIMESTAMP' ;

if l_inv_columns = 0 then

-- Construct the dynamic SQL

-- Starting with number of records and numner of leaf nodes

l_index_q :='select /*+ no_parallel_index(t,"I_O") dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index(t,"'||p_index||'") */ count(*) as nrw,count(distinct sys_op_lbid(';

-- The object ID of the index is needed for the query

select

object_id

into

l_obj_id

from

dba_objects

where

object_name = p_index

and owner = p_owner

and object_type = 'INDEX' ;

l_index_q :=l_index_q||l_obj_id||',''L'',t.rowid)) as nlb,count(distinct ';

-- Now building the clause that would compute the number of distinct entries

-- Find the number of columns in the index

select

count(*)

into

l_num_ind_columns

from

dba_ind_columns

where

index_name = p_index

and index_owner = p_owner;

if l_num_ind_columns = 1 then

-- If index is only on one column

for cols in

(select

column_name

from

dba_ind_columns

where

index_name = p_index

and index_owner = p_owner

)

loop

-- Should fire only ones

l_index_q:=l_index_q||'"'||cols.column_name||'")';

end loop;

else

for cols in

(select

column_name ,

column_position

from

dba_ind_columns

where

index_name = p_index

and index_owner = p_owner

order by

column_position

)

loop

if cols.column_position = 1 then

-- special rule for the first column. This is to produce syntactically correct statement

l_index_q :=l_index_q||' hextoraw(sys_op_descend( "'||cols.column_name||'")';

else

l_index_q:=l_index_q||'||sys_op_descend( "'||cols.column_name||'")';

end if;

end loop;

l_index_q:=l_index_q||'))';

end if;

select

table_name

into

l_table_name

from

dba_indexes

where

index_name = p_index

and owner = p_owner;

-- Adding the clause for the clustering factor

l_index_q :=l_index_q||' , sys_op_countchg(substrb(t.rowid,1,15),1) as clf from '||p_owner||'.'||l_table_name||' t ';

-- Going through all records and adding a "not NULL" clause

for cols in

(select

column_name ,

column_position

from

dba_ind_columns

where

index_name = p_index

and index_owner = p_owner

order by

column_position

)

loop

if cols.column_position = 1 then

l_index_q :=l_index_q||' where '||cols.column_name||' is not NULL ';

else

l_index_q:=l_index_q||' and '||cols.column_name||' is not NULL ';

end if;

end loop;

-- Execute the query and store the results in package variables

execute immediate l_index_q into l_ind_cnt ,

l_ind_leaf_blocks ,

l_ind_distinct ,

l_ind_clustering_factor ;

-- Saving the above gathered statistics in the data dictionary

save_index_stats (p_owner , p_index ) ;

end if;

end;

end just_stats;