| Previous | Table of Contents | Next |
The output of utlbstat and utlestat can offer some very valuable performance information. Listing 11.3 shows the basic statistics for the database during the period between your bstat and the estat. While all of the information can be important, we can view several critical performance statistics here:
Note: This parameter takes effect for each user who accesses Oracle. Check your “high water mark” in your alert.log to ensure that your host has enough memory to support your choice. The formula is:total_memory = oracle_high_water_mark * sort_area_size
The benefit to running bstat-estat is the ability to measure changes over a period of time. It is relatively simple to write a script that invokes bstat (begin statistics) at a specified time and runs the estat (end statistics) utility at a later time. Some sites compute bstat-estat statistics each hour so that hourly variations can be easily detected.
Running the utilities is very simple. Within a script, go to $ORACLE_HOME/rdbms/admin, enter SQL*DBA, and invoke the utlbstat.sql script. The report will be generated when utlestat.sql is invoked. The standard estat utility creates a report called report.txt that is dumped into the $ORACLE_HOME directory.
Once the report.txt file is created, there are two ways to extract the information. A command script in perl, awk, or Korn shell can be written to interrogate the report, extract the information, and write it into Oracle tables. The estat utility can also be altered to dump the data into Oracle tables while it creates the report.txt file.
Here is a sample Unix script that can be invoked from a cron process. In this case, the script called perf would be addressed as “perf bstat” and “perf estat,” as shown in Listing 11.4.
Note that the regular estat utility has been replaced by a customized version called specialestat, and this customized estat utility is run in lieu of the one in $ORACLE_HOME/rdbms/admin. The specialestat is identical to the ultestat, except that it does not delete the temporary tables that are used to create report.txt. In this way, the temporary tables can be interrogated and loaded into special performance tracking tables. Following specialestat, the tracker utility is then invoked to capture the estat information into permanent tables, dropping the temporary tables.
Listing 11.4 A Unix script to gather performance information.
if [ $# != 1 ]
then
echo "usage: $0 { bstat | estat }"
exit 1
fi
if [ "${1}" != bstat -a "${1}" != estat ]
then
echo "usage: $0 { bstat | estat }"
exit 1
fi
SQLPATH=/usr/perfmon
if [ "${1}" = bstat ]
then
# Begin collection
sqldba << !
connect internal
@${ORACLE_HOME}/rdbms/admin/utlbstat
exit
!
else
# End data collection
sqldba << !
connect internal
@${SQLPATH}/specialestat
exit
!
sqlplus / @${SQLPATH}/tracker ${ORACLE_SID}
fi
exit 0
After the data has been transferred into permanent tables, the statistical information can be analyzed and graphed to show performance trends. Predictions can be extrapolated from the data using linear forecasting methods, and problems such as full tablespaces can be predicted with relative accuracy. Listing 11.5 presents the script that will load the performance tables.
Listing 11.5 tracker.DDL—an SQL script to gather performance statistics.
INSERT INTO track_stats
( oracle_sid, collection_started)
SELECT '&1',min(stats_gather_times)
FROM sys.stats$dates;
UPDATE track_stats
SET collection_ended =
(SELECT max(stats_gather_times)
FROM sys.stats$dates),
run_date = to_date(substr(collection_started,1,12),
'DD-MON-YY HH24'),
consistent_gets =
(SELECT change
FROM sys.stats$stats
WHERE name = 'consistent gets'),
block_gets =
(SELECT change
FROM sys.stats$stats
WHERE name = 'db block gets'),
physical_reads =
(SELECT change
FROM sys.stats$stats
WHERE name = 'physical reads'),
buffer_busy_waits =
(SELECT change
FROM sys.stats$stats
WHERE name = 'buffer busy waits'),
buffer_free_needed =
(SELECT change
FROM sys.stats$stats
WHERE name = 'free buffer requested'),
free_buffer_waits =
(SELECT change
FROM sys.stats$stats
WHERE name = 'free buffer waits'),
free_buffer_scans =
(SELECT change
FROM sys.stats$stats
WHERE name = 'free buffer scans'),
enqueue_timeouts =
(SELECT change
FROM sys.stats$stats
WHERE name = 'enqueue timeouts'),
redo_space_wait =
(SELECT change
FROM sys.stats$stats
WHERE name = 'redo log space wait time'),
write_wait_time =
(SELECT change
FROM sys.stats$stats
WHERE name = 'write wait time'),
write_complete_waits =
(SELECT change
FROM sys.stats$stats
WHERE name = 'write complete waits'),
rollback_header_gets =
(SELECT sum(trans_tbl_gets)
FROM sys.stats$roll),
rollback_header_waits =
(SELECT sum(trans_tbl_waits)
FROM sys.stats$roll)
WHERE collection_ended is null;
INSERT INTO LATCHES
(ls_latch_name, ls_latch_gets, ls_latch_misses,
ls_latch_sleeps, ls_latch_immed_gets, ls_latch_immed_misses)
SELECT name, gets, misses, sleeps, immed_gets, immed_miss
FROM sys.stats$latches;
UPDATE LATCHES SET
ls_collection_started =
(SELECT min(stats_gather_times)
FROM sys.stats$dates)
WHERE ls_oracle_sid IS NULL;
UPDATE LATCHES SET
run_date = to_date(substr(ls_collection_started,1,12),'DD-MON-YY HH24')
WHERE ls_oracle_sid IS NULL;
UPDATE LATCHES
SET ls_oracle_sid =
(SELECT '&1'
FROM sys.dual),
ls_collection_ended =
(SELECT max(stats_gather_times)
FROM sys.stats$dates)
WHERE ls_oracle_sid IS NULL;
| Previous | Table of Contents | Next |