RE: Tracing FGAC/VPD differences between 9i and...

RE: Tracing FGAC/VPD differences between 9i and 10g

At http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_rls.htm#i998159

CONTEXT_SENSITIVE Server re-evaluates the policy function at statement execution time if it detects context changes since the last use of the cursor. For session pooling where multiple clients share a database session, the middle tier must reset context during client switches. Note that the server does not cache the value returned by the function for this policy type; it always executes the policy function on statement parsing. Applies to only one object.

SHARED_CONTEXT_SENSITIVE Same as CONTEXT_SENSITIVE except that the server first looks for a cached predicate generated by the same policy function of the same policy type within the same database session. If the predicate is found in the session memory, the policy function is not reexecuted and the cached value is valid until session private application context changes occur. Shared across multiple objects.

Note the last sentence of each: "Applies to only one object" vs. "Shared across multiple objects". The description of shared_context_sensitive appears to me to be consistent with the behavior you mention.

Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@credit-suisse.com
www.credit-suisse.com

-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Schultz, Charles
Sent: Wednesday, April 26, 2006 2:43 PM
To: Oracle-L
Subject: RE: Tracing FGAC/VPD differences between 9i and 10g

I apologize about running a one-side conversation here, but....

From what I can tell, the documentation is a bit screwed up (would not be the first time):
http://download-east.oracle.com/docs/cd/B19306_01/network.102/b14266/apdvcntx.htm#sthref2431

I am most concerned about context_sensitive and shared_context_sensitive.

For shared_context_sensitive, table 15-3 seems to indicate that the policy function only executes the first time the object is referenced in a session. We found that the policy only executed for the first object referenced. Hence, if you reference two objects, the first one gets cached (I have a working example for those that are curious). Does not the documentation imply that each object should have its own predicate?

_____________________________________________
From: Schultz, Charles
Sent:   Wednesday, April 26, 2006 8:24 AM
To:     'Oracle-L'
Subject:        RE: Tracing FGAC/VPD differences between 9i and 10g

Correction: Shared_context_sensitive does help in some situations, but in my "simple" example, we still have the same issue due to caching (with shared_context_sensitive).

_____________________________________________
From: Schultz, Charles
Sent:   Wednesday, April 26, 2006 8:20 AM
To:     'Oracle-L'
Subject:        RE: Tracing FGAC/VPD differences between 9i and 10g

Not having any prior experience with VPD, I am kinda jumping in the water on this one. We have made progress with various tracing options and now have other issues. Specifically, we would like to reduce the library cache latch contention due to heavy parsing caused by a policy type of "context_sensitive". "Shared_context_sensitive" seems to help, but "static" would be even better, if we can figure out how to appropriately deploy it.

Currently, our predicate function revolves around campus code (as we are a multi-campus educational facility), hence the VPD tables each have a vpdi column for use with the campus code. The problem is that the table name is part of the column name (a "naming standard" from the 3rd party vendor), hence if we use a static policy, subsequent queries against VPD tables fail because, obviously, the function is cached with the first table name that is executed.

My question for the list: what is the best compromise we can achieve? Granted, there are other VPD issues as the situation is a bit complex - I am starting simple since that is all I understand at the moment. =) I have been trying to read up on the documentation, but it tends to be distracting when people keep asking questions as if I know the answer. *grin*

_____________________________________________
From: Schultz, Charles
Sent:   Tuesday, April 25, 2006 12:31 PM
To:     Oracle-L
Subject:        Tracing FGAC/VPD differences between 9i and 10g

Does the sql trace facility (ie, event 10046) in 10g do better recursive tracing than 9i? From some tests we are running, we are seeing sql statements under 10g that do not show up under 9i (same application). I tried to scour the Concepts guide, but did not find anything relevant there (perhaps I missed it?).

TIA,

charles schultz
oracle dba
aits - adsd
university of illinois

==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer: 

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================

RE: Tracing FGAC/VPD differences between 9i and 10g

Baumgartel, Paul

2006-04-26