Since databases will have a time delay to respond to the new allocation of resources, using real time industrial control systems to automatically tune database resources will not be easy. The walk around is to record the cyclic performance behavior (baseline) of a database and tune it with a dynamic or static configuration based on the particular cyclic behavior, (some domain experts call it the “Signature” or “Baseline” of a database, but I like to call it “Cyclic behavior”). Basically, to analyze or forecast this behavior involves some complicated mathematical time-series forecasting and modeling techniques.

After two years of research, I found that using traditional mathematics is not an easy way to solve database’s time-series forecasting and modeling problems; so I tried to develop a new AI method to solve this problem. The result looks good and most database statistics can be modeled and forecasted properly, but it is not the main topic of today’s discussion. Once the prototype was completed, I tried to see whether I could use this algorithm in our Quest SQL Optimizer product. In order to collect some sample data, I scheduled a SQL statement which was randomly fired each hour in our server for two months. 

The following is the SQL and the execution plan
SELECT COUNT (*) FROM EMPLOYEE E, DEPARTMENT D
WHERE E.EMP_DEPT = D.DPT_ID

The size of the “Employee” and “Department” tables was not changed; this QA server was used to test our SQL Tuning products without heavy workload.  

I then enabled AWR to collect statistics for this SQL statement for two months. The statistics show that the execution plan remained unchanged for the 2 month period; however, the Elapsed Time and CPU fluctuated quite significantly.  I had predicted that the CPU time would have been a steady line for such a simple SQL statement, with the Elapsed Time fluctuating a bit according to the resource availability of the server.  I WAS WRONG.

The CPU variation was as high as 30% and the SQL Elapsed Time variation was as high as 200%. This story tells us that to predict a SQL statement's performance is not an easy job, regardless of how good the cost estimation in the execution plan or whether you have actually executed the statement with actual statistics. The significant factor is actually the current system's resources availability, which means that the combination of various resources (uch as CPU, Memory, IO and etc…) at each time will make your SQL runtime differently. 

So, don't blame your database optimizer the next time you encounter a poor performing SQL statement - they are already doing a good job.

Elapsed Time

CPU Time