Sort question

From: srivenu <srivenu_at_hotmail.com>
Date: 10 Jun 2003 00:43:05 -0700
Message-ID: <1a68177.0306092343.5f074cd3@posting.google.com>

I have a question on sorting and i request someone to explain the following the me.
I have an Oracle instance running with a SORT_AREA_SIZE & SORT_AREA_RETAINED_SIZE of 5MB each.
Still i saw that the application users were doing some sorting (I have plenty of RAM to burn).
So I increased the sort sizes
ALTER SYSTEM SET SORT_AREA_SIZE=26214400 DEFERRED; ALTER SYSTEM SET SORT_AREA_RETAINED_SIZE=26214400 DEFERRED; I use a locally managed temporary tablespace with an extent size of 5MB.
(I will increase it to 25MB).
Now 3 days after increasing the size, i ran this script to see the sort usage.
( I removed all formatting lines in the script for brevity) (users connect everyday. so the sessions are new i.e. started after the increase in sort sizes)

select name,value/1048576 value,'MB'
from v$parameter
where name like '%sort_area%'
/
select name,value
from v$sysstat
where name like '%sort%'
/
select current_users,total_extents,used_extents,free_extents,added_extents, freed_extents,extent_hits,max_size,max_used_size from v$sort_segment
/
select (a.max_sort_blocks*b.value/1048576) ind_sort_size,

	 (a.max_used_blocks*b.value/1048576) total_sort_size,
	 (a.total_blocks*b.value/1048576) total_segment_size

from v$sort_Segment a,v$parameter b
where b.name='db_block_size'
/

select   se.username


        ,se.sid

        ,su.extents

        ,su.blocks * to_number(rtrim(p.value))/1048576 as Space

        ,tablespace

from     v$sort_usage su


        ,v$parameter  p

        ,v$session    se

where    p.name          = 'db_block_size'
and      su.session_addr = se.saddr

order by se.username, se.sid
/

The following is the output.

sort_area_size               25.00 MB
sort_area_retained_size      25.00 MB

sorts (memory)             146341001
sorts (disk)                     305
sorts (rows)             10236257510

  No Of                                                         No Of
  Users    No of    No Of      No Of      No Of      No Of      Freed 
           Max Extents
Sorting  Current     Used       Free    Extents    Extents    Extents
Max Extents Used By all
    now  Extents  Extents    Extents      Added      Freed     reused 
 Ever Used       Sorts

------- -------- -------- ---------- ---------- ---------- ---------- ----------- -----------

      1     1225        3       1222        408          0     111762 
      1225        1225

                  Largest       Current
               Concurrent  Sort Segment
Largest Sort   Sort Usage          Size
     (in MB)      (in MB)       (in MB)
------------ ------------ -------------
    5128.000     6134.570      6134.570

                                      Space
                                       Used
USERNAME            SID    EXTENTS    in MB TABLESPACE
------------ ---------- ---------- -------- ------------
TNCH2490             46          3    15.02 TEMP

Now there is one user using 15MB of sort segment space.

I repeat the same script after some time.

sort_area_size               25.00 MB
sort_area_retained_size      25.00 MB

sorts (memory)             146347753
sorts (disk)                     305
sorts (rows)             10236321785

  No Of                                                         No Of
  Users    No of    No Of      No Of      No Of      No Of      Freed 
           Max Extents
Sorting  Current     Used       Free    Extents    Extents    Extents
Max Extents Used By all
    now  Extents  Extents    Extents      Added      Freed     reused 
 Ever Used       Sorts

------- -------- -------- ---------- ---------- ---------- ---------- ----------- -----------

      2     1225        6       1219        408          0     111774 
      1225        1225

                  Largest       Current
               Concurrent  Sort Segment
Largest Sort   Sort Usage          Size
     (in MB)      (in MB)       (in MB)
------------ ------------ -------------
    5128.000     6134.570      6134.570

                                      Space
                                       Used
USERNAME            SID    EXTENTS    in MB TABLESPACE
------------ ---------- ---------- -------- ------------
KABL2136            156          3    15.02 TEMP

NDDLOF13             24          3    15.02 TEMP

As you can see the sorts(disk) has remained at 305. but still the sort space is being used by the sessions. And to complicate things further.
When sessions are sorting, i give the following query.

select a.sid,

    decode(command,0,'None',2,'Insert',3,'Select',

            6,'Update',7,'Delete',10,'Drop Index',12,'Drop Table',
            45,'Rollback',47,'PL/SQL',command) command,

    event,p1,p2,p3,state,wait_time
from v$session_wait a,V$session b
where b.sid=a.sid
and (a.sid>10 and event not in('SQL*Net message from client',

                   'SQL*Net message to client')

or (a.sid<=10 and event not in ('rdbms ipc message','smon timer',

   'pmon timer','SQL*Net message from client'))) order by decode(event,'pipe get','A',event),p1,p2

                                                                      
                   Last
             Event                                                    
                   Wait
 SID Command  Waiting For                            P1          P2   
     P3 STATE      Time
---- ------- ------------------------------ ----------- -----------
---------- ---------- ----
  52 Select  SQL*Net more data to client     1413697536        2035   
      0 WAITED SHO   -1
  24 Select  direct path read                       202       73838   
    114 WAITING       0

As can be seen, session 24 is doing a read from the temporary segment. Why isnt this sort getting added as a disk sort in system statistics? I'am pretty sure that the sort area size of 25 MB i gave is sufficient for doing the sorting.
Can some one explain me all this ?
Thanks in advance for any replies. Received on Tue Jun 10 2003 - 02:43:05 CDT