ASA SQL Reference
System Procedures and Functions
System and catalog stored procedures
Reports information about the execution time for each line within procedures that have been executed in a database.
sa_procedure_profile ( [ p_object_name [ , p_owner_name ] [ , p_table_name ] ] )
DBA authority required
None
sa_server_option system procedure
sa_procedure_profile_summary system procedure
Before you can profile your database, you must enable profiling.
For more information about enabling procedure profiling, see sa_server_option system procedure.
The result set includes information about the execution times for individual lines within procedures, and what percentage of the total procedure execution time those lines use. The DBA can use this profiling information to fine-tune slower procedures that may decrease performance. The procedure returns the same information for stored procedures, functions, events, and triggers as the Profile tab in Sybase Central.
The result set is as follows:
object_type The type of object. It can be:
P stored procedure
F function
E event
T trigger
object_name The name of the stored procedure, function, event, or trigger.
owner_name The object's owner.
table_name The table associated with a trigger (the value is NULL for other object types).
line_num The line number within the procedure.
executions The number of times the line has been executed.
millisecs The time to execute the line, in milliseconds.
percentage The percentage of the total execution time required for the specific line.
By calling the procedures of interest before you begin a profiling session, you eliminate the start-up time required for procedures to load and for the database to access tables for the first time.
The procedure accepts three optional arguments:
p_object_name Selects a specific object.
p_owner_name Selects all objects belonging to one owner.
p_table_name Selects all triggers associated with the specified table.
If you specify more than one of these arguments, you must list them in the order shown (p_object_name, p_owner_name, p_table_name). The arguments are strings, and must be enclosed in quotes. The server returns data for all procedures in the database if you do not include any arguments.
The following statement returns profiling information about the tr_manager trigger:
CALL sa_procedure_profile (p_object_name = 'tr_manager')