| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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 <http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_rls.htm#i998159>
sthref6221CONTEXT_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. sthref6222SHARED_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_at_credit-suisse.com
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_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> 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?
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).
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*
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
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 26 2006 - 13:59:59 CDT
 |
 |