Find functions with pragma RESTRICT_REFERENCES

If you want to do parallel DML and use packaged functions in the statement, the functions need to fulfill certain requirements. Otherwise you will get the following note in the plan and PDML is not used:

- PDML disabled because function is not pure and not declared parallel enabled

This means your function must be either pure or declared with the parallel-enable clause.

The parallel enabled requirement is easy to check, you can find it out by querying DBA_PROCEDURES.PARALLEL

Pure functions

The second one is a bit harder, because it is not directly exposed via a database view.

First we need to know what pure means in this case. A function is considered as pure if it is defined with the pragma RESTRICT_REFERENCES and all four constraints RNDS, WNDS, RNPS and WNPS.

This information is encoded in SYS.PROCEDUREINFO$.PROPERTIES. The following query can be used to display which constraints of the pragma are used in a function/procedure:

select o.OBJECT_NAME,
       pi.procedurename,
       decode(bitand(pi.properties, 8388608), 8388608, 'NO', 'YES')
         as pragma_restrict_references,
       decode(bitand(pi.properties, 524288), 524288, 'NO', 'YES') as rnds,
       decode(bitand(pi.properties, 1048576), 1048576, 'NO', 'YES') as wnds,
       decode(bitand(pi.properties, 131072), 131072, 'NO', 'YES') as rnps,
       decode(bitand(pi.properties, 262144), 262144, 'NO', 'YES') as wnps
  from sys.procedureinfo$ pi
  join dba_objects o
    on (o.OBJECT_ID = pi.obj#);

So to finally find out if your function can be used in PDML statements, the following query can be used:

select o.OBJECT_NAME, pi.procedurename
  from sys.procedureinfo$ pi
  join dba_objects o on (o.OBJECT_ID = pi.obj#)
  join dba_procedures p on (o.OWNER = p.OWNER
    and o.OBJECT_NAME = p.OBJECT_NAME
    and pi.procedurename = p.PROCEDURE_NAME)
 where (p.PARALLEL = 'YES' 
   or (bitand(pi.properties, 8388608) <> 8388608 and
       bitand(pi.properties, 524288) <> 524288 and
       bitand(pi.properties, 1048576) <> 1048576 and
       bitand(pi.properties, 131072) <> 131072 and
       bitand(pi.properties, 262144) <> 262144))
   and p.OBJECT_NAME = '&packageName'
   and p.PROCEDURE_NAME = '&procedureName';

Conclusion

The above query provides an easy and fast way to determine if a function can be used in PDML statements. This is especially useful if you have a function for which you don’t own the source.

Example:

We had a DML statement which was using UTL_I18N.STRING_TO_RAW. This function is neither parallel enabled nor does it have the required pragma RESTRICT_REFERENCES. Therefore no PDML was performed.

We simply changed the statement to use UTL_RAW.CAST_TO_RAW instead, which has the pragma RESTRICT_REFERENCE required for PDML.

Of course these two functions do not provide the exact same functionality, but in our case (destination charset was the same as database charset) we were able to swap them without risk.