Re: Explain Plan and Security

Ah! Indeed. Thanks for that Jonathan. So, the takeaway is that DBMS_SQL is slightly better than EXPLAIN PLAN. But, only slightly?

Thanks!

Andy K

On Thu, Jun 14, 2018 at 12:08 PM, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> wrote:

>
> Andy,
>
> If you add a "describe columns" to your SQL PARSE TEST BIND you find that
> you do get a plan before the execute.
>
> DECLARE
> cursor_name INTEGER;
> rows_processed INTEGER;
> m_desc_table dbms_sql.desc_tab;
> m_colcount number;
>
> BEGIN
> cursor_name := dbms_sql.open_cursor;
> dbms_sql.parse(cursor_name, 'select /* SQL_PARSE TEST BIND */ * from
> parse_test where n = :n', dbms_sql.NATIVE);
> dbms_sql.bind_variable(cursor_name, ':n', 2);
> dbms_sql.describe_columns( cursor_name, m_colcount, m_desc_table );
> END;
> /
>
> SQL> select sql_id, plan_hash_value, sql_text from V$sql where sql_text
> like '%SQL_PARSE TEST BINDwq%'
> 2 /
>
> SQL_ID PLAN_HASH_VALUE
> ------------- ---------------
> SQL_TEXT
> ------------------------------------------------------------
> ------------------------------------------------------------------------
> 911jt1m3dxrba 903671040
> select sql_id, plan_hash_value, sql_text from V$sql where sql_text like
> '%SQL_PARSE TEST BINDwq%'
>
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: andyklock_at_gmail.com <andyklock_at_gmail.com> on behalf of Andy Klock <
> andy_at_oracledepot.com>
> Sent: 14 June 2018 16:56
> To: mauro.pagano_at_gmail.com
> Cc: Jonathan Lewis; oracle-l_at_freelists.org; l.flatz_at_bluewin.ch
> Subject: Re: Explain Plan and Security
>
> Agreed. It's kind of a cool idea, however, BINDs are checked after the
> DBMS_SQL.PARSE call and is only evaluated after the call to DBMS_SQL.EXECUTE
>
> https://docs.oracle.com/database/121/ARPLS/d_sql.htm#i996870
>
> I ran a quick test to see what shows up in the cursor cache after setting
> bind_variable, but not calling EXECUTE and as expected you don't get a plan
> at all.
>
>
> SQL> DECLARE
> cursor_name INTEGER;
> rows_processed INTEGER;
> BEGIN
> cursor_name := dbms_sql.open_cursor;
> dbms_sql.parse(cursor_name, 'select /* SQL_PARSE TEST */ * from
> parse_test where n = 1', dbms_sql.NATIVE);
> END;
> /
>
> PL/SQL procedure successfully completed.
>
>
> SQL> DECLARE
> cursor_name INTEGER;
> rows_processed INTEGER;
> BEGIN
> cursor_name := dbms_sql.open_cursor;
> dbms_sql.parse(cursor_name, 'select /* SQL_PARSE TEST BIND */ * from
> parse_test where n = :n', dbms_sql.NATIVE);
> dbms_sql.bind_variable(cursor_name, ':n', 2);
> END;
> /
>
> PL/SQL procedure successfully completed.
>
> SQL> DECLARE
> cursor_name INTEGER;
> rows_processed INTEGER;
> BEGIN
> cursor_name := dbms_sql.open_cursor;
> dbms_sql.parse(cursor_name, 'select /* SQL_PARSE TEST BIND 2 */ * from
> parse_test where n = :n', dbms_sql.NATIVE);
> dbms_sql.bind_variable(cursor_name, ':n', 2);
> rows_processed := dbms_sql.execute(cursor_name);
> dbms_sql.close_cursor(cursor_name);
> END;
> /
>
> PL/SQL procedure successfully completed.
>
> SQL> _at_findsq SQL_PARSE
>
> SQL_ID PLAN_HASH_VALUE LENGTH(SQL_FULLTEXT) SUB_TEXT
> EXECUTIONS AVG_ELAPSED
> ------------- --------------- --------------------
> -------------------------------------------------- ---------- -----------
> 4wbhzrjq0k7fd 464636435 57 select /* SQL_PARSE TEST */ * from
> parse_test wher 0 .002671
> 8xbgz1hbjk0vz 0 63 select /* SQL_PARSE TEST BIND */ * from parse_test
> 0 .000467
> 0bavj3vaszvw2 464636435 65 select /* SQL_PARSE TEST BIND 2 */ * from
> parse_te 1 .00503
>
> Final note, if you don't actually execute the SQL then you don't get all
> that other Oracle runtime stuff like cardinality feedback or dynamic
> sampling, etc which adds to even more headaches.
>
> Andy K
>
> On Thu, Jun 14, 2018 at 11:45 AM, Mauro Pagano <mauro.pagano_at_gmail.com<
> mailto:mauro.pagano_at_gmail.com>> wrote:
> 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<mailto: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<mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on
> behalf of l.flatz_at_bluewin.ch<mailto:l.flatz_at_bluewin.ch> <
> l.flatz_at_bluewin.ch<mailto:l.flatz_at_bluewin.ch>>
> Sent: 14 June 2018 13:36:46
> To: oracle-l_at_freelists.org<mailto: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
>
>
>

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