ASA SQL Reference
System Procedures and Functions
System and catalog stored procedures
Reports summary information about the execution times for all procedures that have been executed in a database.
sa_procedure_profile_summary ( [p_table_name [ , p_owner_name ] [ , p_object_name ] [ , p_object_type ] [ , p_ordering ] ] )
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 procedure displays 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. The procedure returns the same information for stored procedures, functions, events, and triggers as the Profile tab in Sybase Central.
The procedure returns the following results:
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).
executions The number of times each procedure has been executed.
millisecs The time to execute the procedure, in milliseconds.
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 five 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 from a specified table.
p_object_type Selects the type of object to profile. It can be one of the following:
P stored procedure
F function
E event
T trigger
p_ordering Determines the order of columns in the result set. If no value is given, the results are listed from the longest execution time to the shortest execution time. Values and the resulting order are:
P object_type, owner_name, object_name, table_name desc
N object_name, owner_name, table_name, object_type desc
O owner_name, object_type, object_name, table_name desc
T table_name, owner_name, object_name, object_type desc
E executions desc, object_name, owner_name, table_name, object_type desc
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, p_object_type, p_ordering). If you specify any of these arguments, the procedure returns only rows that match the parameters; otherwise, the server returns data for all procedures in the database. Note that the argument values are strings, and must be enclosed in quotes.
The following statement returns profiling information about all the triggers owned by the DBA on the Product table:
CALL sa_procedure_profile_summary (p_owner_name = 'dba', p_table_name = 'Product', p_object_type = 'T')