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
Forgot to add configuration.
Toad version: 12.1.0.22
Oracle Version: 11.2.0.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
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.
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.
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
Please consider the environment before printing this email.
From: Gregory Liss [mailto:bounce-GTDG@toadworld.com]
Sent: Monday, April 14, 2014 1:37 PM
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
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.
I am executing with F9 and no autotrace.
BTW, thanks a lot for all the replies.
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
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.
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.
My datatypes are only VARCHAR2 and NUMBER.
How many column in your table? I’ve seen some users have performance issues when tables have hundreds of columns …
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.
when you run:
select * from v$version;
in both env, do they return same Oracle home?
I am going to run Damir’s suggestion. Hang on. 
BTW, I am running the same query on all environments. The query is an extract of a CSV file.
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
}
Have you tried other queries or is it just this one that causes problems?
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
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.
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 
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.