Pro Oracle SQL Development by 2023

Author:2023
Language: eng
Format: epub

Chapter 10 Optimize the Database with OraCle arChiteCture

UNDO_RETENTION parameter, or increasing the space available to the undo tablespace.

The UNDO_RETENTION parameter is not a guarantee; Oracle will only keep undo data for

that long if there is space available.

While redo and archive logs need to be sized for to the largest possible DML, undo needs to be sized for the longest possible SELECT.

Temporary Tablespace

A temporary tablespace is used for storing intermediate results for sorting, hashing,

and large object processing. We need to allocate enough space to support our SQL

statements, but we don’t want to over-allocate space that will never be used.

Oracle first tries to process everything in memory, but the amount of memory

allowed for sorting and hashing depends on the parameter PGA_AGGREGATE_TARGET, with

a hard limit set by the parameter PGA_AGGREGATE_LIMIT. The memory for intermediate

results is shared among many sessions, and when operations cannot fit in memory, they

are written to disk through the temporary tablespace.

The amount of space required to sort or hash data is roughly equal to the size of that

data. If we have to process large amounts of data in a single query, as is common in a

data warehouse, there’s no way all that data will fit in memory. The minimum size of

our temporary tablespace should be equal to the size of the largest objects that will be

hashed or sorted at the same time.

It can be difficult to predict how much data needs to be hashed or sorted at the

same time. We might have to find a good value through trial and error. Trial and error is

a painful way to configure a system, but Oracle provides features that can help us with

this task.

First, we can look at the data dictionary to check current and historical values. We

can look at DBA_SEGMENTS.BYTES for large objects that will be sorted and hashed. We

can check the column DBA_HIST_ACTIVE_SESS_HISTORY.TEMP_SPACE_ALLOCATED for

the temporary tablespace used in previous SQL statements. And we can check the view

V$TEMPSEG_USAGE for current usage.

When we start running our large workloads, we can enable resumable sessions.

A resumable session will become suspended when it runs out of space, instead of

immediately throwing an error. When the session is suspended, we can quickly add

space, and then the session will automatically continue processing. Resumable sessions

are enabled with the parameter RESUMABLE_TIMEOUT. We can monitor for suspended

288

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.