What would cause performance degradation using the SQL-Ease Explain option?

Document ID : KB000021742
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

Using SQL-Ease for Explain processing, performance is impacted due to the creation of 2 tables (PLAN_TABLE and DSN_STATEMENT_TABLE) and their correlating indexes in the background. This occurs upon each Explain process. How can this be minimized?

Answer:

To alleviate SQL-Ease from creating these objects each time, change the Explain options for the PLAN_TABLE option from 'ROLLBACK' to 'COMMIT', thus making the Explain tables and indexes that are auto-created for the Explain process permanent. Once the objects are permanent, then the Explain options for the PLAN_TABLE option can revert back to 'ROLLBACK'.