Database Software

Software Versions :

1. Windows 2003 Server

2. Database :
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

3. OWB :
Client - 10.1.0.2.0
Repo - 10.1.0.1.0


Question :

How do we request/force Oracle to NOT execute packaged functions in parallel ?

We have a package which stores "globals" declared in the package body i.e. the package constructs a package
state which will be used by the session. Access to the packaged globals is via get/set functions (which do
NOT make use of the PARALLEL_ENABLE keyword). These get/set functions are called from within an OWB mapping.
The mapping sources and traget are configured without the parallel hint.

--------------------------------------------------------------------------------

Problem :

OWB generates a mapping which when executed runs in parallel - this can be seen through the query plan of
the SQL. The mapping runs end-to-end successfully but the data produced is incorrect i.e. the values
to be merged (some of which are taken from the packaged global variables) are wrong.

Note also that there has been no re-compilation of the package - either manually or implicitly while the
mapping is running i.e. the package state has not been discarded though compilation.

As a simple test :

1. From OWB, generate the intermediate results set of the final merge.
2. Start a SQL*Plus session.
3. Execute the following :
ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL QUERY;
4. Run the generated SQL.

This works consistently and the explain shows no parallel execution - as we would expected.

Attempted to add the RESTRICT_REFERENCES pragma with WNDS, RNDS i.e.. we only use package variables - doesn't
work. In fact, as of 10g we do not need to supply the PRAGMA.

Looking at the OWB 10g documentation, chapter 9, Importing PL/SQL, bullet point 8 states :

<< BEGIN QUOTE...
Check if function should be enabled for parallel execution:
This option declares that a stored function can be used safely in the child sessions
of parallel DML evaluations. The state of a main (logon) session is never shared
with child sessions. Each child session has its own state, which is initialized when
the session begins. The function result should not depend on the state of session
(static) variables. Otherwise, results might vary across sessions.
...END QUOTE >>

We are NOT presented with ANY check boxes during import of the PL/SQL... but clearly the OWB designers saw a need
for this option.

Finally, looking at the Oracle 10g documentation it all seems to imply that Oracle will analyse the packaged
function and see that it makes use of a package state i.e. WNPS and RNPS are violated and therefore NOT execute
the function in parallel.

So, can somebody explain :

1. Where in OWB we can find the "...enabled for parallel execution" check box ?

2. Why the generated SQL runs in parallel ?

Options such as using global temporary tables have been avoided due to volume/performance issues - the package
approach is memory bound.


Any thoughts ?

Thanks

Ajay