Re: Re: Re: Explain Plan and Security

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 19 Jun 2018 09:41:41 +0000
Message-ID: <MM1P123MB084278E008A9D55FF26EACA7A5700_at_MM1P123MB0842.GBRP123.PROD.OUTLOOK.COM>


"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: l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> Sent: 19 June 2018 10:34:44
To: Jonathan Lewis
Cc: contact_at_soocs.de; oracle-l_at_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 : jonathan_at_jlcomp.demon.co.uk
Datum : 19/06/2018 - 11:23 (GMT)
An : contact_at_soocs.de, l.flatz_at_bluewin.ch Cc : oracle-l_at_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-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> Sent: 19 June 2018 09:53
To: contact_at_soocs.de
Cc: oracle-l_at_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 : contact_at_soocs.de
Datum : 19/06/2018 - 10:19 (GMT)
An : oracle-l_at_freelists.org, l.flatz_at_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: _at_OracleSK
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 19 2018 - 11:41:41 CEST

Original text of this message