Hello!

We have a report definition defined on a class related to a simple view (select from worklist, join with work, union with 3 more selects of the same kind). The report definition by itself has only three simple filters. 

The report worked great for a year. Then we modified one of filters by coping it to the production ruleset. After the modification it worked fine for three days then a strange problem occurred. The report works fine during the whole day but every morning nearly at the same time it stops working by failing on timeout (30 seconds).

Firstly we decided that the problem is related to the fact that we made changes to the report in production ruleset. However making its version in prod ruleset not available didn't solve the problem. At the moment we have a strange workaround: each morning after it stops working we change the set of filters on the report definition by removing some filters or even adding any randoms. Then it starts working perfectly, but only till the next morning.

The following strange things should be also mentioned:

1) The problem occurs only with this simple report even though we have much more complex reports that work fine under the same user while the problematic report isn't working.

2) The direct execution of SQL request generated by the report at the time it is not working returns data in less than a second.

3) We faced the described issue on our production environment only and couldn't reproduce it anywhere else.

4) The report starts working even after complicating it by adding filters to the version in production ruleset.

5) The system load is not peak at the moment the report fails (in the morning) and usually the report loads in less than a second.

6) There are no any infrastructure activities in the morning.

Currently we are out of any ideas how to solve the problem. Any help would be useful.

Exception in logs:

Not returning connection 4 for database "pegadata" to the pool as it previously encountered the following error

Last SQL: SELECT DISTINCT "PC0"."ASSIGNKEY" AS "AssignKey" , "PC0"."REFINSID" AS "RefInsID" , "PC0"."TASKLABEL" AS "TaskLabel" , "PC0"."AGREEMENTNUMBER" AS "AgreementNumber" , "PC0"."CLIENTNAME" AS "ClientName" , "PC0"."INN" AS "INN" , "PC0"."UPDATEDATETIME" AS "UpdateDateTime" , "PC0"."ASSIGNEDOPERATORID" AS "AssignedOperatorId" , "PC0"."ASSIGNEDUSERNAME" AS "AssignedUserName" , "PC0"."REFOBJECTKEY" AS "RefObjectKey" , "PC0"."RESPONSIBLE" AS "Responsible" , "PC0"."DEPUTY" AS "Deputy" , "PC0"."CONCLUSIONDATE" AS "ConclusionDate" , "PC0"."CREATEDATE2" AS "CreateDate2" , "PC0"."DEADLINEDATE" AS "DeadlineDate" , "PC0"."PYERRORTYPE" AS "pyErrorType" FROM PRPC.CAD_WORKLIST "PC0" WHERE  (  ( "PC0"."ASSIGNEDOPERATORID" = ?  OR "PC0"."RESPONSIBLE" = ?  OR "PC0"."DEPUTY" = ?  )  AND "PC0"."ORGUNITID" = ?  AND  ( "PC0"."CASESTATUS" <> ?  OR ROUND(SYSDATE - TRUNC("PC0"."CREATEDATE2")) <= ?  )  AND  (  "PC0"."REFOBJECTKEY" NOT LIKE ?   OR ROUND(SYSDATE - TRUNC("PC0"."CREATEDATE2")) <= ?  )  AND  "PC0"."REFINSID" NOT LIKE ?   )  ORDER BY 14 DESC, 7 DESC

java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation