|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%';
Here is the output:
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
name like 'table%';
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