The New DBA FAQS Part 2- by Kevin Loney

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

This article cannot 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)

Part 2. Database Monitoring

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.