Oracle-L: Re: Row level security and latch waits

From: Madhavan Amruthur <mad5698_at_fastmail.fm>
Date: Thu, 21 Aug 2003 08:34:26 -0800
Message-ID: <F001.005CBFD3.20030821083426@fatcity.com>

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: mad5698_at_fastmail.fm

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: ListGuru_at_fatcity.com (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).

Received on Thu Aug 21 2003 - 11:34:26 CDT

Re: Row level security and latch waits - LONG email...