Re: Explain Plan and Security

Lothar,

To add on Jonathan's "odd note", because of 9630032 (disabled by default) you might see an even odder behavior (difference between describe vs exec). Just saying that DBMS_SQL might translate in some headaches :-(

Cheers,
Mauro

On Thu, Jun 14, 2018 at 7:13 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:

>
> As far as I know explain plan will produce a misleading plan only if:
>
> a) the query uses bind variables - which can't be peeked and are assumed
> to be character
> or
> b) the literals used in the explain plan are a bad choice compared to what
> happens in production
> (which includes wrong type, wrong character set, wrong implicit date
> format etc.)
>
> Using dbms_sql won't (necessarily) be any better. If you supply a
> statement with a bind variable in the text the call to dbms_parse will
> assume that it's an unknown varchar - just as explain plan will. This is
> why you sometimes see systems with lots of statements parsed twice per
> execute - the first time was a parse call the that used guesses for bind
> types, the second was with information about the actual bind types.
>
> (I have an odd note from 16 years ago that you don't get the plan on the
> call to dbms_parse, but have to call dbms_describe_colums as well).
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>
> Sent: 14 June 2018 13:36:46
> To: oracle-l_at_freelists.org
> Subject: Explain Plan and Security
>
> Hi,
>
> you might know Kerry´s classic blog: http://kerryosborne.oracle-
> guy.com/2008/10/explain-plan-lies/.
> Normally my work around for explain plan issues is to run the query and
> use dbms_xplan.display_cursor.
> Now I am working in an environment where I must not run a query, but I can
> do explain plan.
> But still I think I can not tolerate explain plan weaknesses.
> I think it should be possble to use DBMS_SQL to parse a statement and
> receive a proper plan without actually running the statement.
> Then use dbms_xplan.display_cursor.
> Before I spent time, has anybody done it already?
>
> Regards
>
> Lothar
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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