Tuning and health

Are developers getting worse as databases mature, or are they just not able to grow their skills at the same rate the requirements are exploding? Either way, Oracle is doing a lot to help DBAs discover and deal with resource-hogging code.

For starters, Database 11g adds self-learning capabilities to Oracle's automatic SQL tuning. Now the engine detects high-load SQL statements and saves them for tuning during a maintenance window. It can apply some automatic fixes or suggest structural changes, such as indexes. If you're leery of letting the engine do too much tuning, you can let it simply capture your queries and suggest fixes. Then, if the engine turns out to be right most of the time, you can start trusting it more. One problem is that Oracle can't take your entire workload into account, so it may suggest changes that would damage your normal flow for a query that gets run only every so often.

There's more than one way to quantify a runaway workload, and Oracle has risen to the occasion here as well. When workloads go awry, it's typically in one of the three main areas of server performance: CPU, memory, or disk I/O. Usually resource governors quantify a runaway workload by measuring CPU or memory, but Oracle Database 11g has also instituted per-session I/O limits.

These I/O limits allow you to specify a maximum value (either in I/O requests or in megabytes) of work that connections are allowed to perform on the server. I/O limits are a very important addition (especially in the case of large warehouses) because these systems can become disk-bound very easily, and CPU or memory resource capping doesn't adequately address disk contention.

I/O caps can also help DBAs put the kibosh on long-running queries. Because there's no way to natively define a policy that checks whether a query has used 20 per cent of the CPU for 20 minutes, for example, DBAs will often write their own checks, and then have the code do something based off of the results. Being able to cap total I/O consumption means no longer having to manage these checks by hand.

And as usual, you can move these long-running queries to a lower-priority resource group as they become a problem. It's like being put in the penalty box at a hockey game. If you're going to write SQL that drags the system down, then you'll be given fewer resources so that you can't affect others. And of course, as a result, your query will take a lot longer.

Result Cache

Result Cache is a feature that can make or break you, depending on who's holding the wheel. Result Cache allows you to effectively pin a query result into a special buffer in memory, therefore bypassing the disk lookup for subsequent calls to the same query. You can cache entire queries, subqueries, or even PL/SQL functions. Of course, the query call has to be the same as the cached version -- and therein lies the rub. Quite often, queries differ only in the parameters being passed (the case with most OLTP queries), so you'll have a lot of individual queries being cached, but very little reuse.

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.