I have been working on this for quite a while. It is possible to analyze the
buffer cache, but it takes a lot of digging through such files as sql.bsq,
catparr.sql and catpart.sql to find how things hang together. As for linking
object ID's, on 8.1.6, x$bh.obj can be joined to one of sys.obj$.obj# or
sys.obj$.DataObj#, depending on whether its partitioned, or a LOB 'virtual'
object etc (note LOBs are particularly tricky to trace). I have built a pl/sql
script that loads a table with data on each object in the buffer cache, how many
blocks it has, their status, buffer pool, partition etc.
After MUCH trial and error, Ive found various means of removing objects and
loading objects to buffer cache using pl/sql scripts, which allows me to
switchover from OLTP to end of day DSS processing without losing my KEEP pool.
Also allows OLTP daily partitioned tables to roll in/out without blatting
memory.
Works like a treat (It autorefreshes to an access97 interface with buttons to
control the oracle buffer cache).
IMHO Oracle should allow DBA's to do this stuff : it should be possible to
define various buffer pools and assign objects to them to control how memory is
used (Oracle 10 will be here any day now).
Regards
Mark Teehan
- ERG Group --------------------------
The contents of this email and any attachments are confidential
and may only be read by the intended recipient.
Received on Wed Jul 26 2000 - 20:37:12 CDT
