FAQ: DBA_Monitoring_FAQ
Date: Mon, 23 May 1994 00:01:09 GMT
Message-ID: <1994May23.000257.11715_at_arbi.Informatik.Uni-Oldenburg.DE>
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.
:::::::::: :: :: :::::Alself me to myduce introlow Scott/::: ::::::< ::::: . :::::::::kml_at_cellar.org ::Tiger:::::::::: :: ::: ::::::::Damn the electric fence!DBA FAQ
(Frequently Asked Questions for/from Database Administrators)
by Kevin M. Loney, aka kml_at_cellar.org (username "Scott Tiger")
Fourth in a Series.
30-NOV-92 Initial posting. KML.
Part 4. Database Administration, Bits and Pieces
This is several pages long, you may want to just print it out and read it offline.
The scripts in this FAQ are V6 specific.
Unlike the other FAQ's, this isn't geared toward a specific topic.
It contains tips and scripts for a variety of areas.
These may not be published without prior approval of the author.
Even then, they may not be published in the Midwest Oracle Users
Group newsletter.
Topics:
- Monitoring the Dictionary Cache usage.
- "Mapping" tablespace space usage
- Displaying "widow" grants
- Automating cleanup of widow grants.
- Monitoring the Dictionary Cache usage.
In V6, information about the database - such as table names, grants, and usernames, is stored in the dictionary cache. When a user performs a query against a table, the cache is searched to see if that table is already there. If it is not, the descriptive information about that table is loaded into the cache (this is called a recursive call). That information stays in the cache until the cache runs out of space. The least recently used entry is then moved out of the cache to make room for new entries.
The sizes of these caches are determined by the 'dc_' init.ora parameters.
The v$rowcache table can be used to determine how many queries scored
'hits' against the cache (found the sought entry in the cache), or 'missed'
(performed a recursive call). Current usage and the dc setting ('count')
can also be queried:
set linesize 80
SET PAGESIZE 60
SELECT PARAMETER, GETS, GETMISSES, USAGE, count
FROM V$ROWCACHE
ORDER BY 1;
Part of a sample output:
PARAMETER GETS GETMISSES USAGE COUNT -------------------------------- ---------- ---------- ---------- ---------- dc_column_grants 18 6 6 100 dc_columns 575005 14571 9579 14000 dc_constraint_defs 0 0 1 100 dc_constraints 2 2 2 500 dc_files 6466 53 53 64 dc_free_extents 511 211 211 600 dc_indexes 119282 741 784 2000 dc_object_ids 7281 35 11 2000 dc_objects 113726 1943 1974 3000
So for the dc_objects cache, the init.ora parameter setting is 3000, 1974 of which are currently being used. There have been 113,726 requests against this cache, and all but 1,943 were hits ('gets').
Note that the caches are reset as part of database startups, so systems which are kept up for long periods should have higher cache settings.
2. "Mapping" tablespace space usage
Space in a tablespace is either used or free; free space is tracked in the dba_free_space table, while used space is tracked in dba_extents. By combining the two tables in one query, you can quickly see just how badly fragmented your tablespace is. This also allows you to determine how contiguous the free space extents are.
SET PAGESIZE 60
SET LINESIZE 132
SET VERIFY OFF
SPOOL MAP
SELECT 'FREE SPACE' OWNER,' ' OBJECT,FILE_ID, BLOCK_ID, BLOCKS
FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = '&&TS'
UNION
SELECT SUBSTR(OWNER,1,20), SUBSTR(SEGMENT_NAME,1,32),FILE_ID,BLOCK_ID,BLOCKS
FROM DBA_EXTENTS
WHERE TABLESPACE_NAME = '&&TS'
ORDER BY 3,4;
SPOOL OFF
UNDEFINE TS
Partial Sample Output:
(This output has been trimmed to 80 chars by shortening the Object field)
OWNER OBJECT FILE_ID BLOCK_ID BLOCKS -------------------- -------------------- ---------- ---------- ---------- ABC_MGR ABC 3 2 5120 ABC_MGR ABC_PK 3 5122 70 ABC_MGR ABC_DOC_NO 3 5192 97 FREE SPACE 3 5289 7 ABC_MGR ABC_CHILDREN_UPDATE 3 5296 36 ABC_MGR PARENT 3 5332 10 ABC_MGR ABC_CORPORATE_SOURCE 3 5342 18 ABC_MGR ABC_FILE_COLOR_FOR_S 3 5360 3 ABC_MGR ABC_LOC 3 5363 2145 FREE SPACE 3 7508 384 FREE SPACE 3 7892 384 FREE SPACE 3 8276 576 FREE SPACE 3 8852 385 FREE SPACE 3 9237 385 ABC_MGR ABC_LOC 3 9622 10 FREE SPACE 3 9632 84 ABC_MGR ABC_WORD_INDEX 4 2 39 ABC_MGR ABC_WORD_INDEX 4 41 39 FREE SPACE 4 80 3 ABC_MGR ABC_WORD_INDEX 4 83 59 ABC_MGR ABC_INDEX 4 142 14 ABC_MGR ABC_INDEX 4 156 14 ABC_MGR ABC_INDEX 4 170 21 ABC_MGR ABC_INDEX 4 191 32 ABC_MGR ABC_WORD_INDEX 4 223 89 ABC_MGR ABC_PK 4 312 70 FREE SPACE 4 382 35 ABC_MGR ABC_DOC_NO 4 417 97 FREE SPACE 4 514 57 FREE SPACE 4 571 76230
Let's take a look at this. In file 3, there are 5 contiguous free extents; these could be coalesced into 1 by creating an object of exactly their combined size (I prefer to first create objects of the size of all larger free chunks). Also, the ABC_WORD_INDEX is fragmented, with extents in several locations. Exporting the ABC_MGR user, rebuilding the tablespace, and re-importing the user will reorder the extent allocation. Using the COMPRESS=Y flag on export will cause all allocated space to be compressed into one allocated extent.
Note that the block_id+blocks can be used to determine if the two free extents are contiguous. This is the fact exploited in Kathy Jou's article from the 1991 IOUG, available via the RTSS as bulletin 100954.708. I am working on a more robust version of this for a future article.
3. Displaying "widow" grants
When an account is deleted (ie, connect privilege is revoked), it maintains
an grants made to it. Those grants can only be revoked by the grantor.
(Ideally, only the owner of a table should make any grants on it). Thus,
"widow" grants are left hanging out there in the database - they will be
reactivated as soon as the account is re-granted connect priv. Plug this
security hole.
To list existing "widow" grants, run the following script. *NOTE: I reference a table called DROPS. This is a denormalized table of all dis-connected users, created because selecting from dba_users was too slow.*
create table drops
as select username from dba_users where connect_priv = '0';
rem widow_grants.sql
set termout off
set echo off
set feedback off
set newpage 0
set pagesize 60
set linesize 132
break on grantee skip 1 on owner on grantor
spool widows.lis
select grantee, owner, grantor, table_name, select_priv, update_priv, insert_priv, delete_priv from dba_tab_grants where grantee in (select username from drops) order by 1,2,3,4;
spool off
This will show the privileges owned by any account that no longer has connect privilege.
4. Automating cleanup of widow grants
Which brings us to the logical question: how can I clean up those grants? Well, you need to remember that each distinct grantor must do the revoking.
Run the following under each grantor that showed up in the prior listing:
set termout off
set echo off
set feedback off
set newpage 0
set pagesize 0
spool cleanup.sql
select 'revoke all on '||table_name||' from '||grantee||';'
from user_tab_grants_made
where grantee in
(select username from drops);
spool off
_at_cleanup
host delete cleanup.sql;*
5. One last thing...
Since it's denormalized,
DROP TABLE DROPS;
In the next FAQ:
How to "become" another user in V6.
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 and questions are always welcome.
:::::::::: :: :: :::::Alself me to myduce introlow Scott/::: ::::::< ::::: . :::::::::kml_at_cellar.org ::Tiger:::::::::: :: ::: ::::::::Damn the electric fence!Received on Mon May 23 1994 - 02:01:09 CEST