Lesson 5	Monitoring sort Activity
Objective	Monitor for disk sorts.

Monitor for Disk Sorts

Recall that in-memory sorts are always preferable to disk sorts, and disk sorts will surely slow down an individual task, as well as impact concurrent tasks on the Oracle instance. Also, excessive disk sorting will cause a high value for free buffer waits, paging other tasks data blocks out of the buffer.
Fortunately, Oracle provides several mechanisms for monitoring the amount of disk and memory sorting. The main source is the V$SYSSTAT view.

The V$SYSSTAT view

The V$SYSSTAT view gives an accumulated number for disk and memory sorts for the Oracle instance since startup time. The number of disk sorts should comprise a very small percentage of the total sorts. You can check this by issuing the following query against the V$SYSSTAT table:

COLUMN VALUE FORMAT 999,999,999
SELECT NAME, VALUE FROM V$SYSSTAT
  WHERE NAME LIKE 'sort%';
SPOOL OFF;

Here is the output:

SQL> @sorts
 
NAME                         VALUE
--------                    ---------
sorts (memory)                  7,019
sorts (disk)                       49
sorts (rows)                3,288,608

Here, you can see that there were 49 sorts to disk. Out of a total of 3.2 million total sorts, this is well below one percent and is probably acceptable for the database system.

Another V$SYSSTAT query

Below is another query against the Oracle data dictionary that interrogates the V$SYSSTAT view. This query will display the numbers of SQL queries (since database start time) that used each type of table access method.

set pages 9999;
column value format 999,999,999
 
select 
   name, 
   value 
from 
   v$sysstat
where 
   name like 'table%';
SQL> @sql
NAME                            VALUE
------------------------------- ------------
table scans (short tables)      73,304
table scans (long tables)       21,082
table scans (rowid ranges)      84
table scans (cache partitions)  0
table scans (direct read)       84
table scan rows gotten          585,897,481
table scan blocks gotten        10,595,584
table fetch by rowid            6,484,711
table fetch continued row       1,995

In the next lesson, you will learn to enable direct sorting.

Monitoring Sort Activity - Exercise

Before going on, click the Exercise link below to practice monitoring for disk sorts.
Monitoring Sort Activity - Exercise