0

The problem is very unclear,

I have an application server, that constructs queries and sends it to Oracle to get response.

The Oracle database is installed on a high perfomance server:
Linux 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

The application server was tested on different locations inside of the intranet, including that high perfomance server, where the database is located.

Several versions of the application server (from old to newest) were tested also.

The problem:

When the application server sends a query to the database, it has to wait for about 300 seconds to get response and construct logged output.

But when I'm connecting to the database with PL/SQL Developer and running that query using that IDE, I'm getting a response in less than one second.

Is there something familiar for you, guys?

We're just cracked our heads trying to determine where the problem could be located.

If you need more information - I'll update this post immideately.

Improve this question
5
  • 300 seconds sound like a TCP timeout, yet you say the query is returning results. I wonder if the app server is trying to connect via multiple addresses and only manages to connect after the first times out?
    – Colin 't Hart
    Commented Oct 2, 2013 at 9:07
  • @Colin'tHart, yes, the server uses dbpool reference (the only reference of dbpool) and I tried to set up different timeout parameters, but still no changes... ( how can I detect problems ? is there any tools or methods ?
    – Alexander.Iljushkin
    Commented Oct 2, 2013 at 10:52
  • @Colin'tHart what's the difference could be between server request and pl/sql developer request (Also, Toad shows prefect timing too)
    – Alexander.Iljushkin
    Commented Oct 2, 2013 at 10:54
  • tnsnames settings on the client are different? Different network/route from client to server compared to from the appserver to the server?
    – Colin 't Hart
    Commented Oct 2, 2013 at 11:28
  • How many rows does the query return? In PL/SQL Developer, are you measuring the time required to fetch the first row? Or to fetch the last row? Is your application logging something when the first row is returned? Or when the last row is returned? Are you certain that the query you are running in PL/SQL Developer has the same sql_id, that it uses bind variables in the same way that your application does, etc.?
    – Justin Cave
    Commented Jan 1, 2014 at 17:59

2 Answers 2

Reset to default
1

Any query which returns more than 50 rows could seem to execute faster on SQL Developer. This is because SQL developer only fetches 50 rows (and more after you press Page Down), while your application possibly fetches many more rows.

The proper way to test is to use sqlplus with SET AUTOTRACE TRACEONLY.

Improve this answer
0

Sounds like the difference in query plans with bind variables (coming from the application server) and literals (coming from SQL Developer). See if enabling bind peeking helps. More info: http://www.toadworld.com/platforms/oracle/w/wiki/4780.bind-variables-literals-and-bind-peeking.aspx

Improve this answer
1
  • unfortunately, app server passing query with binds. and i'm passing it with binds through the pl sql developer. I get noted that that quiery from app server , sometimes, precessed wery quickly. seems that oracle caches sql plans and some times sql plan for this query overrides with different sql plan, but for not long time. it's magic for me... i dont understand how to fix it.
    – Alexander.Iljushkin
    Commented Oct 3, 2013 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.