Hello ,
We have a hit a strange scenario where a query when run directly on the database returns results within seconds, but takes hours together and doesnt finish when run from the app.
Here's the difference between both the queries.
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS ELAPSED_TIME USER_IO_WAIT_TIME CONCURRENCY_WAIT_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED
------------- --------------- ------------ ---------- ------------ ----------------- --------------------- ---------- ----------- --------------
38f09t1rh42zn 1200094404 0 0 6464265444 2776 0 147 805128112 0
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS ELAPSED_TIME USER_IO_WAIT_TIME CONCURRENCY_WAIT_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED
------------- --------------- ------------ ---------- ------------ ----------------- --------------------- ---------- ----------- --------------
4ka33bfa8uazn 3311447596 0 1 31946 0 0 0 46 0
The sql_id is different only because few fields in the query change. Otherwise it is the same query.
We run Oracle 10.2.0.4 Standard Edition. And this problem has started very recently.
What could have caused this? Any help on this is highly appreciated.
Regards