Re: RAC cache fusion details

On Mon, 17 Dec 2007 19:38:56 -0800 (PST), K Gopalakrishnan <kaygopal_at_gmail.com> wrote:

>Sybrand,
>
>When we require a set of blocks (known) cached in the other instance,
>we wait on a 'gc cr multiblock' request and not mbrc number of 'gc cr
>request' s. However if your request size is greater than the udp send/
>receive buffer size there could be some buffer overflows/socket loss.
>There are some bugs associated with timeouts on gc cr multiblock
>requests and we had discussed the same issue in the past. You may be
>able to find them in the newsgroup archives.
>
>-Gopal

`
Gopal,

I really don't want to argue with you, and I don't doubt your expertise,
However, I think the situation you describe refers to 10gR2, and I am on 9.2.0.8 and can't change that as the app isn't certified against any 10g release.
I have a block size of 8k and a mbr of 8 blocks. My udp buffer size is greater than 64k.
I observed this behavior running event 10046 against a full table scan.
I *never* (I repeat *never*) saw any 'gc cr multiblock requests' in the trace file!!!! I doubt they exist in 9iR2, the cache fusion mechanism probably has been enhanced.

Currently statspack shows a single server (4 cores) is waiting for 170000 seconds per day on gcs messages, and yet another 80000 seconds for ges messages, while only 10 to 20 percent of lookups are by rowid, the rest is full table scan. As I have unindexed tables and customer is running at only 20 percent of requested capacity, and CPU is doing nothing because of IO, it is of vital importance for me, I get this issue ironed out.
OTS hasn't been of a great help, they suggested I disabled cache fusion by setting gc_file_to_locks after *months* of research.

As I stated, I can't upgrade. The app is running a mixture of RBO and CBO (by virtue of developers using ANSI joins) and I simply don't know what is going to happen when I upgrade without changing anything. Developers know exactly 0 about Oracle, their background is sqlserver. There is a fat client, they don't make us of stored procedures, and and if they do, they don't use BULK. They also don't use bind variables, and they commit by logging off (the driver is ODBC).

The problem is, I think my analysis of the situation is correct, but customer doesn't believe me. I need to have a verdict of a third party (preferably Oracle) this application is beyond repair, and shouldn't be run on RAC.

Regards,

-- 
Sybrand Bakker
Senior Oracle DBA