From: kml@cellar.org (Scott Tiger)
Newsgroups: comp.databases.oracle
Subject: DBA FAQ Part 2 - Monitoring Space
Date: 6 Oct 92 15:06:12 GMT
Organization: The Cellar BBS and public access system

DBA FAQ
(Frequently Asked Questions for/from Database Administrators)
by Kevin M. Loney, aka kml@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 Finkelstadt(andy@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@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.
=======

              Also, Charlottesville must be destroyed.

   These may not even be my opinions, let alone anyone else's.
  kml%cellar@tredysvr.tredydev.unisys.com - My other account is a Mercedes.