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
------------------------------------------------------------
------------------------------------------------------------
------------------------------
Plan hash value: 464636435
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| PARSE_TEST |
----------------------------------------
8 rows selected.
DBMS_SQL.EXECUTE
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 EXECUTE 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> @findsq EXECUTE
SQL_ID PLAN_HASH_VALUE LENGTH(SQL_FULLTEXT) SUB_TEXT EXECUTIONS
AVG_ELAPSED
------------- --------------- --------------------
-------------------------------------------------- ---------- -----------
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
SQL> @dplan
Enter value for sql_id: c7s8yq1atczdx
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------
-----------------------------------------------------------------
SQL_ID c7s8yq1atczdx, child number 0
-------------------------------------
select /* SQL_PARSE TEST BIND EXECUTE 2 */ * from parse_test where n =
:n
Plan hash value: 464636435
------------------------------------------------------------
---------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------
---------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
|* 1 | TABLE ACCESS FULL| PARSE_TEST | 12501 | 109K| 9 (0)|
00:00:01 |
------------------------------------------------------------
---------------------
EXECUTING:
SQL> select /* EXECUTE! TEST */ * from parse_test where n = :n;
N C
---------- ----------
2 GARCIA
SQL> @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> @dplan
Enter value for sql_id: ammqtfmhv5tpk
Enter value for child_no:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ammqtfmhv5tpk, child number 0
-------------------------------------
select /* EXECUTE! TEST */ * from parse_test where n = :n
Plan hash value: 2679036580
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------------
| 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)|
00:00:01 |
-------------------------------------------------------------------------------------------------------
Thanks,
Andy K
On Thu, Jun 14, 2018 at 1:01 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx
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@xxxxxxxxx <andyklock@xxxxxxxxx> on behalf of Andy Klock <
andy@xxxxxxxxxxxxxxx>
Sent: 14 June 2018 17:15
To: Jonathan Lewis
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> 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.
