Friday, August 3, 2012

Ways to optimize sorting in Oracle

A sort area in memory is used to sort records before they are written out to disk. Increasing the size of this memory by increasing the value of the initialization parameters SORT_AREA_SIZE or PGA_AGGREGATE_TARGERT, lets you sort more efficiently. SORT_AREA_SIZE is a dynamic parameter that can be set at either the session level using an ALTER SESSION command or system wide using an ALTER SYSTEM command. PGA_AGGREGATE_TARGET is an initialization parameter that defines the total size of all working areas, including the sort area, for all sessions. This parameter is available in Oracle 9i and 10g.

Sorting is performed by the following statements:
  • Unindexed table joins
  • Some correlated subquerie

If the data being sorted does not fit in memory, Oracle must sort it in small runs. As each run is completed, Oracle stores the data in temporary segments on disk. After all of the runs have completed, Oracle merges the data to produce the sorted data. This is less efficient than doing the entire sort in memory.

In general, try to allocate as much space in memory as possible for SORT_AREA_SIZE (assuming that your entire sort can fit into memory). When defined, this parameter will cause memory to be allocated from the Process Global Area (PGA) unless the database is using Oracle’s Shared Server architecture (called Multi Threaded Sever (MTS) prior to Oracle9i). If the Oracle Shared Server architecture is being used, then the sort memory will be allocated from the large pool so long as the large pool is allocated. The sort memory will be allocated alternatively from the User global area (UGA) in the shared_pool if the large pool is not available. 

Consider using a two megabyte SORT_AREA_SIZE when your sorted data exceeds 100 megabytes in size. Because SORT_AREA_SIZE is allocated per user, increasing this parameter can exhaust memory very quickly if a large number of users are logged on. You also need to make sure that the temporary segments to which the sort operation will write its output (if it runs out of memory) are large enough, with appropriately sized INITIAL and NEXT extents. Again, the PGA_AGGREGATE_TARGET method of allocating working areas helps with this problem.

To find out whether sorting is affecting performance in your system, monitor the sorting disk activity in your system and then adjust accordingly. One good way to do this is to define a separate tablespace for temporary tables. By watching the I/O rate on the temporary tablespaces, you can detect how frequently the sort process failed to perform the entire sort in memory. You can monitor the V$SYSSTAT table to observe memory and disk activity.
Below is an example of querying the V$SYSSTAT table:

SQL> select name, value from v$sysstat where name like 'sort%';

      NAME                                                         VALUE
---------------------------------------------------------------- ----------
sorts (memory)                                                         1658
sorts (disk)                                                              2
sorts (rows)                                                        2232346
The "sorts(memory)" statistic shows the total number of sort operations that could be performed completely within the sort buffer in memory, without using the temporary tablespace segments. The "sorts(disk)" statistic shows the number of sort operations that could not be performed in memory. (Note that this number does not represent the total number of times a temporary table extent was written by a sort process.) Out of a total of 1293 sorts, only two required disk usage.

Using dedicated temporary tablespaces (created via the create temporary tablespace command) can provide some performance improvements with regards to sorting. This is because the temporary sort segments, once used, will not be removed but will instead be reused by the next process that needs to do a sort to disk. This eliminates the overhead of having to remove and recreate sort segments. This also means that your temporary tablespace may appear to contain segments in it most of the time. This is perfectly natural.
Also, consider in Oracle 9i and later, assigning a default temporary tablespace to the database. This has the benefit of allowing you to assign users to a temporary tablespace other than SYSTEM automatically when the user is created. Sorts occurring in the SYSTEM tablespace can badly fragment the tablespace and can cause other performance related problems.

Your goal should be to always reduce or eliminate sorts completely. Again, this starts at the application layer. Here is a list of suggested things you can look for in application code, which might be causing unneeded sort operations. While these different operations may well be needed, it’s a good idea to review your code and make sure.
  1. Avoid using the DISTINCT clause unless necessary.
  2. Use the UNION ALL clause in place of the UNION clause unless duplicates need to be eliminated.
  3. Try to use HASH JOINS instead of SORT MERGE JOINS. The use of hints will cause the optimizer to choose this join.
  4. Use appropriate index hints to avoid sorts.
  5. The cost based optimizer will try to avoid a sort operation when the FIRST_ROWS hint is used.
  6. Make sure that your SQL query is taking advantage of the best available indexing options.
  7. Review operational SQL code for unneeded sort operations, such as order by clauses.

Follow these suggestions if your monitoring shows that you have a sorting problem:
  1. Check if the sort is really necessary. Has an index been inadvertently overlooked? Can a SQL statement be structured more efficiently?
  2. Increase the value of the SORT_AREA_SIZE parameter. Because this increase applies to all user processes, this is likely to consume a lot of memory. Make sure you don't increase the value of SORT_AREA_SIZE to the point where you have little free memory. The maximum allowable value is system-dependent. If you are using the PGA_AGGREGATE_TARGET method of work area allocation, then check the V$PGASTAT view to ensure that this parameter is set correctly.
  3. Verify your temporary tablespace extents are large enough. Make sure that you specify large enough table extents (in the INITIAL and NEXT parameters on the CREATE statement) to allow SORT_AREA_SIZE of memory to be written to disk without having to throw multiple extents. Make your temporary segments a minimum of SORT_AREA_SIZE + 1 block. It is a very good idea to use Locally Managed Tablespaces with the UNIFORM extent allocation method for your temporary tablespaces. This way, you ensure that the extent sizes are the same for every sort segment.
  4. Let users who require larger sorts use a temporary tablespace with larger INITIAL and NEXT default tablespace storage parameters. This is a less likely, but possible, alternative which will help reduce the degree of dynamic extension. For example, during daily online transaction processing hours, set your default settings to:

    For overnight processing, you might set the default storage to:

    If you are using Locally Managed Tablespaces, then you will not be able to modify the default storage clause as shown above. Instead, create two Locally Managed temporary tablespaces, one with smaller extents and the other with larger extents. Instead of altering the default storage clause information above, you would modify the default temporary tablespace for the entire database (in Oracle 9i and 10g). For overnight processing, you would issue a command Similar to the following:
  5. Set your INITIAL and NEXT extent sizes to one block plus a multiple of the sort area size. This will achieve minor improvements in response times. Assuming that you have a DB_BLOCK_SIZE of 4K and a SORT_AREA_SIZE of 64K, you may consider any of the following sizes or a higher size, depending on your requirements:
    4K  +  (1 * 64K)  =   68K
    4K  +  (2 * 64K)  =  130K
    4K  +  (3*  64K)  =  196K
    4K  +  (4 * 64K)  =  260K
    If you are using Locally Managed Tablespaces, set the uniform extent size to one of the above values.
  6. Make sure to use the SORT_AREA_RETAINED_SIZE. Oracle will restore the sort area available to user processes to the size specified in this parameter if it believes that the sort area data will not be referenced in the near future. This will save memory. If memory is tight, we highly recommend that you take advantage of this feature by setting your SORT_AREA_RETAINED_SIZE to half the SORT_AREA_SIZE. For example, you might set:
    SORT_AREA_SIZE           = 131072   (128K)
    SORT_AREA_RETAINED_SIZE  = 65536     (64K)
  7. Use the TEMPORARY type when creating tablespaces. This tablespace type is tuned especially for sorting and will boost your performance. We recommend that you use it as your users' TEMPORARY tablespace.


1 comment:

  1. Bluehost is one of the best hosting provider for any hosting services you might require.