Hands-On 08 (Optimize Sort Operations)

As a DBA, you are responsible to optimize sort operations of your organization’s database in case of performance problems. Your job’s responsibilities dictate that you should be aware of the following basic fundamental subjects:

Monitoring a sort statement operation

Optimizing a sort statement operation

Using the V$SYSSTAT view

SORTS (DISK)

SORTS (MEMORY)

Calculating the sort Ratio

SORT_AREA_SIZE

PGA_AGGREGATE_TARGET

Increasing the SORT_AREA_SIZE parameter

-- Hands-On 08 (Optimize Sort Operations)
-- Optimize Sort Operations
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a40
col parameter format a40

col username format a10
pause

--Start

CLEAR SCR
-- In this exercise, you will learn how to monitor and optimize
-- sort operations.

-- Now, connect to SQLPlus as the SYSTEM/MANAGER user.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- Important note to remember:
-- The Oracle Server Processes will sort as much as they can in 
-- the memory sort area before using any disk sort space.

-- Now, query the V$SYSSTAT view to track the number of in-memory 
-- and to-disk sorts, as well as the total number of rows sorted.

pause

SELECT name, value
FROM v$sysstat
WHERE name like 'sorts%'
/

-- Notice that the sorts (disk) number must be very low, and
-- the sorts (memory) number can be as high as possible.

-- This query shows that all of the sorting was done in the memory.
-- That is good.

pause

CLEAR SCR
-- Now, determine the sort ratio of the in-memory vs. to-disk sorts.

pause

SELECT 100*(a.value-b.value)/(a.value) AS "Sort Ratio"
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'sorts (memory)'
AND b.name ='sorts (disk)'
/

-- Notice that the sort ratio should be greater than 
-- 95%. 

-- If you are not using the automatic PGA memory and the number 
-- is less than 95 percent, you should greatly consider increasing
-- the value of the SORT_AREA_SIZE parameter.

-- If you are using the automatic PGA memory and the number is
-- less than 95 percent, you should greatly consider increasing
-- the value of the PGA_AGGREGATE_TARGET parameter.

pause

CLEAR SCR
-- Increase the SORT_AREA_SIZE parameter to 819200 bytes.

pause

ALTER SYSTEM SET sort_area_size=819200 SCOPE=spfile
/

-- In order for the change to take effect, the database
-- needs to be restarted.

-- Shutdown and startup the database in order to see the 
-- changes.

pause


CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.