Question
Maantic Inc
US
Last activity: 15 Jul 2017 14:18 EDT
DB Fetching issue in pega 7.2.1 - ORA-01013
Hi,
When I run Report definition against external database, getting below error.It is more fequent in pega 7.2.1, same query works fine in pega 7.1.6 region.
Retrieving the report data exceeded the limit of '120' seconds. Please try making the report more specific by further limiting which rows to include.
There was a problem performing a database query: There was a problem getting a list: code: 1013 SQLState: 72000 Message: ORA-01013: user requested cancel of current operation
DatabaseException caused by prior exception: java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation
| SQL Code: 1013 | SQL State: 72000
Pegasystems Inc.
US
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.
Pegasystems Inc.
US
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.