Recreate Lob table or big table in PARALLEL

Recreating LOB/Big Tables in Parallel

This section provides steps to recreate a large table, potentially containing LOBs, in parallel for improved performance. This is useful for reorganizing data, applying new storage attributes, or simply moving data efficiently.

Source Table: APP_USER.APP_TABLE1 Target Table: APP_USER.APP_TABLE2

Step 1: Create a Sequence (for Logging Timing)

Create a sequence to generate unique IDs for logging the start and end times of the operation.

CREATE SEQUENCE Myuser.T_SQ
  START WITH 1
  INCREMENT BY 1
  NOCACHE;

-- Create a table to log the job times (if it doesn't exist)
CREATE TABLE Myuser.job_time (
    t_id    NUMBER,
    t_name  VARCHAR2(100),
    t_type  VARCHAR2(10),
    t_time  DATE
);

Step 2: Create a Procedure

Create a PL/SQL procedure that performs the parallel insert operation and logs its timing.

CREATE OR REPLACE PROCEDURE Myuser.my_proc AS
  v_seq NUMBER;
BEGIN
  -- Enable parallel processing for the current session
  EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_FORCE_LOCAL=TRUE';
  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

  -- Get the next sequence value for logging
  SELECT Myuser.T_SQ.NEXTVAL INTO v_seq FROM dual;

  -- Log the start time of the operation
  INSERT INTO Myuser.job_time (t_id, t_name, t_type, t_time) VALUES (v_seq, 'APP_TABLE1', 'START', SYSDATE);
  COMMIT;

  -- Perform the parallel insert from source to target table
  -- APPEND hint for direct path insert, PARALLEL hint for parallel execution
  INSERT /*+ APPEND PARALLEL(A,60) */ INTO APP_USER.APP_TABLE2 A
  SELECT /*+ PARALLEL(B,60) */ * FROM APP_USER.APP_TABLE1 B;
  COMMIT;

  -- Log the end time of the operation
  INSERT INTO Myuser.job_time (t_id, t_name, t_type, t_time) VALUES (v_seq, 'APP_TABLE1', 'END', SYSDATE);
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    -- Log any errors
    INSERT INTO Myuser.job_time (t_id, t_name, t_type, t_time) VALUES (v_seq, 'APP_TABLE1', 'ERROR', SYSDATE);
    COMMIT;
    RAISE; -- Re-raise the exception after logging
END;
/

Note:

• PARALLEL(A,60) and PARALLEL(B,60) hints suggest using 60 parallel slaves. Adjust this number based on your system's CPU cores and I/O capabilities.

• APPEND hint performs a direct-path insert, which is faster for large data volumes as it bypasses the buffer cache.

• Ensure that APP_USER.APP_TABLE2 is already created with the desired structure (including LOB segments if APP_TABLE1 has them) and any necessary indexes or constraints are handled separately.

Step 3: Execute Procedure

Execute the procedure to start the parallel table recreation process.

EXEC Myuser.my_proc;

After execution, you can query Myuser.job_time to check the start and end times of the operation.

This comprehensive overview should provide a solid understanding of Oracle's Multitenant Architecture and its evolution across recent database releases.

Parallel DDL and DML (create table as select)

Oracle Parallel DDL and DML Operations

Oracle Database provides powerful capabilities for parallelizing both Data Definition Language (DDL) and Data Manipulation Language (DML) operations. This can significantly reduce the execution time for large operations such as creating tables from existing data, or inserting large volumes of data.

Parallel DDL: CREATE TABLE AS SELECT

Parallel DDL allows the creation of database objects (like tables, indexes) to be performed by multiple parallel execution servers. This is particularly beneficial when creating a new table based on a SELECT statement from a large existing table.

Example: Creating a new table APP_SCHEMA2.APP_TABLE2 from APP_SCHEMA1.APP_TABLE1 in parallel.

CREATE TABLE APP_SCHEMA2.APP_TABLE2 PARALLEL 15 AS
SELECT /*+ PARALLEL(A,15) */ *
FROM APP_SCHEMA1.APP_TABLE1 A;

Explanation:

• PARALLEL 15 (in CREATE TABLE clause): This specifies the default degree of parallelism (DOP) for the newly created table APP_SCHEMA2.APP_TABLE2. Any subsequent DML or DDL on this table might use this DOP if parallel execution is enabled.

• /*+ PARALLEL(A,15) */ (hint in SELECT clause): This hint explicitly tells the optimizer to use 15 parallel execution servers for the SELECT operation on APP_SCHEMA1.APP_TABLE1. The CREATE TABLE AS SELECT operation will then leverage this parallelism to read data and populate the new table concurrently.

Benefits: Faster table creation, especially for very large tables, by distributing the work among multiple processes.

Parallel DML: INSERT Operations

Parallel DML enables INSERT, UPDATE, DELETE, and MERGE statements to be executed by multiple parallel execution servers. For INSERT statements, this is most effective with direct-path inserts.

Prerequisite: Enable parallel DML for the session.

ALTER SESSION ENABLE PARALLEL DML;

Example: Inserting data from APP_USER1.APP_TABLE1 into APP_USER2.APP_TABLE2 using parallel DML.

INSERT /*+ APPEND PARALLEL(A,60) */ INTO APP_USER2.APP_TABLE2 A
SELECT /*+ PARALLEL(B,60) */ *
FROM APP_USER1.APP_TABLE1 B;

Explanation:

• ALTER SESSION ENABLE PARALLEL DML;: This statement is mandatory to enable parallel DML for the current session. Without it, the parallel hints in the DML statement will be ignored.

• /*+ APPEND */: This hint instructs Oracle to perform a direct-path insert. Direct-path inserts write data directly to the datafiles, bypassing the buffer cache, which is significantly faster for bulk data loading. It also acquires an exclusive table lock during the operation.

• /*+ PARALLEL(A,60) */ (hint in INSERT clause): This hint specifies that the INSERT operation on APP_USER2.APP_TABLE2 should use 60 parallel execution servers.

• /*+ PARALLEL(B,60) */ (hint in SELECT clause): This hint specifies that the SELECT operation on APP_USER1.APP_TABLE1 should also use 60 parallel execution servers.

Benefits: Dramatically faster data insertion for large datasets by leveraging multiple CPUs and I/O channels.

Important Considerations

• Resource Consumption: Parallel operations consume more CPU, memory, and I/O resources. Ensure your system has sufficient capacity.

• Table Locking: Direct-path inserts (APPEND hint) acquire an exclusive lock on the target table, preventing other DML operations on that table until the insert completes.

• Undo Generation: Direct-path inserts generate minimal undo, which is another reason for their speed.

• Degree of Parallelism (DOP): The optimal DOP depends on your hardware (number of CPU cores, I/O bandwidth) and the nature of the operation. Experimentation is often required to find the best value.

• Indexing: Indexes on the target table will be maintained during parallel DML, but this can impact performance. For very large inserts, it might be faster to drop indexes, perform the insert, and then rebuild the indexes.

• Transaction Management: Parallel DML operations are part of a transaction and require a COMMIT or ROLLBACK.

By strategically using parallel DDL and DML, you can significantly improve the performance of large-scale data operations in your Oracle database.

Parallel Backup of the Same Datafile (Intrafile parallel backup)

RMAN Intrafile Parallel Backup (SECTION SIZE)

This document describes the concept of Intrafile Parallel Backup in Oracle Recovery Manager (RMAN), specifically focusing on the SECTION SIZE feature, which allows for parallel backup of a single large datafile.

Understanding Intrafile Parallel Backup

Traditionally, when you allocate multiple channels in RMAN, each channel backs up a separate datafile. This means that even with several channels, a single large datafile is still backed up by only one channel at a time, which might not fully utilize the parallel capabilities of your backup infrastructure.

Starting with Oracle Database 11g RMAN, the concept of Intrafile Parallel Backup was introduced. This allows RMAN channels to break a single large datafile into smaller, independent units called "sections." Each section can then be backed up concurrently by a different channel, truly parallelizing the backup of a single large file.

How SECTION SIZE Works

The SECTION SIZE clause in the RMAN BACKUP command allows you to specify the maximum size of each section. RMAN will then divide the datafile into chunks of this specified size, and each chunk can be processed by an available channel in parallel.

Example RMAN Command

Here's an example demonstrating how to use SECTION SIZE to back up a large datafile in parallel:

RMAN> run {
2>      allocate channel c1 type disk format '/backup1/%U';
3>      allocate channel c2 type disk format '/backup2/%U';
4>      backup
5>      section size 500m
6>      datafile 6;
7> }

Explanation:

• allocate channel c1 type disk format '/backup1/%U'; and allocate channel c2 type disk format '/backup2/%U';: These commands allocate two disk channels, c1 and c2, and specify their respective backup destinations (/backup1 and /backup2).

• backup section size 500m datafile 6;: This is the core command. It instructs RMAN to back up datafile 6. The SECTION SIZE 500m clause tells RMAN to divide datafile 6 into 500MB sections. Each of these sections will then be backed up concurrently by the allocated channels (c1 and c2).

This setup allows for a significant speedup when backing up very large individual datafiles, as multiple channels can work on different parts of the same file simultaneously.

Listing Backed-Up Sections

When a datafile is backed up using SECTION SIZE, the backup pieces reflect these sections.

RMAN> list backup of datafile 6;

Example Output:

...
...
    List of Backup Pieces for backup set 901 Copy #1
    BP Key  Pc# Status      Piece Name
    -------    ---  -----------      ----------
    2007    1   AVAILABLE   /backup1/9dhk7os1_1_1
    2008    2   AVAILABLE   /backup2/9dhk7os1_1_1
    2009    3   AVAILABLE   /backup1/9dhk7os1_1_3
    2009    3   AVAILABLE   /backup2/9dhk7os1_1_4

Notice how the backup pieces (/backup1/9dhk7os1_1_1, /backup2/9dhk7os1_1_1, etc.) correspond to different sections of the datafile. Since each section can go to a different channel, you can direct them to different mount points (like /backup1 and /backup2 in the example), enabling parallel writing to disk or even tape.

Important Consideration for Disk Performance

While SECTION SIZE offers great parallelization benefits, it's crucial to consider the underlying storage.

• No Advantage on Single Disk: If the large datafile resides entirely on a single physical disk, there is generally no performance advantage to using parallel backups with SECTION SIZE. In such a scenario, the disk head would have to constantly move back and forth to access different sections of the file, which can outweigh the benefits of parallel processing and actually degrade I/O performance.

• Benefit with Striped/Multiple Disks: The true benefit of SECTION SIZE comes when the large datafile is spread across multiple physical disks (e.g., via RAID, ASM, or striped file systems) or when the backup pieces are written to different physical backup destinations. This allows the parallel I/O operations to be truly concurrent.