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@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Lothar Flatz <l.flatz@xxxxxxxxxx>
Sent: 17 June 2018 15:55
To: oracle-l@xxxxxxxxxxxxx
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:
could model and run the query to see if you can get the plan you want
Lothar,
Can you export the object definitions and object stats ? If so then you
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.
stats instead of the segment HWM - but off the top of my head I can't give
You may have to set a couple of events to tell Oracle to use the object
you an immediate description of exactly what you'd have to do for that bit.
not ours. We are by policy forbitten to run queries on prod other than
Regards
Jonathan Lewis
________________________________________
From: l.flatz@xxxxxxxxxx <l.flatz@xxxxxxxxxx>
Sent: 16 June 2018 14:28
To: martin.a.berger@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; 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
against the dictionary.
We must look for an other way. Maybe some instant clone would work.execute the query. Everything else will generate different results.
Thanks
Lothar
----Ursprüngliche Nachricht----
Von : martin.a.berger@xxxxxxxxx
Datum : 15/06/2018 - 21:04 (CEST)
An : l.flatz@xxxxxxxxxx
Cc : jonathan@xxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Betreff : Re: Re: Explain Plan and Security
If I followed this thread right, there is nothing you can do than
Is there any chance you get permission to execute the query if you canguarantee it only runs for "a very short time"?
E.g. a special (proxy) user with a very strict LOGICAL_READS_PER_SESSIONcomes to my mind.
Or you add an additional filter with "where 1 =impossible_function" andyour "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.might convince your customer?
The execution-trap can be tested in non-profit environment and so you
hth,
berx
--
//www.freelists.org/webpage/oracle-l
--
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l