Collecting Debug Profiles for DML and SELECT Statements in Stored Procedures

{question}

How can I collect a debug profile for DML (INSERT, UPDATE, DELETE) or SELECT statements within a stored procedure?

{question}

{answer}

A debug profile is collected to analyze query performance issues. This profile provides valuable insights, such as table DDLs, statistics, cardinality estimates, and operator-level skew, which help diagnose performance bottlenecks.

Debug profiles are typically collected via Studio or CLI by executing the query with actual values substituted for any parameters or filters.

However, collecting debug profiles becomes challenging when DML or SELECT statements reside within a stored procedure (SP), especially if they depend on procedure parameters. Here's how to collect profiles in such cases:

If Parameter Values Are Known:

You can extract the query from the SP, replace parameters with actual values, and run it standalone with the PROFILE keyword to collect the debug profile.

If Parameter Values Are Unknown (or for runtime collection):

Follow these steps to collect the debug profile at runtime during SP execution:

1. Modify the Stored Procedure:

• • Prefix the target DML or SELECT statement inside the SP with the PROFILE keyword as shown below:
    
    

    DELIMITER //
    
    CREATE OR REPLACE PROCEDURE courses_sp (course_code TEXT, section_number INT, number_students INT) AS
    
      DECLARE
    
        code TEXT = UCASE(course_code);
        num_students INT = number_students + 1;
    
      BEGIN
    
        PROFILE INSERT INTO courses 
                SELECT code, section_number, num_students;
    
    END //
    
    DELIMITER ;

     

  • Note: If multiple statements are prefixed withPROFILEOnly the last one’s profile will be retained. To collect profiles for multiple statements:

    • • Prefix one statement at a time with PROFILE.

      • Compile the procedure.

      • Run the SP and collect the profile.

      • Repeat for each of the following statements.

2. Enable Required Session Variables:
Before executing the procedure (either inside the SP or externally), run:

SET profile_for_debug = ON;
SET sql_select_limit = 18446744073709551615;

Note: sql_select_limit removes the 300-row limit in Studio or SQL client in general.

3. Execute the Stored Procedure:
Run the procedure with the intended parameters.

Example:

CALL courses_sp('MATHS101', 1, 20);

4. Collect the Debug Profile:
After execution, run:

SHOW PROFILE JSON;

Optionally, use INTO OUTFILE '/path' to save the output if the profile is large.

If using Studio, you can simply click "Save as CSV" to export the profile.

Limitation:
The above method does not apply to INSERT INTO..VALUES statements in the stored procedures.

{answer}