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 


 

 

 


 

 

 
 

dba_hist_sqlstat View

Oracle Tips by Burleson Consulting

The dba_hist_sqlstat View

This view contains more than 20 statistics related to SQL statements. The structure of the dba_hist_sqlstat view is:

 

SQL> desc DBA_HIST_SQLSTAT

 

Name                                      Null?    Type

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

SNAP_ID                                            NUMBER

DBID                                               NUMBER

INSTANCE_NUMBER                                    NUMBER

SQL_ID                                             VARCHAR2(13)

PLAN_HASH_VALUE                                    NUMBER

OPTIMIZER_COST                                     NUMBER

OPTIMIZER_MODE                                     VARCHAR2(10)

OPTIMIZER_ENV_HASH_VALUE                           NUMBER

SHARABLE_MEM                                       NUMBER

LOADED_VERSIONS                                    NUMBER

VERSION_COUNT                                      NUMBER

MODULE                                             VARCHAR2(64)

ACTION                                             VARCHAR2(64)

SQL_PROFILE                                        VARCHAR2(64)

PARSING_SCHEMA_ID                                  NUMBER

FETCHES_TOTAL                                      NUMBER

FETCHES_DELTA                                      NUMBER

END_OF_FETCH_COUNT_TOTAL                           NUMBER

END_OF_FETCH_COUNT_DELTA                           NUMBER

SORTS_TOTAL                                        NUMBER

SORTS_DELTA                                        NUMBER

EXECUTIONS_TOTAL                                   NUMBER

EXECUTIONS_DELTA                                   NUMBER

LOADS_TOTAL                                        NUMBER

LOADS_DELTA                                        NUMBER

INVALIDATIONS_TOTAL                                NUMBER

INVALIDATIONS_DELTA                                NUMBER

PARSE_CALLS_TOTAL                                  NUMBER

PARSE_CALLS_DELTA                                  NUMBER

DISK_READS_TOTAL                                   NUMBER

DISK_READS_DELTA                                   NUMBER

BUFFER_GETS_TOTAL                                  NUMBER

BUFFER_GETS_DELTA                                  NUMBER

ROWS_PROCESSED_TOTAL                               NUMBER

ROWS_PROCESSED_DELTA                               NUMBER

CPU_TIME_TOTAL                                     NUMBER

CPU_TIME_DELTA                                     NUMBER

ELAPSED_TIME_TOTAL                                 NUMBER

ELAPSED_TIME_DELTA                                 NUMBER

IOWAIT_TOTAL                                       NUMBER

IOWAIT_DELTA                                       NUMBER

CLWAIT_TOTAL                                       NUMBER

CLWAIT_DELTA                                       NUMBER

APWAIT_TOTAL                                       NUMBER

APWAIT_DELTA                                       NUMBER

CCWAIT_TOTAL                                       NUMBER

CCWAIT_DELTA                                       NUMBER

DIRECT_WRITES_TOTAL                                NUMBER

DIRECT_WRITES_DELTA                                NUMBER

PLSEXEC_TIME_TOTAL                                 NUMBER

PLSEXEC_TIME_DELTA                                 NUMBER

JAVEXEC_TIME_TOTAL                                 NUMBER

JAVEXEC_TIME_DELTA                                 NUMBER

 

The statistics for every SQL statement are stored in two separate columns:

§       <Statistic Name>_total column stores the total values of statistics since the last instance startup;

§       <Statistic Name>_delta column reflects the change in a statistic’s value between end_interval_time and begin_interval_time that is stored in the dba_hist_snapshotview.

Using this core dba_hist_sqlstat view, poor SQL statements can be identified using such criteria as:

§       High number of buffer gets.

§       High number of physical reads.

§       Large execution count.

§       High shared memory usage.

§       High version count.

§       High parse count.

§       High elapsed time.

§       High execution CPU time.

§       High number of rows processed.

§       High number of sorts, etc

This view does not contain the actual text of SQL statements; however, it does contain a sql_id column.  The Ion tool is great for using dba_hist_sqlstat for execution details:

Figure 15.21: Signature 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 in Figure 15.22.

 

Figure 15.22: 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.

This view 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.

awr_sqlstat_deltas.sql

 

 
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. Physical 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:

awr_sqlstat_deltas_detail.sql

 
col c1 heading ‘Begin|Interval|time’    format a8
col c2 heading ‘Exec|Delta’             format 999,999
col c3 heading ‘Buffer|Gets|Delta’      format 999,999
col c4 heading ‘Disk|Reads|Delta’       format 9,999
col c5 heading ‘IO Wait|Delta’          format 9,999
col c6 heading ‘App|Wait|Delta’         format 9,999
col c7 heading ‘Cncr|Wait|Delta’        format 9,999
col c8 heading ‘CPU|Time|Delta’         format 999,999
col c9 heading ‘Elpsd|Time|Delta’       format 999,999
 
accept sqlid prompt ‘Enter SQL ID: ‘
 
ttitle ‘time series execution for|&sqlid’
 
break on c1
 
select
  to_char(s.begin_interval_time,’mm-dd hh24’)  c1,
  sql.executions_delta     c2,
  sql.buffer_gets_delta    c3,
  sql.disk_reads_delta     c4,
  sql.iowait_delta         c5,
  sql.apwait_delta         c6,
  sql.ccwait_delta         c7,
  sql.cpu_time_delta       c8,
  sql.elapsed_time_delta   c9
from
   dba_hist_sqlstat        sql,
   dba_hist_snapshot         s
where
   s.snap_id = sql.snap_id
and
   sql_id = ‘&sqlid’
order by
   c1
;

 

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 Figure 15.20 below, the Ion tool allows one to plot time-series charts for particular sql_id of interest:

Figure 15.20: The time-series plot for particular SQL statement

 

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.

The Ion tool is also excellent for identifying SQL to tune and it can show SQL execution over time with stunning SQL graphics.

 

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest 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:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 


 

 

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