This script, prepfiles.sh, is a powerful utility for Oracle Database Administrators designed to automate the generation of a comprehensive SQL toolkit for managing and testing database statistics.
By reading a list of tables from mtx_tablelist.lst, the script creates a sequenced set of SQL files that handle the entire lifecycle of Pending Statistics. This allows DBAs to gather and validate new statistics in a “private” area before making them visible to the optimizer, reducing the risk of unexpected execution plan changes.
# config parameters
# Need to provide filename that has all tables in 'OWNER','TABLE_NAME' fomrat.Here it is tablelist.lst
# Provide backup stats table name
# modify 01_cr_stat_table.sql for that backup stats table creation.
# stat table creation systax: exec dbms_stats.create_stat_table('OWNER', 'TABLE_NAME');
#touching file
touch 01_cr_stat_table.sql
sleep 1
# preset.sql file creation
cat <<EOD00 > preset.sql
set time on timing on echo on
EOD00
# file# 2 creation
sed 's/^/exec dbms_stats.export_table_stats( /' tablelist.lst > tempfile
sed "s/$/, NULL, 'DMS_STAT_TABLE_10MAR2026');/" tempfile > tempfile1
cat preset.sql tempfile1 > 02_exp_stats.sql
sleep 1
#cp 03_view_stat_history_reten.sql 03_view_stat_history_reten.sql
#./cr_03_view_stat_history_reten.sh
cat <<EOD1 > 03_view_stat_history_reten.sql
--check default retention
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
--Change default retention for statistics
--SQL> execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (120);
--Where xx=number of days to retain stats.
-- Check stats availability
select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
EOD1
sleep 1
# file# 4 creation
sed "s/^/select dbms_stats.get_prefs('PUBLISH', /" tablelist.lst > tempfile
sed 's/$/) from dual;/' tempfile > tempfile1
cat preset.sql tempfile1 > 04_check_publish_prefs.sql
# file# 5 creation
sed 's/^/exec dbms_stats.set_table_prefs( /' tablelist.lst > tempfile
sed "s/$/, 'PUBLISH', 'FALSE');/" tempfile > tempfile1
cat preset.sql tempfile1 > 05_set_publish_pref.sql
sleep 1
# file# 6 creation
sed "s/^/select dbms_stats.get_prefs('PUBLISH', /" tablelist.lst > tempfile
sed 's/$/) from dual;/' tempfile > tempfile1
cat preset.sql tempfile1 > 06_check_publish_prefs.sql
sleep 1
# file#7 creation
#cp 07_check_pending_param.sql 07_check_pending_param.sql
cat << EOD2 > 07_check_pending_param.sql
-- This parameter should be false for now. We need to make it true for optimize to use pending stats later.
show parameter OPTIMIZER_USE_PENDING_STATISTICS
EOD2
sleep 1
#file#8 creation
#cp 08_check_existing_pending_stats.sql 08_check_existing_pending_stats.sql
cat << EOD3 > 08_check_existing_pending_stats.sql
-- Need to check if anything else is using pening stats in db or not.
-- following should return 0 rows now.
select * from dba_tab_pending_stats;
EOD3
sleep 1
# file# 9 creation
sed 's/,/, tabname => /' tablelist.lst > tempfile
sed 's/^/exec dbms_stats.gather_table_stats(ownname => /' tempfile > tempfile1
sed 's/$/, estimate_percent => 100, degree => 4, cascade => true, no_invalidate => true); /' tempfile1 > tempfile
cat preset.sql tempfile > 09_gather_stats.sql
sleep 1
#file 10 creation
#cp 10_confirm_pending_stats.sql 10_confirm_pending_stats.sql
cat << EOD4 > 10_confirm_pending_stats.sql
--The last analyzed for these tables will not be changed as stats are gathered in pending area.
col owner format a20
col table_name format a30
col last_analyzed format a30
alter session set nls_date_format='DD-MON-RRRR:HH24:MI:SS';
select owner, table_name, num_rows, avg_row_len, last_analyzed from dba_tables
where (owner, table_name) in
(
select owner, table_name from dba_tab_pending_stats;
);
-- You can see the pending stats gathered in dba_tab_pending_stats table.
select owner, table_name, last_analyzed from dba_tab_pending_stats;
EOD4
sleep 1
#file#11 creation
#cp 11_use_pending_stats.sql 11_use_pending_stats.sql
cat << EOD5 > 11_use_pending_stats.sql
-- try to see from session level or system level.
--alter session set OPTIMIZER_USE_PENDING_STATISTICS = true;
--or
--alter system set OPTIMIZER_USE_PENDING_STATISTICS = true scope=both sid='*';
EOD5
sleep 1
# file #12 creation
sed 's/^/exec dbms_stats.publish_pending_stats(/' tablelist.lst > tempfile
sed 's/$/);/' tempfile > tempfile1
cat preset.sql tempfile1 > 12_publish_pendning_stats.sql
# file #13 creation
sed 's/^/exec dbms_stats.delete_pending_stats(/' tablelist.lst > tempfile
sed 's/$/);/' tempfile > tempfile1
cat preset.sql tempfile1 > 13_ROLLBACK_ONLY_delete_pendning_stats.sql
# file #14 creation
sed 's/,/, tabname => /' tablelist.lst > tempfile
sed 's/^/exec dbms_stats.import_table_stats(ownname => /' tempfile > tempfile1
sed "s/$/, stattab => 'STAT_TABLE_PHASE21'); /" tempfile1 > tempfile
cat preset.sql tempfile > 14_ROLLBACK_ONLY_imp_table_stats.sql
# file #15 creation
sed 's/,/, tabname => /' tablelist.lst > tempfile
sed 's/^/Execute DBMS_STATS.RESTORE_TABLE_STATS ( /' tempfile > tempfile1
sed "s/$/ , sysdate - 10); /" tempfile1 > tempfile
cat preset.sql tempfile > 15_ROLLBACK_ONLY_restore_from_stats_history.sql
rm tempfile tempfile1