Is internal database optimization a cure for performance bottlenecks?

How important the fast database response is, I hope I needn't explain. But making database easily available and performing well when a system rapidly grows is hard to enforce even for experienced database administrators. In this post I would like to describe the impact of internal database objects on database performance for different processes taking place in database.

The classical approach

If performance problems occur then each DBA should have some steps to proceed in order to improve database effectiveness. Donald Burleson, one of the world's Oracle experts, suggests using hierarchy like the one described below (see “Oracle Tuning: The Definitive Reference” book):

• Review the External Environment – CPU, RAM, Network, Disk bottlenecks

• Review the Instance Metrics – find top wait events over time

• Perform the Instance Tuning – determine best long-term setting for initialization parameters

• Perform the Object Tuning – optimize tablespaces, tables, indexes, etc.

• Perform the SQL Tuning – optimize execution plans

However, it is important not to act like a cowboy and blindly follow all steps listed above. If one does, then it's very easy to decrease performance in some unforeseeable way if we really don't understand the internal mechanisms.

To help DBA analyzing and reporting stored in database objects, Oracle provide tools like Statspack and Automatic Workload Repository (AWR). First Statspack's release was put out together with Oracle 8i and it is used nowadays in latest Oracle database version. However, next generation of this tool was created and it is called AWR. Main differences between these utility tools are shown in table below (feel free to comment below the post):

Oracle Statspack vs. Oracle AWR comparison

Criterion	Statspack	AWR
Oracle release	8i and newer	10g and newer
Price tag	Free	Additional cost (information not allowed for public; your sales person tell you more)
Installation	More complex (need additional database account, many tasks require manual admin work)	Created automatically at database installation time
Standalone	Yes (it can be added or deleted any time)	No (it’s located in database kernel and can’t work without it)
Usage database views / tables	Yes (gather information in stats$ database views)	Yes (gather information in wrh$ database views)
Gather snapshots	Manually or Oracle job	MMON process
Gather Active Session History	No	Yes (help with analyse of wait events for each session)
Purge old snapshots	Manually ( Statspack stops working when tablespaces run out of space!)	Automatically (MMON process)

As you can see, there are both pros and cons of using one of them. The big advantage of using commercial AWR instead of free Statspack is capturing more accurate statistics. It performs more steps automatically, and we don't have to create external scheduler as Manageability Monitor (MMON) process does it for us.

The  prospective approach

In GridwiseTech we have created our own methodology as a kit of patterns and tools which help with database optimization. Our methods are not only restricted to internal database optimization, but in order to increase chance of success with performance improvements they also include solutions like In-Memory Database (like Oracle TimesTen, solidDB, Polyhedra, Altibase etc.) or In-Memory Data Grid (like GigaSpaces, GemStone, JBoss Cache, Oracle Coherence etc.) as well. Adding such technologies to existing infrastructure always causes changes in the way database works inside, but the changes are not usually visible to database users. During the process of changing architecture it is necessary to monitor and change parameters to best adjust to new infrastructure. From our experience, the best results have been seen when internal solutions, such as database views or tables optimization, have been used compared to the solutions from third party vendors.

Some final thoughts

One may ask, why am I talking about internal database objects and In-Memory solutions in one blog post? That is a good question because provisionally these subjects are not related, but keep in mind that only provisionally. If you want to implement one of In-Memory solutions you shouldn't use profilers dedicated only for In-Memory solutions, but monitor new database behaviour during tests and change database and operating system properties as soon as it proves to improve.

If care is not taken of internal databases performance, then In-Memory solutions will also suffer. On many occasions, when the system is deployed, the required data can be held all in memory. However, as the system scales, not all required data can be held in the cache, due to lack of RAM. The underlying database reduces performance with the increased communication and data synchronization between the In-Memory solutions and the underlying database.

One of my favourite examples that proves the thesis described below is a manufacturing company from Malaysia. They have databases with the amount of space totalling dozens of terabytes. Their main tables have a few millions of rows, so it was impossible to put the whole infrastructure to memory. Our idea was to detect the most time-consuming objects and keep them in RAM. Unfortunately, we still needed to connect to the database for the expected data very often. First performance test showed that the speed-up was less than 2 times – such a result wasn't satisfactory neither for the customer nor for us! It was just using the internal database system objects and In-Memory profilers, making both layers 'one organism', that gave us 10 times speed-up.

Consequently, it is important to highlight that the additional layer provided by In-Memory solutions, can not be seen as a fix-all solution. All layers must work together to provide optimal performance. Only profiling the In-Memory layer, is a short sighted approach.