Re: Explain Plan and Security
Date: Thu, 14 Jun 2018 16:08:03 +0000
Message-ID: <MM1P123MB0842BCDDC8A9722618247401A57D0_at_MM1P123MB0842.GBRP123.PROD.OUTLOOK.COM>
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
BEGIN
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
Regards
cursor_name INTEGER;
rows_processed INTEGER;
m_desc_table dbms_sql.desc_tab;
m_colcount number;
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_TEXT
911jt1m3dxrba 903671040
select sql_id, plan_hash_value, sql_text from V$sql where sql_text like '%SQL_PARSE TEST BINDwq%'
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
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:
- the query uses bind variables - which can't be peeked and are assumed to be character or
- 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-lReceived on Thu Jun 14 2018 - 18:08:03 CEST