Database Reference
In-Depth Information
The first query breaks up the profiling data at namespace level. If, as in this case, you see that the PL/SQL code is
responsible for a relevant part of the response time (45.1% in this case), it makes sense that you continue looking, in
more detail, at the data provided by the profiler. On the other hand, if you see that SQL is responsible for most of the
response time, a PL/SQL profiler is the wrong tool of choice; there are better tools, such as SQL trace, to find out which
SQL statements are the slower ones. Either way, the first query provides valuable information, helping you to know
where to focus your attention next.
Here's an example of the first query along with some output:
SQL> SELECT sum(function_elapsed_time)/1000 AS total_ms,
2 100*ratio_to_report(sum(function_elapsed_time)) over () AS total_percent,
3 sum(calls) AS calls,
4 100*ratio_to_report(sum(calls)) over () AS calls_percent,
5 namespace AS namespace_name
6 FROM dbmshp_function_info
7 WHERE runid = 1
8 GROUP BY namespace
9 ORDER BY total_ms DESC;
TOTAL [ms] TOT% CALLS CAL% NAMESPACE_NAME
---------- ------ ---------- ------ ---------------
565 54.9 89 5.6 SQL
464
45.1
1,494 94.4 PLSQL
The second query, which is very similar to the previous one, breaks up the profiling data at module level. In this
case you can see that most of the PL/SQL response time (44.9%) was spent inside the
perfect_triangles
procedure:
SQL> SELECT sum(function_elapsed_time)/1000 AS total_ms,
2 100*ratio_to_report(sum(function_elapsed_time)) over () AS total_percent,
3 sum(calls) AS calls,
4 100*ratio_to_report(sum(calls)) over () AS calls_percent,
5 namespace,
6 nvl(nullif(owner || '.' || module, '.'), function) AS module_name,
7 type
8 FROM dbmshp_function_info
9 WHERE runid = 1
10 GROUP BY namespace, nvl(nullif(owner || '.' || module, '.'), function), type
11 ORDER BY total_ms DESC;
TOTAL [ms] TOT% CALLS CAL% NAMESPACE MODULE_NAME TYPE
---------- ----- ------ ----- --------- --------------------------- ------------
521 50.6 1 0.1 SQL __static_sql_exec_line5
462
44.9
1,214 76.7 PLSQL CHRIS.PERFECT_TRIANGLES PROCEDURE
44 4.3 88 5.6 SQL SYS.XML_SCHEMA_NAME_PRESENT PACKAGE BODY
1 0.1 44 2.8 PLSQL SYS.XML_SCHEMA_NAME_PRESENT PACKAGE BODY
1 0.1 3 0.2 PLSQL __plsql_vm
0 0.0 3 0.2 PLSQL __anonymous_block
0 0.0 46 2.9 PLSQL __plsql_vm@1
0 0.0 179 11.3 PLSQL SYS.DBMS_OUTPUT PACKAGE BODY
0 0.0 1 0.1 PLSQL SYS.DBMS_UTILITY PACKAGE BODY
0 0.0 1 0.1 PLSQL SYS.DBMS_SESSION PACKAGE BODY
0 0.0 1 0.1 PLSQL SYS.DBMS_APPLICATION_INFO PACKAGE BODY
0 0.0 1 0.1 PLSQL SYS.DBMS_APPLICATION_INFO PACKAGE SPEC
