|
Even though it is called procedure profiling, you are actually able to view profiling results for stored procedures, user-defined functions, triggers, system triggers, and events in your database.
If you have not already done so, connect to the database as user DBA, and enable procedure profiling. See Enabling procedure profiling.
In the left pane, select the type of object for which you would like to see profiling information. Your choice must be one of the following: Triggers, System Triggers, Procedures & Functions, or Events.
A list of all objects in the database for that type appears in the right pane. For example, if you selected Procedures & Functions, the list of all procedures and user-defined functions in the database appears.
In the right pane, click the Profiling Results tab.
A list appears of all the objects of the selected type that have executed since you enabled procedure profiling. For example, if you selected Procedures & Functions, a list of all of the procedures and user-defined functions that have executed since you enabled procedure profiling displays.
If you expected to find an object there but it is missing, for example an event, it may be because it hasn't executed yet. Or, it may have executed but the view has not yet been refreshed. While details are refreshed periodically, you can cause a refresh at any time by pressing F5.
If you find more objects listed than you expected, remember that one object can call other objects, so there may be more items listed than those that users explicitly called.
To view in-depth profiling results for a specific object, for example, line-by-line execution details, double-click the object on the Profiling Results tab.
The right pane details are replaced with in-depth profiling information for the object.
The Profiling Results tab provides a summary of the profiling information for all of the objects, grouped by type, that have been executed within the database since you started procedure profiling. The information displayed includes:
| Column | Description |
|---|---|
| Name | The name of the object. |
| Owner | The owner of the object. |
| Table or Table Name | The table a trigger belongs to (this column only appears on the database Profile tab). |
| Event | The type of object, for example, a procedure. |
| Type | The type of trigger for system triggers. This can be Update or Delete. |
| # Execs. | The number times each object has been called. |
| # msecs. | The total execution time for each object. |
These columns, and their content, may vary depending on the type of object.
When you double-click a specific object, such as a procedure, in the Profiling Results tab, in-depth information specific to that object appears. The information displayed includes:
| Column | Description |
|---|---|
| Execs | The number of times the line of code in the object was executed. |
| Milliseconds | The total amount of time that a line took to execute. |
| % | The percent of total time that a line took to execute. |
| Line | The line number within the object. |
| Source | The code that was executed. |
Lines with long execution times compared to other lines in the code should be analyzed to see whether there is a more efficient way to achieve the same functionality. You must be connected to the database, have profiling enabled, and have DBA authority to access procedure profiling information.