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
1250
1300
1500
1600
2450
2850
2975
3000
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.