Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Retrieving profiling information using system procedures

You can use system procedures to view procedure profiling information for the following objects: stored procedures, functions, events, system triggers, and triggers. You must be connected to the database as the DBA. Also, procedure profiling must already be enabled. See Enabling profiling using sa_server_option.

The sa_procedure_profile system procedure shows in-depth profiling information, including execution times for the lines within each object; each line in the result set represents an executable line of code in the object.

The sa_procedure_profile_summary system procedure shows you the overall execution time for each object, giving you a summary of all objects that ran; each line in the result set represents the execution details for one object.

When reviewing the results from these system procedures, there may be more objects listed than those specifically called. This is because one object can call another object. For example, a trigger might call a stored procedure that, in turn, calls another stored procedure.

For information on the syntax for, and the results returned by, these system procedures, see sa_procedure_profile_summary system procedure, and sa_procedure_profile system procedure.

To view summary profiling information (Interactive SQL)

1. Execute the sa_procedure_profile_summary system procedure.

   For example, enter:

   CALL sa_procedure_profile_summary;

2. Choose SQL > Execute.

   A result set with information about all of the procedures in your database appears on the Results pane.

To view in-depth profiling information (Interactive SQL)

1. Execute the sa_procedure_profile system procedure.

   For example, enter:

   CALL sa_procedure_profile;

2. Choose SQL > Execute.

   A result set with profiling information appears in the Results pane.