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.

Syntax 
sa_procedure_profile(  
  [ filename
  [, save_to_file ] ]
)
Arguments

  • filename   Use this optional LONG VARCHAR(128) parameter to specify the file to which the profiling information should be saved, or from which file it should be loaded. See the Remarks section below for more about saving and loading the profiling information.

  • save_to_file   Use this optional INT(1) parameter to specify whether to save the profiling information to a file, or load it from a previously stored file.

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. 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_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).
line_num UNSIGNED INTEGER The line number within the procedure.
executions UNSIGNED INTEGER The number of times the line has been executed.
millisecs UNSIGNED INTEGER The time to execute the line, in milliseconds.
percentage DOUBLE The percentage of the total execution time required for the specific line.
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 detailed procedure profiling information   To do this, you can simply call the procedure without specifying any arguments.

  • Save detailed procedure profiling information to file   To do this, you must include the filename argument and specify 1 for the save_to_file argument.

  • Load detailed procedure profiling information from a previously saved file   To do this, you must include the filename argument and specify 0 for the save_to_file argument (or leave it off, since the default is 0). 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 result set includes information about the execution times for individual lines within procedures, triggers, functions, and events, and 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 Enable procedure profiling.

The object_type field 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

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

Permissions

DBA authority required

Side effects

None

See also
Example

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" );
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1