Re: Explain Plan and Security

  • From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
  • To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Thu, 14 Jun 2018 17:52:14 +0000

Well ... dbms_xplan.display_cursor gives you definitively the execution plan 
you just got for your SQL/child. It might not be the plan you get every 
execution under all circumstances but you can’t take away that you got that 
once. You can’t say the same about explain plan. 
If someone is executing a piece of sql, say they are testing / making a change, 
and they want to document the execution plan that they got during their test 
and show that, for one execution at least the plan and performance was ok, then 
dbms_xplan.display_cursor (or getting the same info direct from v$sql_plan) is 
the source for that. That’s what I expect developers to provide plus the 
runtime execution stats.
Ditto for extracting the plan information for any particular child cursor that 
is in memory, display_cursor tells you what it is/was. No doubts.

Sent from my iPhone

On 14 Jun 2018, at 18:40, 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.

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@xxxxxxxxx<mailto:andyklock@xxxxxxxxx
<andyklock@xxxxxxxxx<mailto:andyklock@xxxxxxxxx>> on behalf of Andy Klock 
<andy@xxxxxxxxxxxxxxx<mailto:andy@xxxxxxxxxxxxxxx>>
Sent: 14 June 2018 16:56
To: mauro.pagano@xxxxxxxxx<mailto:mauro.pagano@xxxxxxxxx>
Cc: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>; 
l.flatz@xxxxxxxxxx<mailto:l.flatz@xxxxxxxxxx>
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://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.oracle.com%2Fdatabase%2F121%2FARPLS%2Fd_sql.htm%23i996870&data=02%7C01%7C%7C7f3f2e59790c49647afd08d5d21ddcb7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636645948067016044&sdata=4kyFobAwG5FlvMc2tloVVZpwaeRzJ%2BpMu7JvwWzxJqo%3D&reserved=0

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> @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@xxxxxxxxx<mailto:mauro.pagano@xxxxxxxxx><mailto:mauro.pagano@xxxxxxxxx<mailto:mauro.pagano@xxxxxxxxx>>>
 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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx><mailto:jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>>
 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx><mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>>
 
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx><mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>>>
 on behalf of 
l.flatz@xxxxxxxxxx<mailto:l.flatz@xxxxxxxxxx><mailto:l.flatz@xxxxxxxxxx<mailto:l.flatz@xxxxxxxxxx>>
 
<l.flatz@xxxxxxxxxx<mailto:l.flatz@xxxxxxxxxx><mailto:l.flatz@xxxxxxxxxx<mailto:l.flatz@xxxxxxxxxx>>>
Sent: 14 June 2018 13:36:46
To: 
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx><mailto:oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Subject: Explain Plan and Security

Hi,

you might know Kerry´s classic blog: 
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fkerryosborne.oracle-guy.com%2F2008%2F10%2Fexplain-plan-lies%2F&data=02%7C01%7C%7C7f3f2e59790c49647afd08d5d21ddcb7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636645948067016044&sdata=0MfIBWr%2FbqDjJeSA9AMeySqqeQnBo5odXPC8wxYqthY%3D&reserved=0.
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

--
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=02%7C01%7C%7C7f3f2e59790c49647afd08d5d21ddcb7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636645948067016044&sdata=KWBDk34WKqsWc5xaLQTbAk12iDSDkjI56zoLvoYEppM%3D&reserved=0




--
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=02%7C01%7C%7C7f3f2e59790c49647afd08d5d21ddcb7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636645948067016044&sdata=KWBDk34WKqsWc5xaLQTbAk12iDSDkjI56zoLvoYEppM%3D&reserved=0



--
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=02%7C01%7C%7C7f3f2e59790c49647afd08d5d21ddcb7%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636645948067016044&sdata=KWBDk34WKqsWc5xaLQTbAk12iDSDkjI56zoLvoYEppM%3D&reserved=0


Other related posts: