Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#! /usr/local/bin/perl
# script: prod3_schema_stats_job_1.pl (renamed from 56_win_901_prod3_schema_stats_job_1.pl)
# Features: This perl script generates sql scripts which are then run
# in order to gather schema statistics for all schemas except SYS and SYSTEM.
#
# Output Files: prod3_schema_stats_schemas_get_1.sql
# prod3_schema_stats_gather_schema_stats_1.sql
# c:\backup\prod3_schema_stats_job_1.log
#
# Script Sequence#: 56
# Used By: run automatically via AT scheduler
# Usage:
# ******** Gather Oracle schema CBO stats weekly on Fridays at 9:00PM ********
# AT 21:00 /every:F c:\server_scripts\prod3_schema_stats_job_1.bat
#
# Copyright 2002 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 12-22-2001 dsimpson Initial Release
# 12-27-2001 dsimpson Added ORACLE_SID environment variable
#
# This output file was created by Installgen version 1.38 on Thu Nov 14 17:16:25 2002. By .com Solutions Inc. www.dotcomsolutionsinc.net
use strict;
# insure that environment variable is used by this perl script
$ENV{'ORACLE_SID'} = "PROD3";
my @proglist = '';
my $tempsqlcode="";
my $temp_sql_filename="temp_sql.sql";
# write the SQL file which obtains the list of schemas
open (FILE1,">c:\\server_scripts\\prod3_schema_stats_schemas_get_1.sql") || die ("Could not open output file c:\\server_scripts\\prod3_schema_stats_schemas_get_1.sql for writing. \n Does the full directory path exist?");
print FILE1 "-- script: prod3_schema_stats_schemas_get_1.sql\n";
print FILE1 "-- Features: This sql script obtains a list of schemas which\n";
print FILE1 "-- will get analyzed with dbms_stats.\n";
print FILE1 "-- Output File: prod3_schema_stats_gather_schema_stats_1.sql \n";
print FILE1 "--\n";
print FILE1 "-- Copyright 2002 by .com Solutions Inc.\n";
print FILE1 "--\n";
print FILE1 "-- ---------------------- Revision History ---------------\n";
print FILE1 "-- Date By Changes\n";
print FILE1 "-- 12-07-2001 dsimpson Initial Release\n";
print FILE1 " \n";
print FILE1 "set echo off\n";
print FILE1 "set feedback off\n";
print FILE1 "set verify off\n";
print FILE1 "set pagesize 0\n";
print FILE1 "set linesize 250\n";
print FILE1 "spool c:\\server_scripts\\prod3_schema_stats_gather_schema_stats_1.sql\n";
print FILE1 "select '-- script: prod3_schema_stats_gather_schema_stats_1.sql ' from dual;\n";
print FILE1 "select '-- Features: This sql script analyzes all schemas with dbms_stats' from dual;\n";
print FILE1 "select '-- except the SYS and SYSTEM schemas. ' from dual;\n";
print FILE1 "select '-- ' from dual;\n";
print FILE1 "select '-- Used By: executed by 56_win_901_prod3_schema_stats_job_1.pl ' from dual;\n";
print FILE1 "select '-- ' from dual;\n";
print FILE1 "select '-- Copyright 2002 by .com Solutions Inc. ' from dual;\n";
print FILE1 "select '-- ' from dual;\n";
print FILE1 "select '-- ---------------------- Revision History --------------- ' from dual;\n";
print FILE1 "select '-- Date By Changes ' from dual;\n";
print FILE1 "select '-- 12-07-2001 dsimpson Initial Release ' from dual;\n";
print FILE1 "select '-- 12-22-2001 dsimpson Added exclusion of SYS_IOT_OVER_ tables. ' from dual;\n";
print FILE1 "select '-- ' from dual;\n";
print FILE1 "select 'set echo on ' from dual;\n";
print FILE1 "select '-- analyze tables only to validate structure of the table and its indexes' from dual;\n";
print FILE1 "select 'analyze table ' || owner || '.' || table_name || ' validate structure cascade;' from dba_all_tables where owner not in ('SYS','SYSTEM') and table_name not like ('%SYS_IOT_OVER_%');\n";
print FILE1 "select '-- generate stats for all schemas except SYS and SYSTEM' from dual;\n";
print FILE1 "select 'exec dbms_stats.gather_schema_stats(ownname=>' || CHR(39) || username || CHR(39) || ', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>' || CHR(39) || 'AUTO' || CHR(39) || ', degree=>DBMS_STATS.DEFAULT_DEGREE, granularity=>' || CHR(39) || 'DEFAULT' || CHR(39) || ', cascade=>TRUE);' || CHR(10) from dba_users where username not in ('SYS','SYSTEM');\n";
print FILE1 "spool off\n";
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
set feedback on
set verify on
set pagesize 24
-- exec dbms_stats.gather_schema_stats(ownname=>'OUTLN', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'AUTO', degree=>DBMS_STATS.DEFAULT_DEGREE, granularity=>'DEFAULT', cascade=>TRUE);
-- generate the list of sql commands to generate schema stats for all schemas except SYS and SYSTEM
\@c:\\server_scripts\\prod3_schema_stats_schemas_get_1.sql
-- now gather the stats for all schemas except SYS and SYSTEM using the generated sql code
\@c:\\server_scripts\\prod3_schema_stats_gather_schema_stats_1.sql
exit;
EOF
open (FILE1,">$temp_sql_filename") || die ("Could not open output file $temp_sql_filename for writing. \n Does the full directory path exist?");
print FILE1 ($tempsqlcode);
# close the output file
close (FILE1);
@proglist = ("c:\\v901\\bin\\sqlplus.exe /nolog \@$temp_sql_filename");
system (@proglist);
