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