PQ & FGAC

oh yeah, one more thing. i wanted to suggest a good reference book for topics on Oracle PX and the Parallel Server:

ORACLE PARALLEL PROCESSING by Tushar Mahapatra and Sanjay Mishra, O'Reilly Publishing

nick :)

I think the problem only happens in Sys Context:

Extracted from doco:

Parallel Query Not Recommended for Use with SYS_CONTEXT

If SYS_CONTEXT is used inside a SQL function which is embedded in a parallel query, the function will not be able to pick up the application context. The application context exists only in the user session.

Consider, for example, a user-defined function within a SQL statement, which sets the user's ID to 5:

CREATE FUNC proc1 AS RETURN NUMBER;
BEGIN
IF SYS_CONTEXT ('hr', 'id') = 5
THEN RETURN 1; ELSE RETURN 2;
END
END;


Now consider the statement:

SELECT * FROM EMP WHERE proc1( ) = 1;


If this statement is run as a single query (that is, if one process is used to run the entire query), there will be no problem.

However, if this statement is run as a parallel query, the parallel execution servers (query slave processes) do not have access to the user session which contains the application context information. The query will not produce the expected results.

By contrast, if you use the SYS_CONTEXT function within a query, there is no problem. For example,

SELECT * FROM EMP WHERE SYS_CONTEXT ('hr', 'id') = 5


In this way, it works like a bind variable: the query coordinator can access the application context information and pass it on to the parallel execution servers.

Versioning in Application Context

When you execute a statement, Oracle8i takes a snapshot of the entire application context being set up by SYS_CONTEXT. Within the duration of a query, the context remains the same for all fetches of the query.

If you (or a function) attempt to change the context within a query, the change will not take effect in the current query. In this way, SYS_CONTEXT enables you to store variables in a session.


Do you have an exmaple of doing it without context :-?

hi pando,

hmmm...you bring up a very interesting point. i've never considered the idea that a parallel slave process would not be able to access the original user's session application context. however, i don't believe this applies in a PX/FGAC situation; i would bet that in a PX/FGAC situation the parallel slave processes are able to execute the correct sql, with the correct predicate.

the doc excerpt you provided states that a "function will not be able to pick up the the application context". FGAC, it seems to me*, uses a function call ONCE, during the policy implementation to 'rewrite' a user's sql, which is then passed on to subsequent parallel slave processes. the function call is NOT part of the parsed sql and therefore does not need access to the user's application context.

also, the fact that Oracle Corporation seems to push the notion of using its PX feature along with its FGAC feature so heavily, leads me to believe that these two features would work in harmony. i mentioned that i do not use application contexts to store user's predicates. i do, however, use application contexts to retrieve the current user's username within my FGAC functions. and stating once again, I use both PX and FGAC quite heavily and have never had a problem. i am also very much sure that all my parallel slave process are executing correct sql statements.

i would suggest testing your current FGAC setup to see if all parallel slave processes are indeed implementing the correct sql. and regarding your request for an example of FGAC without contexts, if you still want them, let me know and i can provide some sql for you.

* the basis for my claims is simply the culmination of my personal experiences and observations. i have not found direct statements to substantiate or unsubstantiate my personal hypothesis concerning these matters in official Oracle documentation or other sources.

nick :)

Actually I extracted these paragraph from FGAC section in Oracle Doco so basically Oracle is suggesting not use FGAC with PQ!