12
Disk Space and Resource Management

CERTIFICATION OBJECTIVES

12.01 Manage resumable space allocation

12.02 Describe the concepts of transportable tablespaces and databases

12.03 Reclaim wasted space from tables and indexes by using the segment shrink functionality

12.04 Understand the database resource manager

12.05 Create and use Database Resource Manager Components

Two-Minute Drill

Self Test

Your two most valuable database resources are disk space and CPU time. With careful management of both, you can most likely delay upgrade costs for new disk drives or additional CPUs. In some cases, both these resources go hand in hand: (1) you will save CPU cycles if your disk allocation operations succeed the first time or (2) you are able to recover gracefully from low disk space scenarios.

Oracle provides several tools to help you manage disk space and other resources such as CPU time, idle time, and the number of concurrent connections. First, this chapter will cover Resumable Space Allocation, which enables you to suspend and resume large or long-running database operations that temporarily run out of space. Instead of a long-running job failing and requiring the user to start over after the disk space problem is corrected, the long-running job is suspended. After you take corrective action by providing more disk space for the long-running job, the suspended job automatically resumes.

Next, the chapter will cover another disk-related time-saving feature: transportable tablespaces. You can use this feature to quickly copy one or more tablespaces from one database to another without using the much more time consuming export/import method from previous versions of Oracle. An extension to transportable tablespaces, transportable databases, makes it easy to create a new database and move all non-SYSTEM tablespaces to the new database.

Oracle’s segment shrink functionality will also be covered. Over time, heavy DML activity on a table or index may lead to fragmented free space below the high water mark (HWM). Segment shrink compacts the data blocks, leading to better OLTP performance because fewer blocks need to be in the cache for a given set of rows. Full table scans also benefit from segment shrink for the same reason: fewer blocks need to be read to perform the full table scan. This benefits decision support and data warehouse systems.

Finally, the Oracle Database Resource Manager will be covered. You will always have contention among many different concurrent users throughout the day, and you may want to allocate your database resources differently depending on the time of day and which users are using those resources. In one case, you might want to give one user a higher priority over another; the user with a lower priority, however, may need more resources overall. In another scenario, you may want to limit the number of concurrent sessions during the day but not at night. Resource Manager can provide this flexibility and control.

CERTIFICATION OBJECTIVE 12.01
Manage Resumable Space Allocation

Resumable space allocation avoids headaches and saves time by suspending, instead of terminating, a large database operation requiring more disk space than is currently available. While the operation is suspended, you can allocate more disk space on the destination tablespace or increase the quota for the user. Once the low space condition is addressed, the large database operation automatically picks up where it left off.

The following sections first explore the details of resumable space allocation, such as the types of statements that can be resumed. In addition, you will learn how to configure Resumable Space Allocation in your database. Finally, no discussion of resumable space allocation would be complete without an example and an exercise.

Understanding Resumable Space Allocation

As you might expect, the statements that resume are known as resumable statements. The suspended statement, if it is part of a transaction, also suspends the transaction. When disk space becomes available and the suspended statement resumes, the transaction can be committed or rolled back whether or not any statements in the transactions were suspended. The following conditions can trigger resumable space allocation:

Out of disk space in a permanent or temporary tablespace

Maximum extents reached on a tablespace

User space quota exceeded

You can also control how long a statement can be suspended. The default time interval is two hours, at which point the statement fails and returns an error message to the user or application as if the statement was not suspended at all.

There are four general categories of commands that can be resumable: (1) SELECT statements, (2) DML commands, (3) SQL*Loader operations, and (4) DDL statements that allocate disk space.

Resumable SELECT Statements SELECT statements are resumable only when they run out of space in a temporary tablespace, which typically means that the SELECT statement performs a sort operation such as ORDER BY, DISTINCT, or UNION. SELECT statements issued via an application using Oracle Call Interface (OCI) are also candidates for suspension when you run out of sort space.

Resumable DML Commands As you might expect, DML commands such as INSERT, UPDATE, and DELETE may cause an out of space condition. For example, a DELETE may cause an out of space condition in the undo tablespace. As with resumable SELECT statements, the DML may come from OCI calls or even PL/SQL programs. In addition, INSERT INTO . . . SELECT from internal or external tables are resumable.

Resumable SQL*Loader Operations SQL*Loader import operations may cause an out of space condition. On the SQL*Loader command line (using the command sqlldr), you can set the RESUMABLE parameter to TRUE. Once you set RESUMABLE to TRUE, you can assign a name to the resumable operation with the RESUMABLE_NAME parameter, as well as set the resumable timeout with RESUMABLE_TIMEOUT. RESUMABLE, RESUMABLE_NAME, and RESUMABLE_TIMEOUT will be covered later in this chapter in the context of the ALTER SESSION command.

Resumable DDL Commands All DDL commands that allocate disk space for new or existing segments are resumable:

CREATE TABLE . . . AS SELECT (CTAS)

CREATE INDEX

ALTER TABLE . . . [MOVE | SPLIT] PARTITION

ALTER INDEX . . . REBUILD

ALTER INDEX . . . [REBUILD | SPLIT] PARTITION

CREATE MATERIALIZED VIEW

Configuring Resumable Space Allocation

Enabling and disabling Resumable Space Allocation is easy to do, and does not consume any significant resources unless you use it. It can be enabled at the instance level using the RESUMABLE_TIMEOUT initialization parameter, or by an ALTER SESSION command.

If you set the initialization parameter RESUMABLE_TIMEOUT to a non-zero value, Resumable Space Allocation is enabled. The value you specify for the initialization parameter specifies how long the suspended operation will wait for more resources to be allocated before terminating the operation. Since this is a dynamic parameter, you can change it on the fly without a database restart:

By default, the value is 0, which means that Resumable Space Allocation is disabled. If it is enabled at the system level, then all sessions can take advantage of Resumable Space Allocation. If you need more fine-grained control of which users can enable Resumable Space Allocation, then you grant the RESUMABLE system privilege:

Once a user has the RESUMABLE privilege, she can enable it at will using the ALTER SESSION command, as in this example:

The default resumable timeout value is 7200 seconds (two hours), unless the RESUMABLE_TIMEOUT parameter is set to a non-zero value or you override it with ALTER SESSION:

To make it easy to identify your resumable statement in the data dictionary views DBA_RESUMABLE and USER_RESUMABLE, you can enable Resumable Space Allocation with the NAME parameter:

When you query DBA_RESUMABLE or USER_RESUMABLE, you see the status of the session along with the current SQL statement executing for the sessions with Resumable Space Alocation enabled:

As you might expect, you can disable Resumable Space Allocation with ALTER SESSION:

You can further automate enabling Resumable Space Allocation for specific users by creating a LOGON trigger that will enable Resumable Space Allocation, assign a name to the session (most likely containing the user name or other easily identifiable keywords), and set the resumable timeout specific for that user.

Using Resumable Space Allocation

In practice, the DBA or a user can register a trigger for the AFTER SUSPEND system event. This trigger is fired immediately after a qualifying resumable statement suspends. The code in the trigger can perform different actions depending on which user’s resumable statement was suspended, the reason for the suspension, or the time of day. For example, a user running a month-end query may run out of undo space and you want to let the query complete if at all possible. Therefore, you may want to increase the timeout value and send an email to the DBA and to the user that undo space is low and that the statement is temporarily suspended. For all other types of resumable statements in this scenario, you will abort the statement and send an email. Here is the basic structure of a PL/SQL trigger to handle resumable statements:

EXERCISE 12-1
Configure Resumable Space Allocation for the HR User

In this exercise, detect and correct a space problem for the HR user.

1. Grant the RESUMABLE privilege to HR:

2. Create a tablespace for the HR user and allocate all space in the tablespace using any preferred method:

3. Once the tablespace is full, attempt to create a new table:

4. Enable Resumable Space Allocation in the HR session for 3600 seconds (60 minutes):

5. As the HR user, retry the CREATE TABLE statement in step 3. The statement does not terminate with an error but appears to hang. The alert log reflects the statement suspension:

As part of the Automatic Diagnostic Repository (ADR), the message also appears in XML format in the directory $ORACLE_BASE/DIAG/RDBMS/DW/DW/LOG.XML:

The suspended statement and the out of space condition also appear on the EM home page in the Alert section, as you can see in Figure 12-1.

FIGURE 12-1     Details of suspended statement on EM home page

6. Query the data dictionary view DBA_RESUMABLE for additional details about the suspended statement:

7. As the DBA, allocate another 100MB for the USERS9 tablespace:

The alert log indicates that the resumable statement for HR has resumed:

8. Query DBA_RESUMABLE again to confirm the completion status of the resumable operation:

9. The statement for the HR user completes successfully, although not as quickly as the user expected, because the user had to wait for the DBA to manually allocate more space:

To notify the user that a problem was averted (and explain why the CREATE TABLE took so long), you can create a trigger for the AFTER SUSPEND event as described earlier in this chapter that includes sending the details of the suspend and resume operations via email.

CERTIFICATION OBJECTIVE 12.02
Describe the Concepts of Transportable Tablespaces and Databases

There are many ways to move data from one database to another, such as database links, Data Pump Export/Import, and transportable tablespaces. For large volumes of data, using transportable tablespaces is by far the fastest method. In a nutshell, you export just the metadata for the objects in the tablespace using Data Pump, copy the datafiles comprising the tablespace to the destination database, and import the tablespace’s metadata into the destination database.

The following sections further investigate some of the restrictions of transportable tablespaces such as platform limitations. Even platforms with different hardware architectures are candidates for transportable tablespaces. In addition, you will learn how to transport a tablespace using both EM and SQL commands. RMAN is required to transport a tablespace in some scenarios. Finally, a brief overview of how to transport an entire database will be given.

Configuring Transportable Tablespaces

Transporting tablespaces has many uses, such as quickly distributing data from a data warehouse to data marts in other databases, or perhaps being used to converting an entire database from one platform to another. When transporting between platforms, both the source and destination platforms must be on Oracle’s list of supported platforms. Most likely, your source and destination platforms will be supported. For example, virtually all hardware platforms based on either 32-bit or 64-bit Intel architecture are supported, along with Solaris, Mac OS, and AIX on proprietary hardware.

The following sections step through the compatibility requirements as well as any additional steps you need to perform for some hardware platforms. Once the compatibility and configuration steps are complete, you will step through an example using both EM and SQL.

Determining Compatibility Requirements

Oracle Database feature compatibility is controlled by the COMPATIBLE initialization parameter, which enables or disables the use of certain features in the database. For the purposes of discussing transportable tablespaces, these are features that require a specific file format on disk. For example, if you want to upgrade to Oracle Database 11g from Oracle Database 10g R2, you may want to set COMPATIBLE to 10.0.0 for a short time. Thus you can downgrade to version 10g R2 if you encounter problems in production, without requiring a restore and recover from backup because the datafile formats for version 11g are not usable on version 10g R2. Even though you may have tested the upgrade on a backup server, some problems with a new release do not appear until a week after you go live with the new release in production!

When you create a transportable tablespace set, Oracle determines the minimum compatibility level of the target database and stores this value in the metadata for the transportable tablespace set. As of Oracle Database 11g, you can always transport a tablespace to another database with the same or higher compatibility level, regardless of the target platform.

Regardless of the similarities or differences in hardware platforms between the source and target database, both databases must be using the same character set.

Table 12-1 shows the minimum compatibility settings for the source and target database depending on the differences in block size and hardware platforms.

TABLE 12-1 Minimum Compatibility Settings for Transportable Tablespace Scenarios

Operation Type	Source Database Minimum Compatibility Release Number	Target Database Minimum Compatibility Release Number
Source and target database on same platform	8.0	8.0
Source database has different database block size than target database	9.0	9.0
Source and target database are on different platforms	10.0	10.0

In other words, even if you are running Oracle Database 11g with COMPATIBLE set to 11.0.0, you can transport a tablespace from a database on a different platform that has COMPATIBLE set to 10.0.0.

Determining Endian Requirements

Oracle’s transportable tablespace feature, although available for nearly every platform that Oracle Database runs on, requires an extra step depending on the underlying hardware platform. On Intel-based hardware, pairs of bytes in numeric or string values are reversed. For example, the value 2 is stored as 0x0200. This byte ordering is known as little-endian because the least significant byte is first. In contrast, a big-endian system stores bytes in order of most significant to least significant byte. Therefore, on a big-endian hardware platform, such as Sun SPARC, the value 2 is stored as 0x0002. Understandably, a conversion must be done on column data transported between platforms with different endian formats.

To determine the endian formats of all supported platforms, you can query the dynamic performance view V$TRANSPORTABLE_PLATFORM, as in this example:

This query also shows you all supported platforms for transportable tablespaces. If the value of ENDIAN_FORMAT is different then you must use RMAN commands at the source or target database to convert the datafiles to the target database’s endian format. The required RMAN commands will be discussed later in this chapter. To determine the endian format of your platform, you can join V$DATABASE to V$TRANSPORTABLE_PLATFORM:

Transporting Tablespaces

Whether you use SQL commands or EM to transport a tablespace, the general steps are the same. EM does provide some automation to the process that streamlines the procedure and helps to avoid errors. However, as with most EM-based tools, EM does not cover every possible option available at the SQL< command line. Here are the steps:

1. Make tablespace(s) read-only on the source database.

2. Use Data Pump Export to extract tablespace metadata from the source database.

3. If target does not have the same endian format, convert the tablespace contents.

4. Transfer tablespace datafiles and metadata dump file from source to target.

5. Use Data Pump Import to import tablespace metadata into the target tablespace.

6. Make tablespace(s) read-write on both source and target databases.

The following two sections give you an overview of using EM to transport a tablespace and then provide the SQL version of the same operation.

Using EM to Transport a Tablespace

To transport a tablespace using EM, start at the EM home page, and select the Data Movement tab. Under the Move Database Files section, click on the Transportable Tablespaces link and you’ll see the page shown in Figure 12-2.

FIGURE 12-2     Transport Tablespaces start page

In this step, you’ll generate a transportable tablespace set containing the tablespace XPORT_DW in the DW database, and then transport it to the HR database. Ensure that the Generate radio button is selected and that you have provided the hostname credentials for the oracle user (the Linux Oracle user). Click on the Continue button. On the page shown in Figure 12-3, add the XPORT_DW tablespace to the list.

FIGURE 12-3     Selecting tablespaces for transport

In addition, you can check that all objects in the tablespace are self-contained. In other words, there are no dependencies on objects in other tablespaces. You can take it a step further and check for the reverse condition in that there are no objects in other tablespaces dependent on the tablespace you are transporting.

When you click on the Next button, EM checks for dependencies between XPORT_DW tablespace and other tablespaces in the database. On the page shown in Figure 12-4, you specify the destination platform, which is the same for the source and target databases (in this case, both are Linux Intel Architecture 32-bit). If the target database has a different endian format, then EM will convert the tablespace. Select the appropriate options for your environment and click on the Next button.

FIGURE 12-4     Specifying destination database characteristics

On the page shown in Figure 12-5, you specify the directory where you want to save the dump file containing the tablespace’s metadata, as well as a copy of the tablespace’s datafile(s). This location should be accessible by the target database, meaning the location could be on a Network File Server or some similar shared storage area. If it is not, then you will have to use another utility such as FTP to move the files later. In this example, the directory /Temp is accessible by both servers.

FIGURE 12-5     Specifying transportable tablespace dump file location

Click on the Next button shown in Figure 12-5 and you will schedule a job to transport the tablespace as shown in Figure 12-6.

FIGURE 12-6     Scheduling transportable tablespace job

Figure 12-7 shows the final step in the first half of the transport process, which will give you the opportunity to review the settings you specified before you submit the job.

FIGURE 12-7     Review transportable tablespace job settings

When you click on the Submit Job button shown in Figure 12-7, EM submits the job to process the transportable tablespace export. EM generates a confirmation page, and you can monitor the progress of the job with the link provided.

After the job completes successfully, you can perform the transportable tablespace import on the destination database. On the page shown in Figure 12-8, you have logged in to EM on the destination database (shown as the HR.WORLD database in Figure 12-8), and you are at the same page as shown in Figure 12-2, except that you will specify Integrate (import) instead of Generate (export).

FIGURE 12-8     Transport tablespace import home page

Clicking on the Continue button in Figure 12-8, you see the page shown in Figure 12-9 where you can specify the dump file name and the datafile name you specified in Figure 12-5. When you click the Next button, EM reads the dump file and datafile(s).

On the next page, shown in Figure 12-10, you can change or accept the value EM chooses for the new datafile destination. In addition, you can leave the new datafile in place. After specifying the desired options, click on the Next button.

FIGURE 12-10     Specifying alternate location for imported tablespace

On the page shown in Figure 12-11, you can optionally remap objects from one schema to another. In other words, if both the source and destination database have an HR schema with identically named tables, you can remap the imported HR objects to the HR_IMPORT user instead and work out the differences after the import has completed.

FIGURE 12-11     Specifying schema remapping during tablespace import

After you click on the Next button in Figure 12-11, you see the job scheduling page shown in Figure 12-12. Click on the Next button.

FIGURE 12-9     Transport tablespace dump file and datafile locations

FIGURE 12-12     Scheduling transportable tablespace import processing

On the page shown in Figure 12-13, you get one more opportunity to review the import job. When you are satisfied with the parameters click on Submit Job. You can monitor the progress of the tablespace import, as you can see in Figure 12-14. When the job completes, the tablespace XPORT_DW is ready to use.

FIGURE 12-13     Reviewing transportable tablespace job parameters

FIGURE 12-14     Monitoring transportable tablespace import job

Using SQL to Transport a Tablespace

You can use SQL commands and PL/SQL packages to perform the transportable tablespace operation. You use the expdp and impdp utilities along with DBMS_FILE_TRANSFER PL/SQL package to copy tablespaces from one database to another. Here are the high-level steps:

1. Set up the directory objects on the source and target databases for the dump file sets and the tablespace datafiles (one-time setup).

2. Check for tablespace self-consistency with DBMS_TTS.TRANSPORT_SET_CHECK.

3. Use expdp to create the metadata for the XPORT_DW tablespace.

4. Use DBMS_FILE_TRANSFER to copy the dump file set(s) and datafile(s) to the target database.

5. On the target database, use impdp to “plugin” the tablespace.

EXERCISE 12-2
Transport a Tablespace Using SQL and PL/SQL

In this exercise, use SQL and the DBMS_FILE_TRANSFER procedure to transport the XPORT_DW tablespace from the DW database on server dw, to the HR database on server srv04.

1. Set up the directory objects on the source and target databases for the dump file sets and the tablespace datafiles (one-time setup).

On the dw source database you need to create the directory objects that will hold the dump file set as well as a directory object pointing to the location where the datafile for the XPORT_DW tablespace is stored. Below are the SQL commands on the DW database. The file system directory /Temp is common to all servers:

If the source or target tablespace are stored in an ASM disk group then to make a copy you must use ftp with the /sys/asm virtual directory in the XML DB repository, DBMS_FILE_TRANSFER, or the cp command in the asmcmd utility.

On the HR destination database you will execute similar commands as you can see here:

These directory objects are persistent, and you may use them in the future for other Data Pump or file-transfer operations.

2. Check for tablespace self-consistency with DBMS_TTS.TRANSPORT_SET_CHECK.

Before transporting the XPORT_DW tablespace, you should check to make sure that all objects in the tablespace are self-contained with the procedure DBMS_TTS.TRANSPORT_SET_CHECK, as follows:

Not finding any rows in TRANSPORT_SET_VIOLATIONS means that the tablespace has no external dependent objects or any objects owned by SYS. This view is re-created every time you run DBMS_TTS.TRANSPORT_SET_CHECK.

3. Use expdp to create the metadata for the XPORT_DW tablespace.

On the DW database, you will run the expdp command to export the metadata associated with the XPORT_DW tablespace after making the XPORT_DW tablespace read-only:

To run expdp, you open an operating system command prompt and perform the metadata export as follows:

4. Use DBMS_FILE_TRANSFER to copy the dump file set(s) and datafile(s) to the target database.

In this step, you will copy the tablespace’s datafile to the remote database using DBMS_FILE_TRANSFER as follows (although you could use the /Temp directory for this step as well):

If the tablespace was created using OMF, you will have to use the value of DB_FILE_CREATE_DEST and some detective work, or use the dynamic performance views V$DATAFILE and V$TABLESPACE to track down the actual subdirectory and datafile name on the host operating system.

5. On the target database, use impdp to “plugin” the tablespace. In the final step, you will run impdp on the target database to read the metadatafile and “plugin” the tablespace datafile. Here is the output from this operation:

Note that you must change the tablespace from READ ONLY back to READ WRITE. When a tablespace is transported to another database, by default the copy of the tablespace is online but read-only. Also, don’t forget to change the source tablespace back to READ WRITE after the completion of the tablespace transport if you made it read-only during a non-RMAN tablespace transport operation.

CERTIFICATION OBJECTIVE 12.03
Reclaim Wasted Space from Tables and Indexes by Using the Segment Shrink Functionality

Frequent inserts, updates, and deletes on a table may over time leave the space within a table fragmented. Oracle can perform segment shrink on a table or index to reduce this fragmentation. Shrinking the segment makes the free space in the segment available to other segments in the tablespace with the potential to improve future DML operations on the segment. This is because fewer blocks may need to be retrieved for the DML operation after the segment shrink. Full table scans will also benefit from a segment shrink operation on a table because the table occupies fewer blocks after the shrink operation.

Segment shrink is very similar to online table redefinition in that space in a table is reclaimed. However, segment shrink can be performed in place without the additional space requirements of online table redefinition.

To determine which segments will benefit from segment shrink, you can invoke Segment Advisor to perform growth trend analysis on specified segments. In the following sections, you will invoke Segment Advisor on some candidate segments that may be vulnerable to fragmentation using both SQL command-line tools as well as EM.

Understanding Segment Shrink

To reclaim unused space in a segment, Oracle’s segment shrink feature performs two different tasks: (1) compacting the data rows, and (2) moving the high water mark (HWM). Figure 12-15 shows a table segment before and after a shrink operation.

FIGURE 12-15     Table Segment Before Segment Shrink Table Segment After Segment Shrink

The first phase of the segment shrink is compaction, which moves data rows to as far left in the segment as possible, in order to maximize the number of rows retrieved per block. However, since a full table scan reads all blocks up to the HWM, you

can relocate the HWM as far left as possible in the segment to improve the performance of full table scans as well. The reason Oracle divides the segment shrink operation into two parts is because relocating the HWM can potentially block a user’s DML operations, but the shrink-only operation does not. As a result, you can perform compaction during the day with minimal impact on response time for user DML. You can then run the shrink operation at night when DML activity is low. You will see how to initiate one or both phases of a segment shrink operation later in this chapter.

A shrink operation is executed internally as a series of INSERT and DELETE operations. No DML triggers are executed during a shrink.

There are many other benefits to a segment shrink operation. When you shrink an index, the index tree is more compact, and therefore fewer I/Os are required to look up a ROWID in the index. Note also that indexes for tables that are shrunk are maintained, and therefore don’t need to be rebuilt after a shrink operation. In addition, since the HWM is relocated after a shrink operation, more free space is available for other objects in the tablespace.

Here is a list of candidates for segment shrink:

Heap- (standard) and index-organized tables

Indexes

Partitioned and sub-partitioned tables

Materialized views and materialized view logs

There are, as you’d expect, a few restrictions to segment shrink operations. First, the tablespace containing the objects to be shrunk must be managed with Automatic Segment Space Management (ASSM), and not freelists. There is no compelling reason since Oracle Database 10g to use freelist-based space management, so this should not be a problem. There are a few objects that cannot be shrunk:

Clustered tables

Tables with LONG columns

Tables with on-commit or ROWID-based materialized views

Index-organized table (IOT) mapping tables

Tables containing function-based indexes

Most of these restrictions should have a minimal impact to your environment. For example, you should have converted any LONG columns to CLOB or BLOB columns long ago.

Automating Segment Advisor

Oracle’s Segment Advisor can identify candidates for segment shrink, which can run either manually or on a schedule as part of the Automatic Segment Advisor job. Whether it runs manually or automatically, the first phase of Automatic Segment Advisor’s analysis uses growth statistics and data sampling stored in the Automatic Workload Repository to determine which objects need further analysis. In addition, Automatic Segment Advisor focuses on tablespaces that have exceeded a critical or warning space threshold since the last analysis.

After performing the analysis on the candidate objects identified in the first phase, the Automatic Segment Advisor can recommend segments (tables or indexes) that are candidates for segment shrink. Once it has made recommendations, you can run segment shrink for a single table or for an entire tablespace.

The Automatic Segment Advisor runs as a scheduler job in the default maintenance window. The total scheduled window time is four hours every weeknight and on weekends for 20 hours a day. Although you cannot specify what objects and tablespaces are analyzed by the Automatic Segment Advisor, you can change when it runs and how many resources it consumes when it runs. Figure 12-16 shows the configuration page for the Automatic Segment Advisor, statistics gathering, and automatic SQL tuning jobs.

FIGURE 12-16     Configuring automated maintenance tasks

Shrinking Segments

You can perform advisor tasks and segment shrink operations using a series of SQL commands on a single segment, or alternatively, use the EM interface to perform the same task. The following sections show you both methods. You can use either method on a selected object, or an object already identified by the Automatic Segment Advisor.

Using SQL to Shrink Segments

Before you can shrink a segment, you must also enable row movement on the table or index. Some applications or operations, such as the table’s indexes, rely on ROWID values to access the table rows. Therefore you must make sure that the segment you may shrink will not require ROWIDs to be the same for a given row. As you might expect, you enable row movement with ALTER TABLE as follows:

The easy part of shrinking a segment is the actual command to shrink the segment. Here is an example:

The COMPACT clause is optional. If you specify COMPACT, only the compaction step occurs, and the HWM stays in place until you run the same ALTER TABLE command again without the COMPACT keyword.

The exam will require that you understand the purpose of the COMPACT keyword. Unlike most Oracle SQL commands, adding the COMPACT keyword actually performs fewer actions rather than more actions!

To initiate Segment Advisor on a specific table or on an entire tablespace, you use the PL/SQL package DBMS_ADVISOR. This package is used for other advisors as well, including the SQL Access Advisor and the Undo Advisor. Here are the PL/SQL procedures within DBMS_ADVISOR that you use to perform an analysis:

CREATE_TASK Create a new task in the advisor repository.

CREATE_OBJECT Specify a database object for analysis.

SET_TASK_PARAMETER Set the parameters for the analysis.

EXECUTE_TASK Perform the analysis.

The results of the analysis produce one or more recommendations, which are not implemented automatically. After Segment Advisor has been invoked to give recommendations, the findings from Segment Advisor are available in the DBA_ADVISOR_FINDINGS data dictionary view. To show the potential benefits of shrinking segments when Segment Advisor recommends a shrink operation, the view DBA_ADVISOR_RECOMMENDATIONS provides the recommended shrink operation along with the potential savings for the operation in bytes.

EXERCISE 12-3
Perform Segment Analysis and Shrink Operations

In this exercise, you will manually run Segment Advisor to analyze a table, and then run the recommendations from the analysis, which will usually include a segment shrink.

1. Add a new text column to the table HR.EMPLOYEES and enable row movement because you will most likely be shrinking the segment later in this exercise:

2. Perform DML on the HR.EMPLOYEES table, first inserting 100 rows with large values for WORK_RECORD and then updating those rows with small values.

3. Use an anonymous PL/SQL block to set up and initiate a Segment Advisor job, run it and then retrieve the value of the TASK_ID variable:

The procedure DBMS_ADVISOR.CREATE_TASK specifies the type of advisor. In this case it is Segment Advisor. The procedure will return a unique task ID and an automatically generated name to the calling program. You will assign your own description to the task.

Within the task, identified by the uniquely generated name returned from the previous procedure, identify the object to be analyzed with DBMS_ADVISOR.CREATE_OBJECT. Depending on the type of object, the second through the sixth arguments vary. For tables, you only need to specify the schema name and the table name.

Using DBMS_ADVISOR.SET_TASK_PARAMETER, tell Segment Advisor to give all possible recommendations about the table. If you want to turn off recommendations for this task, you would specify FALSE instead of TRUE for the last parameter.

Finally, initiate the Segment Advisor task with the DBMS_ADVISOR.EXECUTE_TASK procedure. Once it is done, you display the identifier for the task (from the variable TASK_ID) so that you can query the results in the appropriate data dictionary views later.

4. Using the task number from the previous step, query DBA_ADVISOR_FINDINGS to see what you can do to improve the space utilization of the HR.EMPLOYEES table:

The results are fairly self-explanatory. You can perform a segment shrink operation on the table to reclaim space from numerous insert, delete, and update operations on the HR.EMPLOYEES table. Because the WORK_RECORD column was added to the HR.EMPLOYEES table after the table was already populated, you may have created some chained rows in the table. In addition, since the WORK_RECORD column can be up to 4000 bytes long, updates or deletes of rows with big WORK_RECORD columns may create blocks in the table with free space that can be reclaimed.

5. Query the data dictionary view DBA_ADVISOR_RECOMMENDATIONS using the same TASK_ID to see the summary of recommendations:

6. Implement the recommendation:

As mentioned earlier, the shrink operation does not require extra disk space and does not prevent access to the table during the operation, except for a very short period of time at the end of the process to free the unused space. All indexes are maintained on the table during the operation.

Using EM to Shrink Segments

To run Segment Advisor from EM, start at the home page and click on the Advisor Central link at the bottom of the page. On the Advisors tab, click on the Segment Advisor link. On the page shown in Figure 12-17, select the Schema Objects radio button because you are going to analyze the HR.EMPLOYEES table.

FIGURE 12-17     Segment Advisor: select object type for analysis

Click on the Next button. On the page shown in Figure 12-18, specify the table HR.EMPLOYEES for analysis. Click on the Submit button to initiate the analysis task.

FIGURE 12-18     Segment Advisor: select objects for analysis

On the Advisor Central page, you can monitor the progress of the job. You can see the job in progress in Figure 12-19. When the job is completed, click on the job name link to see the results of the analysis.

FIGURE 12-19     Segment Advisor: monitor job progress

Figure 12-20 shows the results of the analysis. As you might expect, the Segment Advisor task recommends that you shrink the table. Clicking on the Shrink button will perform the shrink.

FIGURE 12-20     Segment Advisor: analysis results

As mentioned earlier in the chapter, the Automatic Segment Advisor will generate recommendations as well. In Figure 12-21, Segment Advisor provides four recommendations.

FIGURE 12-21     Automatic Segment Advisor: recommendations summary

Clicking on the Recommendations link shown in Figure 12-22, you see the individual recommendations for the SYSAUX tablespace, which includes shrinking four tables because they have a relatively significant amount of reclaimable space.

FIGURE 12-22     Automatic Segment Advisor: SYSAUX recommendations

CERTIFICATION OBJECTIVE 12.04
Understand the Database Resource Manager

Your database server has a limited number and amount of resources, and usually there are many users competing for those resources. Controlling allocation to your database resources using the underlying operating system is not desirable for two reasons: first, the operating system software has little or no visibility to the users and groups within the database. Secondly, context switching within the operating system between database processes can cause excessive and unnecessary overhead. Instead, you can use Database Resource Manager to control the distribution of resources among database sessions in a more efficient and fine-grained way than the operating system can.

The following sections give you an overview of Resource Manager terminology, including consumer groups, resource plans, and resource plan directives. Next, you will find an explaination on the types of resources that Resource Manager can control.

Understanding Resource Manager Terminology

Understanding the three basic constructs of Resource Manager is required, not only for the purposes of the certification exam, but to effectively use Resource Manager “out of the box.” The three basic constructs are resource consumer groups, resource plans (and subplans), and resource plan directives.

First, you need resource consumer groups to identify groups of users or sessions that have similar resource needs. For example, you have system administrators that most likely need as many resources (CPU and disk space) as possible when fixing a critical problem. In contrast, you have batch job users that can wait longer for the results of a query, and therefore will consume less resources if there are other users, such as users in an OLTP group, that need near instantaneous response time when querying a customer’s order history.

Second, you need a resource plan that assigns various resources at specific percentages or relative priority to a resource group. You can have as many resource plans as you want but only one resource plan can be active at a time. A resource plan can also have a subplan that further subdivides resource allocation within a plan. Resource plans prioritize resources by using up to eight levels, with level 1 at the highest priority and level 8 at the lowest priority. The consumer groups at level 1 must have their resource requests satisfied before groups in lower levels.

Within the resource plan are resource plan directives which associate consumer groups with a resource plan and specify how the resources are divided among the consumer groups or subplans. For example, you may have a resource plan called WEEKDAY that manages three consumer groups: (1) OLTP, (2) REPORTING, and (3) OTHER_GROUPS. Any user or process not explicitly defined in a consumer group within the resource plan is in OTHER_GROUPS by default. The WEEKDAY plan has three directives:

OLTP gets up to 70% of CPU resources

REPORTING gets up to 20% of CPU resources

OTHER_GROUPS gets up to 10% of CPU resources

Note that if the only users requesting resources are in the REPORTING group, they can get more than 20% of the resources until any OLTP or OTHER_GROUPS users request CPU resources.

Figure 12-23 shows an entity-relationship (E-R) diagram containing users, resource consumer groups, resource plans, and resource plan directives.

FIGURE 12-23     Resource Manager component relationships

Oracle automatically adds OTHER_GROUPS to any resource plan to ensure that users not assigned to any group in the current resource plan will still have resources available.

Understanding Resource Manager Allocation Methods

Resource Manager can allocate resources based on one or more of these units of measure:

CPU usage Divide CPU usage among consumer groups.

Degree of parallelism Control the maximum degree of parallelism allowed for members of the resource group.

Number of active sessions Limit the number of active sessions for users in the resource group. New users are queued until a current session finishes.

Undo space Control the amount of space in the UNDO tablespace generated by DML statements. Exceeding the quota blocks new DML until other users within the consumer group release space. The user can, however, run SELECT statements until enough undo becomes available.

CPU time limit Maximum CPU usage. Resource Manager estimates execution time using the optimizer and does not start execution of the statement unless it is under the time limit.

Idle time limit Limit the maximum amount of time that a session is idle.

In addition, Resource Manager can automatically switch a session or process from one consumer group to another based on criteria such as using a certain amount of CPU time or undo space. For example, a session that uses over 100 seconds of CPU can be switched to another consumer group with a lower priority while still allowing the session to continue running. The session’s owner is not aware of the consumer group switch other than possibly noticing a slight delay in returning the results of the operation.

CERTIFICATION OBJECTIVE 12.05
Create and Use Database Resource Manager Components

Now that you have a solid understanding of Resource Manager terminology and the types of resources you can control and allocate, it’s time to get your hands dirty and see how Resource Manager works using both EM and PL/SQL procedures.

First, a thorough understanding of the default resource plan provided by Oracle, and how each database user uses the default resource plan will be given. Next, you will be shown how to create a new resource plan, create a new consumer group, assign users to the consumer group, then activate the resource plan. Finally, you’ll be given several monitoring tools to see how your resources are being used, including several data dictionary views.

The PL/SQL package DBMS_RESOURCE_MANAGER has everything you need to view, create, maintain, and use Resource Manager components. Where appropriate, you’ll be given the PL/SQL equivalent of EM pages when exercising Resource Manager.

Understanding DEFAULT_PLAN

Oracle provides a default plan, appropriately named DEFAULT_PLAN, when you create a database. To view this plan, start at the EM home page, click on the Server tab, then click on the Plans link under Resource Manager. You’ll see the page shown in Figure 12-24.

FIGURE 12-24     EM Resource Plans

Click on the DEFAULT_PLAN link, and you’ll see the page shown in Figure 12-25.

FIGURE 12-25     Details of the DEFAULT_PLAN plan

The plan DEFAULT_PLAN contains four groups or subplans. In short, members of the SYS_GROUP (such as SYS and SYSTEM) get up to 100 percent of the resources they need when requested. All other consumer groups get a certain percentage of what the Level 1 group (SYS_GROUP) does not use. Here is a brief explanation of the groups or subplans within this plan:

SYS_GROUP Administrative users, such as SYS and SYSTEM that usually need all available resources for urgent maintenance tasks.

ORA$AUTOTASK_SUB_PLAN A subplan for all automated background tasks, using 5 percent of what SYS_GROUP does not use.

ORA$DIAGNOSTICS Routine diagnostics and database analysis, using

5 percent of what SYS_GROUP does not use.

OTHER_GROUPS All other users that do not fall into the other groups, such as OLTP users, batch users, and so forth, using 90 percent of what SYS_GROUP does not use.

Since ORA$AUTOTASK_SUB_PLAN is a sub-plan, you can return to the previous page and query its directives, as you can see in Figure 12-26.

FIGURE 12-26     Querying the contents of a sub-plan

As mentioned earlier, a sub-plan is identical to a resource plan, except that it can further allocate resources within an existing plan. Therefore, you could use a sub-plan as a top-level plan, as long as it includes OTHER_GROUPS. However, sub-plans are almost always used only as sub-plans, as the example in Figure 12-26 indicates.

To find out which plan is active (remember, only one resource plan can be active at a time), query the initialization parameter RESOURCE_MANAGER_PLAN:

Note that switching between resource plans is done by a scheduler process. As a result, if you want to manually control the current resource plan, you will have to disable the switch via the scheduler process. If you do not set a value for the RESOURCE_MANAGER_PLAN parameter, resource management is not performed in the instance.

You can use the PL/SQL packages CREATE_PLAN, UPDATE_PLAN, and DELETE_PLAN to create, update, and delete resource plans. Some of these procedures will be used in an exercise later in the chapter.

Creating a New Resource Plan

If you are using EM, you can create a new plan from the Resource Plans page by clicking on the Create button on the page shown in Figure 12-24. Figure 12-27 shows you the details of creating a plan using EM.

FIGURE 12-27     Creating a resource plan using EM

The PL/SQL equivalent is as follows:

Note that before you use any Resource Manager commands, you must create a “pendingarea” for your work. To create a pending area, use the CREATE_PENDING_AREA procedure of the DBMS_RESOURCE_MANAGER package. When you have completed your changes, use the VALIDATE_PENDING_AREA procedure to check the validity of the new set of plans, subplans, and directives. You can then either submit the changes (via SUBMIT_PENDING_AREA) or clear the changes (via CLEAR_PENDING_AREA). The procedures that manage the pending area do not have any input variables. So, a sample creation of a pending area uses the following syntax:

If the pending area is not created, you will receive an error message when you try to create a resource plan. If you are using EM, the pending area is created for you automatically.

Creating and Assigning Consumer Groups

Figure 12-28 shows you how to create consumer groups and assign users to the group. You can create the group and add users on the same page.

FIGURE 12-28     Creating a resource consumer group

The equivalent PL/SQL procedure looks like this:

Understanding Resource Allocation Methods

Creating or modifying directives within a plan is straightforward using EM. If you click on the Edit button shown in Figure 12-25, you can change the resource allocations for groups within the plan. Figure 12-29 shows you the page where you can edit the DEFAULT_PLAN resource plan. Note that you can use the tabs on this page to define resource plan directives for parallelism, undo space usage, idle time, and so forth.

FIGURE 12-29     Editing a resource plan using EM

To assign directives to a plan using PL/SQL, use the CREATE_PLAN_DIRECTIVE procedure of the DBMS_RESOURCE_MANAGER package. The syntax for the CREATE_PLAN_DIRECTIVE procedure is shown in the following listing:

The multiple CPU variables in the CREATE_PLAN_DIRECTIVE procedure support the creation of multiple levels of CPU allocation. For example, you could allocate 75 percent of all your CPU resources (level 1) to your online users. Of the remaining CPU resources (level 2), you could allocate 50 percent to a second set of users. You could split the remaining 50 percent of resources available at level 2 to multiple groups at a third level. The CREATE_PLAN_DIRECTIVE procedure supports up to eight levels of CPU allocation.

Activating Resource Plans

To activate a resource plan, you can use the RESOURCE_MANAGER_PLAN initialization parameter as shown earlier in the chapter. You can also select the desired plan from the page shown in Figure 12-24, select Activate from the Actions dropdown menu, and click on the Go button.

EXERCISE 12-4
Create and Use a New Resource Manager Plan

In this exercise you will create a new resource plan, consumer group, and directives within the resource plan.

1. Create a pending area for your resource manager session:

If the pending area is not created, you will receive an error message when you try to create a resource plan.

2. Create a plan called DEVELOPERS. By default, the CPU allocation method will use the “emphasis" method, allocating CPU resources based on percentage:

3. Create two new consumer groups, ONLINE_DEVELOPERS and BATCH_DEVELOPERS:

4. Assign the user ELLEN_K to the ONLINE_DEVELOPERS group, and JOHNDOE to the BATCH_DEVELOPERS group:

5. Create two plan directives to allocate 75 percent of CPU resources to the ONLINE_DEVELOPERS group and 25 percent to the BATCH_DEVELOPERS group. In addition, limit parallelism to 12 for ONLINE_DEVELOPERS and to 6 for BATCH_DEVELOPERS:

6. Validate and submit the pending area:

Remember that your plan must include OTHER_GROUPS so that users other than those in the consumer groups assigned to the DEVELOPERS plan will have some resources available when the plan is activated. Therefore, create one more plan directive for the DEVELOPERS plan that includes OTHER_GROUPS:

7. Revalidate the pending area and submit:

8. Navigate to the EM page where you can view plans and plan directives. The page should look something like the page shown in Figure 12-30.

FIGURE 12-30     Validated and submitted resource plan with directives

Understanding Resource Manager Views

Table 12-2 contains the data dictionary views relevant to resource management.

TABLE 12-2 Resource Manager Data Dictionary Views

For example, to see the status and characteristics of each plan, query DBA_RSRC_PLANS:

The column STATUS is PENDING if the plan has not yet been validated and successfully submitted. Otherwise, it is NULL. The column MANDATORY is YES if the plan cannot be dropped.

Monitoring Resource Manager

You can monitor Resource Manager using EM or dynamic performance views. From the EM home page, select the Server tab, then click on the Statistics link under the Resource Manager heading. You will see the page shown in Figure 12-31, where you can query CPU, I/O, waits, and queued sessions broken down by resource consumer group.

FIGURE 12-31     Using EM to retrieve Resource Manager statistics

Three dynamic performance views give you Resource Manager statistics:

V$RSRC_CONSUMER_GROUP CPU utilization by consumer group

V$SYSSTAT CPU usage for all sessions

V$SESSTAT CPU usage by session

CERTIFICATION SUMMARY

This chapter started with a discussion of resumable space allocation. Resumable space allocation can be a big time-saver. Jobs that run out of disk space after 95 percent completion will suspend and not terminate. If, within a specific time period, the DBA allocates or frees up more disk space, the long-running job will automatically resume and finish. Resumable space allocation is triggered for out of disk space conditions in permanent or temporary tablespaces as well as a user exceeding his or her quota.

Continuing the theme of moving or processing large amounts of data in the shortest possible time, transportable tablespaces were introduced. In a fraction of the time it would take to restore and recover a backup of your database to another database, you can export the metadata for one or more tablespaces, copy the tablespace(s) to the new database, then import the metadata to “plug-in” the tablespace. Only a couple of caveats apply to transportable tablespaces: (1) database compatibility level, and (2) platform endian formats. If the target platform has a different endian format, you must use RMAN either at the source database or at the target database to change the endian formats of the data by intelligently “flipping” pairs of bytes in the data blocks.

Next, you learned how to save disk space and potentially reduce I/O time for your table and index segments by using segment shrink. Segment shrink moves data rows as far left (closer to the beginning of the segment) as possible, thus reusing unused space in the segment. Optionally, segment shrink will also relocate the HWM of the segment, freeing up space for other objects in the tablespace. Before you perform a segment shrink, you need to know which segments need shrinking! As a result, you can run the Segment Advisor manually on segments you suspect are using space inefficiently, or you can use the results from Automatic Segment Advisor to identify segments or entire tablespaces that may benefit from a shrink operation. You can use either the EM interface or SQL commands to analyze and shrink segments.

Finally, a whirlwind, yet in-depth tour of using the Database Resource Manager to control resource allocation among the many different types of database users you typically have in your database was given. These users include OLTP users, DSS users, maintenance jobs, and system users. The different components of Resource Manager along with how they are interrelated were presented. The default resource plans may be sufficient for some environments, but eventually you’ll want to control how many resources a particular user or group is using in relation to all other users. This will become evident the first time a user runs an ad-hoc query that runs for 20 minutes while the web site is selling merchandise at a time such as the busiest shopping day of the year. Database Resource Manager provides efficiencies in consumer group switching and fine-grained resource management not available at the operating system level. The Resource Manager discussion was closed by presenting an in-depth exercise that created a plan, added users to two consumer groups, registered the consumer groups with the plan, and defined directives in the plan based on CPU usage and the level of parallelism.

TWO-MINUTE DRILL

Manage Resumable Space Allocation

Resumable space allocation suspends instead of terminating large database operations which require more disk space than is currently available.

Once a low space condition is addressed, the large database operation automatically picks up where it left off.

Conditions such as out of disk space or quota exceeded can trigger resumable space allocation.

There are four general categories of commands that can be resumable: (1) SELECT statements, (2) DML commands, (3) SQL*Loader operations, and (4) DDL statements that allocate disk space.

All DDL commands that allocate disk space for new or existing segments are resumable, such as CREATE TABLE, CREATE INDEX, and CREATE MATERIALIZED VIEW.

Resumable space allocation can be enabled at the instance level using the RESUMABLE_TIMEOUT initialization parameter or by an ALTER SESSION command.

Setting RESUMABLE_TIMEOUT to 0 disables resumable space allocation.

The default resumable timeout value is 7200 seconds (two hours).

To make it easy to identify your resumable statement in the data dictionary views DBA_RESUMABLE and USER_RESUMABLE, you can enable Resumable Space Allocation with the NAME parameter.

The DBA or a user can register a trigger for the AFTER SUSPEND system event which is fired immediately after a qualifying resumable statement suspends.

Describe the Concepts of Transportable Tablespaces and Databases

When transporting between platforms, both the source and destination platforms must be on Oracle’s list of supported platforms.

Oracle Database feature compatibility is controlled by the COMPATIBLE initialization parameter.

When you create a transportable tablespace set, Oracle determines the minimum compatibility level of the target database and stores this value in the metadata for the transportable tablespace set.

A conversion process must be peformed for data columns transported between platforms with different endian formats.

To determine the endian formats of all supported platforms, you can query the dynamic performance view V$TRANSPORTABLE_PLATFORM.

When transporting a tablespace, the source tablespace must be read-only during the copy process, and changed to read-write after import to the target database.

You use expdp, impdp, and the DBMS_FILE_TRANSFER PL/SQL package to copy metadata and the tablespace’s datafiles from one database to another.

Reclaim Wasted Space from Tables and Indexes by Using the Segment Shrink Functionality

Shrinking the segment makes the free space in the segment available to other segments in the tablespace.

Segment shrink has the potential to improve future DML operations on the segment because fewer blocks may need to be retrieved for the DML operation after the segment shrink.

Segment shrink is similar to online table redefinition in that space in a table is reclaimed, but can be performed in place without the additional space requirements of online table redefinition.

Segment shrink has two phases: compacting the data rows and moving the high water mark (HWM).

Moving the HWM is optional or can be delayed to reduce any impact on concurrent DML operations on the segment.

A shrink operation is executed internally as a series of INSERT and DELETE operations; no DML triggers are executed during a shrink.

Segment shrink cannot be used on clustered tables, tables with LONG columns, IOT mapping tables, tables with ROWID-based or on-commit materialized views, and tables containing function based indexes.

Oracle’s Segment Advisor, run either manually or on a schedule as part of the Automatic Segment Advisor job, can identify candidates for segment shrink.

Automatic Segment Advisor can recommend segments (tables or indexes) that are candidates for segment shrink.

You cannot specify what objects and tablespaces are analyzed by the Automatic Segment Advisor, you can change when it runs and how many resources it consumes when it runs.

The COMPACT clause of the ALTER TABLE . . . SHRINK SPACE command only performs the compaction step, and the HWM stays in place until you run the same ALTER TABLE command again without the COMPACT keyword.

Before you can shrink a segment, you must also enable row movement on the table or index.

To initiate Segment Advisor on a specific table or on an entire tablespace, you use the PL/SQL package DBMS_ADVISOR.

The findings from Segment Advisor are available in the DBA_ADVISOR_FINDINGS data dictionary view.

Understand the Database Resource Manager

Using the operating system for resource management is not desirable because context switching within the operating system between database processes can cause excessive and unnecessary overhead.

The three basic constructs of Resource Manager are resource consumer groups, resource plans (and subplans), and resource plan directives.

Resource consumer groups to identify groups of users or sessions that have similar resource needs.

A resource plan assigns various resources at specific percentages or relative priority to a resource group.

Within the resource plan are resource plan directives which associate consumer groups with a resource plan, and specify how the resources are divided among the consumer groups or subplans.

Resource Manager can allocate resources based on CPU usage, degree of parallelism, number of active sessions, undo space, CPU time limit, and idle time limit.

Resource Manager can automatically switch a session or process from one consumer group to another based on criteria such as using a certain amount of CPU time or undo space.

Create and Use Database Resource Manager Components

The PL/SQL package DBMS_RESOURCE_MANAGER has everything you need to view, create, maintain, and use Resource Manager components.

Oracle provides a default plan named DEFAULT_PLAN when you create a database.

A sub-plan is identical to a resource plan, except that it can further allocate resources within an existing plan.

Only one resource plan can be active at a time.

To find out which plan is active, query the initialization parameter RESOURCE_MANAGER_PLAN.

You can use the PL/SQL packages CREATE_PLAN, UPDATE_PLAN, and DELETE_PLAN to create, update, and delete resource plans.

To create a pending area, use the CREATE_PENDING_AREA procedure of the DBMS_RESOURCE_MANAGER package.

Use DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP to create a new consumer group.

To assign directives to a plan using PL/SQL, use the CREATE_PLAN_DIRECTIVE procedure of the DBMS_RESOURCE_MANAGER package.

To create a pending area for your resource manager session, use DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA().

To create a new Resource Manager plan, use DBMS_RESOURCE_MANAGER.CREATE_PLAN.

To create a consumer group, use DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP.

To create plan directives within a plan, use DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE, specifying the plan name, the group affected by the directive, along with CPU, parallelism, and undo space usage limits.

You must validate the pending area with DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA() before submitting the pending area with DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA().

The data dictionary view DBA_RSRC_PLAN shows you all plans in the database plus the status and the permanence of each plan.

The primary dynamic performance view for monitoring Resource Manager is V$RSRC_CONSUMER_GROUP.

SELF TEST

The following questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully, because there might be more than one correct answer. Choose all correct answers for each question.

Manage Resumable Space Allocation

1. Which of the following commands is not a candidate for resumable space allocation?

A. CREATE INDEX . . . ON . . .

B. SELECT . . . ORDER BY

C. $ sqlldr

D. CREATE TABLE . . . (COL1, COL2. . .);

E. DELETE FROM . . .

2. A user with the RESUMABLE privilege executes the following command:
SQL< alter session enable resumable;

The value for the initialization parameter RESUMABLE_TIMEOUT is 3600.

What is the timeout value for resumable statements?

A. The ALTER SESSION command fails because it does not specify a value for TIMEOUT

B. 7200

C. 3600

D. 10000

E. The ALTER SESSION command fails because it does not specify a value for NAME

Describe the Concepts of Transportable Tablespaces and Databases

3. You are running Oracle Database 11g with the COMPATIBLE initialization parameter set to 11.0.0. What is the minimal compatibility level for transporting a tablespace from a database on a different platform?

A. 8.0

B. 10.0

C. 9.0

D. 11.0

E. All of the above

4. When transporting a tablespace, what is the purpose of DBMS_TTS.TRANSPORT_SET_CHECK? (Choose the best answer)

A. It ensures that the COMPATIBILITY level is high enough for the transport operation

B. It compares the endian level for the source and target databases and runs RMAN to convert the datafiles before transportation

C. It validates that the metadata for the tablespace does not have any naming conflicts with the target database schemas

D. It checks for tablespace self-consistency

Reclaim Wasted Space from Tables and Indexes by Using the Segment Shrink Functionality

5. Which of the following commands will shrink space in a table or index segment and relocate the HWM?

A. alter table employees shrink space compact hwm;

B. alter table employees shrink space hwm;

C. alter table employees shrink space compact;

D. alter table employees shrink space;

E. alter table employees shrink space cascade;

6. Which of the following objects are not candidates for segment shrink? (Choose all that apply.)

A. Clustered tables

B. Tables with function-based indexes

C. Index-organized tables

D. Tables with fast-refreshable materialized views.

E. Tables with CLOB columns

Understand the Database Resource Manager

7. Identify the correct statement about Database Resource Manager components. (Choose the best answer)

A. Multiple resource plans can be active at once, but only one can be active for a specific user.

B. The resource consumer group OTHER_GROUPS is optional in a resource plan.

C. Resource consumer groups identify groups of users with similar resource needs, and a user can belong to more than one consumer group.

D. Resource consumer groups identify groups of users with similar resource needs, and a user can belong to only one consumer group.

Create and Use Database Resource Manager Components

8. Which of the following resources cannot be allocated by Resource Manager? (Choose all that apply.)

A. Network bandwidth

B. CPU usage

C. Degree of parallelism

D. Undo space usage

E. Temporary tablespace usage

9. You attempt to validate the Resource Manager pending area with DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(). However, you get this error message:

How can you fix this problem? (Choose the best answer.)

A. Create a new pending area, and add OTHER_GROUPS as the first consumer group in the plan OLTP_NIGHT.

B. The consumer group OTHER_GROUPS was mistakenly added to a sub-plan, therefore you must remove it from all sub-plans.

C. You must drop the consumer group OTHER_GROUPS before creating this plan.

D. Add the consumer group OTHER_GROUPS to the plan OLTP_NIGHT, and then revalidate.

10. What is the value of the STATUS column in the data dictionary view DBA_RSRC_PLANS for a plan that is in the pending area but has failed validation?

A. NULL

B. PENDING

C. UNKNOWN

D. INVALID

SELF TEST ANSWERS

Manage Resumable Space Allocation

1. D. A CREATE TABLE statement cannot cause a low disk space situation unless you use CREATE TABLE AS SELECT or the SYSTEM tablespace runs out of room in the data dictionary.

A, B, C, and E are incorrect. CREATE INDEX allocates space in a tablespace, and therefore it can trigger resumable space allocation. A SELECT statement can trigger resumable space allocation if it runs out of sort space in the TEMP tablespace for ORDER BY, GROUP BY, or DISTINCT clauses. SQL*Loader operations are resumable as long as you use the RESUMABLE parameter. Finally, a DELETE statement may cause a low space condition in the UNDO tablespace.

2. C. If you do not specify a value for the timeout with ALTER SESSION, it defaults to either 7200 seconds (two hours) or the value of the initialization parameter RESUMABLE_TIMEOUT (in this case 3600) if it is non-zero.

A, B, D and E are wrong. The TIMEOUT and NAME keywords are optional in ALTER SESSION ENABLE RESUMABLE. The default of 7200 only applies if RESUMABLE_TIMEOUT is not set or is zero.

Describe the Concepts of Transportable Tablespaces and Databases

3. B. If the source and target databases are on different platforms, both the source and target must have a compatibility level of at least 10.0.

A, C, D, and E are wrong. For transporting between identical platforms, you only need COMPATIBLE=8.0. For transporting between databases with different block sizes, you only need COMPATIBLE=9.0.

4. D. DBMS_TTS.TRANSPORT_SET_CHECK checks to ensure that there are no objects in the tablespace to be transported that have dependencies on objects in other tablespaces in the source database.

A, B, and C are not valid uses for DBMS_TTS.TRANSPORT_SET_CHECK.

Reclaim Wasted Space from Tables and Indexes by Using the Segment Shrink Functionality

5. D. SHRINK SPACE both compacts the data and moves the HWM. While the HWM is being moved, DML operations on the table are blocked.

A, B, and E are syntactically incorrect. C is wrong because COMPACT only performs the shrink operation but does not move the HWM after shrinking the segment.

6. A and B. Clustered tables and tables with function-based indexes are not shrinkable. In addition, you cannot shrink tables with LONG columns, IOT mapping tables, or tables with on-commit or ROWID-based materialized views

C, D, and E are wrong. In addition to IOTs and tables with fast-refreshable materialized views, you can shrink partitioned tables, sub-partitioned tables, and of course heap-based tables (the default table type).

Understand the Database Resource Manager

7. C. Users can belong to more than one consumer group. Resource Manager uses the plan directive mapping the most restrictive consumer group within the plan.

A is wrong because only one resource plan can be active at any given time. B is wrong because OTHER_GROUPS must be specified at the top level of a plan to allocate resources for any user that is not a member of other consumer groups in the plan. D is wrong because users can belong to more than one consumer group.

Create and Use Database Resource Manager Components

8. A and E. The Database Resource Manager cannot allocate or control network bandwidth or temporary tablespace usage.

B, C, and D are all resources under the control of Resource Manager.

9. D. You can add OTHER_GROUPS to the resource plan, and immediately retry the validation step.

A is wrong because you do not need to create a new pending area nor do you have to add OTHER_GROUPS first. B is wrong because OTHER_GROUPS, as well as any consumer group, can belong to any plan or sub-plan. C is wrong because you cannot drop OTHER_GROUPS; it is required for all plans whose groups do not cover all users in the database.

10. B. Until the plan has been successfully validated and submitted, the STATUS column contains the value PENDING.

A, C, and D are incorrect. The only valid values for the STATUS column are NULL and PENDING.