@ExplainProc

Documentation

VoltDB Home » Documentation » Using VoltDB

@ExplainProc

@ExplainProc — Returns the execution plans for all SQL queries in the specified stored procedure.

Synopsis

@ExplainProc String procedure-name

Description

The @ExplainProc system procedure returns the execution plans for all of the SQL queries within the specified stored procedure. Execution, or explain, plans describe how VoltDB expects to execute the queries at runtime, including what indexes are used, the order the tables are joined, and so on. Execution plans are useful for identifying performance issues in query and stored procedure design. See the chapter on execution plans in the VoltDB Performance Guide for information on how to interpret the plans.

Return Values

Returns one VoltTable with one row for each query in the stored procedure.

NameDatatypeDescription
SQL_STATEMENTVARCHARThe SQL query.
EXECUTION_PLANVARCHARThe execution plan as text.

Examples

The following example uses @ExplainProc to evaluate the execution plans associated with the ContestantWinningStates stored procedure in the voter sample application.

try {
    VoltTable[] results = client.callProcedure("@ExplainProc",
       "ContestantWinningStates" ).getResults();
    results[0].resetRowPosition();
    while (results[0].advanceRow()) {
         System.out.printf("Query: %d\nPlan:\n%d",
         results[0].getString(0),results[0].getString(1));
    }
}
catch (Exception e) {
    e.printStackTrace();
}

In the sqlcmd utility, the "explainproc" command is a shortcut for "exec @ExplainProc". So the following two commands are equivalent:

$ sqlcmd
1> exec @ExplainProc 'ContestantWinningStates';
2> explainproc ContestantWinningStates;
>