Managing Storage   « Prev  Next»

Lesson 3 New features for tablespace and resource management
Objective Understand Oracle tablespace and resource management.

Tablespace and Resource Management in Oracle

New types of Tablespaces

The tablespace features give you greater flexibility in controlling the physical location and availability of specific portions of the database. Tablespace improvements allow faster and easier backups, better capabilities to change the status of tablespaces, and more choices for tablespace management.

Database Administrators

Each database requires at least one database administrator (DBA). An Oracle Database system can be large and can have many users. Therefore, database administration is sometimes not a one-person job, but a job for a group of DBAs who share responsibility.A database administrator's responsibilities can include the following tasks:
  1. Installing and upgrading the Oracle Database server and application tools
  2. Allocating system storage and planning future storage requirements for the database system
  3. Creating primary database storage structures (tablespaces) after application developers have designed an application
  4. Creating primary objects (tables, views, indexes) once application developers have designed an application
  5. Modifying the database structure, as necessary, from information given by application developers
  6. Enrolling users and maintaining system security
  7. Ensuring compliance with Oracle license agreements
  8. Controlling and monitoring user access to the database
  9. Monitoring and optimizing the performance of the database
  10. Planning for backup and recovery of database information
  11. Maintaining archived data on tape
  12. Backing up and restoring the database
  13. Contacting Oracle for technical support
Look at the slide show below to see an illustration of the new tablespace enhancements now available with Oracle.

1) To begin exploring new tablespace types, let us start with an example involving two databases, A and B.
1) To begin exploring new tablespace types, let us start with an example involving two databases, A and B. Each one has two tablespaces, shown by the colored rectangles (T-1 through T-4) inside each database. Tablespace T-1 and T-2 are defined as transportable tablespaces. (Transporting tablespaces is an alternative way of moving or copying data from one database to another).

2) This image shows the same two database after the two transportable tablespaces have been transported.
2) This image shows the same two database after the two transportable tablespaces have been transported. Tablespace T-1 was transported from Database A to Database B. Tablespace T-2 was cloned (copied) and then transported from Database A to Database B. Transporting a tablespace is much faster than exporting or replicating it. Transporting moves the associated data files and then builds the tablespace meta-data in the target database. It preserves indexes intact, as long as they are stored within the tablespace being transported.

3) Here is the second example. Database A contains two tablespaces, T-1 and T-2. User A has an active transaction using tablepsace T-1.
3) Here is the second example. Database A contains two tablespaces, T-1 and T-2. User A has an active transaction using tablespace T-1. User B has an active transaction using tablespace T-2. You wish to modify tablespace T-1 to read only status so that you can perform a hot backup of the tablespace. Prior to Oracle8i you would have to wait for all outstanding transactions within the database to complete before this action could be completed.

4) With Oracle 8i, you can perform this change to READ ONLY status as soon as there are no outstanding transactions in the target tablespace.
4) With Oracle 8i, you can perform this change to READ ONLY status as soon as there are no outstanding transactions in the target tablespace. This enhancement makes it easier and faster to change a tablespace's status from updateable to read-only.

5) In this third and final example, Database A contains the same two tablespaces, T-1 and T-2.
5) In this third and final example, Database A contains the same two tablespaces, T-1 and T-2. The blue rectangle marked DD represents the database's data dictionary tables. Prior to Oracle 8i, inserting data into a table required use of the data dictionary to find available space in the tablespace, as shown in the steps listed here.

6) Oracle 8i has enhanced tablespace management to allow for locally managed tablespaces.
6) Oracle has enhanced tablespace management to allow for locally managed tablespaces. A locally managed tablespace does not use the data dictionary to track its open and used space. Instead, the tablespace itself tracks and maintains its usage of space. This means that an insert into the tablespace requires fewer steps. In addition, there is less fragmentation and wasted space in the locally managed tablespace.

Tablespace Enhancements

Enhancements to the Resource Manager

Question: Does the Database Resource Manager which was introduced in Oracle 8i still exist for the modern version of Oracle database?
Yes, as of my knowledge cutoff in September 2021, the Database Resource Manager (DBRM) feature continues to be a fundamental part of Oracle Database in its modern iterations. First introduced in Oracle8i, DBRM has been progressively enhanced in subsequent versions to offer more complex and sophisticated functionality for managing and optimizing database resources. DBRM provides a comprehensive framework to efficiently manage competing resource demands and distribute system resources. The overarching objective is to maximize resource utilization while ensuring that resource allocation aligns with business objectives. Key elements of the DBRM include:
  1. Resource Consumer Groups: These are user sessions grouped based on resource requirements. User sessions are classified into these groups based on various attributes, such as username or the type of application.
  2. Resource Plans: These plans define the policies and allocation of resources among different consumer groups. The plan outlines how resources are distributed and specifies directives for each consumer group, including the allocation percentages for CPU resources.
  3. Plan Directives: These directives form part of a resource plan and determine how resources are allocated to a consumer group. Directives are used to specify the percentage of resources assigned to a consumer group when the system is in high demand.

n modern Oracle Database versions, DBRM offers features like CPU method, which controls CPU allocation, Active Session Pool mechanism, which limits the number of concurrently active sessions, and I/O calibration to assess the performance of the I/O subsystem, among other functionalities.
These DBRM features allow administrators to prioritize the distribution of system resources to those activities that are of greatest importance, ensuring that critical tasks receive the necessary resources for optimal operation.
In addition to these features for tablespace management, the tool called the Database Resource Manager has been enhanced to allow the database administrator to control operating system resources. For example, the DBA can limit a user so the user is not allowed to use more than 40 percent of the CPU at any time. This prevents one user from "hogging" the CPU and causing other users to experience long waits for services from the database. We will look into these features and improvements in a later module of this course.

About the Elements of Resource Manager

The elements of the Resource Manager include resource consumer groups, resource plans, and resource plan directives.
Resource Manager
Resource Manager

You use the DBMS_RESOURCE_MANAGER PL/SQL package to create and maintain these elements. The elements are stored in tables in the data dictionary. You can view information about them with data dictionary views. The next lesson describes storage-handling and space-handling efficiencies that have been added in Oracle.