DBA FAQ 2 - Database Monitoring
Date: 2 Dec 92 15:35:11 GMT
Message-ID: <cTV6uB2w164w_at_cellar.org>
DBA FAQ
(Frequently Asked Questions for/from Database Administrators)
by Kevin M. Loney, aka kml_at_cellar.org (username "Scott Tiger")
Second in a Series.
Part 2. Database Monitoring
This is several pages long, you may want to just print it out and read it offline.
NOTE: Portions of this article previously appeared in "The One-Minute
DBA", in Oracle magazine, Winter 1992. Therefore, do not
attempt to re-publish this material without the express
written consent of Oracle Magazine. For a free subscription,
contact your Oracle sales rep or call 415-506-7000.
************
The scripts in this FAQ are V6 specific.
Since posting my request for DBA FAQ topics and ideas, I have received a number of responses detailing individuals' monitoring scripts. I have tried to integrate the best features of each in the process of writing this FAQ. Special thanks to Andy Finkenstadt(andy_at_homebase.vistachrome.com)
Q: How can I best monitor my database? In particular, how can I track
changes in space usage, fragmentation, and rollback segments?
A: Perform daily monitoring sessions that write their results to
tables. Then write scripts that report on changes in the
statistics gathered. Examples are shown below.
Monitoring files
This may seem like an odd place to start, but it really is significant. This will give you a quick way of determining which files are on which device (which one needs to know when a disk goes down). It also helps to get a quick report on the increase in the total disk space allocated to the database over time.
Create a table to store the data:
create table file_check
(TS CHAR(30) not null,
FILE_NAME CHAR(80),
BLOCKS NUMBER not null,
DISKBLOCKS NUMBER,
CHECK_DATE DATE);
Then, on a daily basis, write file spec information from dba_data_files to that table. Reports on this data will be vital if you ever have to try to take files offline while the database is mounted but not open. A sample insert script:
INSERT INTO file_check
SELECT
TABLESPACE_NAME ts,
FILE_NAME ,
BLOCKS ,
BLOCKS*4 DISKBLOCKS, /*VMS!*/
TO_CHAR(sysdate,'DD-MON-YY') check_date
FROM SYS.DBA_DATA_FILES;
Note that the ratio of Oracle block size to VMS block size is 4:1. Be sure to replace the '4' above with your operating system's value.
Monitoring Space, part 1 - The Big Picture
This section calls for the creation of another table. This will be used to store the results of space monitoring queries and will be used in later sections to generate trend reports.
Create a table to store the data:
create table space_check
(TS CHAR(30),
NUM_EXTS NUMBER,
MAX_EXTS NUMBER,
ORABLOCKS_FREE NUMBER,
DISKBLOCKS_FREE NUMBER,
PERCENTFREE NUMBER,
DISKBLOCKS NUMBER,
CHECK_DATE DATE);
Now, write a script that populates the space_check table once a day. Use a batch job to automate the entry.
CREATE VIEW BLOCK_TOTAL
(TABLESPACE_NAME, TBLOCKS) AS
SELECT
TABLESPACE_NAME,
SUM(BLOCKS)
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME;
INSERT INTO space_check
SELECT
BT.TABLESPACE_NAME ts,
COUNT(DFS.BLOCKS) NUM_EXTS,
MAX(DFS.BLOCKS) MAX_EXTS,
SUM(DFS.BLOCKS) ORABLOCKS_free,
SUM(DFS.BLOCKS)*4 DISKBLOCKS_free,
(SUM(DFS.BLOCKS)/BT.TBLOCKS)*100 PERCENTFREE,
BT.TBLOCKS*4 DISKBLOCKS,
TO_CHAR(sysdate,'DD-MON-YY') check_date
FROM
SYS.DBA_FREE_SPACE DFS,
BLOCK_TOTAL BT
WHERE
DFS.TABLESPACE_NAME = BT.TABLESPACE_NAME
GROUP BY BT.TABLESPACE_NAME, BT.TBLOCKS;
Again, note that a 4:1 ratio of OS block size to db block size is assumed. To report from these tables for any given date:
SELECT
TS,
FILE_NAME,
BLOCKS,
DISKBLOCKS
FROM FILE_CHECK
WHERE CHECK_DATE = to_date('&&CHECK_DATE')
ORDER BY TS, FILE_NAME;
SELECT
TS,
NUM_EXTS,
MAX_EXTS,
ORABLOCKS_FREE,
round(100*MAX_EXTS/
ORABLOCKS_FREE,2) max_pct,
DISKBLOCKS_FREE,
round(PERCENTFREE,2),
DISKBLOCKS
FROM SPACE_CHECK
WHERE CHECK_DATE = to_date('&&CHECK_DATE')
order by ts;
Explanation of the resulting report:
TS = tablespace
Num_Exts = The total number of free extents in the tablespace
Max_Exts = The maximum size, in Oracle blocks, of the largest free extent.
ORAblocks_free = The total amount of free space in the tablespace, in
oracle blocks.
Max_Pct = The size of the largest free extent as a percentage of all the
free space in that tablespace.
Diskblocks_free = The free space in the tablespace, in OS blocks.
Percentfree = The percent of the tablespace that has not been allocated
by any segments.
Diskblocks = The total number of diskblocks allocated to this tablespace.
Monitoring Space, part 2 - Trend Analysis
Now that this information is stored in the database, we can have the database monitor itself for changes. First, we can print out all values; in the next section we will only print those that have changed a certain percentage.
First, to monitor changes in the Percentfree value:
column week4 format 999
column week3 format 999
column week2 format 999
column week1 format 999
column today format 999
column change format 999
ttitle center 'Changes by week in tablespace PercentFree values' skip 2
select ts tablespace_name,
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
round(percentfree),0)) week1,
max(decode(check_date,to_char(sysdate-21,'DD-MON-YY'),
round(percentfree),0)) week2,
max(decode(check_date,to_char(sysdate-14,'DD-MON-YY'),
round(percentfree),0)) week3,
max(decode(check_date,to_char(sysdate-7,'DD-MON-YY'),
round(percentfree),0)) week4,
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
round(percentfree),0)) today,
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
round(percentfree),0)) -
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
round(percentfree),0)) change
from space_check
group by ts
order by decode(
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
round(percentfree),0)) -
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
round(percentfree),0)),0,9999,
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
round(percentfree),0)) -
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
round(percentfree),0))),
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
round(percentfree),0));
I realize that looks a little convoluted. What it says is that for each tablespace, the database is to print out the values from today, a week ago, two weeks ago, three weeks ago, four weeks ago, and the change from the fourth week ago to today. The order by clause forces it to print the tablespaces with the lowest percentfree at the top of the report.
For num_exts, we'd like to reverse the order of the report, so that
the most fragmented will appear at the top:
column week4 format 999
column week3 format 999
column week2 format 999
column week1 format 999
column today format 999
column change format 999
ttitle center 'Changes by week in tablespace Num_Extents values' skip 2
select ts tablespace_name,
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
num_exts,0)) week1,
max(decode(check_date,to_char(sysdate-21,'DD-MON-YY'),
num_exts,0)) week2,
max(decode(check_date,to_char(sysdate-14,'DD-MON-YY'),
num_exts,0)) week3,
max(decode(check_date,to_char(sysdate-7,'DD-MON-YY'),
num_exts,0)) week4,
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
num_exts,0)) today,
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
num_exts,0)) -
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
num_exts,0)) change
from space_check
group by ts
order by decode(
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
num_exts,0)) -
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
num_exts,0)),0,-9999,
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
num_exts,0)) -
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
num_exts,0))) DESC,
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
num_exts,0)) desc,1;
The same can be done for any of the other columns. Note the differing order by clauses for ascending (like percentfree) or descending (like num_exts) change reports.
Monitoring Space - part 3: Automatic alerts
As part of the process that inserts records into Space_check and File_check on a daily basis, you should also kick off a job that checks them for warning conditions. The following script is a modified version of the script above that prints our percent_free values. It only prints out those tablespaces whose free space has dropped 5 percent in four weeks. Note that that 5 percent is relative to the starting value (ie, a drop of 20 to 19 percentfree will be printed out) while the printed values will probably show a smaller drop. This keeps tablespaces from failing to show up as they diminish in size.
column week4 format 999
column week3 format 999
column week2 format 999
column week1 format 999
column today format 999
column change format 999
ttitle center 'Tablespaces whose PCTF values have changed 5 pct this month'
select ts tablespace_name,
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
round(percentfree),0)) week1,
max(decode(check_date,to_char(sysdate-21,'DD-MON-YY'),
round(percentfree),0)) week2,
max(decode(check_date,to_char(sysdate-14,'DD-MON-YY'),
round(percentfree),0)) week3,
max(decode(check_date,to_char(sysdate-7,'DD-MON-YY'),
round(percentfree),0)) week4,
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
round(percentfree),0)) today,
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
round(percentfree),0)) -
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
round(percentfree),0)) change
from space_check
group by ts
having
abs((max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
round(percentfree),0))
-
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
round(percentfree),0)))/
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
round(percentfree),1)))
> 0.05
order by decode(
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
round(percentfree),0)) -
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
round(percentfree),0)),0,9999,
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
round(percentfree),0)) -
max(decode(check_date,to_char(sysdate-28,'DD-MON-YY'),
round(percentfree),0))),
max(decode(check_date,to_char(sysdate,'DD-MON-YY'),
round(percentfree),0));
Note that the percent change detected can be easily changed. Have this script generate a listing file and mail it to your account every day before you get in. That way, in the first minute you're at work you'll have a warning of the most common space problems that may come your way.
Sample output:
TABLESPACE_NAME WEEK1 WEEK2 WEEK3 WEEK4 TODAY CHANGE ------------------------------ ----- ----- ----- ----- ----- ------ PRODUCTION 79 63 62 62 61 -18 PRODUCTION_I 90 74 73 73 73 -17 APPL2_DATA 63 61 60 59 58 -5 APPL2_INDEX 54 53 52 52 51 -3 TEST_DATA 72 72 71 68 78 6
Plotting the Results in SQL*Plus
SQL*Plus can be used to generate some simple line graphs. The following script plots the percentfree value as a function of week. A few things to note: the WW to_char function marks the week beginning with the first day of the year, thus, there are 53 weeks of the year and they start on a different day every year. Secondly, I print only the minimum percentfree value for a week, since this is the most critical value. If you leave SPACE at 1 in SQL*Plus, you'll need a printer that supports 209 characters. If you add a SET SPACE 0 command, you can get by with a 132 column printer.
First, create a view that summarizes the values by week:
create view BY_WEEKS as
select ts,
to_char(check_date,'YYYY') yr,
to_char(check_date,'WW') wk,
max(ROUND(percentfree)) pct,
MIN(ROUND(PERCENTFREE)) minpct
from space_check
group by ts,
to_char(check_date,'YYYY'),
to_char(check_date,'WW');
Then select the values. Most of the repetetive lines are removed for
brevity.
set linesize 240
set pagesize 200
COLUMN WK FORMAT A3
SET FEEDBACK OFF
SET VERIFY OFF
ttitle center 'Min Percentfree value by week for '&&ts skip 2
select
wk,
decode(minpct,0,'o',null) zchk0,
decode(minpct,1,'o',null) chk1,
decode(minpct,2,'o',null) chk2,
decode(minpct,3,'o',null) chk3,
decode(minpct,4,'o',null) chk4,
decode(minpct,5,'o',null) chk5,
decode(minpct,6,'o',null) chk6,
decode(minpct,7,'o',null) chk7,
decode(minpct,8,'o',null) chk8,
decode(minpct,9,'o',null) chk9,
decode(minpct,10,'o',null) tchk10,
etc.
decode(minpct,98,'o',null) chk98,
decode(minpct,99,'o',null) chk99,
decode(minpct,100,'o',null) hchk100
from by_weeks
where ts = upper('&&ts')
and yr = '&&year';
Spool the result to a file. I change the first character of the column alias every 10 characters to give me a marker to go by when reading the graph.
Monitoring Rollback Segments
The following script will display the current status of the rollback segments in the database. I have to confess that I don't store this in a database table, mostly because its values are so subject to change by an outside force (me), and the trends, while possibly helpful to detecting rollback segments approaching the OS limit for number of extents, are not consistent enough on my database to be useful.
SELECT
SUBSTR(DS.SEGMENT_NAME,1,22) R_SEGMENT,
SUBSTR(DS.TABLESPACE_NAME,1,20) TABLESPACE,
DS.BLOCKS,
DS.EXTENTS,
DRS.STATUS
FROM DBA_SEGMENTS DS,DBA_ROLLBACK_SEGS DRS
WHERE DS.SEGMENT_NAME = DRS.SEGMENT_NAME
ORDER BY 1;
Future FAQ postings will explore further database statistics that can be monitored, such as incremental hit ratios, dictionary cache hits, and bstat/estat statistics.
Feedback is always welcome and appreciated.
Kevin Loney (kml_at_cellar.org) regularly writes the User Help column for Oracle magazine. In his spare time, he is an Oracle DBA in southeast PA. Comments, questions, and job offers are always welcome.
Hofstadter's contracrostipunctus acrostically backwards spells 'J.S. Bach'
Something constructive should come here.
kml%cellar_at_tredysvr.tredydev.unisys.com - My other account is a Mercedes.
Received on Wed Dec 02 1992 - 16:35:11 CET