Mandalika's scratchpad [ Work blog @Oracle | Stock Market Notes | My Music Compositions ]

Old Posts: 09.04  10.04  11.04  12.04  01.05  02.05  03.05  04.05  05.05  06.05  07.05  08.05  09.05  10.05  11.05  12.05  01.06  02.06  03.06  04.06  05.06  06.06  07.06  08.06  09.06  10.06  11.06  12.06  01.07  02.07  03.07  04.07  05.07  06.07  08.07  09.07  10.07  11.07  12.07  01.08  02.08  03.08  04.08  05.08  06.08  07.08  08.08  09.08  10.08  11.08  12.08  01.09  02.09  03.09  04.09  05.09  06.09  07.09  08.09  09.09  10.09  11.09  12.09  01.10  02.10  03.10  04.10  05.10  06.10  07.10  08.10  09.10  10.10  11.10  12.10  01.11  02.11  03.11  04.11  05.11  07.11  08.11  09.11  10.11  11.11  12.11  01.12  02.12  03.12  04.12  05.12  06.12  07.12  08.12  09.12  10.12  11.12  12.12  01.13  02.13  03.13  04.13  05.13  06.13  07.13  08.13  09.13  10.13  11.13  12.13  01.14  02.14  03.14  04.14  05.14  06.14  07.14  09.14  10.14  11.14  12.14  01.15  02.15  03.15  04.15  06.15  09.15  12.15  01.16  03.16  04.16  05.16  06.16  07.16  08.16  09.16  12.16  01.17  02.17  03.17 


Tuesday, June 30, 2015
 
Oracle Database : Profiling PL/SQL Code

AWR gathers and reports performance statistics that are useful in monitoring and tuning databases. Even though the combination of AWR/ASH reports and explain plans are very useful in analyzing the performance of queries, their usefulness is limited when dealing with PL/SQL-heavy applications. AWR and ASH reports help figure out if the database is spending significant amount of time and/or CPU cycles in executing PL/SQL code and in what PL/SQL modules -- however to identify performance bottlenecks withinin PL/SQL modules/routines and to pin-point hot spots in PL/SQL code at line level, DBMS_PROFILER, a database package, might be the right tool to use. Also check the documentation of DBMS_HPROF package out.

Rest of this post lists out the steps involved (on a high level) in generating the profile and subsequently generating a report or extracting relevant pieces of information.

Steps:

  1. [Pre-11g env only] Create PLAN_TABLE as SYS user

    SQL> @?/rdbms/admin/utlxplan.sql
    SQL> GRANT ALL ON sys.plan_table TO public;
    SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
    
  2. Create PL/SQL Profiler tables under the application schema

    As application user:

    SQL> @?/rdbms/admin/proftab.sql
    
  3. Install DBMS_PROFILER package as SYS user, if missing

    SQL> @?/rdbms/admin/profload.sql
    
  4. Start PL/SQL Profiler

    As application user:

    SQL> EXEC DBMS_PROFILER.START_PROFILER('SOME_COMMENT');
    
  5. Execute one or more transactions to be profiled

  6. Stop the PL/SQL Profiler

    As application user:

    SQL> EXEC DBMS_PROFILER.STOP_PROFILER;
    
  7. At this point, there are couple of options to analyze the profile.

    • Easy option - if you have access to My Oracle Support (MOS), download the profiler.sql from MOS Document ID 243755.1 "Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data", and execute.

      As application user:

      SQL> profiler.sql
      

      Select appropriate runid from the list displayed on stdout. profiler.sql script generates an HTML report that shows top time consumers for the duration of the execution of the PL/SQL Profiler run.

    • Not-so-Easy option - refer to the DBMS_PROFILER documentation to extract relevant information and details such as top time consumers, top lines sorted by total time, profiled PL/SQL module list, top modules etc.,

      Checking the documentation out for the following three key tables is a good start -- PLSQL_PROFILER_RUNS (information related to a profiling session), PLSQL_PROFILER_UNITS (information about each PL/SQL unit that was executed during the profiler session) and PLSQL_PROFILER_DATA (execution statistics for each line of code in PL/SQL units). Ancillary tables: DBA_SOURCE and PLAN_TABLE.

Labels:





2004-2017 

This page is powered by Blogger. Isn't yours?