Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 53 54 55 56 57 58 < 59 > 60 61 62 63 64 65 .. 218 >> Next

Intermediate Spool Intermediate spool results are retained until no longer needed. You can determine when intermediate spool is flushed by examining the output of an EXPLAIN. Note: The first step performed after intermediate spool has been flushed is designated "Last Use."
Output Spool Output results are either: Final rows returned in the answer set for a query Rows updated within, inserted into, or deleted from a base table The length of time spool space is retained for output results depends on a variety of conditions.

3 - 2

Teradata RDBMS Database Administration Chapter 3: Space Considerations

Defining Temporary Space Limits

Spool Space Allocation

Teradata RDBMS allocates spool space dynamically only from disk cylinders | that are not being used for permanent or temporary data.

Note: Permanent, temporary, and spool data blocks cannot coexist on the same cylinder.

Spool space is not reserved. All unused space in the Teradata RDBMS is considered available spool space. When spool is released, the file system returns the cylinders it was using to the free cylinder list.

You allocate spool space for a database, a user, or a user profile, not at the table level. A SPOOL limit defined in a profile takes effect upon completion of a:

CREATE/MODIFY USER statement that assigns the profile to a user

MODIFY PROFILE statement that changes the spool space limit

If the user is logged on, the profile specification affects the current session.

The maximum and default limits for database, user, and profile spool allocation are determined as follows:

IF you .

specify SPOOL in a CREATE/MODIFY USER/DATABASE statement

THEN .

IF a profile ... THEN the limit may not exceed ...
does not apply the limit of the immediate owner of the user or database.
applies the limit of the user who submitted the CREATE/MODIFY PROFILE statement, determined as follows: If that user has a profile, the limit in the profile If a profile does not apply to that user, the limit in the CREATE/MODIFY USER statement for that user If no SPOOL is defined for that user, the limit of that user's immediate owner

I

3 - 10 Teradata RDBMS Database Administration

Chapter 3: Space Considerations

Defining Temporary Space Limits

IF you .

do not specify a SPOOL limit in a CREATE/MODIFY USER/ DATABASE statement

THEN

IF a profile ... THEN the limit is inherited from ...
does not apply the specification for the immediate owner of the user or database.
applies the profile specification.
applies but the SPOOL parameter is NULL or NONE the specification for the user who submitted the CREATE/MODIFY PROFILE statement, determined as follows: If that user has a profile, the profile specification If a profile does not apply to that user, the specification in the CREATE/MODIFY USER statement for that user. If no SPOOL is defined for that user, the specification for that user's immediate owning database or user.

Spool Space Availability

Available spool space is tracked by the following:

Type Description
MAXSPOOL MaxSpool is a value used to limit the number of bytes the system will allocate to create spool files for a user. The value you specify may not exceed that of a user's immediate parent (database or user) at the time you create the user. If you do not specify a value, MaxSpool defaults to the parent's MaxSpool value. Specify each user's spool limit, taking into consideration the tables they access, to reduce the impact of "runaway" transactions, such as accidental Cartesian product joins.
CURRENTSPOOL CurrentSpool is the number of bytes in use for resolving queries. This value is maintained on each AMP for each user.
PEAKSPOOL PeakSpool is the maximum number of bytes used by a transaction for a user since the value was last reset by the ClearPeakDisk Macro (supplied in user DBC). See "Resetting Peak Values in DBC.DataBaseSpace" on page 3-13 and Teradata RDBMS Data Dictionary for more information on this macro.

3 - 10 Teradata RDBMS Database Administration Chapter 3: Space Considerations

Defining Temporary Space Limits

Reserving Minimum Spool Space

Teradata RDBMS dynamically allocates available permanent space as temporary or spool space when necessary.

To make sure this does not reduce permanent space to the point of impacting transaction processing, NCR recommends that you reserve permanent space for spool requirements.

At a minimum, you should reserve the following percentage of your total disk space (although you may increase the reserve as needed, depending on your actual workload):

IF your applications are mainly . THEN reserve at least this much of the total disk space . Because .
Decision Support statements (DSS) 20% minimum DSS queries often generate large spool files.
Online Transaction Processing (OLTP) 15% minimum OLTP queries usually generates smaller spool files.

Creating a Spool Reserve Database

To guarantee that data tables will never occupy more that 80% to 85% of total available disk space, you can allocate the reserve as permanent space by assigning that space to a special database in which tables will never reside.
Previous << 1 .. 53 54 55 56 57 58 < 59 > 60 61 62 63 64 65 .. 218 >> Next