CODEWITHSUNDEEP
oracle-databaseplsqlsqlplus
hc0re
hc0re

Reputation: 1986

How to get Sql execution plan from a Oracle package runned with function with parameters using SQL*Plus?

I need to sniff out a SQL execution plan of a Oracle package.

The SQL*Plus code to run this package looks like this:

VARIABLE RC REFCURSOR
EXEC :RC :=PACKAGENAME.GETREPORTDATA('12_300',1999,2014,'246246',NULL)
PRINT RC

I have to do it using SQL*Plus, cause It's a part of a Jenkins job, and I need to save the output to a file, for further comparision purposes.

I have been informed about a method to do this, it looks like this:

select 
        sqlplan.operation, 
        sqlplan.options, 
        sqlplan.object_name, 
        sqlplan.cost, 
        sqlplan.depth   
  from v$sqlarea sqlarea, 
       v$session sesion, 
       v$sql_plan sqlplan
where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sqlarea.plan_hash_value = sqlplan.plan_hash_value
   and sesion.username = 'USERNAME' order by sqlplan.depth;

But all I get from this code is

       v$sql_plan sqlplan
       *
ERROR at line 9:
ORA-00942: table or view does not exist

The other thing I found out about is EXPLAIN PLAN command but it is not working for me, or I'm using it wrong.

To be honest, I just don't know where to start with this...

Any information will be honestly appreciated by me :)

Upvotes: 0

Views: 2228

Answers (1)

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

You need to turn on Oracle trace and then go through the tracefile

-- All versions.
SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET sql_trace=FALSE;

SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');

-- Available from SQL*Plus since 8i (commandline utility prior to this.
SQL> CONN sys/password AS SYSDBA;  -- User must have SYSDBA.
SQL> ORADEBUG SETMYPID;            -- Debug current session.
SQL> ORADEBUG SETOSPID 1234;       -- Debug session with the specified OS process.
SQL> ORADEBUG SETORAPID 123456;    -- Debug session with the specified Oracle process ID.

SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
SQL> ORADEBUG TRACEFILE_NAME;      -- Display the current trace file.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

-- All versions, requires DBMS_SUPPORT package to be loaded.
SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace;

SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);

0 - No trace. Like switching sql_trace off. 2 - The equivalent of regular sql_trace. 4 - The same as 2, but with the addition of bind variable values. 8 - The same as 2, but with the addition of wait events. 12 - The same as 2, but with both bind variable values and wait events.

Then you would use the oracle utility "tkprof" to format the trace to a more readable version.

Upvotes: 1

Related Questions