So, the query in question (the big long one) started at 12:00:38.703 and died at 12:01:39.686, which is slightly over one minute. The timing that is listed beside the ORA-01013 error is misleading then.
Srinath mentioned getting an explain plan, but since the query actually runs at the database using SQL Developer in about 2 seconds, I'd guess the explain plan isn't going to uncover too much.
One thing that hasn't been mentioned or clarified is the database setup. Is this a RAC or another clustered database setup?
If you can, install SQL Developer or SQLPlus on the Content Server machine and run the query from the Content Server machine instead of directly on the database server itself - be sure to configure the connection in SQL Developer the same way as the WLS connection is defined. This way, we are introducing the network back into the equation. The fact that sometimes it works and sometimes it doesn't makes me think that the request to the database is getting routed wrong (perhaps to a server in a cluster that is assumed to be functional, but it's not, so the request times out.) If the database is indeed clustered, trying pinging each node in the database cluster from the Content Server, and see if all nodes are reachable. Using tracert and other network diagnostics might help as well.
There is a difference when running the sql queries from ucm (as in when Contrbution folder is selected ) and executing it from SQL Developer . The DB parameters which are set by UCM are different than the ones set by the IDE .
So , before testing the same query from IDE , add the following trace section to ucm :
systemdatabase,system,requestaudit,search* + Full verbose
Clear the server logs and then select Contribution Folder .
Now from the ucm server output you will see the db parameters being set before the query is executed .
Copy those params and set them from IDE . Now ,the 2 instances would be similar from DB perspective . Run the same query and see if it gives same issue . Also , check for the plan recommendations in case there is similar issue shown from IDE .
While knowing the initialization parameters would be nice for apples to apples execution in SQL Developer, I respectfully still don't think those parameters are the issue.
Remember earlier in the thread, it was noted that the behavior is sporadic - sometimes it works correctly and sometimes it doesn't. This sort of rules out the initialization parameters, because these parameters are fixed and would be the same for every connection being established. So that in theory would make every call either 1) fast or 2) timing out. Since the behavior is sporadic, it seems more probable that it's really something environmental in nature. Caching could also explain this, but I'd expect the first call to be slow, and subsequent calls to be fast - the issue however is being reported in the reverse order.
It was also noted earlier in the thread that the timeout was set to 40 seconds; however, the system audit trace showed the timeout clearly occurring after 60 seconds (1 minute). I'd revisit that setting in WLS as well, as well as doing a more holistic review of the connection setup in WLS. Compare the settings to a known correctly functioning system for a sanity check.
I agree to those points , trying to get some more inputs / data on this "sporadic" issue .