query slow in application, fast on SQL*Plus executing [message #554424] |
Mon, 14 May 2012 07:21 |
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 #554432 is a reply to message #554428] |
Mon, 14 May 2012 08:09 |
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 #554542 is a reply to message #554432] |
Tue, 15 May 2012 06:08 |
|
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.
|
|
|