Mandalika's scratchpad | [ Work blog @Oracle | 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 04.17 06.17 07.17 08.17 09.17 10.17 12.17 01.18 02.18 03.18 04.18 05.18 06.18 07.18 08.18 09.18 11.18 12.18 01.19 02.19 05.19 06.19 08.19 10.19 11.19 05.20 10.20 11.20 12.20 09.21 11.21 12.22
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:
[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;
Create PL/SQL Profiler tables under the application schema
As application user:
SQL> @?/rdbms/admin/proftab.sql
Install DBMS_PROFILER
package as SYS
user, if missing
SQL> @?/rdbms/admin/profload.sql
Start PL/SQL Profiler
As application user:
SQL> EXEC DBMS_PROFILER.START_PROFILER('SOME_COMMENT');
Execute one or more transactions to be profiled
Stop the PL/SQL Profiler
As application user:
SQL> EXEC DBMS_PROFILER.STOP_PROFILER;
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: analyze bottleneck database oracle performance plsql profiler
2004-2019 |