For the past few weeks, David Fletcher and I have covered how to manually collect script performance information and create spreadsheets for analysis. We end the topic this week with an explanation and example of how to automate this collection process.
Our goal is to create reliable data collection in a timely manner without impacting the system significantly. To that end, we need to address two key concerns:
- How often should scripts run in a day, and how many days should they run per month?
- How big of an impact will the script analysis create on the system, since the analysis is done in Oracle?
Frequency
How often you should run scripts varies, but we have some guidelines to help you determine the answer. You do not want to take snapshots too often, since you could degrade the performance of the database. I would suggest taking snaps an hour or more apart at first. Taking a snapshot every hour is going to be noticeable since it collects all of the data held in the GV$SQL and GV$SQLTEXT views. You might consider taking snapshots every two hours, but you want to ensure script data is not being flushed from the aforementioned views. Taking more snaps to start will allow you to perform analysis of several snapshots to determine the correct frequency. My example below is built with snapshots every two hours, with several “at –f” commands commented out to reduce the number of snapshots taken.
The frequency for days per month, like snaps per day, can start higher for initial analysis and then taper off. Even though it will have a higher impact, this greater frequency helps create a baseline. Depending on your site, this start could be every day for one week of the month (which will help account for workload changes according to the day), or two or three weekdays in a month.
Impact
The cost associated with script collection and analysis will be available in your data, so be sure to review the cost of DM2_SQLAREA_GATHER and DM2_SQLAREA_ANALYZE scripts in your output files. Make sure to schedule snapshots around each DM2_SQLAREA_ANALYZE command execution, even if you run all of them at a low usage time of night (my example later runs all of them at once).
AIX Auto Collection
To see a detailed example from a previous AIX site I worked with, click here.
Prognosis: Automation will create consistent data collection for more reliable script analysis.
