Re: Sort question

Is this an application or just users. If you have allocated additional sort space, but the user(s) have never disconnected, they will be uable to utilize the additional area. It also appears that your desire is to get sorting to 100% in memory. This may not be possible. There will always be situations where the system is sorted to disk (the reason you have temp).

-Sheilah Scheurich

srivenu wrote:

> 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 - 10:26:23 CDT