Re: Explain Plan and Security

From: Jonathan Lewis <>
Date: Sun, 17 Jun 2018 17:02:33 +0000
Message-ID: <MM1P123MB0842704E70BB37CD69A985A3A5720_at_MM1P123MB0842.GBRP123.PROD.OUTLOOK.COM>

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 ) so not relevant in your case.

Jonathan Lewis

From: <> on behalf of Lothar Flatz <> Sent: 17 June 2018 15:55
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


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: <>
> Sent: 16 June 2018 14:28
> To:
> Cc:; 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 :
> Datum : 15/06/2018 - 21:04 (CEST)
> An :
> Cc :,
> 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
> --



Received on Sun Jun 17 2018 - 19:02:33 CEST

Original text of this message