SCRIPT: How to Determine the Number of Disk Sorts vs Memory Sorts

Subject:	SCRIPT: How to Determine the Number of Disk Sorts vs Memory Sorts
	Doc ID:	Note:100013.1	Type:	SCRIPT
	Last Revision Date:	21-MAY-2006	Status:	PUBLISHED

Abstract Script to determine the Number of Disk Sorts vs. Memory Sorts.   Product Name, Product Version Oracle Server, 7.3.x to 9.x Platform Platform Independent Date Created 30-Apr-2001   Instructions Execution Environment:        Access Privileges:      Requires DBA privileges.   Usage:      sqlplus / @[SCRIPTFILE]   Instructions:      Run the script from SQL*PLus connected as a user with DBA access privileges.   PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.   Description Many times a customer will want to know the number of memory sorts compared to disk sorts. This information is useful in tuning database performance and the SORT_AREA_SIZE. By using information from V$STATNAME and V$SYSSTAT, a DBA can determine whether or not more sorts are being performed on disk than is desired. A large number of disk sorts can adversely affect overall performance of the database. If more disk sorts are being performed than desired, then increase the value of SORT_AREA_SIZE. Any changes made to the SORT_AREA_SIZE parameter will have a like effect on the size of the SGA. Measurements have shown that large sort areas (over 10 Meg)do not perform much better than 1 Meg sort area unless a disk sort is avoided altogether. Sample Output ============= 'INIT.ORASORT_AREA_SIZE:'||VALUE -------------------------------------------------------------------------------- INIT.ORA sort_area_size: 524288     NAME VALUE ---------------------------------------------------------------- ------------ sorts (memory) 448,726 sorts (disk) 0 sorts (rows) 921,405   References <Note:1019592.6> TFTS: MSTAT -- SYSTEMSTATISTICS/TUNING SCRIPT. <Note:102339.1> Temporary Segments: What Happens When a Sort Occurs