Re: Explain Plan and Security

Lothar,

Please also disable dynamic sampling.
If the optimized thinks your stats are suspicious (and they are in such settings)dynamic sampling is a source of joy and happiness.

 Martin

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> schrieb am So., 17. Juni 2018, 19:04:

>
> It took a bit of time to find because I'd remembered it incorrectly.
>
> The thing I had in mind was about the decision to use (or not) serial
> direct path reads for segment scans. It used to be based on the segment
> HWM, but changed to the object level stats in 11.2, with a controlling
> parameter (Tanel has a note
> https://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/
> ) so not relevant in your case.
>
> Regards
> Jonathan Lewis
>
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Lothar Flatz <l.flatz_at_bluewin.ch>
> Sent: 17 June 2018 15:55
> To: oracle-l_at_freelists.org
> Subject: Re: Explain Plan and Security
>
> Hi Jonathan,
>
> yes, we are currently thinking of such a solution. Would be great if you
> could dig up the events.
> An other approach would be a thin clone. But that would actually just
> circumvent the words of the security statement, but violate the meaning.
>
> Regards and thanks
>
> Lothar
>
> Am 17.06.2018 um 13:43 schrieb Jonathan Lewis:
> >
> > Lothar,
> >
> > Can you export the object definitions and object stats ? If so then you
> could model and run the query to see if you can get the plan you want
> against the production stats without seeing any production data (except the
> low, high and histogram values). The rowsource execution stats would be
> meaningless, of course, but the plan should match if you've got a matching
> environment.
> >
> > You may have to set a couple of events to tell Oracle to use the object
> stats instead of the segment HWM - but off the top of my head I can't give
> you an immediate description of exactly what you'd have to do for that bit.
> >
> > Regards
> > Jonathan Lewis
> >
> >
> >
> >
> > ________________________________________
> > From: l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>
> > Sent: 16 June 2018 14:28
> > To: martin.a.berger_at_gmail.com
> > Cc: oracle-l_at_freelists.org; Jonathan Lewis
> > Subject: Re: Re: Re: Explain Plan and Security
> >
> > Hi Martin,
> >
> > that would not work. I am working for a service provider. The data is
> not ours. We are by policy forbitten to run queries on prod other than
> against the dictionary.
> > We must look for an other way. Maybe some instant clone would work.
> >
> > Thanks
> >
> > Lothar
> > ----Ursprüngliche Nachricht----
> > Von : martin.a.berger_at_gmail.com
> > Datum : 15/06/2018 - 21:04 (CEST)
> > An : l.flatz_at_bluewin.ch
> > Cc : jonathan_at_jlcomp.demon.co.uk, oracle-l_at_freelists.org
> > Betreff : Re: Re: Explain Plan and Security
> >
> > If I followed this thread right, there is nothing you can do than
> execute the query. Everything else will generate different results.
> > Is there any chance you get permission to execute the query if you can
> guarantee it only runs for "a very short time"?
> > E.g. a special (proxy) user with a very strict LOGICAL_READS_PER_SESSION
> comes to my mind.
> > Or you add an additional filter with "where 1 =impossible_function" and
> your "impossible_function" does an execute immediate "select 1/0 from dual".
> > More methods come to my mind, but I'm sure you get the idea.
> > The execution-trap can be tested in non-profit environment and so you
> might convince your customer?
> >
> > hth,
> > berx
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
>
> --
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l