Analyze Dynamic Parameters

Adaptive Server version 15.7 allows you to analyze dynamic parameters (which are indicated by question marks) before running a query, helping you avoid inefficient query plans.

Analyze the dynamic parameters using:

@@lwpid global variable – returns the object ID of the most recently prepared lightweight procedure that corresponds to a dynamic SQL prepare statement.
@@plwpid global variable – returns the object ID of the next most recently prepared lightweight procedure that corresponds to a dynamic SQL prepare statement.
show_dynamic_params_in_xml – displays information about parameters in dynamic SQL statements.

Using the value provided by @@plwpid as the value for the show_dynamic_params_in_xml object_id parameter, Adaptive Server displays information about the dynamic parameters in the query. Continue refining the query plan until you determine the parameters that provide you with the best query plan.

See "Displaying Query Optimization Strategies and Estimates" in the Performance and Tuning Series: Query Processing and Abstract Plans.