Neoview Guide to Stored Procedures in Java (R2.5)

Performance Tip
To ensure the optimal performance of SPJs on the Neoview platform, avoid nesting CALL
statements in an SPJ method, which wastes resources and might diminish performance. For more
information, see “Nested Java Method Invocations” (page 27).
Displaying an Execution Plan of a CALL Statement
An execution plan reveals how a CALL statement was optimized. You can display all or part of
the execution plan for a CALL statement by using the EXPLAIN statement or function.
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
62 Performance and Troubleshooting