Home » RDBMS Server » Performance Tuning » query slow in application, fast on SQL*Plus executing (Oracle Database 10g(RAC) Enterprise Edition Release 10.2.0.4.0 - 64bit)
query slow in application, fast on SQL*Plus executing [message #554424] Mon, 14 May 2012 07:21 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi All,

I have a query that runs very fast on SQL*Plus or SQL Developer,
however, in the application it runs very slow.


I encountered this part of trace file, for the query:

SELECT col1, col2, col3, col4
FROM table1

WHERE col_id = :1
ORDER BY 1 ASC,
2 ASC,
3 ASC


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      6      0.07       0.10          0        216          0           0
Fetch        6    115.48     117.89          0        288          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14    115.55     118.00          0        504          0          12

Misses in library cache during parse: 2
Misses in library cache during execute: 2
Optimizer mode: FIRST_ROWS
Parsing user id: 61  (BLR_01_I1L)



I've looked for documentation on the library cache misses
and also on "query" column which seem to be the key to solve this mystery, but haven't found(or understood what i found) much.

can anyone shed some light on this output?
help me understand what does it indicate?


Thanks in advance,
Andrey

p.s I am sorry, I cannot provide a test case for this,
as it involves massive tables combined in a complexed view.
seems impossible to re-create.





Re: query slow in application, fast on SQL*Plus executing [message #554428 is a reply to message #554424] Mon, 14 May 2012 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: query slow in application, fast on SQL*Plus executing [message #554432 is a reply to message #554428] Mon, 14 May 2012 08:09 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Dear Michel,

I wrote in plain English that i have no possibility of posting DDL's for those tables,
and providing a test case cannot be done in the way described in your link.

I've asked if anyone can HELP UNDERSTANDING this part of tkprof output.

What can i do to get help on this(from the POSSIBLE things to do)?
Or should i just ask ONLY "easy" questions here?

Re: query slow in application, fast on SQL*Plus executing [message #554436 is a reply to message #554432] Mon, 14 May 2012 08:40 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The description of tkprof output can be found here
Query is basically the number of blocks read from the cache.
Library cache miss = hard parse

There is nothing in the output you've posted that shows why it's so slow.
Re: query slow in application, fast on SQL*Plus executing [message #554461 is a reply to message #554436] Mon, 14 May 2012 12:16 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You ran that query in SQL*Plus - it was fast.
You ran it as a part of your application - it was slow.

What is the difference between these two environments? In SQL*Plus, you were ... what? The owner? What is the application user? Does it connect to the same schema, or is it another user, perhaps running query over the database link or ... I don't know, anything else that crosses your mind, that makes the difference?
Re: query slow in application, fast on SQL*Plus executing [message #554463 is a reply to message #554461] Mon, 14 May 2012 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have a query that runs very fast on SQL*Plus or SQL Developer,
please quantify
was this done against exact same instance?
what was done about the apparent Bind Variable?

>however, in the application it runs very slow.
please quantify

was posted result from tkprof from fast or slow run?
What does tkprof show for the other results?

Post EXPLAIN PLAN for both variations.
Re: query slow in application, fast on SQL*Plus executing [message #554542 is a reply to message #554432] Tue, 15 May 2012 06:08 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Hello Andrey

you can ask here whatever you want. But you have to supply enough information.

I cannot explain the behavior on the basis of your upload. Could you please upload also the execution plan from the same tkprof output.

I hope, it would be possible.

Regards
Leonid

P.S.

For a testcase you can take the package dbms_sqldiag, that is very easy in using (and always possible by the way). But at the moment I don't need it.
Previous Topic: PLSQL taking long time
Next Topic: Problem in Update
Goto Forum:
  


Current Time: Thu Mar 28 04:55:11 CDT 2024