Home » RDBMS Server » Performance Tuning » Slow running query (3 Merged) (Oracle 11g)
Slow running query (3 Merged) [message #565623] Wed, 05 September 2012 11:39 Go to next message
brown_zzz
Messages: 7
Registered: August 2012
Location: United Kingdom
Junior Member
I have a slow running query from an application which takes 3 minutes and appears for this duration in v$session_longops. It has various joins and a union etc and 5 host variables :1 to :5. The problem occurs in the Java app every time I run it, not just the first time.

When I cut and paste the query into sqlplus adding the real values for the 5 variables it runs in 0.01 seconds and I cannot re-create the problem. The same applies in SQL developer.

Does anyone have any idea why I cannot re-create the problem or what I should do next?

Many thanks...
Re: Slow running query [message #565625 is a reply to message #565623] Wed, 05 September 2012 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Slow query [message #565626 is a reply to message #565623] Wed, 05 September 2012 11:44 Go to previous messageGo to next message
brown_zzz
Messages: 7
Registered: August 2012
Location: United Kingdom
Junior Member
I have a slow running query from an application which takes 3 minutes and appears for this duration in v$session_longops. It has various joins and a union etc and 5 host variables :1 to :5. The problem occurs in the Java app every time I run it, not just the first time.

When I cut and paste the query into sqlplus adding the real values for the 5 variables it runs in 0.01 seconds and I cannot re-create the problem. The same applies in SQL developer.

Does anyone have any idea why I cannot re-create the problem or what I should do next?

Many thanks...
Re: Slow running query (3 Merged) [message #565627 is a reply to message #565623] Wed, 05 September 2012 11:56 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Does anyone have any idea why I cannot re-create the problem or what I should do next?
If you want to run the statement in SQL*Plus with bind variables, you can do it like this:
orcl>
orcl>
orcl> var v1 number
orcl> exec :v1:=10

PL/SQL procedure successfully completed.

orcl> set autotrace on
orcl> select * from emp where deptno=:v1;

     EMPNO ENAME      JOB              MGR HIREDATE                 SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 01-06-12 00:00:00       2450                    10
      7839 KING       PRESIDENT            09-11-12 00:00:00       5000                    10
      7934 MILLER     CLERK           7782 15-01-13 00:00:00       1300                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     5 |   190 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| EMP  |     5 |   190 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - storage("DEPTNO"=TO_NUMBER(:V1))
       filter("DEPTNO"=TO_NUMBER(:V1))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        974  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

orcl>
I used the autotrace facility to get the exec plan and stats, it isn't the best way (follow the BlackSwan's advice for that) but it is easy.
Re: Slow query [message #565628 is a reply to message #565626] Wed, 05 September 2012 11:57 Go to previous message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+bind+variable+peeking

above may or may not be cause for slow response.

A comparison of EXPLAIN PLAN for both fast & slow runs might prove useful.
Previous Topic: How to Control concurrent Inserts of a record by parallel sessions
Next Topic: Explain plan of a query
Goto Forum:
  


Current Time: Wed Apr 16 22:00:42 CDT 2014

Total time taken to generate the page: 0.06886 seconds