Subject: The New DBA FAQs, Part 2

From: Kevin Loney <"75663,327"@compuserve.com>
Newsgroups: comp.databases.oracle
Date: Tue, 19 Nov 1996 13:51:43 -0500

The New DBA FAQs
(Frequently Asked Questions for/from Database Administrators)
by Kevin Loney

Modification Hisotory
1992 Original version Kevin Loney
07-FEB-96 MODIFIED, added NOTES Kevin Loney
19-NOV-96 Updated scripts and article focus. Kevin Loney

Second in a Series.

Part 2. Database Monitoring

************
This article may not be published.

*****
References: ORACLE DBA Handbook, 7.3 Edition (Loney)
Advanced ORACLE Tuning and Administration (Aronoff, Loney, Sonawalla)
see www.osborne.com for the scripts and sample chapters.
Also, "Have Scripts, Will Travel,", ORACLE Magazine, Jan/Feb 97 (Loney)

****


Q: What should I monitor in my database?

A: Items to monitor include:

- Database service outages. Is the database up? Is SQL*Net available? Has the archived redo log
destination area filled?

These are important questions that you need to be able to answer at any point in time; and if the
answers indicate an alarm condition, you should be automatically notified by the monitoring system.
Since these conditions are server-centric, a server-centric monitoring system is appropriate for
monitoring them. Ideally, you can tie in the monitoring of these features to the monitoring or your
operating system.

- database internal statistics, such as free space per tablespace, rollback segment extensions, too
many users, etc.

There are a few critical internal statistics to monitor. Their importance within your environment
depends on your needs. Where applicable, you should monitor their rate of change over time. See
the ORACLE DBA Handbook scripts (from chapter 6, the Monitoring chapter) for scripts to track the
changes in these statistics. The scripts are online at www.osborne.com.

You can also query the database in an ad hoc fashion. The scripts below monitor the database's
current status. If you want to perform trend analysis, store the values in a table (along with a
date/timestamp) and use the reporting queries in the ORACLE DBA Handbook as the basis for your
reports.


CURRENT HIT RATIO:


select
SUM(DECODE(Name, 'consistent gets',Value,0)) Consistent,
SUM(DECODE(Name, 'db block gets',Value,0)) Dbblockgets,
SUM(DECODE(Name, 'physical reads',Value,0)) Physrds,
ROUND(((SUM(DECODE(Name, 'consistent gets', Value, 0))+
SUM(DECODE(Name, 'db block gets', Value, 0)) -
SUM(DECODE(Name, 'physical reads', Value, 0)) )/
(SUM(DECODE(Name, 'consistent gets',Value,0))+
SUM(DECODE(Name, 'db block gets', Value, 0))))
*100,2) Hitratio
from V$SYSSTAT;



HIT RATIO BY SESSION:


column HitRatio format 999.99
select Username,
Consistent_Gets,
Block_Gets,
Physical_Reads,
100*(Consistent_Gets+Block_Gets-Physical_Reads)/
(Consistent_Gets+Block_Gets) HitRatio
from V$SESSION, V$SESS_IO
where V$SESSION.SID = V$SESS_IO.SID
and (Consistent_Gets+Block_Gets)>0
and Username is not null;


ROLLBACK SEGMENT EXTENDS AND WRAPS:


select * from V$ROLLSTAT;


IO PER DATAFILE:


select DF.Name File_Name,
FS.Phyblkrd Blocks_Read,
FS.Phyblkwrt Blocks_Written,
FS.Phyblkrd+FS.Phyblkwrt Total_IOs
from V$FILESTAT FS, V$DATAFILE DF
where DF.File#=FS.File#
order by FS.Phyblkrd+FS.Phyblkwrt desc;


SEGMENTS WITHIN 20 PCT OF THEIR MAXEXTENTS


select
owner, /*owner of segment*/
segment_name, /*name of segment*/
segment_type, /*type of segment*/
extents, /*number of extents already acquired*/
blocks /*number of blocks already acquired*/
from dba_segments s
where /*for cluster segments*/
(s.segment_type = 'CLUSTER' and exists
(select 'x' from dba_clusters c
where c.owner = s.owner
and c.cluster_name = s.segment_name
and c.max_extents <= s.extents*1.2))
or /*for table segments*/
(s.segment_type = 'TABLE' and exists
(select 'x' from dba_tables t
where t.owner = s.owner
and t.table_name = s.segment_name
and t.max_extents <= s.extents*1.2))
or /*for index segments*/
(s.segment_type = 'INDEX' and exists
(select 'x' from dba_indexes i
where i.owner = s.owner
and i.index_name = s.segment_name
and i.max_extents <= s.extents*1.2))
or /*for rollback segments*/
(s.segment_type = 'ROLLBACK' and exists
(select 'x' from dba_rollback_segs r
where r.owner = s.owner
and r.segment_name = s.segment_name
and r.max_extents <= s.extents*1.2))
order by 1,2
/


SEGMENTS WHOSE NEXT EXTENT CAN'T FIT IN THE SUM OF ALL THE FREE SPACE
IN A TABLESPACE

select owner, segment_name, segment_type
from dba_segments
where next_extent>
(select sum(bytes) from dba_free_space
where tablespace_name = dba_segments.tablespace_name);


FREE SPACE UTILIZATION

>
NOTE: This uses the 7.2 feature that allows you to embed subqueries in the FROM clause to create
dynamic views. This query will not work in 7.0 or 7.1.


column Tablespace_Name format A20
column Pct_Free format 999.99

select Tablespace_Name,
Max_Blocks,
Count_Blocks,
Sum_Free_Blocks,
100*Sum_Free_Blocks/Sum_Alloc_Blocks AS Pct_Free
from
(select Tablespace_Name, SUM(Blocks) Sum_Alloc_Blocks
from DBA_DATA_FILES
group by Tablespace_Name),
(select Tablespace_Name FS_TS_NAME,
MAX(Blocks) AS Max_Blocks,
COUNT(Blocks) AS Count_Blocks,
SUM(Blocks) AS Sum_Free_Blocks
from DBA_FREE_SPACE
group by Tablespace_Name)
where Tablespace_Name = FS_TS_NAME;



FINAL NOTE:

This is a cursory overview of reactive monitoring. If you need proactive monitoring
of your production environment, there are a slew of tools available. Pick one that integrates well with
your operating system and network monitoring needs. For reactive monitoring, start with the set listed
above and figure out what you really need to see, and how often you really need to see it, then go
from there.

=======
Kevin Loney is the author of the ORACLE DBA Handbook, 7.3 Edition, coauthor of ORACLE: The
Complete Reference, Electronic Edition, and coauthor of Advanced ORACLE Tuning and
Administration, all published under Osborne/McGraw-Hill's Oracle Press imprint. See
www.osborne.com for scripts and sample chapters.

[Top | Back | Home ]