Solution to the problem


The database administrator earlier concluded the problem for the performance slowdown, to be compilation of SQLs instead of hitting the package cache. The SQLs where compiled as literals are used in the workload, instead of parameter markers. Usage of parameter markers would cause the SQL to be compiled only once, and later execution of SQLs would hit the package cache directly. Hence, the literals in the workload are replaced with parameter markers.

Run sample workload



The SQL compilation problem scenario can be simulated by a java program(SampleCompilationProblem_NP.java) available in "[tutorial-base-directory]/extras" directory.

This program prepares and executes many SQL queries, which introduces many compilation at runtime on the server.

Steps to run workload on command line