DBA Monitoring FAQ (II)
Date: Tue, 14 Sep 1993 12:48:11 GMT
Message-ID: <1qaV0B3w164w_at_cellar.org>
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 Tue Sep 14 1993 - 14:48:11 CEST