| |
 |
|
Oracle trend analysis & performance changes in SQL statements
Oracle Tips by Burleson Consulting |
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 (WISE)
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.
WISE for a specific SQL statement in WISE
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 WISE 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 WISE tool
Table access signature in the WISE 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:
- WISE: The Workload Interface
Statistical Engine, www.wise-oracle.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. |
 |
|
Need Oracle training?
- Get Oracle training from a practicing Oracle
expert
- Get custom training designed to
fit your needs
- Conveniently offered at your
workplace, anywhere in the USA
BC Oracle training offers some of the
USA's most respected Oracle experts and authors. Why spend
thousands on cookie cutter Oracle classes when you can have the
personalized attention of a real Oracle
guru? Just call now: |
|
|
