Many databases have, as part of their daily workload, batch updates or maintenance processes that run during off-hours. For example, a database that supports an accounts receivable function may have an account aging process that runs during the night when users are off the system. There also may be ETL (Extract-Transform-Load) or other maintenance functions that run in the off hours. Typically, these processes are part of a maintenance window in which the database is expected to be unavailable. In some cases, this window is negotiated into a Service Level Agreement (SLA) and can have cost implications if not adhered to. One of the challenges of the DBA is to ensure that even as the application and database grow over time, this maintenance window can still be achieved. When these processes run past the maintenance window, the end result is almost always application downtime and can even be worse. I have seen cases where the maintenance windows have gotten so out of hand that backups can no longer be run consistently. Since the cost of application downtime and/or data loss is often measured in the thousands if not millions of dollars, it is crucial that the maintenance windows be carefully monitored and managed. This is just one area where Dell's SQL Optimizer products can provide a significant return on investment.
There are various strategies to dealing with the maintenance window problem and most include some sort of archiving/purging process to limit database size. Another strategy is to try and performance tune the SQL statements involved in the processing. Proper SQL tuning requires specialized knowledge of the database internal optimizer combined with some trial and error to find the right solution. This can be time consuming and a busy DBA may not have this much time available. Wouldn't it be great if the DBA could automatically rewrite and test the SQL statements? With Dell's SQL Optimizer, they can!
To highlight how Dell's SQL Optimizer can be used to tune these types of statements, I've set up a simple example in my database. I have been told that every employee with a commission percentage of 22% will need to be updated to now have a commission percentage of 25% and I will need to run this as part of my maintenance window. Using SQL Optimizer's SQL Rewrite function, I can paste in my SQL statement, generate Rewrites for the statement, and then Batch Run the rewrites to see which rewrite performs the best.
In this example, I can see that the Alt7 rewrite gave me the fastest Elapsed Time – 4.56 seconds compared to my original statement which took 7.02 seconds to run – a 35% improvement in Elapsed Time. But what if I'm not as concerned with Elapsed Time as I am with Logical Reads or CPU Usage? I can see those results as well and compare that with my original. In this case, my CPU usage and Logical Reads both decreased as well. In some cases, that might not be the case so it is important to be able to see that and choose wisely.
You can learn more about SQL Optimizer for Oracle and download a trial copy here.
Start the discussion at forums.toadworld.com