Explain Plan and Security

Discussion:

l***@bluewin.ch

2018-06-14 12:36:46 UTC

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

Jonathan Lewis

2018-06-14 14:13:35 UTC

Permalink

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-***@freelists.org <oracle-l-***@freelists.org> on behalf of ***@bluewin.ch <***@bluewin.ch>
Sent: 14 June 2018 13:36:46
To: oracle-***@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

Mark W. Farnham

2018-06-14 14:53:42 UTC

Permalink

I "olden days" when I programmed using OCI (which then stood for Oracle Call
Interface) there was an oparse call. Perhaps I will dust off my C brain
cells (although they claimed retirement saying that if 2000 hours is a
standard working year they had done their 50 and out long ago.) and discover
whether that call still exists.

In theory after oparse comes oexec, so it should be the complete plan in
oexec was designed to not have to acquire anything, just run, baby, just
run. So if you stop short of oexec, you could completely parse your query
and then just not run it. It wouldn't have execution actuals, but everything
else should be there.

That actually might not be too hard to set up as an augmented user
interface. Maybe we can trick Jeff Smith or Bryn into doing all the work.

I bid" DBMS_PLAN.Really_parse_the sucka('sql_text) as the procedure name and
calling sequence.

mwf

-----Original Message-----
From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org]
On Behalf Of Jonathan Lewis
Sent: Thursday, June 14, 2018 10:14 AM
To: oracle-***@freelists.org; ***@bluewin.ch
Subject: Re: Explain Plan and Security

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-***@freelists.org <oracle-l-***@freelists.org> on
behalf of ***@bluewin.ch <***@bluewin.ch>
Sent: 14 June 2018 13:36:46
To: oracle-***@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

"Reen, Elizabeth " (Redacted sender "elizabeth.reen" for DMARC)

2018-06-14 14:54:54 UTC

Permalink

Tepidly I will add this to Jonathan's answer. If you have a procedure call or another sql in the query , e.g. select a , (select sysdate from dual) , pk.comvert_curr(amt) from accounts. The CBO does not always get this one right. It frequently assigns a value of 1 to the cost for it. In this case to get the true cost you need to figure out the subparts and add that to the cost the CBO calculates.

Liz

Elizabeth Reen
CPB Database Group Manager
Service Now Group: CPB-ORACLE-DB-SUPPORT

-----Original Message-----
From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, June 14, 2018 10:14 AM
To: oracle-***@freelists.org; ***@bluewin.ch
Subject: Re: Explain Plan and Security

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-***@freelists.org <oracle-l-***@freelists.org> on behalf of ***@bluewin.ch <***@bluewin.ch>
Sent: 14 June 2018 13:36:46
To: oracle-***@freelists.org
Subject: Explain Plan and Security

Hi,

you might know Kerry´s classic blog: https://urldefense.proofpoint.com/v2/url?u=http-3A__kerryosborne.oracle-2Dguy.com_2008_10_explain-2Dplan-2Dlies_&d=DwIFAw&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=V99bHGQuX_UD6UT1L4jRc9VUQmbwTEHKqfG32pRS77U&s=zJnsoqP6zuaB1cSCYj2Uu9mx3PoEyjRr9ZUzcDgRM1g&e=.
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://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwIFAw&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=V99bHGQuX_UD6UT1L4jRc9VUQmbwTEHKqfG32pRS77U&s=I1Kmly9EkX2murjMAhkgj5oYP5E8jk9aaI746UvoQMg&e=

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

Mauro Pagano

2018-06-14 15:45:51 UTC

Permalink

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

Post by Jonathan Lewis
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
________________________________________
Sent: 14 June 2018 13:36:46
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

Andy Klock

2018-06-14 15:56:38 UTC

Permalink

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> @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

Post by Mauro Pagano
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 <

Post by Jonathan Lewis
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
________________________________________
Sent: 14 June 2018 13:36:46
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

Jonathan Lewis

2018-06-14 16:08:03 UTC

Permalink

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: ***@gmail.com <***@gmail.com> on behalf of Andy Klock <***@oracledepot.com>
Sent: 14 June 2018 16:56
To: ***@gmail.com
Cc: Jonathan Lewis; oracle-***@freelists.org; ***@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> @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 <***@gmail.com<mailto:***@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 <***@jlcomp.demon.co.uk<mailto:***@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-***@freelists.org<mailto:oracle-l-***@freelists.org> <oracle-l-***@freelists.org<mailto:oracle-l-***@freelists.org>> on behalf of ***@bluewin.ch<mailto:***@bluewin.ch> <***@bluewin.ch<mailto:***@bluewin.ch>>
Sent: 14 June 2018 13:36:46
To: oracle-***@freelists.org<mailto:oracle-***@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

Andy Klock

2018-06-14 16:15:59 UTC

Permalink

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 <

Post by Jonathan Lewis
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
________________________________________
Sent: 14 June 2018 16:56
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_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
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 <
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
________________________________________
Sent: 14 June 2018 13:36:46
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

Jonathan Lewis

2018-06-14 17:01:27 UTC

Permalink

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: ***@gmail.com <***@gmail.com> on behalf of Andy Klock <***@oracledepot.com>
Sent: 14 June 2018 17:15
To: Jonathan Lewis
Cc: oracle-***@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 <***@jlcomp.demon.co.uk<mailto:***@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: ***@gmail.com<mailto:***@gmail.com> <***@gmail.com<mailto:***@gmail.com>> on behalf of Andy Klock <***@oracledepot.com<mailto:***@oracledepot.com>>
Sent: 14 June 2018 16:56
To: ***@gmail.com<mailto:***@gmail.com>
Cc: Jonathan Lewis; oracle-***@freelists.org<mailto:oracle-***@freelists.org>; ***@bluewin.ch<mailto:***@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> @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 <***@gmail.com<mailto:***@gmail.com><mailto:***@gmail.com<mailto:***@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 <***@jlcomp.demon.co.uk<mailto:***@jlcomp.demon.co.uk><mailto:***@jlcomp.demon.co.uk<mailto:***@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-***@freelists.org<mailto:oracle-l-***@freelists.org><mailto:oracle-l-***@freelists.org<mailto:oracle-l-***@freelists.org>> <oracle-l-***@freelists.org<mailto:oracle-l-***@freelists.org><mailto:oracle-l-***@freelists.org<mailto:oracle-l-***@freelists.org>>> on behalf of ***@bluewin.ch<mailto:***@bluewin.ch><mailto:***@bluewin.ch<mailto:***@bluewin.ch>> <***@bluewin.ch<mailto:***@bluewin.ch><mailto:***@bluewin.ch<mailto:***@bluewin.ch>>>
Sent: 14 June 2018 13:36:46
To: oracle-***@freelists.org<mailto:oracle-***@freelists.org><mailto:oracle-***@freelists.org<mailto:oracle-***@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

Andy Sayer

2018-06-14 17:28:56 UTC

Permalink

No matter what you do, without executing the statement you wonât get any
statistics feedback (of which there are now many flavours). This can make
or break some queries.

Andy Klock

2018-06-14 17:37:57 UTC

Permalink

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

Dominic Brooks

2018-06-14 17:52:14 UTC

Permalink

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

Post by Jonathan Lewis
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
________________________________________
Sent: 14 June 2018 17:15
To: Jonathan Lewis
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
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
________________________________________
Sent: 14 June 2018 16:56
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_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
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
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
________________________________________
Sent: 14 June 2018 13:36:46
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

Jonathan Lewis

2018-06-14 17:56:09 UTC

Permalink

Dominic,

There's no question that if the query has executed and you can get there in time then the plan you get from display_cursor() is the plan that actually happened, but we're discussing the point that we can get execution plans into memory (for display_cursor()) to report) that have never executed - which leafs to the point that those are plans that might never actually happen with any real user inputs.

Regards
Jonathan Lewis

________________________________________
From: Dominic Brooks <***@hotmail.com>
Sent: 14 June 2018 18:52
To: Jonathan Lewis
Cc: oracle-***@freelists.org
Subject: Re: Explain Plan and Security

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

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

Dominic Brooks

2018-06-14 18:59:57 UTC

Permalink

Fair enough :)

Sent from my iPhone

Post by Jonathan Lewis
Dominic,
There's no question that if the query has executed and you can get there in time then the plan you get from display_cursor() is the plan that actually happened, but we're discussing the point that we can get execution plans into memory (for display_cursor()) to report) that have never executed - which leafs to the point that those are plans that might never actually happen with any real user inputs.
Regards
Jonathan Lewis
________________________________________
Sent: 14 June 2018 18:52
To: Jonathan Lewis
Subject: Re: Explain Plan and Security
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

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

l***@bluewin.ch

2018-06-15 08:18:39 UTC

Permalink

Thanks to all for discussing.
Well, actually my case is that we have a sql monitor of a query that went wrong. By analyzing the sql monitor result we have a fair idea what kind of plan we want.
From the monitor we also have the exact bind variable values and can generate a run script.
We can then take some action to fix the issue, like recalculation stats, rewriting the query somewhat.
Of course we want to check if our fix works.
We are not allowed to run the query in production. (The Therefore the next best thing would be good execution plan.)
I tried explain plan, but the bind variables matter and so far I never got a good prediction.

It seems to me the whole matter is a bit more complex than I originally thought. Therefore I really wan´t to ping the usual suspects.

Regards

Lothar

----Ursprüngliche Nachricht----
Von : ***@jlcomp.demon.co.uk
Datum : 14/06/2018 - 19:56 (GMT)
An : oracle-***@freelists.org
Betreff : Re: Explain Plan and Security

Dominic,

There's no question that if the query has executed and you can get there in time then the plan you get from display_cursor() is the plan that actually happened, but we're discussing the point that we can get execution plans into memory (for display_cursor()) to report) that have never executed - which leafs to the point that those are plans that might never actually happen with any real user inputs.

Regards
Jonathan Lewis

________________________________________
From: Dominic Brooks <***@hotmail.com>
Sent: 14 June 2018 18:52
To: Jonathan Lewis
Cc: oracle-***@freelists.org
Subject: Re: Explain Plan and Security

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

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

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

Martin Berger

2018-06-15 19:04:38 UTC

Permalink

If I followed this thread right, there is nothing you can do than execute
the query. Everything else will generate different results.
Is there any chance you get permission to execute the query if you can
guarantee it only runs for "a very short time"?
E.g. a special (proxy) user with a very strict LOGICAL_READS_PER_SESSION
comes to my mind.
Or you add an additional filter with "where 1 =impossible_function" and
your "impossible_function" does an execute immediate "select 1/0 from dual".
More methods come to my mind, but I'm sure you get the idea.
The execution-trap can be tested in non-profit environment and so you might
convince your customer?

hth,
berx

Post by l***@bluewin.ch
Thanks to all for discussing.
Well, actually my case is that we have a sql monitor of a query that went
wrong. By analyzing the sql monitor result we have a fair idea what kind of
plan we want.
From the monitor we also have the exact bind variable values and can generate a run script.
We can then take some action to fix the issue, like recalculation stats,
rewriting the query somewhat.
Of course we want to check if our fix works.
We are not allowed to run the query in production. (The Therefore the next
best thing would be good execution plan.)
I tried explain plan, but the bind variables matter and so far I never got
a good prediction.
It seems to me the whole matter is a bit more complex than I originally
thought. Therefore I really wanÂŽt to ping the usual suspects.
Regards
Lothar
----UrsprÃŒngliche Nachricht----
Datum : 14/06/2018 - 19:56 (GMT)
Betreff : Re: Explain Plan and Security
Dominic,
There's no question that if the query has executed and you can get there
in time then the plan you get from display_cursor() is the plan that
actually happened, but we're discussing the point that we can get execution
plans into memory (for display_cursor()) to report) that have never
executed - which leafs to the point that those are plans that might never
actually happen with any real user inputs.
Regards
Jonathan Lewis
________________________________________
Sent: 14 June 2018 18:52
To: Jonathan Lewis
Subject: Re: Explain Plan and Security
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

Post by Jonathan Lewis
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().

Post by Jonathan Lewis
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.

Post by Jonathan Lewis
Regards
Jonathan Lewis
________________________________________
Sent: 14 June 2018 17:15
To: Jonathan Lewis
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?

Post by Jonathan Lewis
Thanks!
Andy K
On Thu, Jun 14, 2018 at 12:08 PM, Jonathan Lewis <
Andy,
If you add a "describe columns" to your SQL PARSE TEST BIND you find

that you do get a plan before the execute.

Post by Jonathan Lewis
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);

Post by Jonathan Lewis
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%'

Post by Jonathan Lewis
2 /
SQL_ID PLAN_HASH_VALUE
------------- ---------------
SQL_TEXT

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

Post by Jonathan Lewis
911jt1m3dxrba 903671040
select sql_id, plan_hash_value, sql_text from V$sql where sql_text like

'%SQL_PARSE TEST BINDwq%'

Post by Jonathan Lewis
Regards
Jonathan Lewis
________________________________________
Sent: 14 June 2018 16:56
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

Post by Jonathan Lewis
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.

Post by Jonathan Lewis
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);

Post by Jonathan Lewis
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);

Post by Jonathan Lewis
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);

Post by Jonathan Lewis
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_ID PLAN_HASH_VALUE LENGTH(SQL_FULLTEXT) SUB_TEXT

EXECUTIONS AVG_ELAPSED

Post by Jonathan Lewis
------------- --------------- --------------------

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

Post by Jonathan Lewis
4wbhzrjq0k7fd 464636435 57 select /* SQL_PARSE TEST */ * from

parse_test wher 0 .002671

Post by Jonathan Lewis
8xbgz1hbjk0vz 0 63 select /* SQL_PARSE TEST BIND */ * from

parse_test 0 .000467

Post by Jonathan Lewis
0bavj3vaszvw2 464636435 65 select /* SQL_PARSE TEST BIND 2 */ * from

parse_te 1 .00503

Post by Jonathan Lewis
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.

Post by Jonathan Lewis
Andy K
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).

Post by Jonathan Lewis
Just saying that DBMS_SQL might translate in some headaches :-(
Cheers,
Mauro
On Thu, Jun 14, 2018 at 7:13 AM, Jonathan Lewis <
a) the query uses bind variables - which can't be peeked and are assumed

to be character

Post by Jonathan Lewis
or
b) the literals used in the explain plan are a bad choice compared to

what happens in production

Post by Jonathan Lewis
(which includes wrong type, wrong character set, wrong implicit date

format etc.)

Post by Jonathan Lewis
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.

Post by Jonathan Lewis
(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).

Post by Jonathan Lewis
Regards
Jonathan Lewis
________________________________________
Sent: 14 June 2018 13:36:46
Subject: Explain Plan and Security
Hi,

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
.

Post by Jonathan Lewis
Normally my work around for explain plan issues is to run the query and

use dbms_xplan.display_cursor.

Post by Jonathan Lewis
Now I am working in an environment where I must not run a query, but I

can do explain plan.

Post by Jonathan Lewis
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.

Post by Jonathan Lewis
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

Post by Jonathan Lewis
--

l***@bluewin.ch

2018-06-16 13:28:11 UTC

Permalink

Hi Martin,
that would not work. I am working for a service provider. The data is not ours. We are by policy forbitten to run queries on prod other than against the dictionary.
We must look for an other way. Maybe some instant clone would work.
Thanks
Lothar
----UrsprÃŒngliche Nachricht----
Von : ***@gmail.com
Datum : 15/06/2018 - 21:04 (CEST)
An : ***@bluewin.ch
Cc : ***@jlcomp.demon.co.uk, oracle-***@freelists.org
Betreff : Re: Re: Explain Plan and Security
If I followed this thread right, there is nothing you can do than execute the query. Everything else will generate different results.
Is there any chance you get permission to execute the query if you can guarantee it only runs for "a very short time"?
E.g. a special (proxy) user with a very strict LOGICAL_READS_PER_SESSION comes to my mind.
Or you add an additional filter with "where 1 =impossible_function" and your "impossible_function" does an execute immediate "select 1/0 from dual".
More methods come to my mind, but I'm sure you get the idea.
The execution-trap can be tested in non-profit environment and so you might convince your customer?
hth,
berx
***@bluewin.ch <***@bluewin.ch> schrieb am Fr., 15. Juni 2018, 10:20:
Thanks to all for discussing.
Well, actually my case is that we have a sql monitor of a query that went wrong. By analyzing the sql monitor result we have a fair idea what kind of plan we want.
From the monitor we also have the exact bind variable values and can generate a run script.
We can then take some action to fix the issue, like recalculation stats, rewriting the query somewhat.
Of course we want to check if our fix works.
We are not allowed to run the query in production. (The Therefore the next best thing would be good execution plan.)
I tried explain plan, but the bind variables matter and so far I never got a good prediction.
It seems to me the whole matter is a bit more complex than I originally thought. Therefore I really wanÂŽt to ping the usual suspects.
Regards
Lothar
----UrsprÃŒngliche Nachricht----
Von : ***@jlcomp.demon.co.uk
Datum : 14/06/2018 - 19:56 (GMT)
An : oracle-***@freelists.org
Betreff : Re: Explain Plan and Security
Dominic,
There's no question that if the query has executed and you can get there in time then the plan you get from display_cursor() is the plan that actually happened, but we're discussing the point that we can get execution plans into memory (for display_cursor()) to report) that have never executed - which leafs to the point that those are plans that might never actually happen with any real user inputs.
Regards
Jonathan Lewis
________________________________________
From: Dominic Brooks <***@hotmail.com>
Sent: 14 June 2018 18:52
To: Jonathan Lewis
Cc: oracle-***@freelists.org
Subject: Re: Explain Plan and Security
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

Post by Jonathan Lewis
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
________________________________________
Sent: 14 June 2018 17:15
To: Jonathan Lewis
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
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
________________________________________
Sent: 14 June 2018 16:56
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_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
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
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
________________________________________
Sent: 14 June 2018 13:36:46
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

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

Stefan Koehler

2018-06-17 10:28:11 UTC

Permalink

Hello Lothar,
here we go with a (admittedly geeky) solution which is based on Oracle's kernel low-level debug & diagnostics infrastructure.

Set an event with a scope on the specific SQL-ID (if you already have it otherwise without a scope but for your own session only), a filter on the specific call stack that only occurs at the start of fetching data (e.g. opifch*() -> ....) and finally an action to debug the process (e.g. suspend / abort) with an external system debugger.

The syntax of this event might need a few test cycles but then it should work like a charm :-)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de

Post by l***@bluewin.ch
Hi Martin,

that would not work. I am working for a service provider. The data is not ours. We are by policy forbitten to run queries on prod other than against the dictionary.
We must look for an other way. Maybe some instant clone would work.

Thanks

Lothar

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

Jonathan Lewis

2018-06-17 11:43:09 UTC

Permalink

Lothar,

Can you export the object definitions and object stats ? If so then you could model and run the query to see if you can get the plan you want against the production stats without seeing any production data (except the low, high and histogram values). The rowsource execution stats would be meaningless, of course, but the plan should match if you've got a matching environment.

You may have to set a couple of events to tell Oracle to use the object stats instead of the segment HWM - but off the top of my head I can't give you an immediate description of exactly what you'd have to do for that bit.

Regards
Jonathan Lewis

________________________________________
From: ***@bluewin.ch <***@bluewin.ch>
Sent: 16 June 2018 14:28
To: ***@gmail.com
Cc: oracle-***@freelists.org; Jonathan Lewis
Subject: Re: Re: Re: Explain Plan and Security

Hi Martin,

that would not work. I am working for a service provider. The data is not ours. We are by policy forbitten to run queries on prod other than against the dictionary.
We must look for an other way. Maybe some instant clone would work.

Thanks

Lothar
----Ursprüngliche Nachricht----
Von : ***@gmail.com
Datum : 15/06/2018 - 21:04 (CEST)
An : ***@bluewin.ch
Cc : ***@jlcomp.demon.co.uk, oracle-***@freelists.org
Betreff : Re: Re: Explain Plan and Security

If I followed this thread right, there is nothing you can do than execute the query. Everything else will generate different results.
Is there any chance you get permission to execute the query if you can guarantee it only runs for "a very short time"?
E.g. a special (proxy) user with a very strict LOGICAL_READS_PER_SESSION comes to my mind.
Or you add an additional filter with "where 1 =impossible_function" and your "impossible_function" does an execute immediate "select 1/0 from dual".
More methods come to my mind, but I'm sure you get the idea.
The execution-trap can be tested in non-profit environment and so you might convince your customer?

hth,
berx
--
http://www.freelists.org/webpage/oracle-l

Lothar Flatz

2018-06-17 14:55:00 UTC

Permalink

Hi Jonathan,

yes, we are currently thinking of such a solution. Would be great if you
could dig up the events.
An other approach would be a thin clone. But that would actually just
circumvent the words of the security statement, but violate the meaning.

Regards and thanks

Lothar

Post by Mauro Pagano
Lothar,
Can you export the object definitions and object stats ? If so then you could model and run the query to see if you can get the plan you want against the production stats without seeing any production data (except the low, high and histogram values). The rowsource execution stats would be meaningless, of course, but the plan should match if you've got a matching environment.
You may have to set a couple of events to tell Oracle to use the object stats instead of the segment HWM - but off the top of my head I can't give you an immediate description of exactly what you'd have to do for that bit.
Regards
Jonathan Lewis
________________________________________
Sent: 16 June 2018 14:28
Subject: Re: Re: Re: Explain Plan and Security
Hi Martin,
that would not work. I am working for a service provider. The data is not ours. We are by policy forbitten to run queries on prod other than against the dictionary.
We must look for an other way. Maybe some instant clone would work.
Thanks
Lothar
----Ursprüngliche Nachricht----
Datum : 15/06/2018 - 21:04 (CEST)
Betreff : Re: Re: Explain Plan and Security
If I followed this thread right, there is nothing you can do than execute the query. Everything else will generate different results.
Is there any chance you get permission to execute the query if you can guarantee it only runs for "a very short time"?
E.g. a special (proxy) user with a very strict LOGICAL_READS_PER_SESSION comes to my mind.
Or you add an additional filter with "where 1 =impossible_function" and your "impossible_function" does an execute immediate "select 1/0 from dual".
More methods come to my mind, but I'm sure you get the idea.
The execution-trap can be tested in non-profit environment and so you might convince your customer?
hth,
berx
--
http://www.freelists.org/webpage/oracle-l

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

Jonathan Lewis

2018-06-17 17:02:33 UTC

Permalink

It took a bit of time to find because I'd remembered it incorrectly.

The thing I had in mind was about the decision to use (or not) serial direct path reads for segment scans. It used to be based on the segment HWM, but changed to the object level stats in 11.2, with a controlling parameter (Tanel has a note https://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/ ) so not relevant in your case.

Regards
Jonathan Lewis

________________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on behalf of Lothar Flatz <***@bluewin.ch>
Sent: 17 June 2018 15:55
To: oracle-***@freelists.org
Subject: Re: Explain Plan and Security

Hi Jonathan,

yes, we are currently thinking of such a solution. Would be great if you
could dig up the events.
An other approach would be a thin clone. But that would actually just
circumvent the words of the security statement, but violate the meaning.

Regards and thanks

Lothar

--

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

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

Martin Berger

2018-06-17 18:21:05 UTC

Permalink

Lothar,

Please also disable dynamic sampling.
If the optimized thinks your stats are suspicious (and they are in such
settings)dynamic sampling is a source of joy and happiness.

Martin

Post by Jonathan Lewis
It took a bit of time to find because I'd remembered it incorrectly.
The thing I had in mind was about the decision to use (or not) serial
direct path reads for segment scans. It used to be based on the segment
HWM, but changed to the object level stats in 11.2, with a controlling
parameter (Tanel has a note
https://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/
) so not relevant in your case.
Regards
Jonathan Lewis
________________________________________
Sent: 17 June 2018 15:55
Subject: Re: Explain Plan and Security
Hi Jonathan,
yes, we are currently thinking of such a solution. Would be great if you
could dig up the events.
An other approach would be a thin clone. But that would actually just
circumvent the words of the security statement, but violate the meaning.
Regards and thanks
Lothar

Post by Mauro Pagano
Lothar,
Can you export the object definitions and object stats ? If so then you

could model and run the query to see if you can get the plan you want
against the production stats without seeing any production data (except the
low, high and histogram values). The rowsource execution stats would be
meaningless, of course, but the plan should match if you've got a matching
environment.

Post by Mauro Pagano
You may have to set a couple of events to tell Oracle to use the object

stats instead of the segment HWM - but off the top of my head I can't give
you an immediate description of exactly what you'd have to do for that bit.

Post by Mauro Pagano
Regards
Jonathan Lewis
________________________________________
Sent: 16 June 2018 14:28
Subject: Re: Re: Re: Explain Plan and Security
Hi Martin,
that would not work. I am working for a service provider. The data is

not ours. We are by policy forbitten to run queries on prod other than
against the dictionary.

Post by Mauro Pagano
We must look for an other way. Maybe some instant clone would work.
Thanks
Lothar
----UrsprÃŒngliche Nachricht----
Datum : 15/06/2018 - 21:04 (CEST)
Betreff : Re: Re: Explain Plan and Security
If I followed this thread right, there is nothing you can do than

execute the query. Everything else will generate different results.

Post by Mauro Pagano
Is there any chance you get permission to execute the query if you can

guarantee it only runs for "a very short time"?

Post by Mauro Pagano
E.g. a special (proxy) user with a very strict LOGICAL_READS_PER_SESSION

comes to my mind.

Post by Mauro Pagano
Or you add an additional filter with "where 1 =impossible_function" and

your "impossible_function" does an execute immediate "select 1/0 from dual".

Post by Mauro Pagano
More methods come to my mind, but I'm sure you get the idea.
The execution-trap can be tested in non-profit environment and so you

might convince your customer?

Post by Mauro Pagano
hth,
berx
--
http://www.freelists.org/webpage/oracle-l

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

"Reen, Elizabeth " (Redacted sender "elizabeth.reen" for DMARC)

2018-06-18 18:20:54 UTC

Permalink

The question is why are you forbidden to run the query. If it is for performance reasons, a clone would work. If it is because you cannot see the data, then you would be forbidden to clone. Redaction would be your best answer here.

Liz

Elizabeth Reen
CPB Database Group Manager

Service Now Group: CPB-ORACLE-DB-SUPPORT

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of ***@bluewin.ch
Sent: Saturday, June 16, 2018 9:28 AM
To: ***@gmail.com
Cc: oracle-***@freelists.org; ***@jlcomp.demon.co.uk
Subject: Re: Re: Re: Explain Plan and Security

Hi Martin,

that would not work. I am working for a service provider. The data is not ours. We are by policy forbitten to run queries on prod other than against the dictionary.
We must look for an other way. Maybe some instant clone would work.

Thanks

Lothar
----UrsprÃŒngliche Nachricht----
Von : ***@gmail.com
Datum : 15/06/2018 - 21:04 (CEST)
An : ***@bluewin.ch
Cc : ***@jlcomp.demon.co.uk, oracle-***@freelists.org
Betreff : Re: Re: Explain Plan and Security
If I followed this thread right, there is nothing you can do than execute the query. Everything else will generate different results.
Is there any chance you get permission to execute the query if you can guarantee it only runs for "a very short time"?
E.g. a special (proxy) user with a very strict LOGICAL_READS_PER_SESSION comes to my mind.
Or you add an additional filter with "where 1 =impossible_function" and your "impossible_function" does an execute immediate "select 1/0 from dual".
More methods come to my mind, but I'm sure you get the idea.
The execution-trap can be tested in non-profit environment and so you might convince your customer?

hth,
berx

***@bluewin.ch<mailto:***@bluewin.ch> <***@bluewin.ch<mailto:***@bluewin.ch>> schrieb am Fr., 15. Juni 2018, 10:20:
Thanks to all for discussing.
Well, actually my case is that we have a sql monitor of a query that went wrong. By analyzing the sql monitor result we have a fair idea what kind of plan we want.
From the monitor we also have the exact bind variable values and can generate a run script.
We can then take some action to fix the issue, like recalculation stats, rewriting the query somewhat.
Of course we want to check if our fix works.
We are not allowed to run the query in production. (The Therefore the next best thing would be good execution plan.)
I tried explain plan, but the bind variables matter and so far I never got a good prediction.

It seems to me the whole matter is a bit more complex than I originally thought. Therefore I really wanÂŽt to ping the usual suspects.

Regards

Lothar

----UrsprÃŒngliche Nachricht----
Von : ***@jlcomp.demon.co.uk<mailto:***@jlcomp.demon.co.uk>
Datum : 14/06/2018 - 19:56 (GMT)
An : oracle-***@freelists.org<mailto:oracle-***@freelists.org>
Betreff : Re: Explain Plan and Security

Dominic,

There's no question that if the query has executed and you can get there in time then the plan you get from display_cursor() is the plan that actually happened, but we're discussing the point that we can get execution plans into memory (for display_cursor()) to report) that have never executed - which leafs to the point that those are plans that might never actually happen with any real user inputs.

Regards
Jonathan Lewis

________________________________________
From: Dominic Brooks <***@hotmail.com<mailto:***@hotmail.com>>
Sent: 14 June 2018 18:52
To: Jonathan Lewis
Cc: oracle-***@freelists.org<mailto:oracle-***@freelists.org>
Subject: Re: Explain Plan and Security

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

Post by Jonathan Lewis
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
________________________________________
Sent: 14 June 2018 17:15
To: Jonathan Lewis
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
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
________________________________________
Sent: 14 June 2018 16:56
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<https://urldefense.proofpoint.com/v2/url?u=https-3A__nam01.safelinks.protection.outlook.com_-3Furl-3Dhttps-253A-252F-252Fdocs.oracle.com-252Fdatabase-252F121-252FARPLS-252Fd-5Fsql.htm-2523i996870-26data-3D02-257C01-257C-257C7f3f2e59790c49647afd08d5d21ddcb7-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636645948067016044-26sdata-3D4kyFobAwG5FlvMc2tloVVZpwaeRzJ-252BpMu7JvwWzxJqo-253D-26reserved-3D0&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=Xtymk_Z9jTZ3uDcnPmCpcAqtFyB1htXtnIWV4HLqsTE&e=>
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_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
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
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
________________________________________
Sent: 14 June 2018 13:36:46
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<https://urldefense.proofpoint.com/v2/url?u=https-3A__nam01.safelinks.protection.outlook.com_-3Furl-3Dhttp-253A-252F-252Fkerryosborne.oracle-2Dguy.com-252F2008-252F10-252Fexplain-2Dplan-2Dlies-252F-26data-3D02-257C01-257C-257C7f3f2e59790c49647afd08d5d21ddcb7-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636645948067016044-26sdata-3D0MfIBWr-252FbqDjJeSA9AMeySqqeQnBo5odXPC8wxYqthY-253D-26reserved-3D0&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=7kU20QE6TUDd0HLzcuvTmCpfIwtd79YO45g0y_fF8uE&e=>.
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://urldefense.proofpoint.com/v2/url?u=https-3A__nam01.safelinks.protection.outlook.com_-3Furl-3Dhttp-253A-252F-252Fwww.freelists.org-252Fwebpage-252Foracle-2Dl-26data-3D02-257C01-257C-257C7f3f2e59790c49647afd08d5d21ddcb7-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636645948067016044-26sdata-3DKWBDk34WKqsWc5xaLQTbAk12iDSDkjI56zoLvoYEppM-253D-26reserved-3D0&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=9tvmppZFlmQ_8yJUIpRtxKiQ0e812OOVne1pteLcejU&e=>
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__nam01.safelinks.protection.outlook.com_-3Furl-3Dhttp-253A-252F-252Fwww.freelists.org-252Fwebpage-252Foracle-2Dl-26data-3D02-257C01-257C-257C7f3f2e59790c49647afd08d5d21ddcb7-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636645948067016044-26sdata-3DKWBDk34WKqsWc5xaLQTbAk12iDSDkjI56zoLvoYEppM-253D-26reserved-3D0&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=9tvmppZFlmQ_8yJUIpRtxKiQ0e812OOVne1pteLcejU&e=>
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__nam01.safelinks.protection.outlook.com_-3Furl-3Dhttp-253A-252F-252Fwww.freelists.org-252Fwebpage-252Foracle-2Dl-26data-3D02-257C01-257C-257C7f3f2e59790c49647afd08d5d21ddcb7-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636645948067016044-26sdata-3DKWBDk34WKqsWc5xaLQTbAk12iDSDkjI56zoLvoYEppM-253D-26reserved-3D0&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=9tvmppZFlmQ_8yJUIpRtxKiQ0e812OOVne1pteLcejU&e=>

--
http://www.freelists.org/webpage/oracle-l<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=TsT7J8kmJw7z9aG5NObGMm-aMRmLXH141ia3WZ7AlWc&e=>
--
http://www.freelists.org/webpage/oracle-l<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=TsT7J8kmJw7z9aG5NObGMm-aMRmLXH141ia3WZ7AlWc&e=>

l***@bluewin.ch

2018-06-19 07:59:56 UTC

Permalink

Hi Liz,
it is because we must not see the data. Database vault is in place.
Regards
Lothar
----UrsprÃŒngliche Nachricht----
Von : ***@citi.com
Datum : 18/06/2018 - 20:20 (GMT)
An : ***@bluewin.ch, ***@gmail.com
Cc : oracle-***@freelists.org, ***@jlcomp.demon.co.uk
Betreff : RE: Re: Re: Explain Plan and Security
The question is why are you forbidden to run the query. If it is for performance reasons, a clone would work. If it is because you cannot
see the data, then you would be forbidden to clone. Redaction would be your best answer here.

Liz

Elizabeth Reen
CPB Database Group Manager
Service Now Group: CPB-ORACLE-DB-SUPPORT

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org]
On Behalf Of ***@bluewin.ch
Sent: Saturday, June 16, 2018 9:28 AM
To: ***@gmail.com
Cc: oracle-***@freelists.org; ***@jlcomp.demon.co.uk
Subject: Re: Re: Re: Explain Plan and Security

Hi Martin,

that would not work. I am working for a service provider. The data is not ours. We are by policy forbitten to run queries on prod other than against the dictionary.
We must look for an other way. Maybe some instant clone would work.

Thanks

Lothar
----UrsprÃŒngliche Nachricht----
Von : ***@gmail.com
Datum : 15/06/2018 - 21:04 (CEST)
An : ***@bluewin.ch
Cc : ***@jlcomp.demon.co.uk, oracle-***@freelists.org
Betreff : Re: Re: Explain Plan and Security
If I followed this thread right, there is nothing you can do than execute the query. Everything else will generate different results.
Is there any chance you get permission to execute the query if you can guarantee it only runs for "a very short time"?
E.g. a special (proxy) user with a very strict LOGICAL_READS_PER_SESSION comes to my mind.
Or you add an additional filter with "where 1 =impossible_function" and your "impossible_function" does an execute immediate "select 1/0 from dual".
More methods come to my mind, but I'm sure you get the idea.
The execution-trap can be tested in non-profit environment and so you might convince your customer?

hth,
berx

***@bluewin.ch <***@bluewin.ch> schrieb am Fr., 15. Juni 2018, 10:20:
Thanks to all for discussing.
Well, actually my case is that we have a sql monitor of a query that went wrong. By analyzing the sql monitor result we have a fair idea what kind of plan we want.
From the monitor we also have the exact bind variable values and can generate a run script.
We can then take some action to fix the issue, like recalculation stats, rewriting the query somewhat.
Of course we want to check if our fix works.
We are not allowed to run the query in production. (The Therefore the next best thing would be good execution plan.)
I tried explain plan, but the bind variables matter and so far I never got a good prediction.
It seems to me the whole matter is a bit more complex than I originally thought. Therefore I really wanÂŽt to ping the usual suspects.
Regards
Lothar
----UrsprÃŒngliche Nachricht----
Von : ***@jlcomp.demon.co.uk
Datum : 14/06/2018 - 19:56 (GMT)
An : oracle-***@freelists.org
Betreff : Re: Explain Plan and Security
Dominic,
There's no question that if the query has executed and you can get there in time then the plan you get from display_cursor() is the plan that actually happened, but we're discussing the point that we can get execution plans into memory (for display_cursor())
to report) that have never executed - which leafs to the point that those are plans that might never actually happen with any real user inputs.
Regards
Jonathan Lewis
________________________________________
From: Dominic Brooks <***@hotmail.com>
Sent: 14 June 2018 18:52
To: Jonathan Lewis
Cc: oracle-***@freelists.org
Subject: Re: Explain Plan and Security
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

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

Post by Jonathan Lewis
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_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
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
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.

Post by Jonathan Lewis
(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
________________________________________
Sent: 14 June 2018 13:36:46
Subject: Explain Plan and Security
Hi,

Post by Jonathan Lewis
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
--

Post by Jonathan Lewis
--

"Reen, Elizabeth " (Redacted sender "elizabeth.reen" for DMARC)

2018-06-19 13:58:22 UTC

Permalink

I too have that problem. Iâm not even allowed to log into the database. I get to tune from AWR reports. Iâm afraid redaction is the only way you can copy it. Moving the stats was a good idea too. I will try that the next issue I have. This is a hard environment to manage and there is not much you can do to tune. Data privacy rules are not written by the people who have to manage the data.

Liz

Elizabeth Reen
CPB Database Group Manager
Service Now Group: CPB-ORACLE-DB-SUPPORT

From: ***@bluewin.ch [mailto:***@bluewin.ch]
Sent: Tuesday, June 19, 2018 4:00 AM
To: Reen, Elizabeth [ICG-IT]
Cc: ***@gmail.com; ***@jlcomp.demon.co.uk; oracle-***@freelists.org
Subject: Re: RE: Re: Re: Explain Plan and Security

Hi Liz,

it is because we must not see the data. Database vault is in place.

Regards

Lothar
----UrsprÃŒngliche Nachricht----
Von : ***@citi.com
Datum : 18/06/2018 - 20:20 (GMT)
An : ***@bluewin.ch, ***@gmail.com
Cc : oracle-***@freelists.org, ***@jlcomp.demon.co.uk
Betreff : RE: Re: Re: Explain Plan and Security
The question is why are you forbidden to run the query. If it is for performance reasons, a clone would work. If it is because you cannot see the data, then you would be forbidden to clone. Redaction would be your best answer here.

Liz

Elizabeth Reen
CPB Database Group Manager
Service Now Group: CPB-ORACLE-DB-SUPPORT

From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of ***@bluewin.ch
Sent: Saturday, June 16, 2018 9:28 AM
To: ***@gmail.com
Cc: oracle-***@freelists.org; ***@jlcomp.demon.co.uk
Subject: Re: Re: Re: Explain Plan and Security

Hi Martin,

that would not work. I am working for a service provider. The data is not ours. We are by policy forbitten to run queries on prod other than against the dictionary.
We must look for an other way. Maybe some instant clone would work.

Thanks

Lothar
----UrsprÃŒngliche Nachricht----
Von : ***@gmail.com
Datum : 15/06/2018 - 21:04 (CEST)
An : ***@bluewin.ch
Cc : ***@jlcomp.demon.co.uk, oracle-***@freelists.org
Betreff : Re: Re: Explain Plan and Security
If I followed this thread right, there is nothing you can do than execute the query. Everything else will generate different results.
Is there any chance you get permission to execute the query if you can guarantee it only runs for "a very short time"?
E.g. a special (proxy) user with a very strict LOGICAL_READS_PER_SESSION comes to my mind.
Or you add an additional filter with "where 1 =impossible_function" and your "impossible_function" does an execute immediate "select 1/0 from dual".
More methods come to my mind, but I'm sure you get the idea.
The execution-trap can be tested in non-profit environment and so you might convince your customer?

hth,
berx

***@bluewin.ch<mailto:***@bluewin.ch> <***@bluewin.ch<mailto:***@bluewin.ch>> schrieb am Fr., 15. Juni 2018, 10:20:
Thanks to all for discussing.
Well, actually my case is that we have a sql monitor of a query that went wrong. By analyzing the sql monitor result we have a fair idea what kind of plan we want.
From the monitor we also have the exact bind variable values and can generate a run script.
We can then take some action to fix the issue, like recalculation stats, rewriting the query somewhat.
Of course we want to check if our fix works.
We are not allowed to run the query in production. (The Therefore the next best thing would be good execution plan.)
I tried explain plan, but the bind variables matter and so far I never got a good prediction.

It seems to me the whole matter is a bit more complex than I originally thought. Therefore I really wanÂŽt to ping the usual suspects.

Regards

Lothar

----UrsprÃŒngliche Nachricht----
Von : ***@jlcomp.demon.co.uk<mailto:***@jlcomp.demon.co.uk>
Datum : 14/06/2018 - 19:56 (GMT)
An : oracle-***@freelists.org<mailto:oracle-***@freelists.org>
Betreff : Re: Explain Plan and Security

Dominic,

There's no question that if the query has executed and you can get there in time then the plan you get from display_cursor() is the plan that actually happened, but we're discussing the point that we can get execution plans into memory (for display_cursor()) to report) that have never executed - which leafs to the point that those are plans that might never actually happen with any real user inputs.

Regards
Jonathan Lewis

________________________________________
From: Dominic Brooks <***@hotmail.com<mailto:***@hotmail.com>>
Sent: 14 June 2018 18:52
To: Jonathan Lewis
Cc: oracle-***@freelists.org<mailto:oracle-***@freelists.org>
Subject: Re: Explain Plan and Security

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

Post by Jonathan Lewis
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
________________________________________
Sent: 14 June 2018 17:15
To: Jonathan Lewis
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
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
________________________________________
Sent: 14 June 2018 16:56
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<https://urldefense.proofpoint.com/v2/url?u=https-3A__nam01.safelinks.protection.outlook.com_-3Furl-3Dhttps-253A-252F-252Fdocs.oracle.com-252Fdatabase-252F121-252FARPLS-252Fd-5Fsql.htm-2523i996870-26data-3D02-257C01-257C-257C7f3f2e59790c49647afd08d5d21ddcb7-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636645948067016044-26sdata-3D4kyFobAwG5FlvMc2tloVVZpwaeRzJ-252BpMu7JvwWzxJqo-253D-26reserved-3D0&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=Xtymk_Z9jTZ3uDcnPmCpcAqtFyB1htXtnIWV4HLqsTE&e=>
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_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
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
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
________________________________________
Sent: 14 June 2018 13:36:46
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<https://urldefense.proofpoint.com/v2/url?u=https-3A__nam01.safelinks.protection.outlook.com_-3Furl-3Dhttp-253A-252F-252Fkerryosborne.oracle-2Dguy.com-252F2008-252F10-252Fexplain-2Dplan-2Dlies-252F-26data-3D02-257C01-257C-257C7f3f2e59790c49647afd08d5d21ddcb7-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636645948067016044-26sdata-3D0MfIBWr-252FbqDjJeSA9AMeySqqeQnBo5odXPC8wxYqthY-253D-26reserved-3D0&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=7kU20QE6TUDd0HLzcuvTmCpfIwtd79YO45g0y_fF8uE&e=>.
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://urldefense.proofpoint.com/v2/url?u=https-3A__nam01.safelinks.protection.outlook.com_-3Furl-3Dhttp-253A-252F-252Fwww.freelists.org-252Fwebpage-252Foracle-2Dl-26data-3D02-257C01-257C-257C7f3f2e59790c49647afd08d5d21ddcb7-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636645948067016044-26sdata-3DKWBDk34WKqsWc5xaLQTbAk12iDSDkjI56zoLvoYEppM-253D-26reserved-3D0&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=9tvmppZFlmQ_8yJUIpRtxKiQ0e812OOVne1pteLcejU&e=>
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__nam01.safelinks.protection.outlook.com_-3Furl-3Dhttp-253A-252F-252Fwww.freelists.org-252Fwebpage-252Foracle-2Dl-26data-3D02-257C01-257C-257C7f3f2e59790c49647afd08d5d21ddcb7-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636645948067016044-26sdata-3DKWBDk34WKqsWc5xaLQTbAk12iDSDkjI56zoLvoYEppM-253D-26reserved-3D0&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=9tvmppZFlmQ_8yJUIpRtxKiQ0e812OOVne1pteLcejU&e=>
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__nam01.safelinks.protection.outlook.com_-3Furl-3Dhttp-253A-252F-252Fwww.freelists.org-252Fwebpage-252Foracle-2Dl-26data-3D02-257C01-257C-257C7f3f2e59790c49647afd08d5d21ddcb7-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636645948067016044-26sdata-3DKWBDk34WKqsWc5xaLQTbAk12iDSDkjI56zoLvoYEppM-253D-26reserved-3D0&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=xXgtZ8vRUA7ZC4YBlwJ0CG38D-X-4OPa4_NKdAPytEE&s=9tvmppZFlmQ_8yJUIpRtxKiQ0e812OOVne1pteLcejU&e=>

l***@bluewin.ch

2018-06-19 08:53:27 UTC

Permalink

Hi Stefan,

thanks, thats interessting.
I am currently thinking of a much simpler solution though.
We could set up a runner job under the Apps User Authority,
The Job would receive a statement and bind variables, open the cursor but not fetch the data.
Sorry that this is so prosaic.

regards

Lothar

----Ursprüngliche Nachricht----
Von : ***@soocs.de
Datum : 19/06/2018 - 10:19 (GMT)
An : oracle-***@freelists.org, ***@bluewin.ch
Betreff : Re: Explain Plan and Security

Hey Lothar,
today I had a little bit time to geek out and here is a working test case for what I've mentioned here (https://www.freelists.org/post/oracle-l/Re-Re-Explain-Plan-and-Security,1).

******************
-- Create test objects
create table t1 (a number);
create index t1_i on t1(a);
insert into t1 select rownum from dba_objects where rownum <= 10;
commit;
exec dbms_stats.gather_table_stats (USER,'T1');

-- Test SQL just for information
select * from t1; --> SQL-ID: 27uhu2q2xuu7r

-- 11.2 solution
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname opifch2} {pgadep: exactdepth 0} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL ALLSTATS OUTLINE PEEKED_BINDS LAST'));

-- 12.2 solution (as event filter "pgadep" in RDBMS library is not available anymore)
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname opifch} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL ALLSTATS OUTLINE PEEKED_BINDS LAST'));
******************

The process aborts with "ORA-03113: end-of-file on communication channel" right before/at fetching the data - so this should exactly fit your needs / security requirements :-)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de

Post by Jonathan Lewis
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

Jonathan Lewis

2018-06-19 09:23:55 UTC

Permalink

Fantastic bit of geekery from Stefan!.

Lothar, you may find that the underlying library bundles the open, execute and first fetch, so you may not get a plan until actually do the first fetch.

Regards
Jonathan Lewis

________________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on behalf of ***@bluewin.ch <***@bluewin.ch>
Sent: 19 June 2018 09:53
To: ***@soocs.de
Cc: oracle-***@freelists.org
Subject: Re: Re: Explain Plan and Security

Hi Stefan,

thanks, thats interessting.
I am currently thinking of a much simpler solution though.
We could set up a runner job under the Apps User Authority,
The Job would receive a statement and bind variables, open the cursor but not fetch the data.
Sorry that this is so prosaic.

regards

Lothar

----Ursprüngliche Nachricht----
Von : ***@soocs.de
Datum : 19/06/2018 - 10:19 (GMT)
An : oracle-***@freelists.org, ***@bluewin.ch
Betreff : Re: Explain Plan and Security

Hey Lothar,
today I had a little bit time to geek out and here is a working test case for what I've mentioned here (https://www.freelists.org/post/oracle-l/Re-Re-Explain-Plan-and-Security,1).

******************
-- Create test objects
create table t1 (a number);
create index t1_i on t1(a);
insert into t1 select rownum from dba_objects where rownum <= 10;
commit;
exec dbms_stats.gather_table_stats (USER,'T1');

-- Test SQL just for information
select * from t1; --> SQL-ID: 27uhu2q2xuu7r

-- 11.2 solution
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname opifch2} {pgadep: exactdepth 0} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL ALLSTATS OUTLINE PEEKED_BINDS LAST'));

-- 12.2 solution (as event filter "pgadep" in RDBMS library is not available anymore)
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname opifch} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL ALLSTATS OUTLINE PEEKED_BINDS LAST'));
******************

The process aborts with "ORA-03113: end-of-file on communication channel" right before/at fetching the data - so this should exactly fit your needs / security requirements :-)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
--
http://www.freelists.org/webpage/oracle-l

l***@bluewin.ch

2018-06-19 09:34:44 UTC

Permalink

Agrered. I might need Stefans code at an other occaision. Did not know you can do this.
For the cureent task it seems to be overkill.

Well, thanks for pointing out the fetch bit. I guess it does not hurt to do a fetch as long as nobody sees the result. :-)

----Ursprüngliche Nachricht----
Von : ***@jlcomp.demon.co.uk
Datum : 19/06/2018 - 11:23 (GMT)
An : ***@soocs.de, ***@bluewin.ch
Cc : oracle-***@freelists.org
Betreff : Re: Re: Explain Plan and Security

Fantastic bit of geekery from Stefan!.

Lothar, you may find that the underlying library bundles the open, execute and first fetch, so you may not get a plan until actually do the first fetch.

Regards
Jonathan Lewis

________________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on behalf of ***@bluewin.ch <***@bluewin.ch>
Sent: 19 June 2018 09:53
To: ***@soocs.de
Cc: oracle-***@freelists.org
Subject: Re: Re: Explain Plan and Security

Hi Stefan,

thanks, thats interessting.
I am currently thinking of a much simpler solution though.
We could set up a runner job under the Apps User Authority,
The Job would receive a statement and bind variables, open the cursor but not fetch the data.
Sorry that this is so prosaic.

regards

Lothar

----Ursprüngliche Nachricht----
Von : ***@soocs.de
Datum : 19/06/2018 - 10:19 (GMT)
An : oracle-***@freelists.org, ***@bluewin.ch
Betreff : Re: Explain Plan and Security

Hey Lothar,
today I had a little bit time to geek out and here is a working test case for what I've mentioned here (https://www.freelists.org/post/oracle-l/Re-Re-Explain-Plan-and-Security,1).

******************
-- Create test objects
create table t1 (a number);
create index t1_i on t1(a);
insert into t1 select rownum from dba_objects where rownum <= 10;
commit;
exec dbms_stats.gather_table_stats (USER,'T1');

-- Test SQL just for information
select * from t1; --> SQL-ID: 27uhu2q2xuu7r

-- 11.2 solution
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname opifch2} {pgadep: exactdepth 0} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL ALLSTATS OUTLINE PEEKED_BINDS LAST'));

-- 12.2 solution (as event filter "pgadep" in RDBMS library is not available anymore)
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname opifch} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL ALLSTATS OUTLINE PEEKED_BINDS LAST'));
******************

The process aborts with "ORA-03113: end-of-file on communication channel" right before/at fetching the data - so this should exactly fit your needs / security requirements :-)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
--
http://www.freelists.org/webpage/oracle-l

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

Jonathan Lewis

2018-06-19 09:41:41 UTC

Permalink

"So long as nobody sees the result".

In that case I think you could "set pause on" from SQL*Plus, start the query running, wait a bit for the parse to complete, then press ctrl-C.
The plan might be available, but no data will have been displayed.

To be tested, of course.

Regards
Jonathan Lewis

________________________________________
From: ***@bluewin.ch <***@bluewin.ch>
Sent: 19 June 2018 10:34:44
To: Jonathan Lewis
Cc: ***@soocs.de; oracle-***@freelists.org
Subject: Re: Re: Re: Explain Plan and Security

Agrered. I might need Stefans code at an other occaision. Did not know you can do this.
For the cureent task it seems to be overkill.

Well, thanks for pointing out the fetch bit. I guess it does not hurt to do a fetch as long as nobody sees the result. :-)

----Ursprüngliche Nachricht----
Von : ***@jlcomp.demon.co.uk
Datum : 19/06/2018 - 11:23 (GMT)
An : ***@soocs.de, ***@bluewin.ch
Cc : oracle-***@freelists.org
Betreff : Re: Re: Explain Plan and Security

Fantastic bit of geekery from Stefan!.

Lothar, you may find that the underlying library bundles the open, execute and first fetch, so you may not get a plan until actually do the first fetch.

Regards
Jonathan Lewis

________________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on behalf of ***@bluewin.ch <***@bluewin.ch>
Sent: 19 June 2018 09:53
To: ***@soocs.de
Cc: oracle-***@freelists.org
Subject: Re: Re: Explain Plan and Security

Hi Stefan,

thanks, thats interessting.
I am currently thinking of a much simpler solution though.
We could set up a runner job under the Apps User Authority,
The Job would receive a statement and bind variables, open the cursor but not fetch the data.
Sorry that this is so prosaic.

regards

Lothar

----Ursprüngliche Nachricht----
Von : ***@soocs.de
Datum : 19/06/2018 - 10:19 (GMT)
An : oracle-***@freelists.org, ***@bluewin.ch
Betreff : Re: Explain Plan and Security

Hey Lothar,
today I had a little bit time to geek out and here is a working test case for what I've mentioned here (https://www.freelists.org/post/oracle-l/Re-Re-Explain-Plan-and-Security,1).

******************
-- Create test objects
create table t1 (a number);
create index t1_i on t1(a);
insert into t1 select rownum from dba_objects where rownum <= 10;
commit;
exec dbms_stats.gather_table_stats (USER,'T1');

-- Test SQL just for information
select * from t1; --> SQL-ID: 27uhu2q2xuu7r

-- 11.2 solution
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname opifch2} {pgadep: exactdepth 0} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL ALLSTATS OUTLINE PEEKED_BINDS LAST'));

-- 12.2 solution (as event filter "pgadep" in RDBMS library is not available anymore)
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname opifch} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL ALLSTATS OUTLINE PEEKED_BINDS LAST'));
******************

The process aborts with "ORA-03113: end-of-file on communication channel" right before/at fetching the data - so this should exactly fit your needs / security requirements :-)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
--
http://www.freelists.org/webpage/oracle-l

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

Andy Sayer

2018-06-19 11:35:19 UTC

Permalink

Set feedback only
In sql*plus 12.2+ would achieve the same

Post by Jonathan Lewis
"So long as nobody sees the result".
In that case I think you could "set pause on" from SQL*Plus, start the
query running, wait a bit for the parse to complete, then press ctrl-C.
The plan might be available, but no data will have been displayed.
To be tested, of course.
Regards
Jonathan Lewis
________________________________________
Sent: 19 June 2018 10:34:44
To: Jonathan Lewis
Subject: Re: Re: Re: Explain Plan and Security
Agrered. I might need Stefans code at an other occaision. Did not know you can do this.
For the cureent task it seems to be overkill.
Well, thanks for pointing out the fetch bit. I guess it does not hurt to
do a fetch as long as nobody sees the result. :-)
----UrsprÃŒngliche Nachricht----
Datum : 19/06/2018 - 11:23 (GMT)
Betreff : Re: Re: Explain Plan and Security
Fantastic bit of geekery from Stefan!.
Lothar, you may find that the underlying library bundles the open, execute
and first fetch, so you may not get a plan until actually do the first
fetch.
Regards
Jonathan Lewis
________________________________________
Sent: 19 June 2018 09:53
Subject: Re: Re: Explain Plan and Security
Hi Stefan,
thanks, thats interessting.
I am currently thinking of a much simpler solution though.
We could set up a runner job under the Apps User Authority,
The Job would receive a statement and bind variables, open the cursor but
not fetch the data.
Sorry that this is so prosaic.
regards
Lothar
----UrsprÃŒngliche Nachricht----
Datum : 19/06/2018 - 10:19 (GMT)
Betreff : Re: Explain Plan and Security
Hey Lothar,
today I had a little bit time to geek out and here is a working test case
for what I've mentioned here (
https://www.freelists.org/post/oracle-l/Re-Re-Explain-Plan-and-Security,1
).
******************
-- Create test objects
create table t1 (a number);
create index t1_i on t1(a);
insert into t1 select rownum from dba_objects where rownum <= 10;
commit;
exec dbms_stats.gather_table_stats (USER,'T1');
-- Test SQL just for information
select * from t1; --> SQL-ID: 27uhu2q2xuu7r
-- 11.2 solution
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname
opifch2} {pgadep: exactdepth 0} plan_stat=never,wait=false,bind=false
crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL
ALLSTATS OUTLINE PEEKED_BINDS LAST'));
-- 12.2 solution (as event filter "pgadep" in RDBMS library is not available anymore)
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname
opifch} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL
ALLSTATS OUTLINE PEEKED_BINDS LAST'));
******************
The process aborts with "ORA-03113: end-of-file on communication channel"
right before/at fetching the data - so this should exactly fit your needs /
security requirements :-)
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Tanel Poder

2018-06-19 17:04:24 UTC

Permalink

There's an *action* controlc_signal in recent Oracle versions (12.2, maybe
earlier), so you won't have to crash the process:

SQL> *oradebug doc event action controlc_signal*
controlc_signal
- received 1013 signal
Usage
-------
controlc_signal()

SQL> ALTER SESSION SET EVENTS 'sql_trace[SQL: 3vjxpmhhzngu4] {callstack:
fname opifch} *controlc_signal()*';

Session altered.

SQL> SELECT * FROM dual;
ERROR:
*ORA-01013: user requested cancel of current operation*

no rows selected

SQL>

Post by l***@bluewin.ch
Hey Lothar,
today I had a little bit time to geek out and here is a working test case
for what I've mentioned here (
https://www.freelists.org/post/oracle-l/Re-Re-Explain-Plan-and-Security,1
).
******************
-- Create test objects
create table t1 (a number);
create index t1_i on t1(a);
insert into t1 select rownum from dba_objects where rownum <= 10;
commit;
exec dbms_stats.gather_table_stats (USER,'T1');
-- Test SQL just for information
select * from t1; --> SQL-ID: 27uhu2q2xuu7r
-- 11.2 solution
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname
opifch2} {pgadep: exactdepth 0} plan_stat=never,wait=false,bind=false
crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL
ALLSTATS OUTLINE PEEKED_BINDS LAST'));
-- 12.2 solution (as event filter "pgadep" in RDBMS library is not
available anymore)
alter system flush shared_pool;
alter session set events 'sql_trace[SQL: 27uhu2q2xuu7r] {callstack: fname
opifch} plan_stat=never,wait=false,bind=false crash()';
select * from t1;
select * from table (DBMS_XPLAN.DISPLAY_CURSOR('27uhu2q2xuu7r',0,'ALL
ALLSTATS OUTLINE PEEKED_BINDS LAST'));
******************
The process aborts with "ORA-03113: end-of-file on communication channel"
right before/at fetching the data - so this should exactly fit your needs /
security requirements :-)
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de

Post by l***@bluewin.ch
Hi,

http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/.

Post by l***@bluewin.ch
Normally my work around for explain plan issues is to run the query and

use dbms_xplan.display_cursor.

Post by l***@bluewin.ch
Now I am working in an environment where I must not run a query, but I

can do explain plan.

Post by l***@bluewin.ch
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.

Post by l***@bluewin.ch
Then use dbms_xplan.display_cursor.
Before I spent time, has anybody done it already?
Regards
Lothar

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