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
l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> schrieb am Fr., 15. Juni 2018, 10:20:
Thanks to all for discussing.
Well, actually my case is that we have a sql monitor of a query that went wrong. By analyzing the sql monitor result we have a fair idea what kind of plan we want.
From the monitor we also have the exact bind variable values and can generate a run script.
We can then take some action to fix the issue, like recalculation stats, rewriting the query somewhat.
Of course we want to check if our fix works.
We are not allowed to run the query in production. (The Therefore the next best thing would be good execution plan.)
I tried explain plan, but the bind variables matter and so far I never got a good prediction.
It seems to me the whole matter is a bit more complex than I originally thought. Therefore I really wan´t to ping the usual suspects.
Regards
Lothar
----Ursprüngliche Nachricht----
Von : jonathan_at_jlcomp.demon.co.uk
Datum : 14/06/2018 - 19:56 (GMT)
An : oracle-l_at_freelists.org
Betreff : Re: Explain Plan and Security
Dominic,
There's no question that if the query has executed and you can get there in time then the plan you get from display_cursor() is the plan that actually happened, but we're discussing the point that we can get execution plans into memory (for display_cursor()) to report) that have never executed - which leafs to the point that those are plans that might never actually happen with any real user inputs.
Regards
Jonathan Lewis