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'));

  ---

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

> "l.flatz_at_bluewin.ch" <l.flatz_at_bluewin.ch> hat am 14. Juni 2018 um 14:36 geschrieben:
>
> 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