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. You can also use the utlbstat-utlestat report to monitor disk sorting. With this utility you can see the number of sorts for any pre-defined elapsed time period.

• What exactly are UTLBSTAT and UTLESTAT?
  These are scripts that produce snapshots and report statistics for a specific period of time across an entire Oracle database. So you can analyze how the Oracle database performs from the snapshot produced from the time you start UTLBSTAT (begin) until you run the UTLESTAT (end). Basically, you run the UTLBSTAT when you start the database and after the desired period of time, you run the UTLESTAT to end the static-collecting method and generate the hard copy report. The generated report after running these diagnostic utilities contains a detailed and wide-range listing of the activities happened inside the database. It includes detailed information regarding the following:
• 1. Database statistics
  2. Library cache activity
  3. Events causing waits
  4. Rollback segment usage and contention statistics
  5. Latch statistics: In Oracle, latch statistics provide insights into the contention and usage of internal memory structures called latches, which are low-level serialization mechanisms used to protect shared resources in the database. These statistics help identify performance bottlenecks and potential areas of contention by tracking metrics like latch requests, waits, and hit ratios.
  6. Dictionary cache statistics: In Oracle, dictionary cache statistics track the efficiency of accessing data dictionary information, which is metadata about database objects like tables, indexes, and users. These statistics monitor the cache hit ratio and other metrics to help identify if performance issues are related to accessing dictionary data, which can impact overall database performance.
  7. I/O by data files and tablespaces
  8. initSID.ora parameters
  9. Database version and timings of the activities
  10. Period of measurement

It was historically the first method used when serious database tuning begun. If you are interested in UTLBSTAT or UTLESTAT (sometimes named BSTAT or ESTAT), the easiest way to become familiar with these utilities is to run it and interpret the generated results. But it may be overwhelming to deal with the generated report which is lengthy.

• Guidelines to consider: Consider these guidelines when you are running these utilities:
  1. Initially run the report and save the resulting output so that you have the baseline of information.
  2. If you are running the report during poor-performing time, it may not be helpful to understand the real changes inside the database.
  3. Running the report during a specific period of time will fetch you the accurate results.
  4. If the database has crashed or there was a shutdown between the execution of the BSTAT and ESTAT scripts, statistics are invalid and is better to disregard the report.
• Gather more Refined Statistics:
  You need to alter the system to gather more refined statistics regarding the time before you run the generated report. You may choose any of the two options such as
  1. you can set the parameter TIMED_STATISTICS to TRUE (in the init.ora) or
  2. you can set the value using "ALTER SYSTEM".
  Theoretically speaking, such a change should bring slight performance hit, but it is rarely reported.
  You can start the analysis process from any directory. The generated output report is called "report.txt" and it is advised to rename the report with a unique timestamp for identification and save it for further archive purposes.

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.

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