Determine what files are in the Oracle cache

[ecsjohn]

We are having significant problems with disk I/O and would like to determine if the oracle cache is working properly. Is there any way to list the files in the oracle db cache?

If we could figure out which files are being cached, we could then compare this list to a list of files that are most frequently accessed and get an idea if there is a cache problem or something else.

 

[SantaMufasa]

ESCJohn,

Oracle does not cache files per se. Oracle caches only database blocks that come from files that store data in behalf of tablespaces.

Even if we could list the files from which Oracle is extracting database blocks, I cannot imagine how that allows you to determine "if there is a cache problem or something else".

Could you please assert your theory that would determine if "there is a cache problem" based upon knowing the files from which database block originate?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimbopalmer]

While there are third party tools that can report Disk I/O by file, the proper use of that information is for balancing Disk I/O between drives (or mount points). If your database is spread across 120 disk drives, you want approximately the same amount of data to come from each drive.

The SGA (my guess as to the 'oracle cache') can be tuned, but I find it simplest to tune it by looking at the SQL statements that use the most Disk reads (physical I/O), and the ones the use the most SGA reads (logical I/O), not by looking at files.

High physical I/O can mean indexes are not being used, causing full table scans, you can usually add indexes that dramatically reduce physical I/O. High logical I/O can mean the indexes are malformed for the SQL queries you are doing most, better indexes may be needed.

In general, some small blocks of a given table (often 4k to 16k) are stored in the SGA, not whole tables, whole tablespaces or whole files. (If you have a small table, perhaps a single row, or say the beginning and ending ZIP code of the 50 states, it may be worth while to cache the entire table, but for most tables, let Oracle move them in and out of the SGA automatically)

You want to make the SGA as large as possible, that does not cause swapping, as reads from the SGA are about 30,000 times faster than the reads from disk. (Dirty reads, done dirt cheap)

I tried to remain child-like, all I acheived was childish.

 

[jimirvine]

Hi jimbo,
Sorry, but I need to seek clarification on a couple of points that you have made here.
1.

The SGA (my guess as to the 'oracle cache')

I'm assuming that you actually mean, the buffer cache which is but one of the many many SGA components.

2.

but I find it simplest to tune it by looking at the SQL statements that use the most Disk reads (physical I/O), and the ones the use the most SGA reads (logical I/O), not by looking at files.

Do you not take wait events into account (fairly major part of P & T)

3. The general tone of your post seems to me (forgive me if I misunderstand you) that you imply that FTS creates physical reads, whereas Index scans create logical reads. I'm sure that this not what you mean (but it is how it seems to read.

4.

You want to make the SGA as large as possible, that does not cause swapping, as reads from the SGA are about 30,000 times faster than the reads from disk.

Sorry but that is a pretty poor piece of knee-jerk advice. You know nothing of this person's systems. To advise them to 'make their SGA as big as possible' takes no account of their hardware capabilities, their database type (OLTP, DW, somewhere in between) nor the sizing of the many individual components of the SGA.
I'm not trying to get at you as I don't have an answer either, but I think that your contribution could have been better formed.
Regards

 

[jimbopalmer]

1. The size of the Buffer Cache is the most tunable portion of the SGA and certainly the item closest to 'caching files', but the size of the whole SGA is what will cause excessive swapping. As he tunes the buffer cache, he needs to watch the size of the whole SGA.

2. Some part of wait, is going to be hardware or intent related. If your DB is read only, there may be no waits on a write. If your DB is data entered by humans in real time, and you have NVRAM disk caching, there may be no waits on a write.
Disk caching will be almost no help if your DB updates via bulk data transfer from another device, it overwhelms the cache and you wait on disk I/O. If you have NVRAM cache, then try to commit chunks smaller than the cache, if you don't, delay commits as long as program logic allows. I can think of no other general platitudes that can always help with writes.

3 Add four words and I agree that I said that. I think that unneeded Full Table Scans creates excessive physical reads, whereas bad Index scans create excessive logical reads.

4. Correct. I do not know his OS, the size of his RAM, or the sophistication of his disk configuration. I do not even know what other apps his server is stuck running. Therefore I can make no hard recommendations. But RAM not used in day to day DB operations is speed left on the table. There is some 'best size' for the SGA, and at that size, Disk I/O will be at a minimum.

It is very hard to get from 'what files are in the cache?' to perfect DB design. I do hope I gave an overview of where he needs to focus.

I tried to remain child-like, all I acheived was childish.