Undo hints to speed up segment extension and tuning of undo retention

UNDO HINTS TO SPEED UP SEGMENT EXTENSION AND TUNING OF UNDO RETENTION

RELATED APPLICATIONS

This application is related to co-pending U.S. application Ser. No. 10/846,099, filed on May 13, 2004, entitled “Automatic Tur1ing of Undo Retention,” and co -pending U. S. application Ser. No. 11/249,150, filed on Oct. 11, 2005 entitled “Longest Query Duration for Auto Tuning Undo Retention”, both of which are hereby incorporated by reference in their entireties.

FIELD

Embodiments of the invention relate to computer systems, and more particularly to data recovery.

BACKGROUND OF THE INVENTION

In database systems, a “transaction” refers to an atomic set of operations performed against a database, which may access, create, modify or delete database data or metadata. A “commit” occurs when the transaction has completed its processing and any changes to the database by the transaction are ready to be permanently implemented in the database system.

Transaction log records can be maintained in a database system to allow data recovery in the event of an error, that may include hardware failure, network failure, process failure, database instance failure, data access conflicts, user errors, and statement failures in database access programs.

Various types of transaction log records can be maintained in a database system for data recovery. One type of log record that may be maintained is the “undo” record. Undo records contain information about changes that were introduced into the database system. For example, if a row in a table were modified, the changes will be stored in the undo record identifying the block of the database system that includes the modified table row.

Memory or disk space needs to be allocated for storage of undo records. Database managers may set the undo tablespace size by predicting how many undo records may be generated. Often there is not enough statistical information available for database administrators to use in order to arrive at an accurate prediction of undo records generation. Incorrect undo tablespace size may cause errors in the system, as not enough undo records may be available. Altematively, allocating too much memory or disk space for storing undo records is ineflicient.

Moreover, database administrators need to predict how long undo records should be maintained, a parameter known as the “undo retention.” Users may require older versions of the data for various reasons. In order to prevent these users from obtaining error messages, undo records should be maintained in the system to allow the data to be retained to their previous values. However, undo tablespace is limited, and new transactions require undo tablespace. A user may therefore monitor the system activity and adjust the undo retention based on the amount of undo information generated by new transactions.

Undo records that are generated by active transactions may be known as active undo records. Undo records generated by relatively recent transactions, which committed more recently than the undo retention, may be referred to as “unexpired undo records.” Undo records that were generated by

relatively older transactions, which committed more distantly than the undo retention, may be referred to as “expired undo records.”

When the allocated undo tablespace is too small or the system activity exceeds predicted levels, the system may encounter a condition known as “space pressure.” Under space pressure, the undo tablespace may be severely limited. Because a transaction cannot succeed without storing undo records, transactions may be in danger of failing for lack of undo tablespace. Under such conditions, many database systems choose to eliminate existing undo records of non-active transactions and thus risk failing a query, rather than failing the transaction. Such database systems usually do not eliminate active undo records, and usually may choose to eliminate any expired undo records first. However, if no expired undo records can be found, these systems may choose to eliminate unexpired undo records. Such systems may eliminate unexpired undo records indiscriminately, eliminating newer undo records while older undo records remain in the system. This indiscriminate method of eliminating undo records may result in an unacceptable number of failed queries and other operations.

What is needed, therefore, is a solution that overcomes these and other shortcomings of the prior art.

SUMMARY OF THE INVENTION

Methods and systems for using undo hints to speed up segment extension are disclosed. While a process is searching other segments to find available space, the process collects undo hints that describe when space in a segment might become available. These undo hints are placed in a table of undo hints. When a process is not able to find available space, it may consult the table of undo hints to determine how much to decrease the undo retention. After the undo retention is decreased, the process may again consult the table of undo hints to find a segment that likely contains available space now that the undo retention time has been reduced.

In one implementation, the invention may include a method for using undo hints to speed up segment extension in a database. The method may include retrieving an undo hint from a table of undo hints, the undo hint specifying an undo segment, accessing the specified segment based on the retrieved undo hint, and allocating an expired extent from the specified segment to a full segment.

In another implementation, the invention may include a method for adjusting the undo retention in a database system. The method may include accessing a table of undo hints comprising a plurality of buckets, determining whether a first bucket in the plurality of buckets will provide an appropriate number of undo hints, and if the first bucket will provide an appropriate number of undo hints, adjusting the undo retention of the system based on the first bucket.

BRIEF DESCRIPTION OF THE DRAWINGS

The invention is illustrated by way of example and not limitation in the figures of the accompanying drawings, in which like references indicate similar elements and in which:

FIG. 1 is a flow chart illustrating a method for using undo hints to speed up segment extension, in accordance with an embodiment of the present invention;

FIG. 2 is a block diagram illustrating a database system architecture, in accordance with an embodiment of the present invention;

FIGS. 3A-B are block diagrams illustrating tables of undo hints, in accordance with an embodiment of the present invention;

3

FIG. 4 is a flow chart illustrating a method for using undo hints to speed up segment extension, in accordance with an embodiment of the present invention; and

FIG. 5 illustrates a processing system in which embodiments of the invention may be practiced.

DETAILED DESCRIPTION

Methods and apparatuses for data recovery are described. Note that in this description, references to “one embodiment” or “an embodiment” mean that the feature being referred to is included in at least one embodiment of the invention. Further, separate references to “one embodiment” in this description do not necessarily refer to the same embodiment; however, neither are such embodiments mutually exclusive, unless so stated and except as will be readily apparent to those skilled in the art. Thus, the invention can include any variety of combinations and/or integrations of the embodiments described herein.

Overview

In some database systems, undo tablespace may be divided into a plurality of undo segments. Often, each process in a database system is associated with a unique segment that is used to store undo records for that process. Segments may be further divided into extents, each of which may comprise some number of contiguous undo blocks. Each extent may have a particular commit time associated with the extent, and therefore, each extent may be considered to be expired or unexpired as a whole, with respect to undo retention. If a process needs to write a new undo record but carmot find an expired extent in its own segment, it may access the undo tablespace to request free tablespace to be added to its segment as a new extent. If there is no free tablespace, the process may traverse other segments, searching for an expired extent. If the process finds an expired extent, it absorbs that extent into its own segment. If no expired extent can be found, the process may absorb an unexpired extent into its own segment.

Adding a new extent to an additional segment, whether the new extent comes from a portion of unused tablespace, from an expired extent in another segment, or from an unexpired extent in another segment, may be referred to as “segment extension.”

FIG. 1 is a flow chart illustrating a method for using undo hints to speed up segment extension. As shown in FIG. 1, the method may begin in step 100, wherein undo hints may be stored. The undo hints may be stored, for example, in a local table as illustrated in FIG. 3A, and/or in a global table as illustrated in FIG. 3B. The undo hints stored may contain references to segments where expired extents are likely to be found for particular undo retentions. Storing undo hints will be discussed further with reference to FIG. 4.

The method may continue in step 102, wherein an undo hint may be retrieved. Retrieving undo hints will be discussed further with reference to FIG. 4. In step 104, a segment identified by the hint may be accessed. If the segment contains an expired extent for the current undo retention time, the expired extent may be allocated to another segment 106.

In some cases, the hint may not identify a segment with an expired extent. This occurs, for example, when another process has already used the hint and absorbed the expired extent. In this case, the method may retum to step 102, wherein another hint may be retrieved. The segment identified by the second hint will be then be examined to determine whether it contains an expired extent. The method may repeat until an expired extent is found.

4

Embodiments of the present invention also include methods for selectively reducing the undo retention time. The local table may be examined to determine whether any hints exist for a particular undo retention time. The undo retention time may then be selectively reduced to a point where hints exist. Methods for reducing the undo retention time will be discussed further with reference to FIG. 4.

Systems Used in Segment Extension

FIG. 2 is a block diagram illustrating a database system, in accordance with an embodiment of the present invention. As shown in FIG. 2, an undo table 200 include used tablespace 202 and free tablespace 204. The undo tablespace 200 may be divided into a plurality of segments 206a-c and 208a-b. The segments 206a-c and 208a-b may together comprise the used tablespace 202 of the undo table 200. The segments 206a-c and 208a-b may not be separate disk spaces, but may ratherbe segments that have been logically carved out of the undo table 200. Furthennore, the segments 206a-c and 208a-b need not be contiguous spans from the undo table 200, but may rather include a plurality of extents that have been logically configured to represent a discrete space. The segments 206a-c and 208a-b may further be logically configured to simulate circular buffers.

Each segment 206a-c and 208a-b may further include a high-water mark 212a-e that identifies the oldest extent in the segment. This may allow processes to determine if the segment contains any expired extents simply by identifying a single extent in the segment. In addition, each segment 206a-c and 208a-b may include an identification number that may be used to uniquely identify the segment.

The system may further include memory 210. The segments 206a-c and 208a-b may comprises online segments 206a-c and ofliine segments 208a-b. The online segments 206a-c store information relating to active transactions, while the offline segments 208a-b store infonnation relating to committed transactions. Information regarding the online segments 206a-c may be stored in memory 210, while no information regarding the ofliine segments 208a-b may be stored in memory 210.

Periodic processes in the system may search for expired extents in each segment and return the expired extents to the free tablespace 204. By the time segments 208a-b are taken ofliine, most of their space will likely have been retumed to the free tablespace 204. Thus, the ofliine segments 208a-b typically contain relatively little space. Other precautions may also be taken to ensure that ofliine segments contain relatively little space.

In the case of space pressure, the free tablespace 204 in the undo tablespace may no longer exist. In this case, processes may not be able to find expired extents in their own segment. Rather, the processes will examine other segments searching for extents to add to their own segment. In examining other segments, the processes may use a local table of undo hints 214, which may be stored in memory 210, and/or a global table of undo hints, which may be stored, for example, in shared memory.

FIG. 3A is a block diagram illustrating a local table of undo hints 214. As shown in FIG. 3A, the local table of undo hints 214 may be a two-dimensional table divided into a plurality of rows. Each row may correspond to a particular undo retention. In the example shown in FIG. 3A, there are nine rows, begimiing at 90% and decrementing at regular intervals to 10%. The first row corresponds to 90% of the current undo retention, the second row corresponds to 80% of the current undo retention, and so on.