Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Trend Signature Analysis


Oracle Database Tips by Donald BurlesonConsulting

These are snippets from my 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.

Note!  After a decade of work, Ion for Oracle is now available to to help you identify trends and signatures.

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.


Click "Play" to take the tour or visit
www.Ion-DBA.com
Ion for Oracle. Enterprisse Edition.

Ion Enterprise Edition

Also see my valuable notes on Oracle Monitoring Best Practices.

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 MOSC 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 Ion plotting tool (Note that you can also plot this data in MS Excel spreadsheets, using the chart wizard):

Ion 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:

break on begin_interval_time skip 2
 
column phyrds  format 999,999,999
column begin_interval_time format a25
 
select
   begin_interval_time,
   filename,
   phyrds
from
   dba_hist_filestatxs
  natural join
   dba_hist_snapshot
;

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.

SQL> @reads
 
BEGIN_INTERVAL_TIME       FILENAME                                 PHYRDS
------------------------- -------------------------------------- --------
24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF   164,700
                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF   26,082
                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF   472,008
                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF      1,794
                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA     2,123
                                                                          
                                                                         
24-FEB-04 11.30.18.296 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF   167,809
                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF   26,248
                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF   476,616
                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF      1,795
                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA     2,244

 

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.

rpt_sysstat_hr_10g.sql
 
 
prompt  This will query the dba_hist_sysstat view to
prompt  display average values by hour of the day
 
set pages 999
 
break on snap_time skip 2
 
accept stat_name char prompt 'Enter Statistics Name:  ';
 
col snap_time   format a19
col avg_value   format 999,999,999
 
select
   to_char(begin_interval_time,'hh24') snap_time,
   avg(value)                          avg_value
from
   dba_hist_sysstat
  natural join
   dba_hist_snapshot
where
See Code depot for full script

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 Ion tool, which is included free with this book:

SQL> @rpt_sysstat_hr
 
This will query the dba_hist_sysstat view to
display average values by hour of the day
 
Enter Statistics Name:  physical reads
 
SNAP_TIME              AVG_VALUE
------------------- ------------
00                       120,861
01                       132,492
02                       134,136
03                       137,460
04                       138,944
05                       140,496
06                       141,937
07                       143,191
08                       145,313
09                       135,881
10                       137,031
11                       138,331
12                       139,388
13                       140,753
14                       128,621
15                       101,683
16                       116,985
17                       118,386
18                       119,463
19                       120,868
20                       121,976
21                       112,906
22                       114,708
23                       116,340

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 Ion tool is an inexpensive alternative to plotting with Excel spreadsheets and it is handy because it is point-and-click.  You can see www.ion-dba.com for download instructions.

 

Signature output from the Ion 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.

prompt
prompt  This will query the dba_hist_sysstat view to
prompt  display average values by day-of-the-week
prompt
 
set pages 999
 
accept stat_name char prompt 'Enter Statistic Name:  ';
 
 
col snap_time   format a19
col avg_value   format 999,999,999
 
select
   to_char(begin_interval_time,'day')   snap_time,
   avg(value)                           avg_value
from
   dba_hist_sysstat
natural join
   dba_hist_snapshot;
 
where
See Code depot for full script

 

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 Ion tool for a Signature for average physical reads by hour of the day

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:

  • Ion for Oracle: www.ion-dba.com
     
  • Oracle Tuning: The Definitive Reference, 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

Follow the links for additional information on AWR trending and AWR forecasting.

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.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.