Re: FGAC predicate - problem with plan/execution time

From: dnh <nigel.hurst_at_ds-s.com>
Date: Fri, 2 May 2003 10:17:43 +0100
Message-ID: <3eb1d68e$1@cpns1.saic.com>

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:Yddsa.27546$Ye6.1718625_at_twister.socal.rr.com...
> dnh wrote:
> >
> > Hi
> >
> > I am currently using the fgac functionality to limit users view of data.
I
> > am having a problem in that in a particular case I am getting a
different
> > explain plan and much slower execution times on running it as a user to
whom
> > the predicate is applied than if I manually run the same query
(supposedly!)
> > as a user for whom I build a null predicate.
> >
> > I am running oracle 8.1.7.4 on a SUN/Solaris platform.
> >
> > I have not posted the query as I am initially looking for any ideas as
to
> > why I am getting different plans and run times for what is essentially
the
> > same query. Has anybody else come across this problem?? I do get the
same
> > results from both queries
> >
> > cheers
> > nigel
>
> This has been an issue in all the VPD implementations I've worked on.
> The FGAC functions essentially created inline views in place of the
> table with the criteria added. This means Oracle now has a view it can
> merge or not, additional access paths to consider, predicates it might
> push, etc... That increases complexity and makes coming up with the
> optimal execution plan more difficult.
>
> I'm not sure what you mean exactly by "I manually run the same query".
> If you create the same inline views as the FGAC functions produce and
> run from the same schema you should get the same plan (barring session
> configuration changes). Are you claiming you aren't?
>
>
> Richard
>

I thought it just added the predicate onto the query as an 'and' set. Here's a simplified example of the query I'm having problems with

select event.part_number
from event,

       action

where event.system_event_number = action.system_event_number(+)
and   event.part_number = 'l'
and  action.part_number = 'p'

the predicates are

for event table:
exists (select 1 from event_security

                 where event.event_security.system_event_number =

event.system_event_number)

for action table:
exists (select 1 from event_security

                 where action.event_security.system_event_number =

event.system_event_number)

I thought it added these to the end of the where clause thus

select event.part_number
from event,

       action
where event.system_event_number = action.system_event_number(+) and event.part_number = 'l' and action.part_number = 'p' and exists (select 1 from event_security

                 where event_security.system_event_number =

event.system_event_number)
and exists (select 1 from event_security

                 where event_security.system_event_number =

action.system_event_number)

but it seems that they do this, is that what you're saying?

select event.part_number
from (select * from event

        where exists (select 1 from event_security
                 where event_security.system_event_number =
event.system_event_number)) event,
     (select * from action
   where exists (select 1 from event_security
                 where event_security.system_event_number =

action.system_event_number)) action
where event.system_event_number = action.system_event_number(+) and event.part_number = 'l' and action.part_number = 'p'

if I run it using the inline view method I do get the same plan, unfortunately it's the slow one.

I think I understand now, I was mistaken in how oracle added the predicate to the query. It adds it on a per table basis rather than a query basis. Correct??

cheers

   nigel Received on Fri May 02 2003 - 04:17:43 CDT