ORA-01013: user requested cancel of current operation at OracleInternal.ServiceObjects.OracleConnectionImpl

Question

I am having this error - "ORA-01013: user requested cancel of current operation at OracleInternal.ServiceObjects.OracleConnectionImpl." each time I try to run this query. I know this issue is because a part of query is taking too long to respond. I have tried to use Parallel Hints but did not work. I do not have access to modify the timeout limit of the DB as well. Can somebody have a solution to this problem at query level?

SELECT A.CR_ACCOUNT, B.CR_ACCOUNT FROM 
FCUBS.FTTB_CONTRACT_MASTER A
Inner JOIN FCUBS.FTTB_CONTRACT_MASTER B
ON A.CR_ACCOUNT = B.DR_ACCOUNT
AND A.DR_ACCOUNT = B.CR_ACCOUNT
AND A.PAYMENT_DETAILS2 = B.CONTRACT_REF_NO;

Answer 1

"ORA-01013: user requested cancel of current operation" means your client told Oracle to abort the current call. Most likely you have a timeout setting in your client software configured. That's on your client, not the database. It is also possible that the DBAs have something that is doing an "ALTER SYSTEM CANCEL SQL" on your session after an excessive runtime, but that's less common as it has to be manually written; it's not something most Oracle databases would do. Look at your client settings for at timeout.

As for your query runtime, first ensure you don't have a many-to-many join creating a partial Cartesian product. Test your join predicate this way:

SELECT CR_ACCOUNT, DR_ACCOUNT, PAYMENT_DETAILS2, COUNT(*) 
  FROM FCUBS.FTTB_CONTRACT_MASTER 
 GROUP BY CR_ACCOUNT, DR_ACCOUNT, PAYMENT_DETAILS2
HAVING COUNT(*) > 1

SELECT CR_ACCOUNT, DR_ACCOUNT, CONTRACT_REF_NO, COUNT(*) 
  FROM FCUBS.FTTB_CONTRACT_MASTER 
 GROUP BY CR_ACCOUNT, DR_ACCOUNT, CONTRACT_REF_NO
HAVING COUNT(*) > 1

If you get rows back from both those queries, you have a many-to-many join. Fix that by adding more join columns such that at least one side uses a set of join keys that are unique.

Lastly, if you don't have a many to many, it might be using indexes when it shouldn't be. You can try these hints:

SELECT /*+ FULL(a) FULL(b) USE_HASH(a b) PARALLEL(8) */ A.CR_ACCOUNT ...