Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Oct 2 '13 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 ? –  Flextra Oct 2 '13 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) –  Flextra Oct 2 '13 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 Oct 2 '13 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 Jan 1 at 17:59

2 Answers 2

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.

share|improve this answer

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

share|improve this answer
    
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. –  Flextra Oct 3 '13 at 12:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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