Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    9

    Question Unanswered: Execution Plan / Statistics on PL/SQL

    At SQLPLUS, if we issue 'SET AUTOTRACE ON', we are able to get the execution plan and statistics after running a SQL statement.

    However if it is required to view the execution plan and statistics of each SQL statement inside a PL/SQL program, what can I do?

    Migh any expert help and thanks in advance!

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    please give more detail.

    Are you saying you want an explain plan for every sql statement OR are you saying you want explain plans for Procs and Packages?


    Either way, you would probably rather have STATSPACK running which will give you more accurate data. Explain plan is only an ESTIMATE and not totally accurate (only use it as a general guideline and then use TKPROF or statspack or something).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Posts
    9
    In fact I want an explain plan for every SQL statement in the PL/SQL program.

    Thanks for giving me hints in using STATSPACK and I try to consult menu for it.

  4. #4
    Join Date
    Aug 2003
    Posts
    1
    You can "wrap" the procedure or function with another procedure / function that performs the following;

    -----------
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';

    <procedure or function to be traced>

    execute immediate 'alter session set events ''10046 trace name context off''';
    ------------


    This will perform a sql trace with information binds and waits (as well as the explain plan for every statement), generating a dump file in user_dump_dest.

    Please reference metalink notes 21154.1 and 33089.1 for more information regarding the set events 10046 statement.

    Adam

  5. #5
    Join Date
    Jul 2002
    Location
    Beijing . China
    Posts
    9
    Trace the session, then TKProf ....

  6. #6
    Join Date
    Jul 2003
    Posts
    9

    thanks

    thank you very much... already get what i want by using 'alter session set events...'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •