Oracle 23ai: Unrestricted Parallel DMLs

 

- Overview:

• Oracle database allows DML statements to be executed in parallel mode by breaking the DML statements into exclusive smaller tasks.
• However in releases prior Oracle database 23ai, parallel DML operations had a limitation. Once an object is modified by a parallel DML statement, that object cannot be read or modified by later statements of the same transaction before ending the transaction by executing commit or rollback.
• Oracle Database 23ai removes that restriction and by introducing "Unrestricted Parallel DMLs or Unrestricted Direct Loads" feature.

- Benefits: 

• In the same transaction session and before ending the transaction, you can:
• Query the same table multiple times
• Perform serial or parallel DML on the same table
• Perform multiple direct loads 
• Overhead Reduced
• Enable parallel DML in a session
• Separate commits are not required after each parallel DML statement 
• Take the full advantage of using parallel DMLs in the same transaction 

- Restrictions: 

• Heap organized table only.
• No ASSM tablespaces

- How to Enable Parallel DML Mode: 

• The parallel DML mode is required because parallel DML and serial DML have different locking, transaction, and disk space requirements and parallel DML is disabled for a session by default.
• When parallel DML is disabled, no DML is executed in parallel even if the PARALLEL hint is used.
• When parallel DML is enabled in a session, all DML statements in this session are considered for parallel execution.
• Run below SQL statement to enable parallel DML mode in a session:

ALTER SESSION ENABLE PARALLEL DML;

• Enable unrestricted parallel DML mode in a specific SQL statement, include the ENABLE_PARALLEL_DML hint.

INSERT /*+ ENABLE_PARALLEL_DML */ …

• However, even if parallel DML is enabled, the DML operation may still execute serially if there are no parallel hints or no tables with a parallel attribute or if restrictions on parallel operations are violated.

- Practice:

• In this practice, I'll enable parallel DML and perform a parallel insert (non-unrestricted) without commit or rollback. After the insert, I'll run a select from the same table and it will cause an error.
• Then I'll perform the parallel insert using unrestricted parallel hint without commit or rollback, After I'll run select and additional insert without errors.

1. Create a new table.

SQL> create table test_dmls as select * from objects_objects;   Table TEST_DMLS created.

2. Alter the session and enable parallel DML.

SQL> alter session enable parallel dml;   Session altered.

3. Insert into the table with PARALLEL hint.

SQL> insert /*+ parallel(test_dmls 4) */ into test_dmls             select /*+ parallel(test_dmls 4) */ * from test_dmls;    76,237 rows inserted.

4. Select from the table without performing a commit or rollback. You will get and error.

SQL> select count(*) from test_dmls;   Error starting at line : 1 in command - select count(*) from test_dmls SQL ORA-12838: cannot read/modify an object after modifying it in parallel   More Details : https://docs.oracle.com/error-help/db/ora-12838/ SQL> !oerr ora 12838 12838, 00000, "cannot read/modify an object after modifying it in parallel" // *Cause: Within the same transaction, an attempt was made to add read or  // modification statements on a table after it had been modified in parallel // or with direct load. This is not permitted. // *Action: Rewrite the transaction, or break it up into two transactions: // one containing the initial modification and the second containing the // parallel modification operation.

5.  Perform a commit.

SQL> commit;   Commit complete.

6. After the commit is done, now you can select again from the table.

SQL> select count(*) from test_dmls;      COUNT(*) ___________      152474

7. Perform a parallel insert using ENABLE_PARALLEL_DML hint.

SQL> insert /*+ enable_parallel_dml(test_dmls 4) */ into test_dmls             select /*+ enable_parallel_dml(test_dmls 4) */ * from test_dmls;   152,474 rows inserted.

8. Select from the table without performing a commit or rollback. Now you will NOT get an error.

SQL> select count(*) from test_dmls;      COUNT(*) ___________      304948

9. Perform another parallel insert without a commit or rollback. No error.

SQL> insert /*+ enable_parallel_dml(test_dmls 4) */ into test_dmls   2* select /*+ enable_parallel_dml(test_dmls 4) */ * from test_dmls;   304,948 rows inserted.   SQL> /   609,896 rows inserted.   SQL> /   1,219,792 rows inserted. SQL> select count(*) from test_dmls;    COUNT(*)  ___________      2439584

Oracle 23ai: Lock Free Reservations Capability

 

- Overview:

• In previous database releases when a column value of a row is updated by adding or subtracting from it, all other updates to that row are blocked until the transaction is committed or rolled back.
• Oracle Database 23ai introduces a new feature called "Lock Free Reservations" that allows multiple concurrent updates on a numeric column value to proceed without being blocked by uncommitted updates when adding or subtracting from the column value.
• Used with applications operate on numeric aggregate data. Such as data involve subtraction or addition of the values rather than assigning a value.
• The Lock Free Reservation feature is enabled by default. A Lock Free Reservation parameter, named "lockfree_reservation", is provided at the PDB level.
• To use Lock Free Reservation, use the RESERVABLE keyword to declare a RESERVABLE numeric column when you CREATE or ALTER a table.

- Benefits: 

• Reserve values without locking.
• Value locked on commit.
• Short held locks.
• Improved concurrency.
• Reduced bottlenecks. 

- Use Cases:

• Bank account balance (debt and credit transactions).
• Inventory and supply chain control.
• Ticketing 

- Restrictions:

• Only numeric data type.
•  Table must have a primary key.
• A Reservable column cannot be part of foreign key constraint. 
• A Reservable column cannot be a primary key, virtual column, or identity column.
• Indexes are not supported on a Reservable column.
• No updates on multiple Reservable columns in a table. 
• No mixing non-Reservable and Reservable columns updates.

- Demo:

In this demo I'll create a table "ACCOUNTS". The table will have a Reservable numeric column "Balance". I'll reduce the balance in one session without committing the transaction, then I'll try to reduce the balance of the same account from another session where I'll get an error notifying there is not enough value to reduce the balance.

1. Create a table (HR.ACCOUNTS) with a Reservable numeric column (BALANCE). The column will have a CHECK constraint to enforce a minimum balance value.

Note: a CHECK constraint is NOT a mandatory with a Reservable column.

create table accounts ( acc_id number primary key, acc_name varchar2(10), balance number reservable constraint accounts_bal_ck check (balance >= 50) );

2. Select from USER_TAB_COLUMNS and USER_TABLES views to see information about the lock free table and reservable column.

select table_name,column_name,reservable_column  from user_tab_columns where table_name='ACCOUNTS';   select table_name,has_reservable_column from user_tables where table_name='ACCOUNTS';

3. Find the name of the journal table created when the accounts tables was created with a reservable column.

select table_name,tablespace_name from user_tables where table_name like 'SYS_RESER%';

4.  Perform a describe and select from the journal table.

desc SYS_RESERVJRNL_77307 select * from SYS_RESERVJRNL_77307;

5. Insert and commit a row into ACCOUNTS table and run select.

insert into accounts values (100,'SCOTT',89); commit; select * from accounts;

6. Update the row without a commit and select from the journal table. The journal table will have a record for the uncommitted updated row executed in that session.

Note: A select from accounts table will show balance value unchanged, because the update has not committed. We are just reserving the balance.

7. Open a new database session and run the same update statement. 

Even we see the balance is 89, the update will get an error because of CHECK constraint violation. The first transaction in the first session reserved 25 making only 64 available until the transaction commit or rollbacks, the transaction in session 2 would violate the check constraint.

8. Go back to the first first session and perform a rollback.

9. Go back to the second session and run the update again and commit.

10. Go back to the first session and select the row from the table. With the update and commit in session 2 the balance is now updated. 

11. Query the journal table contents. With no pending truncations there are no rows in the table.

 

Oracle 23ai: Enumeration domains - List of Values in the Database

 

- Overview:

• Oracle Database 23ai introduces a new way to create lists of values: enumeration (enum) domains.
• Benefits:
• Create lists of name-value pairs in the database.
• Query the enum as a list of values.
• Limit column values to those in the enum list.
• Use the enum names as constants in SQL statements.
• Display the name of enum values.
• To create an enumeration domain, use create domain as enum command and provide a list of names. This assigns the values to each name in the order listed, starting with one. Each name has a value one higher than the previous.
• By default, they are case insensitive. If you want case sensitive names, as with table names place them in double quotes.
• Enums have an implicit check constraint. The database applies this to the column when you associate the domain. This ensures you can only store the enum’s values in the column.

In this blog, I'll demonstrate the steps to create error message severity name-value pair lists of enums and associate enums with incidents table severity column.   

Prerequisites:

• Oracle Database 23ai.

Demo 

1. Create an enumeration domain using below command.

     create domain <Domain Name> as enum (< comma separated list of values >);

    - In this demo, I'll create two an enumeration domains.

    - The first domain will provide a list of error messages severity. It starts with "Emergency" having the value of 1 to "Debug" which has the value of 8. Domain will provide a list of numbers values.

create domain err_msg_severity as enum (

Emergency, Alert, Critical, Error,

Warning, Notice, Informational, Debug);

    - The second domain will provide the same list of error messages severity. It starts with "Emergency" having the value "emerg" to "Debug" which has the value of "debug". Domain will provide a list of characters values.

create domain err_msg_severity_2 as enum (

Emergency = 'emerg', 

Alert = 'alert', 

Critical = 'crit', 

Error = 'error',

Warning = 'warn', 

Notice = 'notice', 

Informational = 'info', 

Debug = 'debug'

);

2. Create incidents table where severity column uses the first domain (list of numbers values).

     Notice that severity column's data type is NUMBER.

3. Insert rows into incidents table. 

    - Remember that severity column value should be between 1 and 8. Assigning a value not between 1 and 8 will raise ORA-11534.

ORA-11534: check constraint (HR.SYS_C0013233) involving column SEVERITY due to domain constraint 

     - You can use <DOMAIN_NAME>.<ENUM_NAME> when providing a value to severity column.

4. Drop and recreate incidents table where severity column uses the second domain (list of characters values).

     Notice that severity column's data type is VARCHAR2.

5. Insert rows into incidents table. 

    - Remember you need to use <DOMAIN_NAME>.<ENUM_VALUE> when assigning a value to severity column or insert/update command will raise ORA-11534.