SQL Select slow from app server, but fast when ran in DB server SQLPLUS
We have a Sun apps server application talking to an Oracle 10gR2 database hosted on another solaris server (on the same network).
At times, the SQL select issue by the application is taking a long time to fetch the data, sometimes it's ok. (1900seconds vs 45seconds)
During the time when the SQL select took a long time to fetch at the apps server, we checked the db server's CPU and memory and it's healthy. At the same time, we tested the same SQL Select on the DB server's SQLPLUS and it's running very fast.
The DB is not big and we had ran "execute dbms_stats.gather_schema_stats" to analyse the schema already. The slowness still occurs.
Would appreciate if anyone could advise on the possible cause. Or is there any way we could trace the cause of this issue. Many Thanks.