RE: Explain Plan and Security

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 14 Jun 2018 10:53:42 -0400
Message-ID: <033e01d403ef$950a1500$bf1e3f00$_at_rsiz.com>


I "olden days" when I programmed using OCI (which then stood for Oracle Call Interface) there was an oparse call. Perhaps I will dust off my C brain cells (although they claimed retirement saying that if 2000 hours is a standard working year they had done their 50 and out long ago.) and discover whether that call still exists.

In theory after oparse comes oexec, so it should be the complete plan in oexec was designed to not have to acquire anything, just run, baby, just run. So if you stop short of oexec, you could completely parse your query and then just not run it. It wouldn't have execution actuals, but everything else should be there.

That actually might not be too hard to set up as an augmented user interface. Maybe we can trick Jeff Smith or Bryn into doing all the work.

I bid" DBMS_PLAN.Really_parse_the sucka('sql_text) as the procedure name and calling sequence.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, June 14, 2018 10:14 AM
To: oracle-l_at_freelists.org; l.flatz_at_bluewin.ch Subject: Re: Explain Plan and Security

As far as I know explain plan will produce a misleading plan only if:

  1. the query uses bind variables - which can't be peeked and are assumed to be character or
  2. 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 Received on Thu Jun 14 2018 - 16:53:42 CEST

Original text of this message