Oracle - Maximizing Sort Efficiency

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Overview Sort Operations: Causes Index Creation ORDER BY or GROUP BY clause usage DISTINCT keyword usage UNION, INTERSECTION and MINUS Operations Sort-Merge Operations:Example Sort-Merge Operations ANALYZE Command SORT_AREA_SIZE Sort Process: Sequence Sort space requirement > SORT_AREA_SIZE SORT_MULTIBLOCK_READ_COUNT parameter Sort Areas: Setting Parameters Setting Parameters and Sort Areas Multithreaded Server Connection Dedicated Server Connection SORT_AREA_SIZE: Setting SORT_AREA_SIZE: Altering ALTER SESSION with SORT_AREA_SIZE syntax ALTER SESSION with SORT_AREA_RETAINED_SIZE Syntax Summary Quick Quiz

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Overview: Maximizing Sort Efficiency Purpose In this lesson you will learn to identify the different causes for a sort and the sequence of the sort process. You will also learn to select and set the appropriate SORT_AREA_SIZE parameter. Upon completion of this lesson, you should be able to: Identify the SQL operations that require sorts Identify the sequence of the sort process Select and set the appropriate SORT_AREA_SIZE parameter

Sort Operations: Causes The various operations that require a sort are discussed in the following pages. Excessive sorting operations decrease server performance. The operations that require a sort are shown below:

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Index Creation The server process uses a sort to arrange the indexed values before building the B-tree. Server processes also use a sort in cases where the index is created in parallel.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

ORDER BY or GROUP BY Clause Usage When the ORDER BY or GROUP BY clause is used, the server process uses a sort on the values specified in the ORDER BY or GROUP BY clause. However, if an ORDER BY clause is used with an existing index on a NOT NULL column, the server uses the index and does not sort the rows.

DISTINCT Keyword Usage When the DISTINCT keyword is used, the server process is forced to perform a sort. The sort is used to eliminate rows with duplicate selected values.

UNION, INTERSECT, and MINUS Operations For operations involving UNION, INTERSECT, and MINUS, the server process sorts the tables to eliminate or identify duplicates.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Sort-Merge Operations: Example An example of an equijoin is displayed. The query requests for the DEPTNO Columns from the EMP and DEPT tables. For an equijoin request with no indexes, the server performs extensive sorts. The server performs full table scans of EMP and DEPT tables and then sorts each row source separately. This is the SORT process in the sort-merge operation. In the example below, SET AUTOTRACE ON EXPLAIN is used to display the optimizer's execution path. Observe in the ouput that the execution plan shows that a full table scan is done on the EMP table. Example: SQL> SET AUTOTRACE ON EXPLAIN SQL> SELECT e.deptno, d.deptno 2 FROM emp e, dept d 3 WHERE e.deptno = d.deptno; DEPTNO DEPTNO ---------- ---------20 20 30 30 30 30 20 20 30 30 .. .. .. .. 14 rows selected. Execution Plan ---------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=70) 1 0 NESTED LOOPS (Cost=1 Card=14 Bytes=70) 2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=42) 3 1 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

Sort-Merge Operations The equijoin request merges the sorted sources together, combining each row from one source with each matching row of the other source. This is the merge aspect in a sort-merge operation.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Check Your Understanding

Identify the server process for which the tables are sorted to identify or eliminate duplicates: JOIN UNION PRODUCT SELECTION

Correct! Incorrect. A SELECTION does not require a sorting operation. Please try again

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Incorrect. A PRODUCT does not require a sorting operation. Please try again Incorrect. A JOIN does not require a sorting operation. Please try again

ANALYZE Command The ANALYZE command that is used to collect statistics on tables, indexes, and clusters requires a sort. The ANALYZE command helps the Cost Based Optimizer (CBO) define the best execution plan. The sorting process arranges the details of the statistics before providing summarized information for the table or index specified. Show Me an Example

Summary — Sort Operations: Causes In this topic you learned to identify the operations that require sort processing. This information helps you use the commands carefully, remembering that sorting operations can decrease server performance. The operations that require a sort action are: Index Creation

The server process (or processes, if the index is being created in parallel) has to sort the indexed values before building the B-tree.

ORDER BY or GROUP BY The server process must sort the values in Clause Usage the ORDER BY or GROUP BY clauses. DISTINCT Keyword Usage For the DISTINCT keyword, the sort process is required to eliminate duplicates. UNION, INTERSECT, or The server process must sort the tables on MINUS Operations which the UNION, INTERSECT or MINUS Operations are to be performed to eliminate duplicates.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

MINUS Operations

Sort-Merge Operations

ANALYZE Command

which the UNION, INTERSECT or MINUS Operations are to be performed to eliminate duplicates. If there are no indexes available, an equijoin request needs to perform the following operations: scan the tables mentioned in the FROM clause, using full table scans sort each row source separately merge the sorted sources together, combining each row from one source with each matching row of the other source The ANALYZE command helps the Cost Based Optimizer (CBO) define the best execution plan. The sorting process arranges the details of the statistics before providing summarized information for the table or index specified.

SORT_AREA_SIZE Now that we have learned about the operations that require a sort process, let us look at the sequence of steps that takes place in a sort process. The Oracle server sorts in memory if the sorting process can be done in an area smaller than the value of the SORT_AREA_SIZE parameter. The following pages describe the steps that occur when a sort operation exceeds the space specified by the SORT_AREA_SIZE parameter and needs more disk work space. One SORT_AREA_SIZE value exists for each user process at any time. SORT_AREA_SIZE specifies the maximum amount of memory that is used for a sort. You set the value in bytes by using the ALTER SESSION or ALTER SYSTEM DEFERRED command. The lowest value is an equivalent of six data blocks, and the highest value is system-dependent. The default value of the SORT_AREA_SIZE parameter is operating system (OS) dependent.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Sort Process: Sequence The three steps that take place during a sort process to disk are listed below. Click on each of the links to learn more about the three steps. Step 1 Step 2 Step 3

Sort Process to Disk: Step 1 When the sort space requirements exceed SORT_AREA_SIZE, the data is split into two or more smaller pieces called sort runs. Each sort run is sorted individually.

Sort Process to Disk: Step 2 The server process first creates the sort runs and then writes the pieces to temporary segments on the disk. These segments hold intermediate sort run data while the server works on another sort run.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Sort Process to Disk: Step 3 Finally, the server process merges the sorted pieces to produce the result of the sort.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Sort Space Requirement > SORT_AREA_SIZE If SORT_AREA_SIZE is not large enough to merge all the runs at once, the server process uses a number of merge passes to merge the subsets of the runs.

SORT_MULTIBLOCK_READ_COUNT Parameter The SORT_MULTIBLOCK _READ_COUNT parameter forces a sort to read a larger section of each sort run into the memory from a temporary segment on the disk. Show me more about the effect of the SORT_MULTIBLOCK _READ_COUNT parameter on the sort process.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Summary — Sort Process: Sequence You have learned about the steps in the sort process when the work space exceeds SORT_AREA_SIZE. The sequence of steps that takes place in a sort process are:

Step 1

When the sort space requirements exceed SORT_AREA_SIZE, the data is split into two or more smaller pieces called sort runs. Each sort run is sorted individually.

Step 2

The server process first creates the sort runs and then writes the pieces to temporary segments on the disk. These segments hold intermediate sort run data while the server works on another sort run.

Step 3

Finally, the server process merges the sorted pieces to produce the result of the sort.

You use the SORT_MULTIBLOCK _READ_COUNT parameter to specify the number of database blocks to read each time a sort performs a read from a temporary segment.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Sort Areas: Setting Parameters You have learned to identify the operations that require sort processing and about the steps in the sort process. Let us now learn how to increase the sort space for a larger sort. Changing the SORT_AREA_SIZE enables you to improve database performance for certain operations. In this topic you learn how to set the SORT_AREA_SIZE parameter by using the ALTER SESSION command. You use the SORT_AREA_SIZE parameter to specify the maximum amount of memory that is used for a sort. Depending on the size of the operation, you may require a larger SORT_AREA_SIZE.

Setting Parameters and Sort Areas The sort space is part of the Program Global Area (PGA). The location of the PGA, and therefore the sort space, is determined by the type of server connection being used, either dedicated or multithreaded.

Multithreaded Server Connection In a Multithreaded Server Connection (MTS), the User Global Area (UGA) and sort space are in the shared pool of the System Global Area (SGA). The UGA and Program Global Area (PGA) are separate components. In the case of MTS, the DBA must increase the shared pool size if an application uses large sorts. Therefore, it is necessary to avoid requests for large sorts.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Dedicated Server Connection When using a dedicated server connection, the UGA and sort space are stored in the PGA.

SORT_AREA_SIZE: Setting You set the sort space size with the INIT.ORA parameter, SORT_AREA_SIZE. The default value of this parameter is operating system (OS) dependent and adequate for most online transaction processing (OLTP) operations. You might want to adjust the value for decision support

This watermark does not appear in the registered version - http://www.clicktoconvert.com

system applications, batch jobs, or larger transactions. The graphic below shows an extract from an INIT.ORA file. The SORT_AREA_SIZE is set to 65536 bytes.

SORT_AREA_SIZE: Altering If SORT_AREA_SIZE = 64 K and 10 users are sorting, 10 * 64 K memory is required for sorting. Increasing the SORT_AREA_SIZE parameter improves the efficiency of large sorts. Individual users can alter the size of the SORT_AREA_SIZE for the session that they are using by using the ALTER SESSION command. However, if the SORT_AREA_SIZE for the Oracle instance has to be changed, the ALTER SYSTEM command must be used. The following pages discuss these commands in detail.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

ALTER SESSION with SORT_AREA_SIZE Syntax SORT_AREA_SIZE can be set dynamically by using the ALTER SESSION or ALTER SYSTEM DEFERRED command. The ALTER SESSION command stays in effect until you disconnect from the database. However, the ALTER SYSTEM command dynamically alters your Oracle instance. The settings stay in effect as long as the database is mounted. ALTER SESSION SET SORT_AREA_SIZE = ; ALTER SYSTEM SET SORT_AREA_SIZE = ; The DEFERRED keyword sets or modifies the value of the parameter for future sessions that connect to the database. Example: For example, to process a large operation you must dynamically increase the sort area for the session. The following example sets the SORT_AREA_SIZE to 128000 bytes. SQL> ALTER SESSION 2 SET SORT_AREA_SIZE = 128000; Session altered. The session was altered by increasing the SORT_AREA_SIZE to 128,000.

ALTER SESSION with SORT_AREA_RETAINED_SIZE Syntax When a sort is finished, the sort space still contains sorted rows to be fetched. You can shrink the sort area to the size specified in the SORT_AREA_RETAINED_SIZE parameter. This parameter can also be set dynamically by using the ALTER SESSION or ALTER SYSTEM DEFERRED command. ALTER SESSION

This watermark does not appear in the registered version - http://www.clicktoconvert.com

SET SORT_AREA_RETAINED_SIZE = ; ALTER SYSTEM SET SORT_AREA_RETAINED_SIZE = ; The DEFERRED keyword sets or modifies the value of the parameter for future sessions that connect to the database. After the last row is fetched from the sort space, the memory is released back to the UGA, not to the OS. This space is released for reuse by the same Oracle server process.

ALTER SESSION with SORT_AREA_RETAINED_SIZE: Example The default value for the SORT_AREA_RETAINED_SIZE parameter is equal to the value of the SORT_AREA_SIZE parameter. The example below displays the usage of an ALTER SESSION command to change the SORT_AREA_RETAINED_SIZE size. The SORT_AREA_RETAINED_SIZE is set to 32,000 bytes. Example: SQL> ALTER SESSION 2 SET SORT_AREA_RETAINED_SIZE = 32000; Session altered.

Summary — Sort Areas: Setting Parameters You have learned how to set the SORT_AREA_SIZE parameters dynamically by using the ALTER SESSION command. You have also learned about ALTER SYSTEM and ALTER SYSTEM DEFFERED commands. This will help you to increase the sort space for a larger sort. Changing the SORT_AREA_SIZE enables you to improve database performance for certain operations.

This watermark does not appear in the registered version - http://www.clicktoconvert.com

Summary — Maximizing Sort Efficiency In this lesson, you have learned to identify the different causes for a sort and the sequence of the sort process. You have also learned to select and set the appropriate SORT_AREA_SIZE parameter. Sort Operation-Causes: The operations that require a sort are: Index Creation ORDER BY or GROUP BY Clause Usage DISTINCT Keyword Usage UNION, INTERSECT, and MINUS Operations Sort-Merge Operations Sort Process-Sequence: The steps in the sort process when the work exceeds SORT_AREA_SIZE and needs more disk work space are: The data is split into two or more smaller pieces called sort runs. Each sort run is sorted individually. The server process first creates the sort runs and then writes the pieces to temporary segments on the disk. These segments hold intermediate sort run data while the server works on another sort run. The server process merges the sorted pieces to produce the result of the sort. Sort Areas-Setting Parameters: You set the sort space size with the INIT.ORA parameter, SORT_AREA_SIZE. SORT_AREA_SIZE can be set dynamically by using the ALTER SESSION or ALTER SYSTEM DEFERRED command. ALTER SESSION SET SORT_AREA_SIZE = ; ALTER SYSTEM SET SORT_AREA_SIZE = ; You can shrink the sort area to the size specified in the SORT_AREA_RETAINED_SIZE parameter. ALTER SESSION SET SORT_AREA_RETAINED_SIZE = ; ALTER SYSTEM SET SORT_AREA_RETAINED_SIZE = ;