Lesson 8

The management of resources and tablespaces is an important first step in controlling your database.
After completing this module you should be able to:

1. Describe the purpose of tablespace management
2. Create locally managed tablespaces
3. Create transportable tablespaces
4. Describe new features for READ ONLY tablespaces
5. Identify the enhancements to the Database Resource Manager
6. Use the Database Resource Manager to limit resource usage

The Oracle Database Resource Manager has a new procedure that allows you to run IO calibration tests on your database and review the results of that test. When run, the dbms_resource_manager.calibrate_IO procedure will generate a workload across all nodes of the cluster. The procedure takes two input parameters, and returns three values.

The "DBMS_RESOURCE_MANAGER.CALIBRATE_IO" procedure is available in Oracle 19c as part of the Oracle Database Resource Manager feature. It is used to run I/O calibration tests to assess the I/O capabilities of the storage subsystem. This is particularly useful for fine-tuning database performance, especially for databases that experience heavy I/O workloads.
Description of the Process: The CALIBRATE_IO procedure generates a synthetic I/O workload on the database's storage system and measures key performance metrics such as maximum IOPS (Input/Output Operations Per Second), throughput, and latency.
Input Parameters: The procedure takes two mandatory input parameters:

1. NUM_DISKS: This parameter specifies the number of disks (or storage devices) used in the calibration.
2. MAX_LATENCY: This parameter specifies the maximum tolerable latency for an I/O operation, in milliseconds. This is the latency threshold beyond which the storage performance is considered suboptimal.

Return Values: When the procedure is executed, it returns three key metrics:

1. MAX_IOPS: The maximum number of I/O operations per second that the system can handle.
2. MBPS: The maximum throughput, measured in megabytes per second (MB/s), that the storage system can achieve.
3. LATENCY: The average latency in milliseconds for I/O operations under the maximum workload.

Example of Usage: Here is a basic example of how you can run the CALIBRATE_IO procedure:

DECLARE
  l_iops     PLS_INTEGER;
  l_mbps     PLS_INTEGER;
  l_latency  PLS_INTEGER;
BEGIN
  DBMS_RESOURCE_MANAGER.CALIBRATE_IO (
    num_disks   => 4,             -- Number of disks in the system
    max_latency => 20,            -- Maximum acceptable latency (in ms)
    iops        => l_iops,        -- Output: Maximum IOPS
    mbps        => l_mbps,        -- Output: Maximum MBPS
    latency     => l_latency      -- Output: Latency in milliseconds
  );
  DBMS_OUTPUT.PUT_LINE('Max IOPS: ' || l_iops);
  DBMS_OUTPUT.PUT_LINE('Max MBPS: ' || l_mbps);
  DBMS_OUTPUT.PUT_LINE('Latency: ' || l_latency || ' ms');
END;

Restrictions:

• The procedure can only be executed if the database is mounted and open in read/write mode.
• It should be executed during periods of low database activity to avoid interference from ongoing transactions.
• It requires that the database be running in Oracle Real Application Clusters (RAC) if executed on a multi-node cluster.

This feature is highly valuable for database administrators looking to assess and optimize the performance of their Oracle database storage.

The following terms were defined in this module.

1. Locally Managed Tablespace: a tablespace that manages its own storage information.
2. Transportable Tablespace: a tablespace that can be copied (cloned) or moved (transported) from one Oracle database to another.
3. Rowid: a hidden column in every row that contains the physical location of the row. A rowid never changes as long as the row exists.
4. Restricted Rowid: a rowid in the format used prior to Oracle8.
5. Extended Rowid: the Oracle format of a rowid. It contains a tablespace-relative address.
6. Logical Rowid: a new form of rowid that uses the row's primary key values rather than its physical location to locate the row.
7. UROWID: the universal rowid is a new datatype that can reference a traditional rowid, a logical rowid, and a rowid from a non-Oracle table.
8. Consumer Group: a set of users defined within the Database Resource Manager.

The next module looks into improvements in SQL*Loader and table management tools and techniques.