Re: Upgrade to big SUN box or RAC for data warehouse?

>
> GC_FILES_TO_LOCKS
>
> How could someone use this parameter to their benefit? How have you used
> this parameter to your benefit?

It's a tricky parameter to set, that's for sure. It goes something like:

GC_FILES_TO_LOCKS="2,3,6=200EACH:5,8-13=1000!50" ...and so on (and it obviously has to be identically set on all nodes).

Which being interpreted means "switch off cache fusion transfer for files 2, 3, 5, 6, 8, 9 10, 11, 12 and 13. Oh, and by the way, I want multi-block locks for these files, too. Files 2, 3 and 6 should have 200 locks for each file, but the others should have 1000 between the lot of them, grouped into sets of 50"

Meaning that a select of one block from (say) file 8 will take one of the 1000 locks covering all 6 files, and in the process lock about 49 other blocks around it.

When would you use it in a RAC? When the tablespaces concerned are read- only, or read-mostly. Because if that's the case, yes you could ship me a copy of a block across the interconnect, but I could also equally well visit the disk and read exactly the same information from there (if it's read-only, then by definition, what's in your buffer cache must be identical to what's on disk). Another reason for using it would be if you had paritioned your application (ie, decided that tables A, B and C would all be accessed from Node 1, but tables D, E and F would be used on Node 2, and designed your code that way... nothing to do with range, hash or list paritioning in other words). Given that your app. only accesses a set of tables on one node, there should be no need to transfer blocks from those tables across the interconnect anyway, so why threaten to use cache fusion transfer for them?

By not using the interconnect for such read requests, you free up the interconnect for passing only those buffers which really have to passed directly between caches, and fast (ie, write-intensive stuff). Of course, if your interconnect had massive bandwidth and wasn't straining at the seams, you might well choose not to bother, because there's no two ways about: transfer via the interconnect is usually a darn'd sight quicker than having to visit the physical disk.

But the other possible advantage with GC_FILES_TO_LOCKS is the multi-block locking. Instead of each block having its own lock (that is, an entry in the Global Resource Directory), you now have 1 lock (ie, one entry) covering (say) 50 blocks. That matters because in an instance recovery, that part of the Global Resource Directory which was housed on the failed instance has to be reconstructed and re-distributed amongst the surviving instances. And if the resource directory is smaller as a result of the use of multi-block locks, then that reconstruction exercise is going to be a deal quicker than it otherwise would have been. Given that the blocks covered by the reconstructed information are not accessible until that reconstruction is complete, this could well be a recipe for higher availability.

On the other hand, getting the number of block locks too low is a recipe for massive serialization on the database (ie, woeful performance). As I said, it's a tricky one to set.

I don't know of any 2-node RACs which are suffering from interconnect issues, but when you get into 4 and more nodes, the amount of inter- instance traffic across the interconnect grows exponentially, and then it can most definitely be a bottleneck... so yes, I've used it on a 4 node cluster to good effect, for read-only tablespaces. I've also used it on an 8i Parallel Server database that was migrated to RAC: the app had already been partitioned for OPS, so there was no need to master every block in the resource directory individually, since hardly any of them got transmitted across the interconnect anyway.

It's certainly a parameter that needs to be looked at, but probably not in an OLTP database.

~QM Received on Thu Jul 24 2003 - 15:22:27 CDT