Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#! /usr/local/bin/perl
# script: prod3_gather_stats_day_job_1.pl (renamed from 59_win_901_prod3_gather_stats_day_job_1.pl)
# Features: This perl script gathers Oracle CBO statistics
# for 2 hours during daytime processing.
# Notes: ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 1 SCOPE=BOTH may need to be done
# prior to running this script if job_queue_processes = 0.
# DBMS_STATS.CREATE_STAT_TABLE(OWNNAME=>'SYS',STATTAB=>'SYSSTATS')
# needs run once before using this script.
#
# Script Sequence#: 59
# Used By: run automatically via AT scheduler
# Usage:
# ******** Gather daytime Oracle CBO stats Mondays at 9:00AM ********
# AT 09:00 /every:M c:\server_scripts\prod3_gather_stats_day_job_1.pl
# 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";
my $tempsqlcode=<<"EOF";
connect / as SYSDBA
set echo on
set feedback on
set verify on
set pagesize 24
-- gather stats for 2 hours, store results in Oracle data dictionary
exec dbms_stats.gather_system_stats(stattab =>'sysstats', statown => 'SYS', interval=> 120,statid => 'day');
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);
