Toad World® Forums

Query takes long to run in toad than in SQLPLUS or SQLDeveloper


#1

I have a query that runs in 2 ms i sqlplus or SQLDeveloper, however in Toad never returns the result and after sometime it becomes unresponsive.

Any ideas?

Thanks


#2

Forgot to add configuration.

Toad version: 12.1.0.22

Oracle Version: 11.2.0.3


#3

How are you running the query? F5 (as a script) or F9? If using F5, it’s because it’s trying to return the entire dataset.

Greg

From: jbortolotto [mailto:bounce-jbortolotto@toadworld.com]

Sent: Monday, April 14, 2014 1:34 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Query takes long to run in toad than in SQLPLUS or SQLDeveloper

Query takes long to run in toad than in SQLPLUS or SQLDeveloper

Thread created by jbortolotto

I have a query that runs in 2 ms i sqlplus or SQLDeveloper, however in Toad never returns the result and after sometime it becomes unresponsive.

Any ideas?

Thanks

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#4

I am just hitting F9.

Please consider the environment before printing this email.

From: Gregory Liss [mailto:bounce-GTDG@toadworld.com]
Sent: Monday, April 14, 2014 1:37 PM
To: toadoracle@toadworld.com
Subject: RE: [Toad for Oracle - Discussion Forum] Query takes long to run in toad than in SQLPLUS or SQLDeveloper

RE: Query takes long to run in toad than in SQLPLUS or SQLDeveloper

Reply by Gregory Liss

How are you running the query? F5 (as a script) or F9? If using F5, it’s because it’s trying to return the entire dataset.

Greg

From: jbortolotto [mailto:bounce-jbortolotto@toadworld.com]
Sent: Monday, April 14, 2014 1:34 PM
To: toadoracle@toadworld.com
Subject: [Toad for Oracle - Discussion Forum] Query takes long to run in toad than in SQLPLUS or SQLDeveloper

Query takes long to run in toad than in SQLPLUS or SQLDeveloper

Thread created by jbortolotto

I have a query that runs in 2 ms i sqlplus or SQLDeveloper, however in Toad never returns the result and after sometime it becomes unresponsive.

Any ideas?

Thanks

To reply, please reply-all to this email.


#5

Does it execute with F5? Do you have Auto Trace enabled?

On 04/14/2014 01:38 PM, jbortolotto wrote:

RE: Query takes long to run in toad than in SQLPLUS or SQLDeveloper

Reply by jbortolotto
I am just hitting F9.

Jeferson Bortolotto, Sr Architect/DBA - NARS Team

(904) 251-8825 Office

6600 Corporate Center Parkway, Jacksonville, FL 32216-0973

jbortolotto@acosta.com

Please consider the environment before printing this email.

From: Gregory Liss [mailto:bounce-GTDG@toadworld.com]

Sent: Monday, April 14, 2014 1:37 PM

To:
toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Query takes long to run in toad than in SQLPLUS or SQLDeveloper

RE: Query takes long to run in toad than in SQLPLUS or SQLDeveloper

Reply by Gregory Liss

How are you running the query? F5 (as a script) or F9? If using F5, it’s because it’s trying to return the entire dataset.

Greg

From: jbortolotto [mailto:bounce-jbortolotto@toadworld.com]

Sent: Monday, April 14, 2014 1:34 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Query takes long to run in toad than in SQLPLUS or SQLDeveloper

Query takes long to run in toad than in SQLPLUS or SQLDeveloper

Thread created by jbortolotto

I have a query that runs in 2 ms i sqlplus or SQLDeveloper, however in Toad never returns the result and after sometime it becomes unresponsive.

Any ideas?

Thanks

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#6

I am executing with F9 and no autotrace.

BTW, thanks a lot for all the replies.


#7

What data types are in you result set? You can do a CTRL+F9 to display that info.

From: jbortolotto [mailto:bounce-jbortolotto@toadworld.com]

Sent: Monday, April 14, 2014 1:36 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Query takes long to run in toad than in SQLPLUS or SQLDeveloper

RE: Query takes long to run in toad than in SQLPLUS or SQLDeveloper

Reply by jbortolotto

I am executing with F9 and no autotrace.

BTW, thanks a lot for all the replies.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#8

you should run with next statement in both envs

SET AUTOTRACE TRACEONLY

in Toad run with F5 and in SQL developer as normal (Ctrl+Enter suppose).

Please return us here execution plan from both actions.

Example for Toad:

SET AUTOTRACE TRACEONLY
select 1 from dual;

   1

    1

1 row selected.

Execution Plan

0 SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=2 Card=1)
1 0 FAST DUAL (Cost=2 Card=1)

Statistics

     5  user calls
     0  total cf enq hold time
     0  db block gets from cache (fastpath)
     0  consistent gets - examination
     0  redo k-bytes read for recovery
     0  workarea executions - multipass
     0  LOB table id lookup cache misses
     0  parse time cpu
  1350  bytes sent via SQL*Net to client
  1085  bytes received via SQL*Net from client
     1  rows processed

and the same from Sql*Plus:

SQL> SET AUTOTRACE TRACEONLY
SQL>
SQL> select 1 from dual;

Execution Plan

Plan hash value: 1388734953

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

Statistics

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

SQL>

They should tell where do you loose most of the time. Please accept that “select 1 from dual” is mine sql example which you’ll change with your real example.


#9

My datatypes are only VARCHAR2 and NUMBER.


#10

How many column in your table? I’ve seen some users have performance issues when tables have hundreds of columns …


#11

And what about executing with F5, just to see if that returns data?

On 04/14/2014 02:36 PM, jbortolotto wrote:

RE: Query takes long to run in toad than in SQLPLUS or SQLDeveloper

Reply by jbortolotto
I am executing with F9 and no autotrace.

BTW, thanks a lot for all the replies.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#12

when you run:

select * from v$version;

in both env, do they return same Oracle home?


#13

I am going to run Damir’s suggestion. Hang on. :slight_smile:


#14

BTW, I am running the same query on all environments. The query is an extract of a CSV file.


#15

I tried to run with autotrace, but I get the same result, it means that the query never retunrs anything in Toad.

In SQLPlus I get a result in less than a sec:

{Statistics


40 recursive calls

0 db block gets

39156 consistent gets

432 physical reads

0 redo size

54453 bytes sent via SQL*Net to client

773 bytes received via SQL*Net from client

25 SQL*Net roundtrips to/from client

14 sorts (memory)

0 sorts (disk)

354 rows processed

}


#16

Have you tried other queries or is it just this one that causes problems?


#17

If nothing else you can try this in both environments. It will tell you exactly where the problem is. There are other ways to start tracing but this one is available on all versions…

Alter session set tracefile_identifier=toad;

(Or sql plus accordingly.)

Alter session set events ‘10046 trace name context forever, level 12’;

Your SQL goes here;

Alter session set events ‘10046 trace name context off’;

When both have run look in the location on the database server pointed to by user_dump_dest for a trace file with TO AD or SQLPLUS in the name.

Look at both and compare.

Cheers,

Norm. [TeamT]

Sent from my Samsung Galaxy Note III - powered by Three


#18

I do not have too many columns, just a few.

I did had another similar query that had the same problem.

As far as I remember only these two queries, but they are very similar, with a couple difference in the columns selected and the conditions.


#19

Can you post the DDL for the table and the SQl for the query then. Because I’ve run out of ideas to try -sorry :frowning:


#20

Norm, he cannot complete the query at all. And have no time to wait for TNS operation timeout error.
And poster didn’t answer the result on second query from mine ask to see that he is using the same env and the best of all didn’t say which method he is using in connection string.
S mine conclusion-I think this is TNS names problem.