Using SQL Trace Facility

NuoDB SQL lets you log non-DDL statements to a persistent table that users can query. This trace facility allows users to review the SQL statements that execute, including the explain plan, which can assist with performance tuning. The trace table can also be used for debugging purposes, especially when trying to identify the statements that execute inside a stored procedure. This trace facility can also be useful for extracting all non-DDL SQL statements that executed and saving them to a file for replaying. It should be noted that turning the trace facility on can have a negative impact on performance due to the overhead of inserting the statements into the trace table.

The table that NuoDB SQL logs to does not exist as a database object until tracing is turned on the first time. The user that turns on tracing initially, causing the trace table to be created, is the owner of the trace table. Once the trace table exists, it is a persistent table defined to the database. Only the table owner can DROP the trace table or DELETE rows from the trace table. The table owner can grant the DELETE privilege on the trace table to other users. The next time tracing is turned on, this same persistent table will be used for logging.

About Default Trace Behavior

Use the SET command to turn tracing on or off. By default, NuoDB SQL tracing behaves such that it will:

When you turn tracing on, you can specify optional parameters (see opt_trace_params below) that allow the user to override this default behavior. This can be useful if a user wants to set tracing on at the CONNECTION level and create their own trace table to log to. Only the SQL statements that this user executes on the current connection will be logged to this table. And only this user will have the ability to DROP or DELETE rows from this table.

Dropping a trace table is the equivalent of issuing the SET command to turn trace off. Statements can no longer be logged to the trace table. To resume trace, you must set trace off and then set trace back on and specify a table name that is different from the name of the table that was dropped. If you do not specify a different table name, NuoDB SQL does not create the trace table.

Tracing does not log any of the SHOW command statements executed or any statements that the system generates when the SHOW command is executed.

Format for Turning Tracing On

The SET command format for controlling tracing is as follows:

SET [ trace_level ] TRACE { ON [ opt_trace_params ] | OFF }

Defining the Trace Level

The NuoDB SQL trace facility allows for an optional trace_level parameter that can be specified when setting tracing on. This option defines which non-DDL statements will be logged. The valid values are CONNECTION or GLOBAL. If no option is specified, the default is CONNECTION. These tracing levels are defined as:

CONNECTION level tracing takes precedence over GLOBAL level tracing. Suppose that GLOBAL level tracing has been turned on for a given database and you connect to that database and turn CONNECTION level tracing on. The statements that you execute will be logged to the trace table that has been defined for CONNECTION level tracing. The statements that all other users of the database execute will continue to be logged to the trace table that has been defined for GLOBAL level tracing. After you have turned tracing off at the CONNECTION level, the statements that you execute will log to the trace table that has been defined for GLOBAL level tracing.

A user can use the SHOW command to view the current trace parameter settings. This will show both the GLOBAL and CONNECTION level trace settings. By default, tracing is set to off.

SHOW
    Database Settings:
...

    System Properties:
...
                           DB_TRACE:                  OFF
                     DB_TRACE_TABLE: TRACESCHEMA.TRACETABLE
                   DB_TRACE_PATTERN:                     
                  DB_TRACE_MIN_TIME:             0.000000
                DB_TRACE_PROCEDURES:                     

    NuoSQL Settings:
...
	connection trace is set to OFF  

Parameters for Changing Default Tracing Behavior

NuoDB SQL logs all non-DDL statements to a particular table according to the defined level of tracing. You can change the default behavior by specifying one or more parameters when you turn tracing on. For example, you can control which non-DDL statements are logged. You can also override the default trace table where the statements will be logged.

To change default tracing behavior, replace opt_trace_params with one or more parameter name/value pairs in the following format:

parameter_name parameter_value [ , parameter_name parameter_value , ... ]

The parameters you can specify are described below. The settings you specify are in effect as long as tracing is turned on. The settings reset to the default values when tracing is turned off. One or more of these parameters can be specified when tracing is turned on. If more than one parameter value is provided, only SQL statements that meet ALL criteria defined by these parameter values are logged.

If tracing is already turned on, a user can change the tracing behavior by executing the SET command to turn tracing on and providing different values for these parameter settings. This has the same effect as turning tracing off and then turning tracing back on.

Description of Trace Table Content

The following table describes the content of a trace table.

Column Type Description
SQLSTRING string A non-DDL SQL statement that was executed and logged. A SQL statement is logged upon completion of execution. A logged statement can be the execute statement for a stored procedure as well as a processing block statement, such as an IF statement, WHILE loop, FOR loop, from a stored procedure.
COMPILETIME bigint

In microseconds, the amount of time it took to compile the executed SQL statement. If the statement is already cached, this can be 0.

For stored procedures, this will usually be 0 because stored procedures are compiled when they are created. The exception to this is when the stored procedure contains dynamic SQL. The dynamic SQL statements are compiled at runtime. Therefore, this COMPILETIME is the sum of the compile time of all dynamic SQL statements.

RUNTIME bigint The duration of the statement execution specified in microseconds. This RUNTIME also includes the COMPILETIME for the statement. To compute actual RUNTIME only, subtract COMPILETIME from RUNTIME.
USER string The SQL username for the database connection that executed the SQL statement.
SCHEMA string The current SQL schema for the database connection where the SQL statement being logged is being executed. The SQL statement itself can contain database objects from different schemas.
NUMPARAM integer

The number of SQL parameters specified in the SQL statement. This is 0 if there are no parameter values.

For an execute statement for a stored procedure, this includes parameters in statements that are executed inside the stored procedure.This does not include parameters that are passed to a stored procedure, unless it is an actual NuoDB SQL parameter, for example, execute proc1(?,?)).

PARAMS string A string that represents the parameters specified in the statement. Each parameter is delimited from the next with a '#' character. This can be ambiguous if the parameter string also happens to contain the '#' character. If the parameter is an assigned value to a variable in a stored procedure, both the variable name and the value are logged. See Example 3.
CALLSTACK string The call stack lists the names of stored procedures that are currently active and that lead to the execution of SQL statements being logged. See Example 4.
NODEID integer The identifier for the transaction engine (TE) to which the client was connected.
CONNID integer A unique identifier for the connection.
EXECID integer

An identifier assigned to each SQL statement executed on a connection and logged to the trace table. This identifier:

  • Is unique across all statements logged for a given connection.
  • Starts with a value of 1 and is incremented by 1 for each statement logged on the connection.
  • Is reset back to 1 for each user connection.
EXECBLOCKID string

Specifies the EXECIDs of all statements that are currently active in the CALLSTACK and lead to the execution of this current SQL statement that is being logged.

For example, an EXECUTE statement for a stored procedure is assigned an EXECID of 1. The first SQL statement that is executed inside that stored procedure is assigned an EXECID of 2. Since the EXECUTE statement for the stored procedure remains active in the CALLSTACK, the EXECID for this EXECUTE statement is assigned to the EXECBLOCKID column of the row being inserted for this first SQL statement that is executed inside that stored procedure. See Example 4.

TRANSID integer The identifier for the transaction that was executed on the connection and logged to the trace table. This value corresponds to ID in SYSTEM.TRANSACTIONS.
NROWS integer The number of rows in the result set of a SELECT statement or the number of records updated for DML statements.
TIMESTAMP string Indicates when statement execution began.
EXPLAIN string The EXPLAIN string for the statement. See EXPLAIN for more information. Due to the large size of this column, SET OUTPUT VERTICAL can be useful when browsing the contents of the trace table.

Examples

Example 2
This example turns on tracing for statements that contain the word SELECT. In this example, both the INSERT and the SELECT statements are logged to the trace table. The DELETE statement is not logged to the trace table.

SET TRACE ON TABLE traceconnection.tracetable, PATTERN 'SELECT';

SHOW;
...
	connection trace is set to ON table TRACECONNECTION.TRACETABLE, pattern 'SELECT'

USE hockey;
DROP TABLE IF EXISTS hockeydup;
CREATE TABLE hockeydup (number INTEGER,name STRING, position STRING, team STRING);
INSERT INTO hockeydup (SELECT number,name,position,team FROM hockey;

SELECT * FROM hockeydup limit 1;
 NUMBER        NAME       POSITION   TEAM  
 ------- ---------------- --------- ------ 
   37    PATRICE BERGERON  Forward  Bruins 

DELETE from hockeydup;
SELECT SQLSTRING,EXECID,EXECBLOCKID,PARAMS,COMPILETIME,RUNTIME FROM traceconnection.tracetable;

                               SQLSTRING                               EXECID  EXECBLOCKID  PARAMS  COMPILETIME  RUNTIME  
 --------------------------------------------------------------------- ------- ------------ ------- ------------ -------- 

 INSERT INTO hockeydup (SELECT number,name,position,team FROM hockey);    1                             2331       2700   
 SELECT * FROM hockeydup limit 1;                                         2                              205        342   


Example 3
This example shows tracing for SQL statements that are executed in a stored procedure. After executing the prc_trace_proc(35) stored procedure, the output from the trace table shows:

Row Description of what is logged in the trace table
1 The INSERT into the hockeydup table. Note the CALLSTACK column has the stored procedure name, prc_trace_proc(). The EXECBLOCKID is the EXECID of the EXECUTEprc_trace_proc() statement (row #17). SQL statements are logged when they finish execution, not when they begin execution. Therefore, a stored procedure is not logged until after all of its SQL statements are logged.
2 The SELECT statement that executes as defined by the FOR SELECT statement (row #16). This SELECT statement is logged for each iteration of the FOR SELECT statement. The EXECBLOCKID shows the EXECID of both the stored procedure execute statement and the FOR SELECT statement.
3-6 The variable assignment statements that execute inside the FOR SELECT statement of the EXECUTE stored procedure statement.
7 The variable assignment executed in the nested stored procedure, prc_trace_proc_nested(). Note the CALLSTACK column, which shows all active SQL statements at the time this statement executed. It shows both the calling stored procedure and the current stored procedure that executed this statement. Also, the EXECBLOCKID shows the EXECID of the currently active statements.
8 The IF statement being evaluated. (row #11). The fact that this IF statement is logged to the trace table does not necessarily mean it evaluated to TRUE. This can be tested by checking to see if the statements inside the IF processing block executed (see Row #9).
9 The UPDATE statement that executed as a result of the IF statement evaluating to TRUE.
10
  • A variable assignment.
  • 11
  • The IF processing block statement about to be evaluated (see Row #8 where it is evaluated).
  • 12

    The nested stored procedure, prc_trace_proc_nested(), being executed.

    13 The IF statement being evaluated (Row #15).
    14 The INSERT statement that executed as a result of the IF statement evaluating to TRUE (Row #13).
    15

    The IF processing block statement about to be evaluated (Row #13).

    16 The FOR SELECT processing block statement that will be executed and return a result set (Row #2).
    17 The stored procedure, prc_trace_proc(), being executed.
    SET CONNECTION TRACE ON TABLE traceschema.connectiontrace;
    
    USE HOCKEY;
    DROP PROCEDURE IF EXISTS prc_trace_proc;
    DROP PROCEDURE IF EXISTS prc_trace_proc_nested;
    DROP TABLE IF EXISTS hockeydup;
    DROP TABLE IF EXISTS hockeygoalies;
     
    CREATE TABLE hockeydup 
    (NUMBER integer
    ,NAME string
    ,POSITION string
    ,TEAM string);
    
    CREATE TABLE hockeygoalies
    (NUMBER integer
    ,NAME string
    ,TEAM string);
    
    SET DELIMITER @
    create procedure prc_trace_proc_nested (IN i_number INTEGER, IN i_position STRING, OUT o_Updated STRING)
    AS
        o_Updated = 'N';
        IF ( i_position = 'Goalie' )
            UPDATE hockey.hockeydup SET position = 'Goalie-Updated'
              WHERE number = i_number;
            o_Updated = 'Y';
        END_IF;
    END_PROCEDURE;
    @
    
    create procedure prc_trace_proc (IN i_number INT)
    AS
       VAR l_Updated STRING, l_number INT, l_name STRING, l_position STRING, l_team STRING;
       INSERT INTO hockeydup 
          SELECT number,name,position,team FROM hockey.hockey
            WHERE number = i_number;
       FOR SELECT number,name,position,team FROM hockeydup;
          l_number = number;
          l_name = name;
          l_position = position;
          l_team = team;
          EXECUTE prc_trace_proc_nested (l_number,l_position,l_Updated);
          IF ( l_Updated = 'Y' )
              INSERT INTO hockeygoalies VALUES (l_number,l_name,l_team);
          END_IF;
       END_FOR;
    END_PROCEDURE;
    @
    SET DELIMITER ;
    
    EXECUTE prc_trace_proc(35);
    
    set output vertical;
    SELECT sqlstring,compiletime,runtime,numparam,params,callstack,execid,execblockid,nrows FROM traceschema.connectiontrace;
    ==================================== Row #1 ====================================
    SQLSTRING: INSERT INTO hockeydup        SELECT number,name,position,team FROM hockey.hockey         WHERE number = i_number
    COMPILETIME: 0
    RUNTIME: 119
    NUMPARAM: 0
    PARAMS: 
    CALLSTACK: HOCKEY.PRC_TRACE_PROC
    EXECID: 61
    EXECBLOCKID: 60
    NROWS: 1
    ==================================== Row #2 ====================================
    SQLSTRING: SELECT number,name,position,team FROM hockeydup
    COMPILETIME: 0
    RUNTIME: 2
    NUMPARAM: 0
    PARAMS: 
    CALLSTACK: HOCKEY.PRC_TRACE_PROC
    EXECID: 63
    EXECBLOCKID: 60;62
    NROWS: 0
    ==================================== Row #3 ====================================
    SQLSTRING: l_number = number
    COMPILETIME: 0
    RUNTIME: 5
    NUMPARAM: 2
    PARAMS: L_NUMBER=35#NUMBER=35
    CALLSTACK: HOCKEY.PRC_TRACE_PROC
    EXECID: 64
    EXECBLOCKID: 60;62
    NROWS: 0
    ==================================== Row #4 ====================================
    SQLSTRING: l_name = name
    COMPILETIME: 0
    RUNTIME: 4
    NUMPARAM: 2
    PARAMS: L_NAME=ANTON KHUDOBIN#NAME=ANTON KHUDOBIN
    CALLSTACK: HOCKEY.PRC_TRACE_PROC
    EXECID: 65
    EXECBLOCKID: 60;62
    NROWS: 0
    ==================================== Row #5 ====================================
    SQLSTRING: l_position = position
    COMPILETIME: 0
    RUNTIME: 4
    NUMPARAM: 2
    PARAMS: L_POSITION=Goalie#POSITION=Goalie
    CALLSTACK: HOCKEY.PRC_TRACE_PROC
    EXECID: 66
    EXECBLOCKID: 60;62
    NROWS: 0
    ==================================== Row #6 ====================================
    SQLSTRING: l_team = team
    COMPILETIME: 0
    RUNTIME: 4
    NUMPARAM: 2
    PARAMS: L_TEAM=Bruins#TEAM=Bruins
    CALLSTACK: HOCKEY.PRC_TRACE_PROC
    EXECID: 67
    EXECBLOCKID: 60;62
    NROWS: 0
    ==================================== Row #7 ====================================
    SQLSTRING: o_Updated = 'N'
    COMPILETIME: 0
    RUNTIME: 4
    NUMPARAM: 1
    PARAMS: O_UPDATED=N
    CALLSTACK: HOCKEY.PRC_TRACE_PROC;HOCKEY.PRC_TRACE_PROC_NESTED
    EXECID: 69
    EXECBLOCKID: 60;62;68
    NROWS: 0
    ==================================== Row #8 ====================================
    SQLSTRING: ( i_position = 'Goalie' )
    COMPILETIME: 0
    RUNTIME: 1
    NUMPARAM: 1
    PARAMS: I_POSITION=Goalie
    CALLSTACK: HOCKEY.PRC_TRACE_PROC;HOCKEY.PRC_TRACE_PROC_NESTED
    EXECID: 71
    EXECBLOCKID: 60;62;68;70
    NROWS: 0
    ==================================== Row #9 ====================================
    SQLSTRING: UPDATE hockey.hockeydup SET position = 'Goalie-Updated'           WHERE number = i_number
    COMPILETIME: 0
    RUNTIME: 19
    NUMPARAM: 1
    PARAMS: I_NUMBER=35
    CALLSTACK: HOCKEY.PRC_TRACE_PROC;HOCKEY.PRC_TRACE_PROC_NESTED
    EXECID: 72
    EXECBLOCKID: 60;62;68;70
    NROWS: 1
    ==================================== Row #10 ===================================
    SQLSTRING: o_Updated = 'Y'
    COMPILETIME: 0
    RUNTIME: 4
    NUMPARAM: 1
    PARAMS: O_UPDATED=Y
    CALLSTACK: HOCKEY.PRC_TRACE_PROC;HOCKEY.PRC_TRACE_PROC_NESTED
    EXECID: 73
    EXECBLOCKID: 60;62;68;70
    NROWS: 0
    ==================================== Row #11 ===================================
    SQLSTRING: IF ( i_position = 'Goalie' )         UPDATE hockey.hockeydup SET position = 'Goalie-Updated'           WHERE number = i_number;         o_Updated = 'Y';     END_IF
    COMPILETIME: 0
    RUNTIME: 24
    NUMPARAM: 3
    PARAMS: I_NUMBER=35#I_POSITION=Goalie#O_UPDATED=Y
    CALLSTACK: HOCKEY.PRC_TRACE_PROC;HOCKEY.PRC_TRACE_PROC_NESTED
    EXECID: 70
    EXECBLOCKID: 60;62;68
    NROWS: 1
    ==================================== Row #12 ===================================
    SQLSTRING: EXECUTE prc_trace_proc_nested (l_number,l_position,l_Updated)
    COMPILETIME: 0
    RUNTIME: 28
    NUMPARAM: 3
    PARAMS: L_NUMBER=35#L_POSITION=Goalie#L_UPDATED=Y
    CALLSTACK: HOCKEY.PRC_TRACE_PROC
    EXECID: 68
    EXECBLOCKID: 60;62
    NROWS: 1
    ==================================== Row #13 ===================================
    SQLSTRING: ( l_Updated = 'Y' )
    COMPILETIME: 0
    RUNTIME: 1
    NUMPARAM: 1
    PARAMS: L_UPDATED=Y
    CALLSTACK: HOCKEY.PRC_TRACE_PROC
    EXECID: 75
    EXECBLOCKID: 60;62;74
    NROWS: 0
    ==================================== Row #14 ===================================
    SQLSTRING: INSERT INTO hockeygoalies VALUES (l_number,l_name,l_team)
    COMPILETIME: 0
    RUNTIME: 59
    NUMPARAM: 3
    PARAMS: L_NAME=ANTON KHUDOBIN#L_NUMBER=35#L_TEAM=Bruins
    CALLSTACK: HOCKEY.PRC_TRACE_PROC
    EXECID: 76
    EXECBLOCKID: 60;62;74
    NROWS: 1
    ==================================== Row #15 ===================================
    SQLSTRING: IF ( l_Updated = 'Y' )           INSERT INTO hockeygoalies VALUES (l_number,l_name,l_team);       END_IF
    COMPILETIME: 0
    RUNTIME: 60
    NUMPARAM: 4
    PARAMS: L_NAME=ANTON KHUDOBIN#L_NUMBER=35#L_TEAM=Bruins#L_UPDATED=Y
    CALLSTACK: HOCKEY.PRC_TRACE_PROC
    EXECID: 74
    EXECBLOCKID: 60;62
    NROWS: 1
    ==================================== Row #16 ===================================
    SQLSTRING: FOR SELECT number,name,position,team FROM hockeydup;       l_number = number;       l_name = name;       l_position = position;       l_team = team;       EXECUTE prc_trace_proc_nested (l_number,l_position,l_Updated);       IF ( l_Updated = 'Y' )           INSERT INTO hockeygoalies VALUES (l_number,l_name,l_team);       END_IF;    END_FOR
    COMPILETIME: 0
    RUNTIME: 107
    NUMPARAM: 9
    PARAMS: L_NAME=ANTON KHUDOBIN#L_NUMBER=35#L_POSITION=Goalie#L_TEAM=Bruins#L_UPDATED=Y#NAME=ANTON KHUDOBIN#NUMBER=35#POSITION=Goalie#TEAM=Bruins
    CALLSTACK: HOCKEY.PRC_TRACE_PROC
    EXECID: 62
    EXECBLOCKID: 60
    NROWS: 2
    ==================================== Row #17 ===================================
    SQLSTRING: EXECUTE prc_trace_proc(35);
    COMPILETIME: 208
    RUNTIME: 434
    NUMPARAM: 0
    PARAMS: 
    CALLSTACK: 
    EXECID: 60
    EXECBLOCKID: 
    NROWS: 3
    

    To replay the statements in the order they executed, you can select the SQL statements and their parameters sorted by EXECID.

    SELECT sqlstring,params FROM traceschema.connectiontrace ORDER BY execid;
    ==================================== Row #1 ====================================
    SQLSTRING: EXECUTE prc_trace_proc(35);
    PARAMS: 
    ==================================== Row #2 ====================================
    SQLSTRING: INSERT INTO hockeydup        SELECT number,name,position,team FROM hockey.hockey         WHERE number = i_number
    PARAMS: 
    ==================================== Row #3 ====================================
    SQLSTRING: FOR SELECT number,name,position,team FROM hockeydup;       l_number = number;       l_name = name;       l_position = position;       l_team = team;       EXECUTE prc_trace_proc_nested (l_number,l_position,l_Updated);       IF ( l_Updated = 'Y' )           INSERT INTO hockeygoalies VALUES (l_number,l_name,l_team);       END_IF;    END_FOR
    PARAMS: L_NAME=ANTON KHUDOBIN#L_NUMBER=35#L_POSITION=Goalie#L_TEAM=Bruins#L_UPDATED=Y#NAME=ANTON KHUDOBIN#NUMBER=35#POSITION=Goalie#TEAM=Bruins
    ==================================== Row #4 ====================================
    SQLSTRING: SELECT number,name,position,team FROM hockeydup
    PARAMS: 
    ==================================== Row #5 ====================================
    SQLSTRING: l_number = number
    PARAMS: L_NUMBER=35#NUMBER=35
    ==================================== Row #6 ====================================
    SQLSTRING: l_name = name
    PARAMS: L_NAME=ANTON KHUDOBIN#NAME=ANTON KHUDOBIN
    ==================================== Row #7 ====================================
    SQLSTRING: l_position = position
    PARAMS: L_POSITION=Goalie#POSITION=Goalie
    ==================================== Row #8 ====================================
    SQLSTRING: l_team = team
    PARAMS: L_TEAM=Bruins#TEAM=Bruins
    ==================================== Row #9 ====================================
    SQLSTRING: EXECUTE prc_trace_proc_nested (l_number,l_position,l_Updated)
    PARAMS: L_NUMBER=35#L_POSITION=Goalie#L_UPDATED=Y
    ==================================== Row #10 ===================================
    SQLSTRING: o_Updated = 'N'
    PARAMS: O_UPDATED=N
    ==================================== Row #11 ===================================
    SQLSTRING: IF ( i_position = 'Goalie' )         UPDATE hockey.hockeydup SET position = 'Goalie-Updated'           WHERE number = i_number;         o_Updated = 'Y';     END_IF
    PARAMS: I_NUMBER=35#I_POSITION=Goalie#O_UPDATED=Y
    ==================================== Row #12 ===================================
    SQLSTRING: ( i_position = 'Goalie' )
    PARAMS: I_POSITION=Goalie
    ==================================== Row #13 ===================================
    SQLSTRING: UPDATE hockey.hockeydup SET position = 'Goalie-Updated'           WHERE number = i_number
    PARAMS: I_NUMBER=35
    ==================================== Row #14 ===================================
    SQLSTRING: o_Updated = 'Y'
    PARAMS: O_UPDATED=Y
    ==================================== Row #15 ===================================
    SQLSTRING: IF ( l_Updated = 'Y' )           INSERT INTO hockeygoalies VALUES (l_number,l_name,l_team);       END_IF
    PARAMS: L_NAME=ANTON KHUDOBIN#L_NUMBER=35#L_TEAM=Bruins#L_UPDATED=Y
    ==================================== Row #16 ===================================
    SQLSTRING: ( l_Updated = 'Y' )
    PARAMS: L_UPDATED=Y
    ==================================== Row #17 ===================================
    SQLSTRING: INSERT INTO hockeygoalies VALUES (l_number,l_name,l_team)
    PARAMS: L_NAME=ANTON KHUDOBIN#L_NUMBER=35#L_TEAM=Bruins

    Example 4
    The following example shows how to log SQL statements for only two stored procedures. No other SQL statements that execute will be logged to the trace table.

    SET CONNECTION TRACE ON TABLE traceconnection.tracetable, PROCEDURES (test.proc_test_trace1,test.proc_test_trace3);
    
    USE TEST;
     
    DROP PROCEDURE IF EXISTS proc_test_trace1;
    DROP PROCEDURE IF EXISTS proc_test_trace2;
    DROP PROCEDURE IF EXISTS proc_test_trace3;
    
    DROP TABLE IF EXISTS test_proc;
    CREATE TABLE test_proc (column1 INT, column2 INT);
    
    CREATE PROCEDURE proc_test_trace1 AS INSERT INTO test_proc VALUES (3,3),(4,4); END_PROCEDURE;
    CREATE PROCEDURE proc_test_trace2 AS INSERT INTO test_proc VALUES (5,5),(6,6); END_PROCEDURE;
    CREATE PROCEDURE proc_test_trace3 AS INSERT INTO test_proc VALUES (7,7),(8,8); END_PROCEDURE;
    
    INSERT INTO test_proc VALUES (1,1),(2,2);
    EXECUTE proc_test_trace1;
    EXECUTE proc_test_trace2;
    EXECUTE proc_test_trace3;
    
    SQL> SELECT * FROM test_proc;
     COLUMN1  COLUMN2  
     -------- -------- 
        1        1     
        2        2     
        3        3     
        4        4     
        5        5     
        6        6     
        7        7     
        8        8     
    
    SQL> SELECT sqlstring, execid, execblockid, callstack FROM traceconnection.tracetable;
                    SQLSTRING                 EXECID  EXECBLOCKID        CALLSTACK       
     ---------------------------------------- ------- ------------ --------------------- 
     INSERT INTO test_proc VALUES (3,3),(4,4)   43         42      TEST.PROC_TEST_TRACE1 
     EXECUTE proc_test_trace1;                  42                                       
     INSERT INTO test_proc VALUES (7,7),(8,8)   47         46      TEST.PROC_TEST_TRACE3 
     EXECUTE proc_test_trace3;                  46