15.11 How do I..Determine and improve overall sort performance?

Problem

I suspect that my database’s sort performance is not optimal. I know that Oracle performs some sorts on disk and some sorts in memory and that memory-based sorts are faster than disk sorts. How can I tell if Oracle is performing too many disk-based sorts? If this is the case, how can I improve the situation?

Technique

When a user connects to the Oracle server and creates a session, Oracle creates an area in memory called the Process Global Area or Program Global Area (PGA). Part of this area is dedicated to user-requested sorts. Index creation, ORDER BY and DISTINCT clauses, and some joins all cause Oracle to undertake sorting operations.

If the sort area in the PGA is large enough then Oracle performs the sort in memory. Otherwise, Oracle splits the data to be sorted into smaller pieces, sorts them, writes them to temporary segments on disk, and finally reassembles them to complete the sort. This is one way that the Oracle server may use disk space, as opposed to memory, to complete a sort.

Oracle may also utilize disk for sorts, even when the entire sort could occur in memory. The sort could begin in memory, for example, but then Oracle may attempt to free some PGA memory to reference some data that is not part of the sort area. In this case, Oracle will write a temporary disk segment to contain some of the sorted data.

The UTLESTAT report contains a section summarizing sort performance; it appears in Listing 15.14.

Listing 15.14 An excerpt from the FILE I/O section of the UTLESTAT report

SVRMGR> Rem The total is the total value of the statistic between the time

SVRMGR> Rem bstat was run and the time estat was run. Note that the estat

SVRMGR> Rem script logs on as “internal” so the per_logon statistics will

SVRMGR> Rem always be based on at least one logon.

Statistic Total Per Transact Per Logon Per Second
-------------- -------------- -------------- -------------- --------------
sorts (disk) 40 3 1 .1
sorts (memory) 110 11 5.5 .2
sorts (rows) 653 653 326.5 12.09

The ratio given by sorts(disk)/sorts(memory)x100 should be less than five percent. If this is not the case, as shown in Listing 15.14, then you may wish to consider increasing the values of the initialization parameters SORT_AREA_SIZE or SORT_AREA_RETAINED_SIZE. SORT_AREA_SIZE is the initial size of the sort area in the PGA; its default under Windows NT is 65536 bytes. SORT_AREA_RETAINED_SIZE describes the minimum size to which Oracle can shrink the sort area during a sort. Its Windows NT default value is 0.

Another way to check the ratio of disk sorts to memory sorts is to query the dynamic performance view V$SYSSTAT. The structure of this view appeared earlier in this chapter in Table 15.4.

Steps

1. Run SQL*Plus, connect as the WAITE user, and use the start command to load and execute the script called CHP15_20.SQL. The script and its output appears in Figure 15.16.

The script joins the V$SYSSTAT dynamic performance view with itself in lines 3 through 5 to extract the values for disk sorts and memory sorts from the view.

2. If the ratio reported by the CHP15_20 script or by the UTLESTAT report exceeds five percent, then increase the value of initialization parameter SORT_AREA_SIZE and/or SORT_AREA_RETAINED_SIZE by shutting down the database, modifying the values of these parameters in the INIT.ORA file, and restarting the database.

How It Works

Step 1 queries the V$SYSSTAT table to determine the ratio of memory sorts to disk sorts. If the ratio exceeds 5%, Step 2 increases the value of the appropriate initialization parameters.

Comments

Unless your Oracle installation is using the multithreaded server (MTS), increasing the value of initialization parameters SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE will not increase the size of the SGA. The sort area resides in the PGA in this case. If your installation is using the MTS, the sort area is part of the shared pool and in this case it could cause problems if it becomes too large.

The 5% guideline for the ratio of disk sorts to memory sorts is most realistic for an online transaction processing (OLTP) system. If your database is serving as a datamart or a decision support system (DSS), then the importance of response time will probably exceed any premium on the disk to memory sort ratio.