Talkback
E-mail
Printer Friendly
Reprints
To that end, Oracle gives you three different levels in which you can specify your result cache: the database level, the session level, and the query level. There's a lot of power in those options, for both good and evil, so I would suggest sticking with the query or session level unless you've thoroughly tested it at the database level and you know exactly what to expect. At the database level, all query results will be cached, including those that don't need it. And by default, the result cache is assigned a fairly small portion of the buffer, so you're not likely to see tremendous results unless you expand the allocation.
|
|||||||||||||||||||||||||||||||||||||||||||
Oracle has implemented Result Cache pretty well, and it does exactly what it's told. In my initial tests, I had no problems seeing results. I tested against just a couple queries to get the feel for it, and I did see vast improvements in my query times. After I bumped up the number of queries and parameterized them, however, I didn't see nearly the same changes as I did the first time. That's not unexpected at all and in no way outlines any kind of shortcoming with the feature. I'm merely pointing out that you need to plan carefully and test thoroughly before you implement it in production.
It should also go without saying that this feature is aimed at improving performance on disk-bound systems and isn't going to do any good on memory-bound systems. If you're already experiencing memory pressure, carving out a buffer in an already constrained system is only going to make things worse. I'd also like to point out that in general, this isn't going to help a great deal with your OLTP workloads. And if you use it on your decision support workloads, be mindful of the query results you're caching. It's easy to forget that even 64-bit systems have memory limits, and that caching a 200-million-row result might not be the best use of your memory resources.
I like Result Cache for what I call nuisance queries, which are lookups you do to satisfy joins or to pull results that rely on table scans -- things of that nature. But Result Cache is a good feature that will let you do pretty much whatever you want to do.
Fighting corruption
Oracle Database 11g puts just as much effort into monitoring and recovering from corruption as it puts into query performance.
Automatic health monitoring is a really big feature in this category because it runs reactive checks or manual checks. Reactive
checks get run when a critical error occurs. These checks can examine database structure, data block integrity, redo integrity,
and other conditions. When Oracle runs these checks, it creates a report and quite often suggests fixes.
Automatic quarantine of corrupt undo segments is just plain cool. When Oracle discovers a corrupt undo tablespace, it will quarantine the segment and not allow future transactions to use it. This allows Oracle to contain the damage and keep it from spreading.
The Fast Analyze feature will cure a big headache for DBAs of very large databases. Fast Analyze allows you to scan for table-index corruption much faster than before. This is really important because much of this type of maintenance is done during scheduled maintenance windows, and if the analysis operation can't even complete during the window, then neither can the fix.
Sean McCown is a contributing editor of the InfoWorld Test Center. He also writes the Database Underground blog.
