Concepts of and sample questions on space usage

Oracle Database 10g provides a variety of features for managing the space usage of a tablespace. These features include the monitoring of tablespace usage, the ability to suspend transactions that require more space and successfully resume them when the space is available, and a database advisor that lets you determine whether an object has space available for reclamation.

This column presents sample questions on managing tablespace usage. The questions are of the type you may encounter when taking the Oracle Database 10g Administration Workshop II exam. The Oracle Database 10g Administration Workshop II exam is one of the two exams you must take to achieve the Oracle Certified Professional (OCP) level of certification. Note that the sample question format has been adjusted for presentation in this column.

Monitoring Tablespace Space Usage

Oracle Database 10g provides proactive help for managing the disk space usage of a tablespace. It alerts you when tablespaces run low on available space. Two space-used alert thresholds are defined by default: warning and critical. The warning threshold is the limit at which space starts to run low. The critical threshold is a serious limit that warrants your immediate attention. The database issues alerts at both thresholds.

You can modify the default threshold values and also disable the space-used threshold alerts for a tablespace.

You do not want to use the default threshold values for space used for the EXAMPLE tablespace. Which package would you use to explicitly set the space-used threshold values for a tablespace?

A. DBMS_SERVICE
B. DBMS_SERVER_ALERT
C. DBMS_STORAGE_MAP
D. DBMS_TRACE

The correct answer is B. You can use the SET_THRESHOLD procedure in the DBMS_SERVER_ALERT package to set the space-used threshold values for a particular tablespace. Alternatively, you can use Oracle Enterprise Manager 10g to set the space-used threshold values.

The USERS_TBS tablespace does not include autoextensible datafiles. Identify the situation in which server-managed threshold alerts for tablespace space usage will not be generated for the USERS_TBS tablespace.

A. If USERS_TBS is a bigfile tablespace
B. If USERS_TBS is the default permanent tablespace
C. If USERS_TBS is a temporary tablespace
D. If USERS_TBS is a dictionary-managed tablespace
E. If USERS_TBS is an UNDO tablespace

The correct answer is D. Server-managed threshold alerts for tablespace space usage are supported only for locally managed tablespaces, not for dictionary-managed tablespaces. Answers A, B, and C are incorrect because server-managed threshold alerts are supported for bigfile tablespaces, default permanent tablespaces, and temporary tablespaces. Answer E is incorrect because server-managed threshold alerts are supported for UNDO tablespaces.

You created a locally managed tablespace by using the following SQL statement:

CREATE TABLESPACE TEST_TBS

DATAFILE 'test.dat' SIZE 1M 

AUTOEXTEND ON MAXSIZE 100M;

You set the warning threshold limit to 60 percent for the TEST_TBS tablespace. Which statement is correct regarding the server-managed tablespace space usage threshold alerts in this scenario?

A. A warning alert will be generated when 60K of space is used.
B. No alerts will be generated, because it is not a bigfile tablespace.
C. No alerts will be generated, because the tablespace includes an autoextensible datafile.
D. A warning alert will be generated when 60MB of space is used.
E. A warning alert will be generated when 60MB of space is left as free space.

The correct answer is D. Answer A is incorrect because for tablespaces with autoextensible files, the thresholds are computed according to the maximum file size you specified or the maximum file size of the operating system. Answers B and C are incorrect because the alerts are generated for smallfile tablespaces as well as for tablespaces with autoextensible datafiles. Answer E is incorrect because the alerts are based on the space used and not on the free space left in a tablespace.

Resumable Space Allocation

In the event of space allocation failures, rather than returning the error to the user and stopping the operation, the transaction can be temporarily suspended and corrective action taken. After the error condition is corrected, the suspended operation automatically resumes. This feature is called resumable space allocation. The affected statements are called resumable statements.

Which two statements are correct regarding the resumable space allocation feature?

A. Resumable space allocation can be enabled only at the system level and not at the session level.
B. When a resumable statement is suspended, an error is reported in the alert log.
C. Every resumable operation has a "time out" period associated with it.
D. A resumable statement can be suspended and resumed only once during execution.

The correct answers are B and C. When the execution of a resumable statement is suspended, the system issues a Resumable Session Suspended alert. A suspended operation is automatically aborted if the error condition is not fixed within the time-out period. By default, the time-out period is two hours. Answer A is incorrect because you can enable resumable space allocation either at the system level, by setting the RESUMABLE_TIMEOUT initialization parameter to a nonzero value, or at the session level, by issuing the ALTER SESSION ENABLE RESUMABLE statement. Answer D is incorrect because a resumable statement can be suspended and resumed multiple times during execution.

Which statements are resumable? (Choose all that apply.)

A. INSERT INTO ... SELECT from external tables statement
B. CREATE TABLE ... AS SELECT statement
C. CREATE TABLESPACE statement
D. SELECT statement
E. CREATE USER statement

The correct answers are A, B, and D. DML statements, including INSERT INTO ... SELECT from external tables, are resumable. The interface used to execute the DML statements does not matter. DDL statements, including CREATE TABLE ... AS SELECT, are resumable. SELECT statements that run out of temporary space are also candidates for resumable execution.

Under which three error conditions can the resumable space allocation feature be used?

A. The user has exceeded his or her assigned space quota in the tablespace.
B. The number of extents in a table or index equals the number of maximum extents defined on the object.
C. The operation cannot acquire any more extents for an index in a tablespace.
D. The user application tries to access a table for which the user does not have necessary privileges.
E. The user-specified datafile size exceeded the maximum operating system file size.

The correct answers are A, B, and C. A resumable statement can be suspended under any of these conditions: space quota exceeded, maximum extents reached, and out of space.

Database Advisors

Oracle Database 10g provides a set of component advisors that provide useful feedback about resource utilization and performance. These advisors enable you to analyze the performance of your database, identify potential problems and bottlenecks, and tune the various components of your database.

DML operations on the objects within the USER_TBS tablespace have created pockets of empty space that individually are not big enough to be reused. This is leading to a large amount of wasted space in the USER_TBS tablespace. You plan to reclaim the wasted space through a shrink operation. Which advisor would you use to determine the objects in the USER_TBS tablespace that contain wasted space and are good candidates for the shrink operation?

A. SQL Tuning Advisor
B. Segment Advisor
C. Undo Advisor
D. SQL Access Advisor

The correct answer is B. You can use the Segment Advisor to determine whether an object has space available for reclamation on the basis of the level of space fragmentation within the object. The Segment Advisor can generate advice at three levels: object level (such as table), tablespace level, and segment level.

You can use the SQL Tuning Advisor for tuning SQL statements. The Undo Advisor improves transaction management, especially for automatic-undo management. The SQL Access Advisor provides recommendations for creating indexes and materialized views for a given workload.

Conclusion

You can use space-used threshold values to proactively manage the space in tablespaces. Space usage exceeding the threshold values raises alerts, allowing you to take action to ensure that there is space available. Resumable space allocation lets you suspend a statement temporarily, so that when more space is available, the transaction can resume and complete. The Segment Advisor—one of many database advisors—lets you identify unused space so that you can reclaim it.

REFERENCES

Efficient Space Management By Aradhana Puri , Oracle Corporation

Using Oracle Database Resource Manager facilitates meeting SLAs

A big challenge DBAs face is aligning database resources with the real-world demands of daily business and realigning those resources as demands change—especially in terms of meeting SLAs (service-level agreements) with internal and external customers. Oracle Database Resource Manager, introduced in Oracle8i and improved with each subsequent release, provides new capabilities that make using it a viable approach to managing a fundamental system resource, the CPU, and how the CPU allocates its cycles to the processes running on the system.

Note that the concurrent use of Database Resource Manager with OS resource managers is supported only for utilities that use technologies such as processor sets or domains to isolate each database instance into its own "virtual partition." For more information, see MetaLink or the Oracle Database Administrator's Guide.

Overview of Oracle Database Resource Manager

Oracle Database Resource Manager takes a database-instance-centric approach to managing resources, letting DBAs apply business-operational policies to the system so that resources will be allocated to various user communities and applications in the context of the entire system, adjusting the balance according to user-defined rules.

The rules, or directives, are defined in a resource plan and apply to specific groups of users—consumer groups—that you also define. You can define many different resource plans—one for daytime operations, one for nighttime operations, for example—but only one can be active per database instance at any moment. (By default, Oracle Database Resource Manager is not enabled; it remains disabled until you select an active resource plan for your system. Also note that because Database Resource Manager works at the instance level, in an Oracle Real Application Clusters environment, you must enable it on each instance in the cluster.)

A resource plan can encompass numerous consumer groups and subplans, letting you finely control resource allocation. You can create and manage all the elements of Oracle Database Resource Manager—resource plans, resource consumer groups, and directives—with Oracle Enterprise Manager Database Control (or Grid Control, if you're running Oracle Real Application Clusters) or the DBMS_RESOURCE_MANAGER built-in PL/SQL package.

Getting Started with a Resource Plan

You'll find the Resource Manager section on the Oracle Enterprise Manager Database Control Administration page (click on the Administration link on the Oracle Enterprise Manager Database Control home page to get to the Administration page. The Resource Manager menu is adjacent to the Scheduler). Starting from this page, you can perform the administration tasks for setting up the necessary elements and monitor how an active plan is allocating resources at any moment (on the Resource Monitors page).

Before creating a new plan, you must create a few Consumer Resource Groups (click on Resource Consumer Groups on the menu under Resource Manager on the Administration page). The Resource Consumer Group page lists any existing groups (some are built-in defaults), and you can click on the Create button to create a new group and give it a name and a description, upon which you'll see all the user (schema) names in your system displayed. Until you move users into groups you create, most users in the system are members of the DEFAULT_CONSUMER_GROUP. (Another built-in group, SYS_GROUP, contains the SYSTEM user).

The plan displays the list of consumer groups in our system—CUSTOMERS and BATCH_APPS—and an Oracle-provided consumer group (OTHER_GROUPS). The CUSTOMERS consumer group comprises user accounts with strict SLA requirements—in this case, user accounts associated with purchasing applications. The BATCH_APPS consumer group comprises applications that print invoices and statements. Typically these run at night, but occasionally they are run during the day, time permitting. OTHER_GROUPS is mandatory on every resource plan and is not editable—it exists to allocate resources to processes, such as background processes, that are not accounted for in any other group.

How the Plan Affects Utilization

Consumer groups share system resources, using percentages specified in the plan. CUSTOMERS have been allocated 85 percent of system resources and BATCH_APPS 10 percent; OTHER_GROUPS has been allocated 5 percent. The plan is a simple single-level plan, with all allocations at level 1. You can, however, devise multilevel plans in which level-2 consumer groups (and groups on subsequent levels) receive their allocations only after level-1 consumer groups have received their full specified allocation of resources. (See the Oracle Database Administrator's Guide for details about constructing such plans.)

You can define plans to account for a variety of scenarios, ensuring that batch job processing won't interfere with customers trying to buy products, for example, and that distinct requirements can always be met.

By default, consumer groups share resources as percentages (you can also define simple ratios). However, if CUSTOMERS are ever the only type of consumer using the system, they will get 100 percent of the CPU, not just 85 percent. That's because Oracle Database Resource Manager is designed to maximize Oracle Database throughput rather than arbitrarily enforce allocations; that is, until the CPU or multiple CPUs are fully taxed—reach 100 percent utilization—the allocations don't come into play.

More About Resource Plan Directives

The plan is fairly straightforward, and we didn't change any directives from their default settings (null values). However, using directives lets us build even-more-powerful plans that manage resources proactively and automatically. For example, the max_idle_time directive (whose null default value means unlimited) can be set for a consumer group to ensure that sessions aren't idle beyond the limit set for that group, freeing resources for serving other consumer groups in the plan (or other sessions in the consumer group).

You set plan directives by clicking on the appropriate link on the Resource Plan Detail page; clicking on one displays the appropriate page of options available, with consumer groups and subplans.

Dynamically Switching Sessions into Groups

Several directives available on the Group Switching page (or available through the API) can be combined in various ways in a resource plan to automate your operations.

In Oracle Database 10g, the switching capability has been enhanced, with the switch_time_in_call and switch_time directives. The former sets how long, in seconds, a session can execute before switching to a new consumer group. At the end of the call, the session's consumer group is restored to the previous one. You can implement these directives in a plan with the DBMS_RESOURCE_MANAGER API. Listing 1 shows a slightly modified version of the resource plan we created with Oracle Enterprise Manager, implementing the switch_time_in_call directive for an additional group, WEB_CUSTOMERS.

The switch_time_in_call directive is especially useful in multitier applications in which a middle-tier application server has implemented session pooling. With switch_time_in_call, one client's resource consumption won't affect a future client executed in the same session.

Before setting up the directives for switching in a resource plan, grant the consumer group the privilege to switch into the target consumer group. (Actually, you can set up the consumer group to switch into, but this is ineffective until you grant the proper permission.) To grant permission to switch consumer groups, use the DBMS_ RESOURCE_MANAGER_PRIVS package, as in this example:

begin

dbms_resource_manager_privs

.grant_switch_consumer_group 

('WEB_CUSTOMERS', 'CUSTOMERS', true);

end;

In Listing 1, members of the consumer group WEB_CUSTOMERS are switched to the CUSTOMERS consumer group if the request they're making will exceed five seconds—they're moved into the higher-priority CUSTOMERS group until the request is processed. When the call completes, the user is moved back into the WEB_CUSTOMERS group.

Conclusion

Operating-system-level resource managers do a good job in many circumstances but often don't provide effective control of a system focused primarily on running an Oracle database instance. With new capabilities, such as the ability to switch automatically into and out of groups, Oracle Database Resource Manager is a tool to seriously consider using, especially for running a highly loaded system with a variety of applications and for meeting demanding SLAs.

REFERENCES

Database Resource Manager By Kimberly Floss, Oracle Corporation

How to protect yourself from RAID-related Unrecoverable Read Errors (UREs)

If you are ever rebuilding a RAID system, Unrecoverable Read Error (URE) is one term you don't want to learn about the hard way. As the name implies, a URE makes for a really bad day, as it can stop a RAID rebuild in its track, essentially making the entire RAID volume unusable.

I won't go into a lot of detail about the "why" behind what causes a URE because many other very smart people have already done a good job of explaining it. (Admittedly, some of the warnings might be sensationalist, and there seems to be some confusion on terminology, but do your own math to see if you might have a serious problem.) What I will do is provide you with tips on how to make sure that you don't fall victim to these errors.

1. Don't use RAID 5 if you plan to use large non-enterprise-grade SATA disks.

When it comes to reliability, enterprise grade disks are generally at least one order of magnitude more reliable than their non-enterprise counterparts. If you believe what has been written about UREs, as the size of disks increases and as more disks are added to RAID 5 arrays, the likelihood of total data loss across the entire RAID volume begins to get into dangerous territory.

If you're trying to build a 14 disk RAID array using 1.5TB or 2TB SATA disks you bought at Best Buy, consider using RAID 6 or RAID 10 instead of RAID 5. RAID 6's dual parity mechanism provides additional cushion in the event of drive failures (at the cost of performance), while RAID 10 setups can lose up to half the disks before incurring data loss.

If you're really intent on creating a large array and want minimal RAID overhead, you could even consider RAID 50, which is a RAID 0 of RAID 5 arrays. If you were going to use RAID 50 for that 14 disk array, you'd create three four-disk RAID 5 arrays and do RAID 0 across those and use the remaining two disks as spares.

2. Don't use cheap hardware if you need to push limits.

Yes, budgets are tight, but don't risk your data. Buy enterprise-grade disks such as SAS, fibre channel disks, or at the very least high-grade SATA disks with a higher mean time between failure rating.

If you're using disks that have a bit error rate of 1 in 10^14, you're using a cheap disk. Enterprise-class disks will have a bit error rate of 1 in 10^15 or, better yet, 1 in 10^16, which makes this class of disk much less susceptible to UREs.

Note: I'm not necessarily saying that you should buy SAS disks rather than SATA disks, but do look for disks with a reasonable bit error rate.

3. If you need a lot of disks for spindle performance, use smaller capacities.

There are times when quantity outweighs capacity. For example, when your disk system is constrained by IOPS, throwing more disk spindles at the solution can fix the problem, so in some cases, you might want to have a bunch of disks in a single RAID array. If you do, use smaller capacity disks.

One of the main concerns around the possibility of encountering a URE lies in the sheer amount of time it takes to rebuild after the loss of a massive disk, such as a 1TB or 2TB disk. When disk sizes were smaller, the window of opportunity for data loss was a lot smaller since smaller disks rebuild more quickly. As disk sizes continue to grow, this window of opportunity for data loss grows, and the problem will become more serious unless manufacturers can manage to produce drives with lower bit error rates.

4. Backups, backups, backups

Someone actually asked me once why we still do backups when we use RAID on all of our servers. No matter how reliable disks get, and no matter how far away you are from even the potential of a URE, nothing replaces reliable backups. If you ever run into a URE, you'll need reliable backups.

5. Be patient

Storage needs constantly increase, and RAID 6 is simply not the answer for everyone that becomes uncomfortable with RAID 5. We have a market need and someone, somewhere will come along and fill that need with a new way to provide robust redundancy. We've also seen newer data protection methods in use in products, such as Windows Home Server and Drobo's line of devices, and it's only a matter of time before similar methods - or completely new methods - become available for enterprise gear.

REFERENCES

How to protect yourself from RAID-related Unrecoverable Read Errors (UREs), by Scott Lowe, November 16th, 2009 , www.Techrepublic.com