LISTING 1: This script, skewDscvInst.sql, generates a script called histAnalyzeProbe.sql, which performs the initial statistical analysis of your database.
/* 
Filename : skewDscvInst.sql	skewed data distribution discoverer installation
Author    : Julius J.Y. Lin		01/30/98
Purpose   : work area installation script for determining if data distribution in tables
	    is skewed (=uneven distribution) or not, and plug the information into 
	    skew_discovery.distribution. Eventually the bucket size of this 
	    finding will be put in CONDI_ANALYZE_CTRL. HSTGRM_BUCKETS
*/
-- run create table only once to get inverse density (the wider x length the lower density) from 
-- dba_histograms.endpoint_value
-- this table keeps the number of buckets for every column and status of its data distribution.
drop table skew_discovery ;
CREATE TABLE skew_discovery (  
	owner			varchar2(30),
	table_name		varchar2(30),
	column_name		varchar2(30),
	std_dev			number,		--standard deviation
	mean			number,
	table_buckets		number,		-- number to plug in condi_analyze_ctrl
	column_buckets		number, 		-- actual buckets generated by ANALYZE
	distribution		varchar2(8) 	-- either 'SKEW' or 'null' for evenly distrib
) 
TABLESPACE  data 	--replace the tablespace name data or omit this option
;
ALTER TABLE skew_discovery ADD CONSTRAINT skew_discovery_PK  
PRIMARY KEY ( owner, table_name, column_name ) 
USING INDEX
TABLESPACE  indx 	--replace the tablespace name indx or omit this option
;
-- this table records the Bucket-Density for each bucket, and prepares for the calculation
-- of standard deviation
drop table skew_discovery_detail ;
CREATE TABLE skew_discovery_detail (  
	owner			varchar2(30),
	table_name		varchar2(30),
	column_name		varchar2(30),
	bucket_number		number,
	Bucket_Density		number 		-- changed width at X coordinate
)
TABLESPACE  data 	--replace the tablespace name data or omit this option
 ;
-- in the next select statement you can set size between 75 and 254
-- or sample between 25 and 40 percent. 

set head off echo off feedback off timming off pagesize 0 linesize 132
spool histAnalyzeProbe.sql
select 'analyze table ' || owner ||'.'|| table_name ||
         ' estimate statistics for all columns size 75 sample 40 percent ;'
from   dba_tables
where  owner not in ('SYS', 'SYSTEM', 'MDSYS' )
;	-- you can change 'not in' to 'in' or = the schema you want
	-- MDSYS is to exclude the Spatial Data Option (Multi-Dimension) SYS user
spool off
prompt 'run the script histAnalyzeProbe.sql during off peak time, you may'
prompt 'change sample 40 percent to compute statistic for more precise result'
set document off