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 analysis & performance changes in SQL statements

Oracle Database Tips by Donald Burleson

These are snippets from 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 trend-based performance signatures for SQL

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. Let's examine how signatures can be used to identify time-series execution changes in SQL.

 For more details on general forecasting techniques, see Oracle trending forecasting Signature Analysis

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.

The dba_hist_sql_plan table contains time-series data about each object, table, index, or view, involved in the query.  The important columns include the cost, cardinality, io_cost and temp_space required for the object.

The sample query below retrieves SQL statements which have high query execution costs identified by Oracle optimizer and stored inside the dba_hist_sql_plan table:

col c1 heading 'SQL|ID'              format a13
col c2 heading 'Cost'                format 9,999,999
col c3 heading 'SQL Text'            format a200
 
 
select
  p.sql_id            c1,
  p.cost              c2,
  to_char(s.sql_text) c3
from
  dba_hist_sql_plan    p,
  dba_hist_sqltext     s
where
See Code depot for full script
;

The output of the above query might look like this, showing the high cost SQL statements over time:

SQL
ID                  Cost SQL Text
------------- ---------- -------------------------------------------
847ahztscj4xw    358,456 select
                            s.begin_interval_time  c1,
                            pl.sql_id               c2,
                            pl.object_name          c3,
                            pl.search_columns       c4,
                            pl.cardinality          c5,
                            pl.access_predicates    c6,
                            pl.filter_predicates    c7
                         from
                            dba_hist_sql_plan pl,
                            dba_hist_snapshot s
                         order by
                            c1, c2
 
58du2p8phcznu      5,110 select
                            begin_interval_time  c1,
                            search_columns       c2,
                            count(*)             c3
                         from
                            dba_hist_sqltext
                         natural join
                            dba_hist_snapshot
                         natural join
                            dba_hist_sql_plan
                         where
                            lower(sql_text) like lower('%idx%')
                         group by
                            begin_interval_time,search_columns

 

As we see, the dba_hist_sqlstat table is a goldmine for time-series SQL analysis and this table is very similar to the v$sql view, but it contains important SQL metrics for each snapshot.  These include important change information on disk reads and buffer gets, as well as time-series delta information on application, I/O and concurrency wait times.

 

col c1 heading 'Begin|Interval|time'    format a8
col c2 heading 'SQL|ID'                 format a13
col c3 heading 'Exec|Delta'             format 9,999
col c4 heading 'Buffer|Gets|Delta'      format 9,999
col c5 heading 'Disk|Reads|Delta'       format 9,999
col c6 heading 'IO Wait|Delta'          format 9,999
col c7 heading 'Application|Wait|Delta' format 9,999
col c8 heading 'Concurrency|Wait|Delta' format 9,999
 
 
break on c1
 
select
  to_char(s.begin_interval_time,'mm-dd hh24')  c1,
  sql.sql_id               c2,   
  sql.executions_delta     c3,
  sql.buffer_gets_delta    c4,
  sql.disk_reads_delta     c5,
  sql.iowait_delta         c6,
  sql.apwait_delta         c7,
  sql.ccwait_delta         c8
from
   dba_hist_sqlstat         sql,
   dba_hist_snapshot         s
where
   s.snap_id = sql.snap_id
order by
   c1,
   c2
;

 

The following is a sample of the output.  This is very important because the changes in SQL execution over time periods can be visualized.  For each snapshot period, it is possible to see the change in the number of times that the SQL was executed as well as important performance information about the performance of the statement.

 

Begin                         Buffer   Disk         Application Concurrency
Interval SQL             Exec   Gets  Reads IO Wait        Wait        Wait
time     ID             Delta  Delta  Delta   Delta       Delta       Delta
-------- ------------- ------ ------ ------ ------- ----------- -----------
10-10 16 0sfgqjz5cs52w     24     72     12       0           3           0
         1784a4705pt01      1    685      6       0          17           0
         19rkm1wsf9axx     10     61      4       0           0           0
         1d5d88cnwxcw4     52    193      4       6           0           0
         1fvsn5j51ugz3      4      0      0       0           0           0
         1uym1vta995yb      1    102      0       0           0           0
         23yu0nncnp8m9     24     72      0       0           6           0
         298ppdduqr7wm      1      3      0       0           0           0
         2cpffmjm98pcm      4     12      0       0           0           0
         2prbzh4qfms7u      1  4,956     19       1          34           5
 
10-10 17 0sfgqjz5cs52w     30     90      1       0           0           0
         19rkm1wsf9axx     14     88      0       0           0           0
         1fvsn5j51ugz3      4      0      0       0           0           0
         1zcdwkknwdpgh      4      4      0       0           0           0
         23yu0nncnp8m9     30     91      0       0           0           5
         298ppdduqr7wm      1      3      0       0           0           0
         2cpffmjm98pcm      4     12      0       0           0           0
         2prbzh4qfms7u      1  4,940     20       0           0           0
         2ysccdanw72pv     30     60      0       0           0           0
         3505vtqmvvf40      2    321      5       1           0           0

 

This report is especially useful because it is possible to track the logical I/O (buffer gets) versus the disk I/O for each statement over time, thereby yielding important information about the behavior of the SQL statement.

This output gives a quick overview of the top SQL during any AWR snapshot period and shows how their behavior has changed since the last snapshot period.  Detecting changes in the behavior of commonly executed SQL statements is the key to time-series SQL tuning.

A WHERE clause can easily be added to the above script and the I/O changes plotted over time:

See Code depot for full script

The following output shows changes to the execution of a frequently used SQL statement and how its behavior changes over time:

 

Begin               Buffer   Disk            App   Cncr      CPU    Elpsd
Interval     Exec     Gets  Reads IO Wait   Wait   Wait     Time     Time
time        Delta    Delta  Delta   Delta  Delta  Delta    Delta    Delta
-------- -------- -------- ------ ------- ------ ------ -------- --------
10-14 10      709    2,127      0       0      0      0  398,899  423,014
10-14 11      696    2,088      0       0      0      0  374,502  437,614
10-14 12      710    2,130      0       0      0      0  384,579  385,388
10-14 13      693    2,079      0       0      0      0  363,648  378,252
10-14 14      708    2,124      0       0      0      0  373,902  373,902
10-14 15      697    2,091      0       0      0      0  388,047  410,605
10-14 16      707    2,121      0       0      0      0  386,542  491,830
10-14 17      698    2,094      0       0      0      0  378,087  587,544
10-14 18      708    2,124      0       0      0      0  376,491  385,816
10-14 19      695    2,085      0       0      0      0  361,850  361,850
10-14 20      708    2,124      0       0      0      0  368,889  368,889
10-14 21      696    2,088      0       0      0      0  363,111  412,521
10-14 22      709    2,127      0       0      0      0  369,015  369,015
10-14 23      695    2,085      0       0      0      0  362,480  362,480
10-15 00      709    2,127      0       0      0      0  368,554  368,554
10-15 01      697    2,091      0       0      0      0  362,987  362,987
10-15 02      696    2,088      0       0      0      2  361,445  380,944
10-15 03      708    2,124      0       0      0      0  367,292  367,292
10-15 04      697    2,091      0       0      0      0  362,279  362,279
10-15 05      708    2,124      0       0      0      0  367,697  367,697
10-15 06      696    2,088      0       0      0      0  361,423  361,423
10-15 07      709    2,127      0       0      0      0  374,766  577,559
10-15 08      697    2,091      0       0      0      0  364,879  410,328

In the listing above, it is possible to see how the number of executions varies over time.

In the figure below, we can plot this time-series data for the particular sql_id of interest:

 

A time-series plot for particular SQL statement (Ion)

The above example shows the average elapsed time for the SQL statement over time.  Of course, the execution speed may change due to any number of factors 

  • Different bind variables
  • Database resource shortage
  • High physical reads  from data buffer shortage

With this information, it is possible to drill down into those specific times when SQL statements performed badly and see exactly why its execution time was slow.

For example, one can chart the average executions by day-of-the-week as shown below.

 

Ion for a specific SQL statement in Ion tool

The above script can be changed slightly in order to examine logical I/O (consistent gets) versus physical I/O (disk reads) averages for any given SQL statement as shown below.

 

Logical vs. physical I/O averages for a specific SQL statement in Ion tool.

The plot above shows that the ratio of logical to physical reads changes depending on the day of the week.  If execution speed for this SQL query is critical, the DBA would want to examine those times when it has high physical disk reads and consider segregating the tables that participate in this query into the KEEP pool.

 

How are my tables accessed?

There is much more information in dba_hist_sql_plan besides SQL performance details, and we can use this same table to summarize how our individual tables are being accessed.  The query below will extract important costing information for all objects involved in each query.  SYS objects are not counted.

 

col c1 heading 'Owner'              format a13
col c2 heading 'Object|Type'        format a15
 
col c3 heading 'Object|Name'        format a25
col c4 heading 'Average|CPU|Cost'   format 9,999,999
col c5 heading 'Average|IO|Cost'    format 9,999,999
 
 
break on c1 skip 2
break on c2 skip 2
 
 
select
  p.object_owner    c1,
  p.object_type     c2,
  p.object_name     c3,
  avg(p.cpu_cost)   c4,
  avg(p.io_cost)    c5
from
  dba_hist_sql_plan p
where
See Code depot for full script

The following is a sample of the output.  The results show the average CPU and I/O costs for all objects that participate in queries, over time periods.

 

                                                           Average    Average
              Object          Object                           CPU         IO
Owner         Type            Name                            Cost       Cost
------------- --------------- ------------------------- ---------- ----------
OLAPSYS       INDEX           CWM$CUBEDIMENSIONUSE_IDX         200          0
 
 
OLAPSYS       INDEX (UNIQUE)  CWM$DIMENSION_PK
OLAPSYS                       CWM$CUBE_PK                    7,321          0
OLAPSYS                       CWM$MODEL_PK                   7,321          0
 
 
OLAPSYS       TABLE           CWM$CUBE                       7,911          0
OLAPSYS                       CWM$MODEL                      7,321          0
OLAPSYS                       CWM2$CUBE                      7,121          2
OLAPSYS                       CWM$CUBEDIMENSIONUSE             730          0
 
 
MYSCHEMA                      CUSTOMER_DETS_PK              21,564          2
MYSCHEMA                      STATS$SGASTAT_U               21,442          2
MYSCHEMA                      STATS$SQL_SUMMARY_PK          16,842          2
MYSCHEMA                      STATS$SQLTEXT_PK              14,442          1
MYSCHEMA                      STATS$IDLE_EVENT_PK            8,171          0
 
 
SPV           INDEX (UNIQUE)  WSPV_REP_PK                    7,321          0
SPV                           SPV_ALERT_DEF_PK               7,321          0
 
 
SPV           TABLE           WSPV_REPORTS                 789,052         28
SPV                           SPV_MONITOR                   54,092          3
SPV                           SPV_SAVED_CHARTS              38,337          3
SPV                           SPV_DB_LIST                   37,487          3
SPV                           SPV_SCHED                     35,607          3
SPV                           SPV_FV_STAT                   35,607          3

 

This script can now be changed to allow the user to enter a table name and see changes in access details over time:

 

accept tabname prompt 'Enter Table Name:'

See Code depot for full script

 

This script is great because it is possible to see changes to the table's access patterns over time, which is a very useful feature:

 

Begin                                             Average    Average
Interval            Object     Object                 CPU         IO
time     Owner      Type       Name                  Cost       Cost
-------- ---------- ---------- --------------- ---------- ----------
10-25 17 MYSCHEMA   TABLE      CUSTOMER_DETS       28,935          3
10-26 15 MYSCHEMA              CUSTOMER_DETS       28,935          3
10-27 18 MYSCHEMA              CUSTOMER_DETS    5,571,375         24
10-28 12 MYSCHEMA              CUSTOMER_DETS       28,935          3

Below we see a time-series plot for table access pattern produced by Ion tool

Table access signature in the Ion tool.

Even better, we can see exactly how our tables are accessed as a whole.  If a DBA is really driven to know their system, all they need to do is understand how SQL accesses the tables and indexes in the database to provide amazing insight.  The optimal instance configuration for large-table full-table scans is quite different than the configuration for an OLTP databases, and the report generated by the awr_sql_scan_sums.sql script will quickly identify changes in table access patterns.

 

col c1 heading 'Begin|Interval|Time'          format a20
col c2 heading 'Large|Table|Full Table|Scans' format 999,999
col c3 heading 'Small|Table|Full Table|Scans' format 999,999
col c4 heading 'Total|Index|Scans'            format 999,999
 
 
select
  f.c1  c1,
  f.c2  c2,
  s.c2  c3,
  i.c2  c4
from 
(
select
  to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,
  count(1)                           c2
from
   dba_hist_sql_plan p,
   dba_hist_sqlstat  s,
   dba_hist_snapshot sn,
   dba_segments      o
where
See Code depot for full script
;

The sample output looks like the following, where there is a comparison of index versus table scan access.  This is a very important signature for any database because it shows, at a glance, the balance between index (OLTP) and data warehouse type access.

                          Large      Small
Begin                     Table      Table    Total
Interval             Full Table Full Table    Index
Time                      Scans      Scans    Scans
-------------------- ---------- ---------- --------
04-10-22 15                   2         19       21
04-10-22 16                              1        1
04-10-25 10                             18       20
04-10-25 17                   9         15       17
04-10-25 18                   1         19       22

Visualizing signatures is a great way to spot changes in execution, but the real value happens when we close-the-loop and automatically trigger a corrective action via dbms_scheduler. 

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: The Workload Interface Statistical Engine, 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

 

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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational