Sorting is performed by the following statements:
- CREATE INDEX
- GROUP BY
- ORDER BY
- INTERSECT
- MINUS
- UNION
- DISTINCT
- 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.
- Avoid using the DISTINCT clause unless necessary.
- Use the UNION ALL clause in place of the UNION clause unless duplicates need to be eliminated.
- Try to use HASH JOINS instead of SORT MERGE JOINS. The use of hints will cause the optimizer to choose this join.
- Use appropriate index hints to avoid sorts.
- The cost based optimizer will try to avoid a sort operation when the FIRST_ROWS hint is used.
- Make sure that your SQL query is taking advantage of the best available indexing options.
- 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:
- Check if the sort is really necessary. Has an index been inadvertently overlooked?
Can a SQL statement be structured more efficiently?
- 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.
- 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.
- 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:
ALTER TABLESPACE temp_tspace DEFAULT STORAGE (INITIAL 260K NEXT 260K PCTINCREASE 0);
For overnight processing, you might set the default storage to:
ALTER TABLESPACE temp_tspace DEFAULT STORAGE (INITIAL 5M NEXT 5M PCTINCREASE 0);
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:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE big_temp;
- 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.
- 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)
- 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.
No comments:
Post a Comment