A. SORT_AREA_SIZE
B. LARGE_POOL_SIZE
C. SORT_AREA_RETAINED_SIZE.
D. SORT_MULTIBLOCK_READ_COUNT
Answer: A
Explanation:
The amount of memory set aside for each user’s Server Process to perform these sort operations is impacted by the following init.ora parameters: SORT_AREA_SIZE, SORT_AREA_RETAINED_SIZE, PGA_AGGREGRATE_TARGET, WORKAREA_SIZE_POLICY. The default value for SORT_AREA_SIZE is OS-dependent. The minimum size for this parameter is equivalent to six Oracle blocks. The maximum size is OS-dependent. SORT_AREA_SIZE specifies how much memory each user’s Server Process should set aside to perform in-memory sort operations.
Incorrect Answers
B: LARGE_POOL_SIZE parameter is used to buffer I/O server processes as well as backup and recovery. It also caches session data when the Shared Server feature is used.
C: Once a sort operation is complete, if the sort area still contains sorted rows that need to be returned to the user, the user’s Server Process reduces the memory set aside for the final fetch to the value specified by SORT_AREA_RETAINED_SIZE.
D: SORT_MULTIBLOCK_READ_COUNT specifies the number of database blocks to read each time a sort performs a read from a temporary segment. Temporary segments are used by a sort when the data does not fit in SORT_ARE_SIZE of memory. In these situations, sort writes out sections of data to temporary segments in the form of sorted runs. Once all the data has been partially sorted to these runs, sort merges the runs by reading pieces of them from the temporary segment into memory to produce the final sorted output. If SORT_AREA_SIZE is not large enough to merge all the runs at once, subsets of the runs are merged in a number of merge passes.
OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 412-413
Chapter 8: Tuning Disk I/O
No comments:
Post a Comment