1. The PegaRULES.log will log the SQL Query alongwith the PreparedStatement parameter inserts.
2. Reformat the SQL query substituting the PreparedStatement parameters "?" with the Insert values inside the angular brackets <>.
3. Provide the SQL query to your Oracle DBA to run the explain plan and analyze the cost and time taken. If a database table is frequently modified the database table's indexes get fragmented over time changing the execution plan. Oracle Update Statistics maintenance jobs should be run frequently on such tables.
4. Run Update Statistics on the table and take explain plan again and observe any cost difference. The DBA will also be able to see if adding / modifying indexes would help.
Posted: 5 years ago
Posted: 14 Jul 2017 10:18 EDT
John Paul Raja Christu Raja (JohnPaulRaja,C)
This is something your Oracle DBA can investigate. We have seen Oracle optimizer generating and using different execution plans at runtime. I am presuming the second time the query runs uses a bad execution plan.