GRANT CREATE SESSION TO &&user;
GRANT ANALYZE ANY TO &&user;
GRANT ANALYZE ANY DICTIONARY TO &&user;
GRANT SELECT ANY TABLE TO &&user;
GRANT SELECT ANY SEQUENCE TO &&user;
GRANT SELECT ANY DICTIONARY TO &&user;
GRANT EXECUTE ON DBMS_STATS TO &&user;
GRANT SELECT ON DBA_TAB_MODIFICATIONS TO &&user;
GRANT CREATE JOB TO &&user;
GRANT SCHEDULER_ADMIN TO &&user;
GRANT ALL ON SYS.GATHER_STATS_JOB TO &&user;
GRANT ALL ON SYS.AUTO_SPACE_ADVISOR_JOB TO &&user;

CREATE OR REPLACE PACKAGE &&user..dba_stats_maint_pkg
/* Package is intended for use when managing
the optimizer statistics for 9i and 10g
databases. Once on 11g, each tables' statistics
setting can be customized and used by Oracle's automatic
job

Assumptions:

For 10g, STATISTICS_LEVEL is set to a level
above basic, so that DBA_TAB_MODIFICATIONS
is populated (i.e. tables are monitored)

For 9i, relevant tables have their MONITORING
attribute set, so that DBA_TAB_MODIFICATIONS
view is populated

This package is either compiled as a privileged user
and/or it is compiled under someone else and
all applicable grants on system packages and
dynamic performance views are made

Has a dependency to dbms_lock.sleep procedure - this is assumed to be present

Explicit ANALYZE ANY grant has been made to DB_UTIL user
*/
AS
--Globals change factor setting
--This defines what % a segment must change by in order to update its statistics
gn_change_factor number(16,2) := 5.00;

--Removes all Statistic for the Database
PROCEDURE delete_all_stats;

--Procedure that disables the automatic gathering of stats
--since, until 10g, this 'canned' procedure is not adequate
--to gather statistics responsibly
PROCEDURE disable_auto_stats_job;

--Procedure that disables the automatic segment space advisor job
--The running of this job can use up a lot of resources, hence
--why many disable it
PROCEDURE disable_auto_space_job;

--Procedure gathers stats for SYSTEM and SYS schemas
--Basically a wrapped calls to the applicable DBMS_STATS procedure
PROCEDURE gather_data_dict_stats;

--Procedure gathers fixed object stats
--Whereas, data dictionary stats should be gathers constantly
--fixed object stats only need to be gathered after upgrades, or
--if the profile of the database's usage changes significantly

PROCEDURE gather_fixed_stats;

--Procedure that gathers non-fixed, non-locked table, index, partition, and subpartition statistics
--based upon whether there have been significant changes (i.e. DBA_TAB_MODIFICATIONS)
--and or whether stats are empty
--Package level defaults can be modified for your
PROCEDURE gather_stats;

--Procedure makes internal calls to first delete all stats
--and then gather them, including a gathering of system statistics
PROCEDURE start_over;
END dba_stats_maint_pkg;
/

SHOW ERRORS;



CREATE OR REPLACE PACKAGE BODY &&user..dba_stats_maint_pkg
/* Package is intended for use when managing
the optimizer statistics for 9i and 10g
databases. Once on 11g, each tables' statistics
setting can be customized and used by Oracle's automatic
job

Assumptions:

For 10g, STATISTICS_LEVEL is set to a level
above basic, so that DBA_TAB_MODIFICATIONS
is populated (i.e. tables are monitored)

For 9i, relevant tables have their MONITORING
attribute set, so that DBA_TAB_MODIFICATIONS
view is populated

This package is either compiled as a privileged user
and/or it is compiled under someone else and
all applicable grants on system packages and
dynamic performance views are made

*/
AS
--Removes all Statistic for the Database
PROCEDURE delete_all_stats
AS
BEGIN
DBMS_STATS.delete_dictionary_stats;
DBMS_STATS.delete_database_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;

--Procedure that disables the automatic gathering of stats
--since, until 10g, this 'canned' procedure is not adequate
--to gather statistics responsibly

PROCEDURE disable_auto_stats_job
AS
BEGIN
dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
END;

--Procedure that disables the automatic segment space advisor job
--The running of this job can use up a lot of resources, hence
--why many disable it

PROCEDURE disable_auto_space_job
AS
BEGIN
dbms_scheduler.disable('SYS.AUTO_SPACE_ADVISOR_JOB');
END;

PROCEDURE gather_data_dict_stats
AS
BEGIN
DBMS_STATS.gather_dictionary_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;

PROCEDURE gather_fixed_stats
AS
BEGIN
DBMS_STATS.gather_fixed_objects_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;

PROCEDURE gather_stats
AS
/* Cursor with the names of all the non-partitioned, non-system, non-locked, non-iot overflow type segments
which don't currently have statistics */
lv_owner dba_tables.owner%TYPE;
lv_table_name dba_tables.table_name%TYPE;
lv_part_name dba_tab_partitions.partition_name%TYPE;
lv_change_factor NUMBER (16, 2);
lv_partitioned VARCHAR (10);

CURSOR lc_ns_np_nostats
IS
SELECT owner, table_name
FROM dba_tables
WHERE partitioned = 'NO'
AND NOT owner IN ('SYS','SYSTEM','OUTLN')
AND num_rows IS NULL
AND iot_type IS NULL
AND NOT (owner, table_name) IN (SELECT owner, table_name
FROM dba_tab_statistics
WHERE stattype_locked = 'ALL')
AND NOT (owner, table_name) IN (SELECT owner, table_name
FROM dba_external_tables);

CURSOR lc_ns_p_nostats
IS
SELECT dtp.table_owner, dtp.table_name, dtp.partition_name
FROM dba_tab_partitions dtp, dba_tables dt
WHERE dtp.num_rows IS NULL
AND dtp.table_name = dt.table_name
AND dtp.table_owner = dt.owner
AND dt.iot_type IS NULL
AND NOT dtp.table_owner IN ('SYS','SYSTEM','OUTLN')
AND NOT (dtp.table_owner, dtp.table_name) IN (
SELECT owner, table_name
FROM dba_tab_statistics
WHERE stattype_locked =
'ALL')
AND NOT (dtp.table_owner, dtp.table_name) IN (SELECT owner, table_name
FROM dba_external_tables);

CURSOR lc_ss
IS
SELECT dtm.table_owner, dtm.table_name, dtm.partition_name,
ROUND ( (dtm.inserts + dtm.updates + dtm.deletes)
/ dt.num_rows,
2
)
* 100 "CHANGE_FACTOR",
dt.partitioned
FROM sys.dba_tab_modifications dtm, dba_tables dt
WHERE dtm.table_owner = dt.owner
AND dtm.table_name = dt.table_name
AND NOT dtm.table_owner IN ('SYS','SYSTEM','OUTLN')
AND NOT dt.num_rows IS NULL
AND iot_type IS NULL
AND ( (dt.partitioned = 'YES' AND NOT dtm.partition_name IS NULL
)
OR (dt.partitioned = 'NO' AND dtm.partition_name IS NULL)
)
AND NOT (dtm.table_owner, dtm.table_name) IN (
SELECT dts.owner, dts.table_name
FROM dba_tab_statistics dts
WHERE dts.stattype_locked =
'ALL')
AND NOT (dtm.table_owner, dtm.table_name) IN (
SELECT det.owner,
det.table_name
FROM dba_external_tables det);
BEGIN
/**************************BEGIN EMPTYSTATS************************************/

-- First process the cursor above; alternatively, you could
-- do something similar via a call to DBMS_STATS with the 'GATHER EMPTY' option

OPEN lc_ns_np_nostats; -- open the cursor before fetching

LOOP
FETCH lc_ns_np_nostats
INTO lv_owner, lv_table_name; -- fetches 2 columns into variables

EXIT WHEN lc_ns_np_nostats%NOTFOUND;
-- Call stats package
DBMS_OUTPUT.put_line ( 'Gathering Stats for Table and Indexes of '
|| lv_owner
|| '.'
|| lv_table_name
|| ' because they are empty...'
);
-- For these tables we will use most of the oracle defaults
-- We are assuming that partitioned tables are to be treated differently in terms
-- of sample size, parallelism degree and the like
--trap any errors by placing in its own block
BEGIN
DBMS_STATS.gather_table_stats
(ownname => lv_owner,
tabname => lv_table_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
estimate_percent => DBMS_STATS.auto_sample_size
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
END LOOP;

CLOSE lc_ns_np_nostats;

-- Next we process table partitions whose statistics are null/empty
-- We will use smaller samples and parallelism when appropriate

OPEN lc_ns_p_nostats; -- open the cursor before fetching

LOOP
FETCH lc_ns_p_nostats
INTO lv_owner, lv_table_name, lv_part_name;

-- fetches 3 columns into variables
EXIT WHEN lc_ns_p_nostats%NOTFOUND;
-- Call stats package
DBMS_OUTPUT.put_line ( 'Gathering Stats for Partition '
|| lv_owner
|| '.'
|| lv_table_name
|| '.'
|| lv_part_name
|| ' because they are empty...'
);

-- For these partitioned tables we will use smaller sample sizes
-- Trap Errors in anonymous block

BEGIN
DBMS_STATS.gather_table_stats (ownname => lv_owner,
tabname => lv_table_name,
partname => lv_part_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
DEGREE => 1,
estimate_percent => .00001
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;

END LOOP;

CLOSE lc_ns_p_nostats;

--Execute Global Database Stats Gathering with GATHER EMPTY option
--in order to catch any objects that were missed above because of errors, etc.
DBMS_OUTPUT.put_line ('Executing Global Stats Procedure to Catch Any Missed Empty Objects...');

BEGIN
dbms_stats.gather_database_stats(method_opt=>'for all columns',
granularity=>'AUTO',
estimate_percent=>DBMS_STATS.auto_sample_size,
degree=>DBMS_STATS.default_degree,
cascade=>DBMS_STATS.auto_cascade,
options=>'GATHER EMPTY');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
/**************************END EMPTYSTATS************************************/

-- Lastly we flush the database monitoring information
-- and then use the information therein to gather stats
-- on significantly changed objects

/**************************BEGIN CHANGED OBJECTS STATS***********************/

DBMS_STATS.flush_database_monitoring_info ();

OPEN lc_ss; -- open the cursor before fetching

LOOP
FETCH lc_ss
INTO lv_owner, lv_table_name, lv_part_name, lv_change_factor,
lv_partitioned;

EXIT WHEN lc_ss%NOTFOUND;

-- If change factor is greater than change factor defined in the package header then we will analyze the changed object in question
IF lv_change_factor >= gn_change_factor
THEN
-- Now determine whether the Object is a partition
IF lv_partitioned = 'YES'
THEN
DBMS_OUTPUT.put_line
( 'Gathering Stats for Partition '
|| lv_owner
|| '.'
|| lv_table_name
|| '.'
|| lv_part_name
|| ' because its change factor exceeds '||gn_change_factor||'%...'
);
BEGIN
DBMS_STATS.gather_table_stats
(ownname => lv_owner,
tabname => lv_table_name,
partname => lv_part_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
DEGREE => 1,
estimate_percent => .00001
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
ELSE
DBMS_OUTPUT.put_line
( 'Gathering Stats for Table and Indexes of '
|| lv_owner
|| '.'
|| lv_table_name
|| ' because its change factor exceeds '||gn_change_factor||'%...'
);
BEGIN
DBMS_STATS.gather_table_stats
(ownname => lv_owner,
tabname => lv_table_name,
granularity => 'AUTO',
method_opt => 'for all columns',
CASCADE => DBMS_STATS.auto_cascade,
estimate_percent => DBMS_STATS.auto_sample_size
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
END IF;
ELSE
NULL;
END IF;
END LOOP;

CLOSE lc_ss;

/**************************END CHANGED OBJECTS STATS**************************/
END;

--Procedure makes internal calls to first delete all stats
--and then gather them all over again

PROCEDURE start_over
AS
BEGIN
DBMS_OUTPUT.put_line ('Deleting all stats...');
delete_all_stats;
DBMS_OUTPUT.put_line ('Gathering fixed stats...');
gather_fixed_stats;
DBMS_OUTPUT.put_line ('Gathering user stats...');
gather_stats;
DBMS_OUTPUT.put_line ('Gathering dictionary stats...');
gather_data_dict_stats;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
END dba_stats_maint_pkg;
/

SHOW ERRORS;