RE: Explain Plan and Security
Date: Thu, 14 Jun 2018 14:54:54 +0000
Message-ID: <258575162B63424EB58DAE3A5475B6ED0130DE8D56_at_EXNJMB25.nam.nsroot.net>
Tepidly I will add this to Jonathan's answer. If you have a procedure call or another sql in the query , e.g. select a , (select sysdate from dual) , pk.comvert_curr(amt) from accounts. The CBO does not always get this one right. It frequently assigns a value of 1 to the cost for it. In this case to get the true cost you need to figure out the subparts and add that to the cost the CBO calculates.
Liz
Elizabeth Reen
CPB Database Group Manager
Service Now Group: CPB-ORACLE-DB-SUPPORT
-----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:
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
Hi,
you might know Kerry´s classic blog: https://urldefense.proofpoint.com/v2/url?u=http-3A__kerryosborne.oracle-2Dguy.com_2008_10_explain-2Dplan-2Dlies_&d=DwIFAw&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=V99bHGQuX_UD6UT1L4jRc9VUQmbwTEHKqfG32pRS77U&s=zJnsoqP6zuaB1cSCYj2Uu9mx3PoEyjRr9ZUzcDgRM1g&e=.
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.
Regards
Lothar
--
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
Before I spent time, has anybody done it already?
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 14 2018 - 16:54:54 CEST