Information collection for stored procedures and parallel execution of SQL statements

OceanBase Database collects information about whether DML statements or package variable read/write operations are involved in stored procedures during compilation. This information is primarily used to determine whether DML statements involving UDFs (User-Defined Functions) or triggers need to be executed in parallel in nested scenarios.

The stored procedure collects the following information:

• Whether SQL statements (including DML, DDL, and TCL statements) are read or written in the stored procedure.
• Whether package variables are read or written in the stored procedure.
• Whether a sequence is used in the stored procedure.
• Access to other stored procedures.

Parallel execution of DML statements associated with UDFs or triggers

When a DML statement associated with a UDF or trigger is executed in parallel, if the corresponding stored procedure has parallel execution factors, the system will actively prohibit parallel execution and switch to serial execution. At the same time, the parallel_enable clause is provided to force the UDF to execute in parallel. When users create a UDF, if the parallel_enable clause is specified, the associated PDML statement will ignore whether the UDF has parallel execution factors and will be forced to execute in parallel.

The following common PDML statements will trigger UDF or trigger execution, and parallel execution will be changed to serial execution:

• The PDML statement triggers a UDF or trigger that contains DML statements.

• The PDML statement triggers a UDF or trigger that contains SELECT statements.

• The PDML statement triggers a UDF or trigger that contains read/write operations on package variables, access to external stored procedures, or global objects.

  Notice

  If a parallel query statement is associated with a UDF that contains only SELECT statements, parallel execution is allowed.

For SQL statements that trigger UDFs (triggers), the following limitations apply:

• When the outer SQL statement is a query or DML statement, the UDF cannot end a transaction, create or roll back a savepoint, or execute ALTER SYSTEM/ALTER SESSION.
• When the outer SQL statement is a DML statement, the UDF cannot read or write the same table.

These limitations apply whether the UDF is called from PL, directly embedded in the function body, or executed using EXECUTE IMMEDIATE or the DBMS_SQL package.