Neoview Guide to Stored Procedures in Java (R2.3, R2.4)

Using the EXPLAIN Statement
Suppose that you want to display the execution plan for this CALL statement:
CALL persnl.adjustsalary(202,5.5,?);
Enter this EXPLAIN statement in an NCI session:
SQL>prepare spj1 from call persnl.adjustsalary(202,5.5,?);
--- SQL command prepared.
SQL>explain spj1;
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... SPJ1
PLAN_ID .................. 212060628692228717
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ call persnl.adjustsalary(202,5.5,?)
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value 1,140,064,381
xn_autoabort_interval -1
plan_version ....... 2,300
DETAILED_STATISTICS .... ACCUMULATED
AFFINITY_VALUE ......... -1
SCHEMA ................. NEO.INVENT
CATALOG ................ NEO
select_list ............ NUMERIC(8,2) SIGNED
input_variables ........ ?
CALL ====================================== SEQ_NO 1 NO CHILDREN
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
parameter_modes ........ I I O
routine_name ........... NEO.PERSNL.ADJUSTSALARY
sql_access_mode ........ MODIFIES SQL DATA
external_name .......... adjustSalary
external_file .......... Payroll
signature .............. (Ljava/math/BigDecimal;D[Ljava/math/BigDecimal;)V
language ............... Java
max_result_sets ........ 0
input_values ........... cast(202), cast(cast((cast(5.5) / cast(10))))
--- SQL operation complete.
SQL>
The EXPLAIN statement generates and displays all the columns of the result table of the EXPLAIN
function. For the syntax of the EXPLAIN statement, see the Neoview SQL Reference Manual. For
more information about optimizing query execution plans, see the Neoview Query Guide.
74 Performance and Troubleshooting