Sort Tuning

Least expensive- memory (sort_area_size)

Most Expensive- disk (temporary tablespace)

Dedigate server mode-PGA

Sared Server Mode-UGA

Sort area_size=10 MB

100 user=100*10=1000M

So if 1000M by default allocate without needed it will be unnecessary resource use, so to avoit it we can use sort_area_retained_size for released back memory after use.

sort_area_retained_size= 0 (minimum allocate for users)

SQL> show parameter sort_area_retained_size;

NAME TYPE VALUE

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

sort_area_retained_size integer 0

SQL> select name,value from v$sysstat

where name like '%sort%';

NAME VALUE

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

sorts (memory) 1751

sorts (disk) 0

sorts (rows) 4162

SQL> select name,value from v$sysstat

where name='sorts (rows)';

NAME VALUE

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

sorts (rows) 4114

SQL> select sal from scott.emp order by sal;

SAL

----------

800

950

1100

1250

1300

1500

1600

2450

2850

2975

3000

5000

14 rows selected.

SQL> select name,value from v$sysstat

where name like '%sort%';

NAME VALUE

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

sorts (memory) 1774

sorts (disk) 0

sorts (rows) 4187

After Database open that much number of row is sorted

Tuning SORT Operation:

1: Make it bigger;

SQL> show parameter sort_area_size;

NAME TYPE VALUE

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

sort_area_size integer 524288

SQL> alter system set sort_area_size=104857 scope=spfile;

System altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area 139008884 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 4337664 bytes

Database mounted.

Database opened.

SQL> show parameter sort_area_size;

NAME TYPE VALUE

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

sort_area_size integer 104857

2.PGA_AGGREGATE_TARGET (range from 10mb to 400gb)

Specifies the target aggregate PGA memory of all dedicated sessions attached to the instance.
The value of this parameter can range from 10 MB to 40000 GB.
When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance, subtract the SGA, and then assign the remaining memory PGA_AGGREGATE_TARGET.

SQL> show parameter PGA_AGGREGATE_TARGET;

NAME TYPE VALUE

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

pga_aggregate_target big integer 25165824

SQL> alter system set PGA_AGGREGATE_TARGET=11534336;

System altered.

SQL> show parameter workarea_size_policy;

NAME TYPE VALUE

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

workarea_size_policy string AUTO

AUTO: Dedicated processes use PGA_AGGREGATE_TARGET.

MANUAL: SORT_AREA_SIZE will be use.

NOTE- The PGA_AGGREGATE_TARGET parameter controls the memory that is used by dedicated processes .If your users connect through shared servers, then setting this parameter will have no impact on the performance of shared servers or memory utilization.

solution 3:Tuning Sort/Avoid sort

-use nosort to create indexes

-use union all inserted of union

- use index accesses for table joins

-index in the order by clause

-use estimate rather the compute

[NOTE- Temporary tablespace only deallocate after database shutdown]

SQL> SELECT d.value "Disk",m.value "Mem",

(d.value/m.value)*100 "Ratio"

FROM v$sysstat m,v$sysstat d

WHERE m.name='sorts (memory)'

AND d.name='sorts (disk)';

Disk Mem Ratio

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

23 206 11.165049

In an OLTP system the ratio of sorts(disk) to sorts(memory) should be less than 5%. If the ratio indicates a high number of sorts going to disk, increase the value of SORT_AREA_SIZE/PGA_AGGREGATE_TARGET.