Oracle trend Signature Analysis Oracle Tips by Burleson Consulting

Oracle trend-based performance signatures

There are two ways to address Oracle performance, proactive (a priori) approach and reactive (after the problem has begun).  Time-based proactive tuning is a proven approach to long term success, but the problem is being able to get accurate performance data. 

Proactive tuning differs from ordinary Oracle tuning because of the time dimension that allows the observation of performance in a real-world fashion.  Proactive tuning yields complete “signatures”, patterns of behavior for all performance metrics, and this is invaluable information that allows the prediction of the future through the examination of the past.  As George Santayana said: “Those who cannot remember the past are condemned to repeat it”, and this is especially applicable to Oracle database tuning.

Almost every Oracle database exhibits patterns that are linked to regular processing schedules, called “signatures”.  These signatures allow the DBA to plan long-term solutions based on a database’s documented performance over time. 

Once you find your baselines and signatures for your important performance metrics, time-based tuning becomes the pursuit, recognizing that the Oracle database’s needs may change dramatically depending on the time of day, day of the week, and week of the month.  Once the DBA understands the signatures of the important metrics in the database, the Oracle10g scheduler, (the dbms_scheduler package) can be used to re-allocate system resources just-in-time to anticipate a repeating event.

In the real world, all Oracle applications follow measurable, cyclical patterns called signatures. For example, an Oracle Financials application may be very active on the first Monday of every month when all of the books are being closed and the financial reports are being prepared. Using AWR data, information can be extracted from every first Monday of the month for the past year which will yield a valid signature of the specific performance needs of the end of the month Oracle financials applications.

This article is just a taste of the wonderful value within the STATSPACK and AWR tables, and I highly encourage you to explore the books and tools in the reference section for more detailed information and scripts.  Let’s start with an overview of time-series data capture.

Capturing time-series performance data

Oracle professional have been capturing and graphing time-series data since Oracle7, back when we used special extensions to the Oracle bstat-estat utility to store Oracle performance data into tables for detailed analysis.

When the STATSPACK utility was introduced in Oracle8i, Oracle professionals were first introduced to time-series data analysis with several Oracle Press books devoted to STATSPACK analysis.  STATSPACK eventually became required for all calls to Oracle MetaLink and efforts were made to incorporate time-series data into the database kernel.

With the incorporation of the Automated Workload Repository (AWR) into the Oracle database kernel, Oracle tuning professionals have been given a gold-mine data repository that allows the leisurely analysis of Oracle performance statistics and trends over time. 

STATSPACK and AWR are very similar, so let’s move forward using the Oracle 10g AWR for our examples and explore using script to find performance signatures.

AWR and the Predictive tuning approach

The AWR data can be used to plot values of many important performance metrics, averaged by hour-of-the-day and day-of-the-week.  For example, plotting physical reads and writes signatures will give the DBA insights into the regular variations in database stress.  This information is critical to scheduling just-in-time changes to SGA resources, which is the foundation of creating a self-tuning database.

The AWR allows DBAs to devise a general tuning strategy that addresses the different kinds of processing that take place within all Oracle application.  History repeats itself, we can see it coming, and we take corrective actions before the database is crippled.

Finding Oracle Data Baselines

Oracle databases are always changing, and the databases that are examined at 10:00 AM may be completely different than the databases that exist at 3:00 PM.  For an common example, when the performance of Oracle disk I/O is examined over different time periods, regular signatures appear when the I/O information is aggregated by hours of the day and day of the week as plotted below using the WISE plotting tool (Note that you can also plot this data in MS Excel spreadsheets, using the chart wizard):

WISE plot of average disk reads and writes by hour of the day

Oracle professionals can easily query Oracle STATSPACK or Oracle10g AWR table information to gather these baselines.  Once the repeating I/O trends have been identified, the DBA will be able see repeating trends in disk I/O.

I/O information can be captured at the file level and this can give insight into the way to configure your I/O subsystem (such as applying super0fast solid-state disks).  The following script extracts the physical read information from the Oracle 10g dba_hist_filestatxs  view:

The sample output below shows a running total of physical reads by datafile.  The snapshots are collected every half-hour.  Starting from this script, the DBA could easily add a WHERE clause criteria and create a unique time-series exception report.

A little tweaking to this script and the DBA could report on physical writes, read time, write time, single block reads, and a host of other interesting metrics from the dba_hist_filestatxs view.  Now that you get the idea, lets take a closer look at signature analysis. 

Plotting time-series data for signature analysis

As we have noted, signatures are hidden inside the database and the only way to see these trends is to aggregate and plot database metrics by day-of-the-week and by hour-of-the-day.  The following report, called rpt_sysstat_hr_10g.sql, shows the signature for any Oracle system statistic, averaged by hour-of–the-day.

In the output, there is an average for every hour of the day.  This information can be easily pasted into an MS Excel spreadsheet and plotted with the chart wizard or the WISE tool, which is included free with this book:

Below we see the data after it has been pasted into an MS Excel spreadsheet and plotted with the Excel chart wizard, in this case revealing hourly patterns of disk reads by hour of the day:

The WISE tool is an inexpensive alternative to plotting with Excel spreadsheets and it is handy because it is point-and-click.  You can see www.wise-oracle.com for download instructions.

Signature output from the WISE viewer

The same types of reports aggregated by day-of-the week can also be used to see ongoing daily trends.  Over long periods of time, almost all Oracle databases will develop distinct signatures that reflect the regular daily processing patterns of the end user community.

The following rpt_sysstat_dy_10g.sql script will accept any of the values from dba_hist_sysstat   and plot the average values by hour-of-the-day.

The following is the output from this script:

SQL> @rpt_sysstat_dy

This will query the dba_hist_sysstat view to display

average values by day-of-the-week

Enter Statistics Name:  physical reads

SNAP_TIME              AVG_VALUE

------------------- ------------

sunday                   190,185

monday                   135,749

tuesday                   83,313

wednesday                139,627

thursday                 105,815

friday                   107,250

saturday                 154,279

These results provide an average for every day of the week as shown graphically below.  These types of signatures will stabilize for most Oracle databases and can be used to develop a predictive model for proactive tuning activities.

The WISE tool for a Signature for average physical reads by day of the week

There is not room in this short article for the full-instance tuning approach, but these important scripts should give you the idea about how to plot your STATSPACK and AWR data.  Remember, this time-series data is also available for specific SQL statements, a gold mine for the tuning professional where patterns of changing execution can be examined.  Next, let’s examine how signatures can be used to identify time-series execution changes in SQL.

Time-series analysis of specific SQL statements

Most Oracle databases are remarkably predictable, with the exception of DSS and ad-hoc query systems, and the DBA can quickly track the usage of all SQL components.  Understanding the SQL signature can be extremely useful for determining what objects to place in the KEEP pool, and to determining the most active tables and indexes in the database.

Once a particular SQL statement for which details are desired has been identified, it is possible to view its execution plan used by optimizer to actually execute the statement. For examples and scripts, click below:

Oracle trend analysis performance changes in SQL statements

Of course, this sophisticated trend analysis is only possible after you have spent considerable time understanding your basic trend analysis patterns.

Conclusion

This paper is just a small taste of a very large and exciting subject and it is my hope that you will take these scripts and begin exploring the unique signatures within your own systems.  As people get more sophisticated in their self-tuning endeavors, many more Oracle metrics may become self-tuning. In Oracle10g, the self-tuning capability increases greatly, and it becomes even easier to write detection scripts and schedule tasks to adjust Oracle based on the processing needs. 

The AWR is a veritable goldmine of information, and as Oracle professionals explore more nuances of these remarkable data sources, more sophisticated predictive models will be developed to predict and correct Oracle bottlenecks before they occur.

References:

• WISE: The Workload Interface Statistical Engine, www.wise-oracle.com
   
• Oracle Tuning: The Definitive Reference, Alexey B. Danchenkov and Donald K. Burleson, Rampant TechPress, ISBN 0-9744486-2-1
   
• Creating a Self-Tuning Oracle Database, Donald K. Burleson, Rampant TechPress, ISBN 0-9727513-2-7 
   
• Oracle9i High-performance tuning with STATSPACK, Donald K. Burleson, Oracle Press, ISBN: 007222360X

For more details, see my new book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.