You’ve got APPTUNE. You’ve got SQL Explorer. You even have the entire SQL Performance solution. You’re collecting all this data and looking at it regularly, finding long running SQL, comparing explains and doing so much more. However, you’d like to go back in history. I’m not talking about saving the archives for a few days or a week longer, I’m talking about going back six, eight or even twelve months. You’re not going to keep old archive trace data for twelve months but it certainly would come in handy if you wanted to compare performance or possibly check to see if an index is being used over a long period of time. You certainly want to include month end, quarter end and even year end. How do you do that without keeping all those archives?
Luckily, the SQL Performance team has provided the objects, programs and jobs to extract the data from the DBC and populate DB2 tables. This piece of the solution is optional but it’s certainly worth looking at. In addition to being able to analyze all that data with simple SQL, the REORG Advisor and Exception Advisor components are driven from that data. I’ll discuss these advisor later.
There are plenty of tables and columns but you are not restricted to creating all of them. You can optionally choose to create and populate the set of tables that would benefit your organization. The tables consist of interval, daily, weekly and monthly set of objects, SQL and workload tables. You could also choose to store a history of the SQL statements, errors, exceptions and Index Usage. In addition, you may choose to keep a history of the Real Time Statistics tables, one for the tablespaces and another for the indexes. The RTS history tables come in very handy when you want to see the growth trend on any particular object. The job and DDL you need to customize and create all the objects are in the install SAMP library, member PASETUP.
Once the tables are created, BMC has provided the jobs and programs that extract the data, summarizes it and loads it into the tables. For instance, imagine we are looking at the weekly set of tables. At midnight Monday (Tuesday morning), you’ll run a job (member IODLODWE in SAMP library) that will extract all the data from Monday and populate the Statement, Object and Workload statistics tables with all the data collected on Monday. On Wednesday morning, just after midnight, you’ll run the same job to extract Tuesday’s data and merge it with the data already collected from Monday to give you a summary for the two days. The process continues for the rest of the week until you capture Monday’s data for the following week which enters new rows. In other words, at the end of the full week, you’ll have an average accounting record for each statement or object for the entire week, one row per statement or object. The same could be said for the monthly jobs allowing you to keep months of accounting information for comparison. BMC has provided some sample queries in member QUERIES in the SAMP library but you could write your own queries to analyze the information you desire. All the column definitions are found in the SAMP library member PACOLMNS.
Some of the sample queries BMC provides answer the following questions:
- Identify objects with degraded performance
- Identify Objects with spike in getpages
- Identify read only objects
- Identify non-unique/non-clustered indexes that had no read activity
- Identify number of added index levels since last REORG/Rebuild/Load
- Identify growth trends based on daily, weekly or monthly object statistics
- Identify which statements failed to get the degree of parallelism planned
- Identify statements with RID list failures
The Advisors that come as part of the SQL Performance Solution are dependent on the data in the PADB. REORG Advisor works differently than any other product on the market. There are two parts to this advisor. First, the program will examine the RTS to see if the table is disorganized. If it is, then a closer examination of the performance of the SQL that accessed this object will be analyzed. The program will look at the last time a REORG was executed and compares the performance of the SQL then compared to how it’s performing now. If there isn’t a significant difference, then a REORG would not be of any performance benefit. If the performance was better after the REORG, then a row would be inserted into the BMC_CANDIDATE table indicating that this object is disorganized and would benefit from a REORG.
Reorg Advisor Report (click on the image for a closer look)
The Exception Advisor provides a report that compares an exception with previous executions of that statement. Depending on which data you choose to keep, the exception would be compared to executions in previous days, weeks or months.
This is just a glimpse at some of the things you can do with the Performance Advisor Database. PADB is part of the SQL Performance Solution and it is included at no extra charge when you purchase or upgrade to the SQL Performance Solution.
For more details regarding the uses and implementation of this database, contact your BMC Software Consultant or support