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

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » System Procedures » System procedures

sa_performance_statistics system procedure Next Page

sa_procedure_profile system procedure

Reports information about the execution time for each line within procedures, functions, events, or triggers that have been executed in a database. This procedure provides the same information as the Profile tab in Sybase Central.


[ filename
[, save_to_file ] ]

Result set
Column nameData typeDescription
object_type CHAR(1)The type of object. See the Remarks section below for a list of possible object types.
object_nameCHAR(128)The name of the stored procedure, function, event, or trigger. If the object_type is C or D, then this is the name of the foreign key for which the system trigger was defined.
owner_nameCHAR(128)The object's owner.
table_nameCHAR(128)The table associated with a trigger (the value is NULL for other object types).
line_num UNSIGNED INTEGERThe line number within the procedure.
executionsUNSIGNED INTEGERThe number of times the line has been executed.
millisecsUNSIGNED INTEGERThe time to execute the line, in milliseconds.
percentageDOUBLEThe percentage of the total execution time required for the specific line.
foreign_ownerCHAR(128)The database user who owns the foreign table for a system trigger.
foreign_tableCHAR(128)The name of the foreign table for a system trigger.

You can use this procedure to:

Since the result set includes information about the execution times for individual lines within procedures, triggers, functions, and events, as well as what percentage of the total procedure execution time those lines use, you can use this profiling information to fine-tune slower procedures that may decrease performance.

Before you can profile your database, you must enable profiling. See Enabling procedure profiling.

The object_type field of the result set can be:

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


DBA authority required

Side effects


See also

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

CALL sa_procedure_profile( );

The following statement returns the same detailed procedure profiling information as the example above, and saves it to a file called detailedinfo.txt:

CALL sa_procedure_profile( "detailedinfo.txt", 1 );

Either of the following statements can be used to load detailed procedure profiling information from a file called detailedinfoOLD.txt:

CALL sa_procedure_profile( "detailedinfoOLD.txt", 0 );
CALL sa_procedure_profile( "detailedinfoOLD.txt" );