How to Create a Trigger in Oracle

Database 19c to Enable Parallel

Features at Session Level.

Oracle Database 19c offers robust parallel processing capabilities, and enabling these features for specific users can significantly improve performance for operations such as DML, DDL, and queries. In this blog, we will create a PL/SQL package and an associated trigger to enable parallel features automatically for users User_A, User_B, and User_C. We will also include a test case to verify the implementation.

Objectives

1. Create a PL/SQL package to enable parallel features:

• PARALLEL DML
• PARALLEL DDL
• PARALLEL QUERY

1. Implement a database trigger to apply the package automatically for the specified users.
2. Verify the trigger with a test case.

Step 1: Creating the PL/SQL Package

First, create a PL/SQL package that sets the required parallel features at the session level.

CREATE OR REPLACE PACKAGE Enable_Parallel_Session AS
PROCEDURE Set_Parallel_Options;
END Enable_Parallel_Session;
/

CREATE OR REPLACE PACKAGE BODY Enable_Parallel_Session AS
PROCEDURE Set_Parallel_Options IS
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DDL';
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL QUERY';
END Set_Parallel_Options;
END Enable_Parallel_Session;
/

Step 2: Creating the Trigger

The trigger will invoke the package procedure whenever a session is established for User_A, User_B, or User_C.

CREATE OR REPLACE TRIGGER Set_Parallel_For_Users
AFTER LOGON ON DATABASE
BEGIN
IF USER IN ('USER_A', 'USER_B', 'USER_C') THEN
Enable_Parallel_Session.Set_Parallel_Options;
END IF;
END;
/

Step 3: Verifying the Implementation

To confirm the trigger is functioning correctly, perform the following test steps:

1. Log in as one of the specified users:
   sqlplus User_A/password@database
2. Run a test query to verify that the session settings are applied:
   SELECT SID, SERIAL#, NAME, VALUE FROM V$SESSTAT JOIN V$STATNAME USING (STATISTIC#) WHERE NAME IN ('Parallel DML enabled', 'Parallel execution enabled');
3. Run a query to test parallelism:
   CREATE TABLE parallel_test AS SELECT /*+ PARALLEL(4) */ * FROM DUAL CONNECT BY LEVEL <= 1000; SELECT COUNT(*) FROM parallel_test;
4. Validate execution plan to confirm parallel processing:
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Troubleshooting

If the trigger doesn’t work as expected, check the following:

• Ensure the trigger is enabled:
  SELECT TRIGGER_NAME, STATUS FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'SET_PARALLEL_FOR_USERS';

Conclusion

With this implementation, you can ensure that parallel features are automatically enabled for specific users at the session level. This improves performance for resource-intensive operations and simplifies session configuration.

For more insights on Oracle Database optimizations, stay tuned to our blog!

Author: Rohit Mishri