Re: Row level security and latch waits - LONG email...
- Original Message -
Policy function:
DPA42HP92.DP_PREDICATE_FUNCTION_PKG.DP_PREDICATE_FUNCTION
RLS view :
SELECT CURRENCY,CURRENCY_CODE,CURRENCY_DESC,CURRENCY_KEY FROM
DPA42HP9
2.DPR70_CURRENCY_D DPR70_CURRENCY_D WHERE (CURRENCY_CODE in (select
value_
v
from dp_security_values
where group_id in (select group_id from dp_upd_user_groups where
comp_group_id =
sys_context('dp_comp_group_id_ctx','comp_group_id'))
as you can see the context variable is being used in the generated
predicate clause
That makes perfect sense now.
Thanks a lot for the feedback.
Cheers
Nuno Souto
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Nuno Souto
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Row level security and latch waits - LONG email...
- Original Message -
lets execute another query
SQL select count(*) from dpr70_gl_acct_balance_f;
COUNT(*)
--
2974
from v$SQL
SQL_TEXT
PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LOADS LAST_LOAD_TIME
--- -- --- -- ---
SELECT text from dp_security_text where object_name = :b1
and
comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id')
7 24 2003-08-20/09:29:34 2 2003-08-20/10:39:44
Notice again parse_calls does not change
The above SQL is the predicate clause being generated every time
I don't think this is the issue at all. The SQL in the function
that generates the predicate WILL use bind variables and that
is perfectly expected. That's why you don't see an increase in parses
on the SQL INSIDE the function.
What will not use bind variables is this bit:
SQL select count(*) from dpr70_gl_acct_balance_f;
because the added predicate is a string without bind syntax.
However, I think if the added predicate string contains a reference
to a context variable, which is what Tom is talking about, it might
well be the case this will act as a kind of bind variable
even though the syntax is not the correct one. Anyone found if that
is the case?
Cheers
Nuno Souto
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Nuno Souto
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Re: Row level security and latch waits
Interesting. I didn't get Connor's reply... Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 21, 2003 1:49 AM so in 9i dbms_rls increases the soft parses? From: Connor McDonald [EMAIL PROTECTED] Date: 2003/08/20 Wed AM 11:21:59 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Re: Row level security and latch waits My understanding was that the rls predicate was added at parse time (hence the importance of the contexts and avoiding things like 'sysdate') But also if I remember correctly, this behaviour was changed in v9 to process the security function with each execution (and hence probably increase the amount of parsing going on). You might want to have a play the the _dynamic_policies parameter (or something like that) which can be used to revert the 8i behaviour (which should reduce parsing to a degree) hth connor --- Nuno Souto [EMAIL PROTECTED] wrote: - Original Message - - Use context values within predicates, as bind variables NOTE THIS LINE. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row level security and latch waits - LONG email...
Hi Nuno
I don't think this is the issue at all. The SQL in the function
that generates the predicate WILL use bind variables and that
is perfectly expected. That's why you don't see an increase in parses
on the SQL INSIDE the function.
That is correct. I should have posted the predicate clause generated and
I have posted that below
What will not use bind variables is this bit:
SQL select count(*) from dpr70_gl_acct_balance_f;
because the added predicate is a string without bind syntax.
The added predicate for this is is using context as seen in the trace
file. This can be generated by setting event 10730
-
Logon user : SECUSER1
Table/View : DPA42HP92.DPR70_CURRENCY_D
Policy name: DPR70_CURRENCY_D_PLCY
Policy function:
DPA42HP92.DP_PREDICATE_FUNCTION_PKG.DP_PREDICATE_FUNCTION
RLS view :
SELECT CURRENCY,CURRENCY_CODE,CURRENCY_DESC,CURRENCY_KEY FROM
DPA42HP9
2.DPR70_CURRENCY_D DPR70_CURRENCY_D WHERE (CURRENCY_CODE in (select
value_
v
from dp_security_values
where group_id in (select group_id from dp_upd_user_groups where
comp_group_id =
sys_context('dp_comp_group_id_ctx','comp_group_id'))
as you can see the context variable is being used in the generated
predicate clause
However, I think if the added predicate string contains a reference
to a context variable, which is what Tom is talking about, it might
well be the case this will act as a kind of bind variable
even though the syntax is not the correct one. Anyone found if that
is the case?
That was my point, that using the context variable in the predicate
clause will cause it to act like a bimnd variable and that is behaviour
that I have been seeing
Thanks
Madhavan
http://www.dpapps.com
--
Madhavan Amruthur
DecisionPoint Applications
--
http://www.fastmail.fm - Choose from over 50 domains or use your own
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Madhavan Amruthur
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Re: Row level security and latch waits
Nuno, I've seen previous replies appearing much later than subsequent replies quite many times here... Few times even my posts have got lost for good... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 21, 2003 6:00 PM Interesting. I didn't get Connor's reply... Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 21, 2003 1:49 AM so in 9i dbms_rls increases the soft parses? From: Connor McDonald [EMAIL PROTECTED] Date: 2003/08/20 Wed AM 11:21:59 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Re: Row level security and latch waits My understanding was that the rls predicate was added at parse time (hence the importance of the contexts and avoiding things like 'sysdate') But also if I remember correctly, this behaviour was changed in v9 to process the security function with each execution (and hence probably increase the amount of parsing going on). You might want to have a play the the _dynamic_policies parameter (or something like that) which can be used to revert the 8i behaviour (which should reduce parsing to a degree) hth connor --- Nuno Souto [EMAIL PROTECTED] wrote: - Original Message - - Use context values within predicates, as bind variables NOTE THIS LINE. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Row level security and latch waits
- Original Message - - Use context values within predicates, as bind variables NOTE THIS LINE. None of that implies it's not appending a where clause like it was explained. It uses the context values (if that's the case) in a function which then returns a string. That string is then appended as a predicate to the original statement. That means the original statement is then re-parsed. It's the reason why you need query-rewrite enabled in FGAC. So, you get a lot of parsing. I don't know of any way of passing a context value as a true bind variable in a return string which is appended as a predicate. It certainly is not explained anywhere in the manual how to do it and I couldn't find anything in the literature and asktom either. Appreciate if anyone can explain how. A dynamically generated string is not the same as a bind variable. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Row level security and latch waits
My understanding was that the rls predicate was added at parse time (hence the importance of the contexts and avoiding things like 'sysdate') But also if I remember correctly, this behaviour was changed in v9 to process the security function with each execution (and hence probably increase the amount of parsing going on). You might want to have a play the the _dynamic_policies parameter (or something like that) which can be used to revert the 8i behaviour (which should reduce parsing to a degree) hth connor --- Nuno Souto [EMAIL PROTECTED] wrote: - Original Message - - Use context values within predicates, as bind variables NOTE THIS LINE. None of that implies it's not appending a where clause like it was explained. It uses the context values (if that's the case) in a function which then returns a string. That string is then appended as a predicate to the original statement. That means the original statement is then re-parsed. It's the reason why you need query-rewrite enabled in FGAC. So, you get a lot of parsing. I don't know of any way of passing a context value as a true bind variable in a return string which is appended as a predicate. It certainly is not explained anywhere in the manual how to do it and I couldn't find anything in the literature and asktom either. Appreciate if anyone can explain how. A dynamically generated string is not the same as a bind variable. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row level security and latch waits
Title: Message What latches are you waiting on? Is the package/procedure which implements the security policy kept in SGA (dbms_shared_pool.keep)? --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 19, 2003 11:49 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Row level security and latch waits sorry this is 9202 and little less than 100 users. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Stephane Paquette [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 19, 2003 11:40 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Row level security and latch waits I've developped 3 functions for fined-grain access control and the users have tested them. There are only 3-4users in the acceptance env. and I did not noticed anything. What version are you running and how many users ? We're on 8172 Someday it will go in production here... Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Jamadagni, RajendraSent: Tuesday, August 19, 2003 11:19 AMTo: Multiple recipients of list ORACLE-LSubject: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error,please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient.Wang Trading LLCand any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
Re: Re: Row level security and latch waits
so in 9i dbms_rls increases the soft parses? From: Connor McDonald [EMAIL PROTECTED] Date: 2003/08/20 Wed AM 11:21:59 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Re: Row level security and latch waits My understanding was that the rls predicate was added at parse time (hence the importance of the contexts and avoiding things like 'sysdate') But also if I remember correctly, this behaviour was changed in v9 to process the security function with each execution (and hence probably increase the amount of parsing going on). You might want to have a play the the _dynamic_policies parameter (or something like that) which can be used to revert the 8i behaviour (which should reduce parsing to a degree) hth connor --- Nuno Souto [EMAIL PROTECTED] wrote: - Original Message - - Use context values within predicates, as bind variables NOTE THIS LINE. None of that implies it's not appending a where clause like it was explained. It uses the context values (if that's the case) in a function which then returns a string. That string is then appended as a predicate to the original statement. That means the original statement is then re-parsed. It's the reason why you need query-rewrite enabled in FGAC. So, you get a lot of parsing. I don't know of any way of passing a context value as a true bind variable in a return string which is appended as a predicate. It certainly is not explained anywhere in the manual how to do it and I couldn't find anything in the literature and asktom either. Appreciate if anyone can explain how. A dynamically generated string is not the same as a bind variable. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row level security and latch waits
I got the 9.2 docs and this is in the Concepts manual: Dynamic Predicates The function or package that implements the security policy you create returns a predicate (a WHERE condition). This predicate controls access as set out by the policy. Rewritten queries are fully optimized and shareable. Fully optimized and shareable sure sounds like the queries are parsed after the predicate is added. I couldn't find any init parameter similar to what you mentioned. Does anyone have a concrete reference regarding parsing of FGAC-modified queries? Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Connor McDonald [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] uk cc: Sent by: Subject: Re: Re: Row level security and latch waits [EMAIL PROTECTED] .com 08/20/2003 10:21 AM Please respond to ORACLE-L My understanding was that the rls predicate was added at parse time (hence the importance of the contexts and avoiding things like 'sysdate') But also if I remember correctly, this behaviour was changed in v9 to process the security function with each execution (and hence probably increase the amount of parsing going on). You might want to have a play the the _dynamic_policies parameter (or something like that) which can be used to revert the 8i behaviour (which should reduce parsing to a degree) hth connor ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Row level security and latch waits
some other people were saying if you use it with a 'context' it binds it. is that accurate? From: [EMAIL PROTECTED] Date: 2003/08/20 Wed PM 04:09:26 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Row level security and latch waits I got the 9.2 docs and this is in the Concepts manual: Dynamic Predicates The function or package that implements the security policy you create returns a predicate (a WHERE condition). This predicate controls access as set out by the policy. Rewritten queries are fully optimized and shareable. Fully optimized and shareable sure sounds like the queries are parsed after the predicate is added. I couldn't find any init parameter similar to what you mentioned. Does anyone have a concrete reference regarding parsing of FGAC-modified queries? Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Connor McDonald [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] uk cc: Sent by: Subject: Re: Re: Row level security and latch waits [EMAIL PROTECTED] .com 08/20/2003 10:21 AM Please respond to ORACLE-L My understanding was that the rls predicate was added at parse time (hence the importance of the contexts and avoiding things like 'sysdate') But also if I remember correctly, this behaviour was changed in v9 to process the security function with each execution (and hence probably increase the amount of parsing going on). You might want to have a play the the _dynamic_policies parameter (or something like that) which can be used to revert the 8i behaviour (which should reduce parsing to a degree) hth connor ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row level security and latch waits
Title: RE: Row level security and latch waits Jack, This is from TFM ... manual Starting from 9i, queries against Fine Grained Access enabled objects always execute the policy function to make sure most up to date predicate is used for each policy. For example, in case of the time based policy function, in which queries are only allowed between 8am-5pm, an execute of a cursor that is parsed at noon would result in an execution of the policy function at the execution time to make sure policy function is consulted again for the query. There are only two exceptions to this rule. One is to specify STATIC_POLICY=TRUE when adding the policy to indicate that the policy function always returns the same predicate. Another one is for users whose security policies do not return different predicate within a database session, the init.ora parameter _dynamic_rls_policies can be set to FALSE to reduce the execution overhead. /manual Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 20, 2003 4:09 PM To: Multiple recipients of list ORACLE-L Subject: Re: Row level security and latch waits I got the 9.2 docs and this is in the Concepts manual: Dynamic Predicates The function or package that implements the security policy you create returns a predicate (a WHERE condition). This predicate controls access as set out by the policy. Rewritten queries are fully optimized and shareable. Fully optimized and shareable sure sounds like the queries are parsed after the predicate is added. I couldn't find any init parameter similar to what you mentioned. Does anyone have a concrete reference regarding parsing of FGAC-modified queries? Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Connor McDonald [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] uk cc: Sent by: Subject: Re: Re: Row level security and latch waits [EMAIL PROTECTED] .com 08/20/2003 10:21 AM Please respond to ORACLE-L My understanding was that the rls predicate was added at parse time (hence the importance of the contexts and avoiding things like 'sysdate') But also if I remember correctly, this behaviour was changed in v9 to process the security function with each execution (and hence probably increase the amount of parsing going on). You might want to have a play the the _dynamic_policies parameter (or something like that) which can be used to revert the 8i behaviour (which should reduce parsing to a degree) hth connor ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Row level security and latch waits
Raj, Thanks. A mention of which FM would've saved me some searching, but I found it under Introduction to Fine-Grained Access Control / Automatic Reparse in the Oracle9i Application Developer's Guide - Fundamentals. I'll have to do more investigation, since those paragraphs don't clearly explain (at least, to me) what is meant by the same predicate. Does that mean that predicates with Context Variables included as bind variables will not be reparsed if their Policy Function was created with Static_Policy=True, and yet different sessions can reuse the parsed SQL with their own Context values? ...or not? Also, any idea where init parameters such as _dynamic_rls_policies and _app_ctx_vers are fully documented? A search reveals that they're not in any of the 9.2 docs that I've got, except for the brief mention they get in the above-mentioned section of the Developer's Guide. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Rajendra.Jamadagncc: [EMAIL PROTECTED] Subject: RE: Row level security and latch waits Sent by: [EMAIL PROTECTED] com 08/20/2003 04:09 PM Please respond to ORACLE-L Jack, This is from TFM ... manual Starting from 9i, queries against Fine Grained Access enabled objects always execute the policy function to make sure most up to date predicate is used for each policy. For example, in case of the time based policy function, in which queries are only allowed between 8am-5pm, an execute of a cursor that is parsed at noon would result in an execution of the policy function at the execution time to make sure policy function is consulted again for the query. There are only two exceptions to this rule. One is to specify STATIC_POLICY=TRUE when adding the policy to indicate that the policy function always returns the same predicate. Another one is for users whose security policies do not return different predicate within a database session, the init.ora parameter _dynamic_rls_policies can be set to FALSE to reduce the execution overhead. /manual Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row level security and latch waits - LONG email...
Hi,
With the way we have implemented FGAC, we do not have any parses
occurring with application context.
The application context does act like bind variables and I will try to
illustrate that with an example.
We set the context of the users logging in to a particular group/role
which gets executed from a logon trigger
POLICY_NAMEFUNCTION
-- --
DPR70_CURRENCY_D_PLCY DP_PREDICATE_FUNCTION
The above is the function defined on the object
Login as a user
SQL select sys_context('dp_comp_group_id_ctx','comp_group_id')
2 from dual;
SYS_CONTEXT('DP_COMP_GROUP_ID_CTX','COMP_GROUP_ID')
1012
SQL select count(sql_text) from v$sql where sql_text like '%sys_cont%';
COUNT(SQL_TEXT)
---
12
In v$sql
this is the sql that generates the predicate clause vis the function
SELECT text from dp_security_text where object_name = :b1
and comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id')
SELECT sys_context(:b2,:b1) from sys.dual
Notice how the context gets converted into bind variables
Also notice the parse_calls and executions
6 16 SELECT text from dp_security_text where
object_name = :b1 and comp_group_id =
sys_context('dp_comp_group_id_ctx','comp_group_id')
Now lets login as a different user
SQL select sys_context('dp_comp_group_id_ctx','comp_group_id')
2 from dual;
SYS_CONTEXT('DP_COMP_GROUP_ID_CTX','COMP_GROUP_ID')
1011
1* select count(*) from v$sql where sql_Text like '%sys_cont%'
SQL /
COUNT(*)
--
12
There is a parse because the user_id is different
7 18 SELECT text from dp_security_text where
object_name = :b1 and comp_group_id =
sys_context('dp_comp_group_id_ctx','comp_group_id')
No lets execute queries as this user
SQL select count(*) from dpr70_currency_d;
COUNT(*)
--
3
from V$sql
SQL_TEXT
PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LOADS LAST_LOAD_TIME
--- -- --- -- ---
SELECT text from dp_security_text where object_name = :b1
and
comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id')
7 20 2003-08-20/09:29:34 2 2003-08-20/10:39:44
Notice parse_calls does not change
lets execute another query
SQL select count(*) from dpr70_gl_acct_balance_f;
COUNT(*)
--
2974
from v$SQL
SQL_TEXT
PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LOADS LAST_LOAD_TIME
--- -- --- -- ---
SELECT text from dp_security_text where object_name = :b1
and
comp_group_id = sys_context('dp_comp_group_id_ctx','comp_group_id')
7 24 2003-08-20/09:29:34 2 2003-08-20/10:39:44
Notice again parse_calls does not change
The above SQL is the predicate clause being generated every time
Hope this helps.
Also from Tom Kyte
/QUOTE
Sys_context is treated like a bind variable in a query -- its value is
BOUND in
just like any other value would be.
it rewrites the query which is then sent to the optimizer rewrote the
SYS_CONTEXT calls as bind variables.
/END QUOTE
URL
http://tinyurl.com/knrg
As for the doc below...
I'll have to do more investigation, since those paragraphs don't clearly
explain (at least, to me) what is meant by the same predicate. Does
that
mean that predicates with Context Variables included as bind variables
will
not be reparsed if their Policy Function was created with
Static_Policy=True, and yet different sessions can reuse the parsed SQL
with their own Context values? ...or not?
what this means is in 9i, the policy function will be executed every time
a query is issued against the object.
But if the predicate being is generated is the same everytime then the
policy can be generated with static_policy=true
and if needs to be refreshed, then dbms_policy.refresh needs to be used.
If the predicate generated is different in different database sessions
but they are the same within a session, then
_dynamic_rls_policies=FALSE will prevent it being executed within the
session.
Hope this helps.
Regards,
Sorry for the long winding email
Thanks
Madhavan
http://www.dpapps.com
--
Madhavan Amruthur
DecisionPoint Applications
--
http://www.fastmail.fm - mmm... Fastmail...
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Madhavan Amruthur
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list
RE: Row level security and latch waits
Title: RE: Row level security and latch waits Sorry Jack, I didn't preserve the title ... it was copied from an internal email. Also the _ parameters are never documented in official ora docs, Metalink is good for that. Raj -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 20, 2003 5:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Row level security and latch waits Raj, Thanks. A mention of which FM would've saved me some searching, but I found it under Introduction to Fine-Grained Access Control / Automatic Reparse in the Oracle9i Application Developer's Guide - Fundamentals. I'll have to do more investigation, since those paragraphs don't clearly explain (at least, to me) what is meant by the same predicate. Does that mean that predicates with Context Variables included as bind variables will not be reparsed if their Policy Function was created with Static_Policy=True, and yet different sessions can reuse the parsed SQL with their own Context values? ...or not? Also, any idea where init parameters such as _dynamic_rls_policies and _app_ctx_vers are fully documented? A search reveals that they're not in any of the 9.2 docs that I've got, except for the brief mention they get in the above-mentioned section of the Developer's Guide. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Rajendra.Jamadagn cc: [EMAIL PROTECTED] Subject: RE: Row level security and latch waits Sent by: [EMAIL PROTECTED] com 08/20/2003 04:09 PM Please respond to ORACLE-L Jack, This is from TFM ... manual Starting from 9i, queries against Fine Grained Access enabled objects always execute the policy function to make sure most up to date predicate is used for each policy. For example, in case of the time based policy function, in which queries are only allowed between 8am-5pm, an execute of a cursor that is parsed at noon would result in an execution of the policy function at the execution time to make sure policy function is consulted again for the query. There are only two exceptions to this rule. One is to specify STATIC_POLICY=TRUE when adding the policy to indicate that the policy function always returns the same predicate. Another one is for users whose security policies do not return different predicate within a database session, the init.ora parameter _dynamic_rls_policies can be set to FALSE to reduce the execution overhead. /manual Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Row level security and latch waits
Raj - In this case, RLS would be Row Level Security? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 19, 2003 10:19 AM To: Multiple recipients of list ORACLE-L hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row level security and latch waits
Title: Row level security and latch waits I've developped 3 functions for fined-grain access control and the users have tested them. There are only 3-4users in the acceptance env. and I did not noticed anything. What version are you running and how many users ? We're on 8172 Someday it will go in production here... Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Jamadagni, RajendraSent: Tuesday, August 19, 2003 11:19 AMTo: Multiple recipients of list ORACLE-LSubject: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
Re: Row level security and latch waits
row level security doesnt use bind variables. dont know if there is a way to get it to use them. thats probably your problem. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/08/19 Tue AM 11:19:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1 Title: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: Row level security and latch waits
Title: Row level security and latch waits sorry this is 9202 and little less than 100 users. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Stephane Paquette [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 19, 2003 11:40 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Row level security and latch waits I've developped 3 functions for fined-grain access control and the users have tested them. There are only 3-4users in the acceptance env. and I did not noticed anything. What version are you running and how many users ? We're on 8172 Someday it will go in production here... Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Jamadagni, RajendraSent: Tuesday, August 19, 2003 11:19 AMTo: Multiple recipients of list ORACLE-LSubject: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Row level security and latch waits
Title: RE: Row level security and latch waits Yup. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 19, 2003 11:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: Row level security and latch waits Raj - In this case, RLS would be Row Level Security? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 19, 2003 10:19 AM To: Multiple recipients of list ORACLE-L hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Row level security and latch waits
Title: RE: Row level security and latch waits This is interesting do you know where it is documented quickly? I'll do a rtfm/stfw in the mean time. Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 19, 2003 11:44 AM To: Multiple recipients of list ORACLE-L Subject: Re: Row level security and latch waits row level security doesnt use bind variables. dont know if there is a way to get it to use them. thats probably your problem. *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: RE: Row level security and latch waits
im fairly certain its because DBMS_RLS doesnt use bind variables now. latch waits indicates plus your 100 concurrent users. you can check this by going to v$sqlarea and checking for similiar sql_text statements. if they are there, then your not using bind variables. there is a query in tom kytes book. not sure where it is on the web now that wrox is out of business. its probably on asktom though. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/08/19 Tue AM 11:49:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Row level security and latch waits sorry this is 9202 and little less than 100 users. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, August 19, 2003 11:40 AM To: Multiple recipients of list ORACLE-L I've developped 3 functions for fined-grain access control and the users have tested them. There are only 3-4 users in the acceptance env. and I did not noticed anything. What version are you running and how many users ? We're on 8172 Someday it will go in production here... Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Jamadagni, Rajendra Sent: Tuesday, August 19, 2003 11:19 AM To: Multiple recipients of list ORACLE-L hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1 Title: Row level security and latch waits sorry this is 9202 and little less than 100 users. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Stephane Paquette [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 19, 2003 11:40 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Row level security and latch waits I've developped 3 functions for fined-grain access control and the users have tested them. There are only 3-4users in the acceptance env. and I did not noticed anything. What version are you running and how many users ? We're on 8172 Someday it will go in production here... Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Jamadagni, RajendraSent: Tuesday, August 19, 2003 11:19 AMTo: Multiple recipients of list ORACLE-LSubject: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed
Re: Re: Row level security and latch waits
its just appending a where clause. its not binding it. im not familiar with contexts. never worked with them. someone correct me if im wrong here? Could have sworn i read that somewhere. i looked up application contexts. they appear to be handled differently. am i wrong? From: [EMAIL PROTECTED] Date: 2003/08/19 Tue PM 02:14:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Row level security and latch waits RLS doesn't use bind variables? How then does Oracle treat the Application Context variables that you include in the predicates generated by the Security Policy functions? If those aren't bind variables then I guess I don't know what bind variables are. Please refer me to the documentation on which your assertion is based. Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Row level security and latch waits .com 08/19/2003 10:44 AM Please respond to ORACLE-L row level security doesnt use bind variables. dont know if there is a way to get it to use them. thats probably your problem. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/08/19 Tue AM 11:19:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing
Re: Row level security and latch waits
RLS doesn't use bind variables? How then does Oracle treat the Application Context variables that you include in the predicates generated by the Security Policy functions? If those aren't bind variables then I guess I don't know what bind variables are. Please refer me to the documentation on which your assertion is based. Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Row level security and latch waits .com 08/19/2003 10:44 AM Please respond to ORACLE-L row level security doesnt use bind variables. dont know if there is a way to get it to use them. thats probably your problem. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/08/19 Tue AM 11:19:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Row level security and latch waits
OK, I went and looked in the 8i Concepts manual. It seems pretty clear that Application Context variables are used as bind variables. It may have changed for 9i, but I can't see how or why. Application Context Application context facilitates the implementation of fine-grained access control. It allows you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus). Application contexts permit flexible, parameter-based access control, based on attributes of interest to an application. For example, context attributes for a human resources application could include position, organizational unit, and country while attributes for an order-entry control might be customer number and sales region. You can: - Base predicates on context values - Use context values within predicates, as bind variables NOTE THIS LINE. - Set user attributes - Access user attributes Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Re: Row level security and latch waits .com 08/19/2003 01:39 PM Please respond to ORACLE-L its just appending a where clause. its not binding it. im not familiar with contexts. never worked with them. someone correct me if im wrong here? Could have sworn i read that somewhere. i looked up application contexts. they appear to be handled differently. am i wrong? From: [EMAIL PROTECTED] Date: 2003/08/19 Tue PM 02:14:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Row level security and latch waits RLS doesn't use bind variables? How then does Oracle treat the Application Context variables that you include in the predicates generated by the Security Policy functions? If those aren't bind variables then I guess I don't know what bind variables are. Please refer me to the documentation on which your assertion is based. Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Row level security and latch waits .com 08/19/2003 10:44 AM Please respond to ORACLE-L row level security doesnt use bind variables. dont know if there is a way to get it to use them. thats probably your problem. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/08/19 Tue AM 11:19:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most
Re: Re: Row level security and latch waits
i wasny referring to application contexts. I was referring to just using a policy based on dbms_rls. thats different isnt it? From: [EMAIL PROTECTED] Date: 2003/08/19 Tue PM 03:14:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Re: Row level security and latch waits OK, I went and looked in the 8i Concepts manual. It seems pretty clear that Application Context variables are used as bind variables. It may have changed for 9i, but I can't see how or why. Application Context Application context facilitates the implementation of fine-grained access control. It allows you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus). Application contexts permit flexible, parameter-based access control, based on attributes of interest to an application. For example, context attributes for a human resources application could include position, organizational unit, and country while attributes for an order-entry control might be customer number and sales region. You can: - Base predicates on context values - Use context values within predicates, as bind variables NOTE THIS LINE. - Set user attributes - Access user attributes Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Re: Row level security and latch waits .com 08/19/2003 01:39 PM Please respond to ORACLE-L its just appending a where clause. its not binding it. im not familiar with contexts. never worked with them. someone correct me if im wrong here? Could have sworn i read that somewhere. i looked up application contexts. they appear to be handled differently. am i wrong? From: [EMAIL PROTECTED] Date: 2003/08/19 Tue PM 02:14:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Row level security and latch waits RLS doesn't use bind variables? How then does Oracle treat the Application Context variables that you include in the predicates generated by the Security Policy functions? If those aren't bind variables then I guess I don't know what bind variables are. Please refer me to the documentation on which your assertion is based. Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Row level security and latch waits .com 08/19/2003 10:44 AM Please respond to ORACLE-L row level security doesnt use bind variables. dont know if there is a way to get
RE: Re: Row level security and latch waits
Jack, You are right, app context is treated as a bind variable, but someone may decide not to use it in RLS policy function. Example (maybe a little unreal but valid): CREATE OR REPLACE PACKAGE BODY Oe_security AS FUNCTION Custnum_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2 IS D_predicate VARCHAR2 (2000) BEGIN D_predicate = 'create_date '''||to_char(sysdate-1)||; RETURN D_predicate; END Custnum_sec; END Oe_security; Hence hard parses. Vadim -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2003 3:14 PM To: Multiple recipients of list ORACLE-L OK, I went and looked in the 8i Concepts manual. It seems pretty clear that Application Context variables are used as bind variables. It may have changed for 9i, but I can't see how or why. Application Context Application context facilitates the implementation of fine-grained access control. It allows you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus). Application contexts permit flexible, parameter-based access control, based on attributes of interest to an application. For example, context attributes for a human resources application could include position, organizational unit, and country while attributes for an order-entry control might be customer number and sales region. You can: - Base predicates on context values - Use context values within predicates, as bind variables NOTE THIS LINE. - Set user attributes - Access user attributes Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Re: Row level security and latch waits .com 08/19/2003 01:39 PM Please respond to ORACLE-L its just appending a where clause. its not binding it. im not familiar with contexts. never worked with them. someone correct me if im wrong here? Could have sworn i read that somewhere. i looked up application contexts. they appear to be handled differently. am i wrong? From: [EMAIL PROTECTED] Date: 2003/08/19 Tue PM 02:14:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Row level security and latch waits RLS doesn't use bind variables? How then does Oracle treat the Application Context variables that you include in the predicates generated by the Security Policy functions? If those aren't bind variables then I guess I don't know what bind variables are. Please refer me to the documentation on which your assertion is based. Thanks. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Row level security and latch waits .com 08/19/2003 10:44 AM Please respond to ORACLE-L row level security doesnt use bind variables. dont know if there is a way to get it to use them. thats probably your problem. From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/08/19 Tue AM 11:19:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Row level security and latch waits hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy function defined as part of RLS. The worst part was all this wait was only on one node, the other node was healthy. While we are trying to capture more information, anything else that we can do? Any ideas? TIA Raj Rajendra dot Jamadagni at nospamespn dot com hi all, in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%. Most active sessions seem to run the policy
Re: Row level security and latch waits
Application Context is used to implement FGAC (aka RLS). You need to read those sections in the Concepts doc. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Re: Row level security and latch waits .com 08/19/2003 02:19 PM Please respond to ORACLE-L i wasny referring to application contexts. I was referring to just using a policy based on dbms_rls. thats different isnt it? From: [EMAIL PROTECTED] Date: 2003/08/19 Tue PM 03:14:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Re: Row level security and latch waits OK, I went and looked in the 8i Concepts manual. It seems pretty clear that Application Context variables are used as bind variables. It may have changed for 9i, but I can't see how or why. Application Context Application context facilitates the implementation of fine-grained access control. It allows you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus). Application contexts permit flexible, parameter-based access control, based on attributes of interest to an application. For example, context attributes for a human resources application could include position, organizational unit, and country while attributes for an order-entry control might be customer number and sales region. You can: - Base predicates on context values - Use context values within predicates, as bind variables NOTE THIS LINE. - Set user attributes - Access user attributes Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: Re: Row level security and latch waits .com 08/19/2003 01:39 PM Please respond to ORACLE-L its just appending a where clause. its not binding it. im not familiar with contexts. never worked with them. someone correct me if im wrong here? Could have sworn i read that somewhere. i looked up application contexts. they appear to be handled differently. am i wrong? From: [EMAIL PROTECTED] Date: 2003/08/19 Tue PM 02:14:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Row level security and latch waits RLS doesn't use bind variables? How then does Oracle treat the Application Context variables that you include in the predicates generated by the Security Policy functions? If those aren't bind variables then I guess I don't know what bind variables are. Please refer me to the documentation on which your assertion is based. Thanks. Jack C. Applewhite Database Administrator Austin Independent
