Si vous êtes un client Managed Hosting, cette rubrique ne vous concerne pas.
Configure the TEMP tablespaces
Oracle provides temporary tablespaces that are optimized for the storage of transient data (data available only for the duration of the session). Sort operations are the primary user of temporary tablespaces. For example, if two large tables are joined as part of a query, and Oracle cannot do the sort in memory, space will be allocated in a temporary tablespace for performing the sort operation.
The following SQL operations, all of which can be found in Blackboard Learn, might require disk sorting:
- CREATE INDEX
- ANALYZE
- SELECT DISTINCT
- ORDER BY
- GROUP BY
- UNION
- SORT-MERGE
SORT_AREA_SIZE initialization parameter
Sorting is managed by the SORT_AREA_SIZE initialization parameter, which is managed on a per session basis. For example, if the SORT_AREA_SIZE is set to 16KB and there are 1000 sessions, the maximum size would be 16MB. The sort space identified by this parameter is maintained in PGA memory.
Sort operations are attempted first in the PGA sort area, just as well as hash join operations are attempted first in the PGA hash area. There are different tuning philosophies with regard to pre-allocating space to this region or making use of automatic PGA memory management mode. Although larger sort and hash areas can dramatically improve the performance of a particular operation, they come at the cost of greater memory utilization. When the size of the work area is smaller than desired, the response time of the request increases. Ideally this work area space is configured for nearly 100% of all single pass online transaction processing (OLTP) operations.
SORT SEGMENT REQUEST wait event
Sorting can be measured using the SORT SEGMENT REQUEST wait event. The wait event suggests that a user process tried to acquire a sort segment and had to wait until that segment was obtained. As noted above, the query will attempt to perform the sort operation in memory first. If the memory allocated for the user's sort space is insufficient, the process must acquire space in the TEMP tablespace before performing its sort on disk. This space is called a sort segment.
Although the latency for performing a SORT SEGMENT REQUEST is small (often in the milliseconds), every opportunity for latency to be applied to the service time of a request adds up. Therefore, it is important to measure the effects of sorting on a regular basis.
Configure the REDO tablespace
The REDO tablespace is used as a temporary storage location prior to Oracle performing changes at the data file layer.
Enable the REDO advisor using the FAST_START_MTTR initialization parameter.
Checking messages in the alert log is an easy way to determine how fast Oracle is filling and switching logs. The specific message to watching for is "Checkpoint not complete."
To improve performance, increase the REDO tablespace size so that log switching can occur at Oracle's recommended interval of 15 to 30 minutes. To do so, identify the current size of the REDO log members from V$LOG, record the number of log switches per hour, and then increase the size of the log to allow Oracle to switch at the recommended rate of one switch per 15 to 30 minutes.
Configure the UNDO tablespace
The UNDO tablespace is used for read consistency, data recovery, and rollback. A database running in automatic UNDO management mode transparently creates and manages UNDO segments. Oracle recommends using automatic undo management because it simplifies operational management and removes the need for manual sizing of UNDO using one or more rollback segment.
Use the UNDO advisor to properly size the UNDO space. Be aware of the state and size of the UNDO tablespace is important when trying to eliminate the ORA-1555: snapshot too old messages that identify a poorly sized UNDO space from the Oracle alert log.
