Re: FGAC predicate - problem with plan/execution time

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 Received on Thu May 01 2003 - 12:57:12 CDT