Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Trend Reporting for Oracle Sorts
Oracle Tips by Burleson
 

One important task for you is to develop a sort signature. All databases show repeating trends, both by day of the week and hour of the day. By tracking and plotting sort activity averages for hours of the day and day of the week, you will have a sorting signature that will direct you to the SQL statements that are causing the sorting activity.

Sort Signature by Hour of the Day

The script that follows computes average sorts, aggregated by hour of the day. This script is extremely useful for spotting trends in sort activity.

rpt_avg_sorts_hr.sql

set pages 9999;

column sorts_memory  format 999,999,999
column sorts_disk    format 999,999,999
column ratio         format .99999

select
   to_char(snap_time,'HH24')      mydate,
   avg(newmem.value-oldmem.value) sorts_memory,
   avg(newdsk.value-olddsk.value) sorts_disk
from
   perfstat.stats$sysstat oldmem,
   perfstat.stats$sysstat newmem,
   perfstat.stats$sysstat newdsk,
   perfstat.stats$sysstat olddsk,
   perfstat.stats$snapshot   sn
where
   newdsk.snap_id = sn.snap_id
and
   olddsk.snap_id = sn.snap_id-1
and
   newmem.snap_id = sn.snap_id
and
   oldmem.snap_id = sn.snap_id-1
and
   oldmem.name = 'sorts (memory)'
and
   newmem.name = 'sorts (memory)'
and
   olddsk.name = 'sorts (disk)'
and
   newdsk.name = 'sorts (disk)'
and
   newmem.value-oldmem.value > 0
group by
   to_char(snap_time,'HH24')
;

Here is the output from the script. We can now take this data and create a graph in a spreadsheet:

TOSORTS_MEMORY   SORTS_DISK
-- ------------ ------------
00       18,855           11
01       19,546           15
02       10,128            5
03        6,503            8
04       10,410            4
05        8,920            5
06        8,302            7
07        9,124           27
08       13,492           71
09       19,449           55
10       19,812          106
11       17,332           78
12       20,566           76
13       17,130           46
14       19,071           61
15       19,494           68
16       20,701           79
17       19,478           44
18       23,364           29
19       13,626           20
20       11,937           17
21        8,467            7
22        8,432           10
23       11,587           10

Figure 11-2 shows the plot from the output. Here we see a typical increase in sort activity during the online period of the day. Sort activity rises about 8:00 a.m. and then goes down after 6:00 p.m.

Figure 2: Average memory sorts by hour of the day

Again, we can use these graphs to impress management and also look for clues about where to find SQL statements that cause significant sort activity.

Sort Activity by Day of the Week

We can also get some great trend information by plotting the sort activity averages by the day of the week. This can tell us those days when the sort activity is highest, and if we bounce our database each night, we may want to adjust the sort_area_size according to the expected amount of sort activity for each day.

Now, letís run the script to compute the averages by the day of the week.

rpt_avg_sorts_dy.sql

set pages 9999;

column sorts_memory  format 999,999,999
column sorts_disk    format 999,999,999
column ratio         format .99999

select
   to_char(snap_time,'day')       DAY,
   avg(newmem.value-oldmem.value) sorts_memory,
   avg(newdsk.value-olddsk.value) sorts_disk
from
   perfstat.stats$sysstat oldmem,
   perfstat.stats$sysstat newmem,
   perfstat.stats$sysstat newdsk,
   perfstat.stats$sysstat olddsk,
   perfstat.stats$snapshot   sn
where
   newdsk.snap_id = sn.snap_id
and
   olddsk.snap_id = sn.snap_id-1
and
   newmem.snap_id = sn.snap_id
and
   oldmem.snap_id = sn.snap_id-1
and
   oldmem.name = 'sorts (memory)'
and
   newmem.name = 'sorts (memory)'
and
   olddsk.name = 'sorts (disk)'
and
   newdsk.name = 'sorts (disk)'
and
   newmem.value-oldmem.value > 0
group by
   to_char(snap_time,'day')
;

Here is the output from the above script. Without a visual aid, it is not always easy to see spikes and trends in sorting activity. Letís take the result set from listing below, paste it into a spreadsheet and plot it using a chart wizard. This time, letís plot the disk sorts:

DAY       SORTS_MEMORY   SORTS_DISK
--------- ------------ ------------
friday          12,545           54
monday          14,352           29
saturday        12,430            2
sunday          13,807            4
thursday        17,042           47
tuesday         15,172           78
wednesday       14,650           43

Figure 11-3 is the graph showing average sorts per day. In this database, the activity pattern on Tuesday shows a large number of disk sorts, with another, smaller, spike on Thursdays. For this database, you may want to pay careful attention to the TEMP tablespaces on these days and perhaps issue an alter tablespace TEMP coalesce; command to create continuous extents in the TEMP tablespace. If you bounce the database every night, you may want to increase the sort_area_size every Tuesday to reduce the number of disk sorts. In Oracle, the sort_area_size can be changed with an alter system command, so bouncing the database is not required.

Figure 3: Average disk sorts by day of the week

At the risk of being redundant, I need to reemphasize that the single most important factor in the performance of any Oracle database is the minimization of disk I/O. Hence the tuning of the Oracle sorting remains one of the most important considerations in the tuning of any Oracle database.

Now letís wrap up this chapter by covering the main points and concepts about sorting.

Conclusion

This chapter recognizes that sorting is a time-consuming but necessary operation that is commonly performed for SQL result sets. The main points of this chapter include these:

  • Disk sorts are 14,000 times slower than in-memory sorts.

  • The sort_area_size parameter is the RAM region allocated for each connected session to perform sorting, except for users connecting into the multithreaded server.

  • Oracle will always try to resolve a sort in the memory region allocated by sort_area_size. Only after Oracle cannot continue the sort will Oracle invoke a disk sort and transfer the memory frames to the TEMP tablespace and continue the sort.

  • Some sorts will always be too big to sort in memory, and disk sorts for SQL in large batch reports cannot be avoided.

  • The STATSPACK utility is a great way to track the amount of disk sorting on your database and measure the results from increasing sort_area_size.

Next, letís take a look at how we can use Oracle hints to change the execution plans for Oracle SQL statements and how we can make permanent changes to the execution plan for SQL statements.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter