How to create an explanation plan for an entire stored procedure

Question

I usually generate explanations using the following in sqlplus:

SET AUTOTRACE ON SET TIMING ON SET TRIMSPOOL ON SET LINES 200 SPOOL filename.txt SET AUTOTRACE TRACEONLY; {query goes here} SPOOL OFF SET AUTOTRACE OFF 

But what if I want to create an explanation plan for a stored procedure?

Is there a way to generate an explanation plan for the entire stored procedure? SP has no I / O parameters.

Answer 1

What you generate is rightly called an "execution plan." Explain the plan is the command used to create and view the execution plan, as AUTOTRACE TRACEONLY does in your example.

By definition, an execution plan is for a single SQL statement. The PL / SQL block does not have an execution plan. If it contains one or more SQL statements, then each of them will have an execution plan.

One option is to manually extract the SQL statements from the PL / SQL code and use the process that you have already shown.

Another option is active SQL tracing, and then follow the procedure. This will create a trace file on the server that contains execution plans for all statements executed in the session. The track is in a rather crude form, so it is easiest to format it using the Oracle TKPROF tool; There are also various third-party tools that also process these trace files.

Answer 2

 Hi I have done like below for the stored procedure: SET AUTOTRACE ON SET TIMING ON SET TRIMSPOOL ON SET LINES 200 SPOOL filename.txt SET AUTOTRACE TRACEONLY; @your stored procedure path SPOOL OFF SET AUTOTRACE OFF And got the below statistics: Statistics ----------------------------------------------------------- 6 CPU used by this session 8 CPU used when call started 53 DB time 6 Requests to/from client 188416 cell physical IO interconnect bytes 237 consistent gets 112 consistent gets - examination 237 consistent gets from cache 110 consistent gets from cache (fastpath) 2043 db block gets 1 db block gets direct 2042 db block gets from cache 567 db block gets from cache (fastpath) 27 enqueue releases 27 enqueue requests 4 messages sent 31 non-idle wait count 19 non-idle wait time 44 opened cursors cumulative 2 opened cursors current 22 physical read total IO requests 180224 physical read total bytes 1 physical write total IO requests 8192 physical write total bytes 1 pinned cursors current 461 recursive calls 4 recursive cpu usage 2280 session logical reads 1572864 session pga memory 19 user I/O wait time 9 user calls 1 user commits No Errors. Autotrace Disabled