Re: Explain Plan and Security

Fair enough Jonathan. I was just referring to the OP's original concern with the explain plan lying to you (with Kerry's example of it ignoring bind variables). But, to yours and Mauro's point, and admittedly it does take me longer to evenually catch up, it does appear that EXPLAIN_PLAN and DBMS_SQL(PARSE/BIND/DESCIBE) behave similarly in regards to BINDs. Though, I was actually expecting an INDEX scan with my DBMS_SQL.EXECUTE but it still opted for a FULL, so I'd have to dig a little deeper to figure out why.

EXPLAIN PLAN: SQL> var n number
SQL> exec :n:=2

PL/SQL procedure successfully completed.

SQL> explain plan for select /* EXPLAIN PLAN TEST WITH BIND 2 */ * from parse_test where n = :n;

Explained.

SQL> select plan_table_output from table(dbms_xplan.display(' plan_table',null,'basic'));

PLAN_TABLE_OUTPUT

  dbms_sql.bind_variable(cursor_name, ':n', 2);
  rows_processed := dbms_sql.execute(cursor_name);
  dbms_sql.close_cursor(cursor_name);

------------- --------------- --------------------


-------------------------------------------------- ---------- -----------


488vjz6gr04r3     0        369 DECLARE  cursor_name    INTEGER;
rows_processed I        1      .01316
c7s8yq1atczdx     464636435 73 select /* SQL_PARSE TEST BIND EXECUTE 2 */ *
from        1     .003964

|   0 | SELECT STATEMENT  |        | | |     9 (100)| |
|*  1 |  TABLE ACCESS FULL| PARSE_TEST |  12501 |   109K|     9   (0)|

N C
---------- ----------
2 GARCIA SQL> _at_findsq EXECUTE!

SQL_ID PLAN_HASH_VALUE LENGTH(SQL_FULLTEXT) SUB_TEXT EXECUTIONS AVG_ELAPSED

------------- --------------- --------------------


-------------------------------------------------- ---------- -----------


ammqtfmhv5tpk    2679036580 57 select /* EXECUTE! TEST */ * from parse_test
where        1     .008336

SQL> _at_dplan
Enter value for sql_id: ammqtfmhv5tpk
Enter value for child_no:

PLAN_TABLE_OUTPUT

Plan hash value: 2679036580

|   0 | SELECT STATEMENT     |      |       |       |     2 (100)|       |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PARSE_TEST     |     1 |
 9 |     2 (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | PARSE_TEST_IND |     1 |       |     1 (0)|

Thanks,

Andy K

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

>
> Andy,
>
> I don't think I'd even be that generous. I can't think of any detail where
> dbms_sql/dbms_xplan.display_cursor() gives you safer information than
> explain plan / dbms_xplan.display().
> I suppose the extra complexity of using dbms_sql might make you a little
> more careful as you set up a test, and that could be a benefit.
>
> 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 17:15
> To: Jonathan Lewis
> Cc: oracle-l_at_freelists.org
> Subject: 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<mailto: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.
>
>
>

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