System Architecture

The system architecture shown in Figure 11.2 has been designed to have “agents” on each remote Oracle host to perform the daily data collection, which is accomplished with two cron tasks (bstat-estat). Each remote host will have a small copy of the performance and tuning database to hold one week’s worth of data. Each Saturday, the hosts will execute two cron jobs to:

•  Export_p_and_t_tables (C shell script)—This will export and compress the Oracle performance and tuning and FTP the file to central:/$DBA/DBAX/p_and_t.

•  Create_p_add_t_tables (C shell)—This will drop and re-create the local database tables.

Figure 11.2  A sample performance and tuning architecture.

Weekly Processing

After all remote sites have FTPed their export files, a cron will fire on the central database to:

•  Import_p_and_t_tables (C shell) (For example, runs each Saturday at 1:00 PM)—It will loop through all entries in $DBA/DBAX/p_and_t/hostfile (the list of SIDs) and then load the export file into the main performance and tuning database.

•  Run the script $DBA/DBAX/p_and_t/reports/extract_reports.csh (For example, runs each Saturday at 2:00 PM)—It will loop through all entries in $DBA/DBAX/p_and_t/hostfile (the list of SIDs), and then for each entry execute p_and_t_rpt**.sql (where ** = 01–11), spooling the reports to SERVER.SID.p_and_t_rpt** in directory $DBA/DBAX/p_and_t/reports.

•  Run the script $DBA/DBAX/p_and_t/reports/mail_reports.csh (For example, runs each Saturday at 3:00 PM)—Loops through all entries in $DBA/p_and_t/hostfile (the list of SIDs). For each entry, it will get the email address of the primary DBA for the host (in $DBA/p_and_t/hostfile) and then email reports SERVER.SID_rpt** to the address specified in hostfile. The reports will remain on Central as well, for reference.

Local Oracle Processing

On each remote host, a directory will exist to hold all of the code required to collect the data. The central database will exist on Central:DBAX, but each remote host will have a small, local copy of the master database to temporarily hold the performance and tuning data until the weekly upload.

Each remote host will have the following:

•  Scripts (chmod +x)

•  bstat—A C shell script to start the bstat utility.

•  estat—A C shell script to run the estat utility.

•  export_p_and_t_tables—A C shell script to export and FTP the file.

•  create_stat—A C shell script to drop and re-create the local tables.

•  Parm Files (chmod 440)

•  pass_system—A protected file containing the Oracle SYSTEM password.

•  pass_oracle_central—A protected file with the Unix Oracle password for Central.

•  SQL Files

•  p_and_t_collect_b_e_stats.sql—Retrieves performance and tuning data from the BSTAT-ESTAT tables.

•  p_and_t_collect_object_stats.sql—Retrieves performance and tuning table data from DBA tables.

•  p_and_t_collect_ts_stats.sql—Retrieves performance and tuning tablespace data from DBA tables.

•  create_p_and_t_tables.sql—SQL to drop and re-create the local p_and_t tables with SYSTEM owner.

•  add_server_name.sql—SQL to add the server name to the local p_and_t tables.

•  p_and_t_utlbstat.sql—Customized bstat.

•  p_and_t_utlestat.sql—Customized estat utility to retain the TEMP tables.

•  Other Files

•  create_failed—Error message file for create_stat.

•  export_failed—Error message file from export_p_and_t_tables.

•  export_p_and_t_tables.par—A parfile for the export job.

•  Any other files in $DBA/PROD_SID/p_and_t—Output (i.e., log files) from running the jobs.

cron Run Schedules

The bstat script is run each weekday at 8:00 AM, followed by the estat at 4:00 PM. Here is the crontab listing:

#
# Below are the cron entries for the performance & tuning database
#
00 08 * * 1-5 /u03/home/oracle/admin/tranp/p_and_t/bstat 2>&1
00 16 * * 1-5 /u03/home/oracle/admin/tranp/p_and_t/estat 2>&1
#
00 08 * * 6   /u03/home/oracle/admin/tranp/p_and_t/export_p_and_t_tables 2>&1
30 08 * * 6   /u03/home/oracle/admin/tranp/p_and_t/create_p_and_t_tables 2>&1

On the central server, weekly processes are executed to:

•  Uncompress and load the incoming data

•  Extract the weekly performance and tuning reports

•  Mail the reports to the DBAs

Here is the crontab listing:

#
#
00 13 * * 1 /u01/dba/oracle/admin/DBAX/p_and_t/import_p_and_t_tables 2>&1
#
00 14 * * 1 /u01/dba/oracle/admin/DBAX/p_and_t/reports/extract_reports.csh 2>&1
00 15 * * 1 /u01/dba/oracle/admin/DBAX/p_and_t/reports/mail_reports.csh 2>&1

Performance Reports

This system consists of 10 reports that provide detailed performance information about each Oracle component. Tables 11.2 through 11.12 show samples of each report.