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)
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. |