72% Geek

Find an Ultrasound school

Oracle Cache Contents

This is a really useful little query. Have you ever wondered how to check and see what is being cached in your oracle database? This query will tell you, at least in Oracle 10g. You have to be connected as sys to run it.

select
kcbwbpd.bp_name,
o.name,
count(*) BLOCKS
from sys.x$kcbwds kcbwds,
sys.x$kcbwbpd kcbwbpd,
sys.x$bh bh,
sys.obj$ o
where kcbwds.set_id >= kcbwbpd.bp_lo_sid
and kcbwds.set_id <= kcbwbpd.bp_hi_sid
and kcbwbpd.bp_size != 0
and o.dataobj# = bh.obj
and bh.state !=0
and o.owner# !=0
group by kcbwbpd.bp_name, o.name
order by kcbwbpd.bp_name, blocks desc, o.name

For my First Post, A little about Myself

I am an Oracle DBA. The official title is senior Oracle DBA. I work in the greater Kansas City area. I currently work in a major financial institution based in Kansas City. I have spent my entire working life in the greater Kansas City area, after growing up in Iowa. I am a 1985 graduate of the United States Military Academy at West Point (BEAT NAVY!).

After some time in the Army, as an Armor Platoon leader and some time in the S4 shop, I went to work for a defense contractor at Ft. Leavenworth as a systems analyst. It is there I started in what we now call DBA work. The first database I worked with was called Datatrieve, and it wasn't really a database, more like a query language. The contractor purchased Ingres while I was there, and we started moving our database applications to Ingres. That was my introduction to the then brand new database world. To put that in perspective, a major piece of hardware we purchased (on GSA schedule of course) was a SA482 storage array, which had a grand total of 4.2G of storage. I don't know what it cost, but 4.2G was huge. I can’t even think what it compares to, something in the 100s of petabytes of storage today.

After working for the Defense contractor for 2 years, I went to work for CERNER. I was there for 10 years. That was a highly enjoyable job. My job title there was CCL User Analyst. CCL was the CERNER database language, written originally to put a relational view on COBOL files. In the 10 years I was there, I helped (along with a lot of other good people) CERNER go from a character cell, dumb terminal based system to client server and Oracle. (Version 6 something at first). CERNER was able to modify their database language (CCL) to be used against Oracle and SQL Server, which really cut down on their software development time. CERNER was based on DEC Vax, and later DEC Alpha hardware. By the way, DEC had clustered software working perfectly 10 years ago. I still can’t figure out why it has taken this long for it to move into the computing mainstream.

From CERNER I went to work for Birch Telecom for a year, then NAIC (National Association of Insurance Commissioners) for 6 years. After NAIC, I went to my current position. If you can't tell from the timeframe in my posts, I have been working in the database world in one capacity or another since 1987, so I feel like I have a pretty good feel for what goes on in the DBA world.

My wife and I have two children, and we reside in Blue Springs, Missouri.