sa_procedure_profile_summary System Procedure

Syntax

sa_procedure_profile_summary(    
    [ filename   
    [, save_to_file ] ] )

Parameters

filename – use this optional LONG VARCHAR parameter to specify the file to which the profiling information is saved, or from which file it should be loaded. The default is NULL. See the Remarks section below for more about saving and loading the profiling information.
save-to-file – use this optional INTEGER parameter to specify whether to save the summary information to a file, or to load it from a previously saved file. The default is 0.

Result Set

Column Name	Data Type	Description
object_type	CHAR(1)	The type of object. See the Remarks section below for a list of possible object types.
object_name	CHAR(128)	The name of the stored procedure, function, event, or trigger.
owner_name	CHAR(128)	The object's owner.
table_name	CHAR(128)	The table associated with a trigger (the value is NULL for other object types).
executions	UNSIGNED INTEGER	The number of times each procedure has been executed.
millisecs	UNSIGNED INTEGER	The time to execute the procedure, in milliseconds.
foreign_owner	CHAR(128)	The database user who owns the foreign table for a system trigger.
foreign_table	CHAR(128)	The name of the foreign table for a system trigger.

Remarks

You can use this procedure to:

Return current summary information – To do this, you can simply call the procedure without specifying any arguments.
Save current summary information to file – To do this, you must include the filename argument and specify 1 for the save_to_file argument.
Load stored summary information from a file – To do this, you must include the filename argument and specify 0 for the save_to_file argument. When using the procedure in this way, the loaded file must have been created by the same database as the one from which you are running the procedure; otherwise, the results may be unusable.

Since the procedure returns information about the usage frequency and efficiency of stored procedures, functions, events, and triggers, you can use this information to fine-tune slower procedures to improve database performance.

Before you can profile your database, you must enable profiling.

The object_type column of the result set can be:

P – stored procedure
F – function
E – event
T – trigger
C – ON UPDATE system trigger
D – ON DELETE system trigger

f you want line by line details for each execution instead of summary information, use the sa_procedure_profile procedure instead.

Privileges

You must have EXECUTE privilege on the system procedure, as well as the the MONITOR or MANAGE PROFILING system privilege.

You must also have the following privileges:

SELECT ANY TABLE (when filename is not NULL and save_to_file is 1)
LOAD ANY TABLE (when filename is not NULL and save_to_file is 0)

Example

The following statement returns the execution time for any procedure, function, event, or trigger that has been executed in the database:

CALL sa_procedure_profile_summary( );

The following statement returns the same summary information as the previous example, and saves it to a file called summaryinfo.txt:

CALL sa_procedure_profile_summary( 'summaryinfo.txt', 1 );

Either of the following statements can be used to load stored summary information from a file called summaryinfo.txt:

CALL sa_procedure_profile_summary( 'summaryinfo'.txt, 0 );
CALL sa_procedure_profile_summary( 'summaryinfo.txt' );