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

      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. 

Comments