Well, from Bert’s suggestion (using SQL Monitor), I copied and pasted the 2 instructions that seem to delay everything. From SQL Monitor, the elapsed time was 100 seconds (from 17:04:49:080 to 17:06:29.251). The 2 instructions here account for 96 seconds!!!:
Hi Roger.
The same error happened to me with SQL Monitor; I just closed it and opened it again, and then tried to run it, and it worked. The first time I ran it, the Toad screen showed up and then disappeared, and I guess it caused the avi problem, because the second time, it never appeared.
Test it,
Ignacio.
Morning Roger,
6 days and 6 hours of up time
We have to turn off all our equipment (in the office, not the server
room!) every night on pain of horrible torture from on high. I work for
the Environment Agency after all!
Ouch... 6 days without a reboot... I don't go one day without a
reboot on a Windows PC. I could be mistaken, after all it
was a long time ago, but wasn't it Windows 98 that BSOD'd
itself after some 40 days straight uptime after a fresh boot
with absolutely no one doing anything on it - not even logging in?
If I remember correctly, it was a timer that rolled over and caused the
BSOD. That plus resource leakage etc, but the GDI Resource leakages
usually got you long before the timer rolled over.
It's just not a good idea to use Windows without rebooting
frequently. Especially since it's historically notorious
for memory leaks (my own humble developers opinion). I say
historically because I've been blessed - so far - not to
have to work with either Vista or Windows 7. I'd also
recommend a regular defrag for good measure.
I've got a brand new Dell laptop. It came with Windows 7 on it. I've
used it once - to reduce the partition size so that I could install
Linux on it. I've never been into Windows 7 since. (I'm keeping it
around though - in case I change jobs and need a copy of Toad in my new
position!)
Of course... I've never worked with Delphi so I can't really
speak of the reliability of developing with Delphi on
Windows so in that case, I'll defer to the experience of
Quest and reboot once an hour if I ever have to work with
that combination
I remember many years ago when Delphi 1 came out. I ordered it within 30
seconds of seeing the advert. I'd worked with Visual Basic 3 at the time
and it was dire. Delphi was a whole new world of proper development and
a decently fast compiler. The Pascal was fairly new to me at the time
though - but I soon got into it.
It was simply a joy to use.
I still have a copy of Delphi 7 Personal lying around on a DVD somewhere
- just in case I ever need it. These days I'm not doing much Delphi
coding though. Most of my code is now C++ (using QT) which I'm just
better than hopeless at, or PL/SQL.
Disclaimer: Nostalgia, it's not what it used to be.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Just out of curiosity – could you try collecting stats on the TOAD schema.
I’m just guessing here – but it’s easy enough to eliminate by
trying ….
Actually don’t need this – should have my first cup of coffee in the
morning before replying – sorry. Hmmm – something with dba_objects
and all_tables.
Wonder if you check the option in toad to use dba_ views when possible might
assist by replacing all_tables with dba_tables. Another guess – but once
again a quick and easy thing to try.
I’ll keep looking into this …
Bert, the option to check for access to dba_views is checked on my TOAD since I installed it.
I don’t know if it may help (or make things more confusing), but this morning I ran the “Select 1 FROM DBA_OBJECTS WHERE object_type = ‘PACKAGE’ AND object_name = ‘TOAD_PROFILER’;” instruction from my editor, using the same user, and it took 47 msecs. It still takes 60+ seconds to run when connecting. Strange, eh?
Thanks,
Ignacio.
The same error happened to me with SQL Monitor; I just closed it and opened
it again
Interesting. A piece of info I hadn’t included previously which I should
have: I’m running Toad 9.7.2.5.
I tried your suggestion with regards re-opening in a few different ways. First,
I opened Toad to initialize it with MS, then closed Toad.
Actually 47 seconds to run a single query versus 60 seconds to connect is easily
explainable – the connect does a few dozen queries during connect to get
your Toad session rolling. So I’m going to say these numbers are the same
for all intents and purposes.
But that query taking 47 seconds in the editor as a simple statement answers
something – which is that it’s not a Toad problem, but rather
something with the server. Maybe the init.ora parameters or something. But not
something that Toad caused.
I hate to bail on you – but I’m going to assume it’s with the
database for now ….
Hi Bert.
I guess you need another jug of coffee (just kidding!). It’s 47 msecs, not 47 seconds.
Thanks.
As my hero Homer Simpson would say – doh !
I had the same delay (over 2 min) on start a connection - but for some database conenctions only - others were quick.
i found that the SQl monitor showed me
** **Timestamp: 11:34:06.989SELECT 1FROM SYS.OBJ$ O, SYS.USER$ UWHERE 0=1Runtime error occurred: 942 (O) ----------------------------------Timestamp: 11:36:23.266 **Over 2 minutes ** So then I thought – what am I doing to any user questions . . . I recalled that I used this setting (BELOW) to shrink the list of schema that I can see in schema browser I changed it to SHOW ALL USERS And now it is FAST
Hi all,
I have the same question here and the problem still exists
The symptom:
In Toad, some simple queries need 2:0x mins occassionally, which requires < 1sec normally. The unlucky (maybe lucky?) thing is, not only Toad, but also happens in the JDBC connection of the Websphere connection pool, again, some connections requires 2:0x mins (usually the first query after idle for long)
What I observed in the explain plan was the sql has been completed in a second, but seems the request cannot be processed by oracle
Hardware:
The database is oracle 11.1.0
running on AIX 6.1.3.0 64-bit
Software:
Websphere running on AIX 6.1.3.0 64-bit as well, but not the same machine of oracle’s
Toad installed on WinXP professional 32-bit
What I have done:
oracle memory_target checked
AIX minperm/maxperm tuned
Table are analyzed
I am thinking whether it is related to network configuration or JDBC driver?
Thanks all of you in advance.
Fai
I have the same question here and the problem still exists
Hey Jay;
I was going to respond and ask if you’re issue is occurring on a specific
query. I’ve been getting the effect of a “freeze” – a 2
minute temporary freeze – in some environments myself. However, in using
SQL Monitor to trap the specific query against a database that does not have the
issue (a “before” image so to say), it appeared that login had a
pretty permanent freeze… over 35 minutes so I decided to
“kill” it.
In closing SQL monitor, Toad responded instantly. After shutting down Toad and
restarting it to log in to the same environment, the login was only a couple
seconds – as expected.
It appears SQL Monitor itself has a role to play in causing issues. I’ve
no way to isolate the issue caused by SQL Monitor vs what’s actually
slowing down the other connections. I’m going to have to wait till our
tech support provides an upgrade on both Toad and SQL Monitor before I start my
retesting.
Sorry I couldn’t be of more help, but I figured I’d share my
situation in the event there are potentially other issues masking what
you’re running into.
Roger S.
Hi Roger,
Thanks for your sharing. Our cases looks similar but as you said, I still cannot isolate the problem too. We planed to call oracle support…
For my case, the 2mins “freeze” appears even not using SQL monitor.
Regards,
Jay
Good Morning;
The issue I’ve been experiencing with regards having a long log-in time in
the Dev environment with Toad is directly related to the option:
View -> Toad Options -> Schema Browser -> Data -> User/Schema Lists -> Only show
users that own objects excluding Synonyms and Temporary Tables.
When changed to “Show All Users”, the login is back to normal
response times.
My best guess: our DBA’s aren’t keeping up with ensuring the System
tables are properly statistically analyzed.
Roger S.
Well, the query to load the users gets more complex when you choose that option.
All I can tell you is either ask the DBAs to update the stats on the sys tables,
or don’t use that option. If you have select privs on the DBA views, make
sure ‘check for access to dba views’ is checked.
The query looks like this (or it uses DBA_users and DBA_OBJECTS if you have
privs, and it uses the SYS Tables directly if you have privs to them)
select username
from sys . all_users a
where exists (select owner
from sys . all_objects
where ( owner = a. username )
and ( object_type <> ‘SYNONYM’ )
and not (( object_type = ‘TABLE’ ) and (temporary= ‘Y’ ))
and not (( object_type = ‘INDEX’ ) and (temporary= ‘Y’ )))
If you can improve performance by using a hint after the first
‘select’ keyword, then you can plug that hint into options ->
Oracle -> Optimizer hints for DBA_USERS and whatever version of Oracle
you’re running, and the query should use it.
Funny you should bring this up now though.
In the last beta cycle, we had some discussion about this option, and also
Richard had a suggestion about being able to specify ‘favorite’
schemas in the dropdown, so I’m trying to roll both of these ideas into
one, and centralize some code.
For the next version, I’m thinking about a custom schema dropdown that has
a tree in it. Default and Logon Schemas would be first. I could have another
node for ‘favorite’ schemas if you have any defined (not shown
here), and you could set up your own categories too, and define which schemas go
in each one. Everything else would just fall under ‘Other schemas’.
Of course incremental search and everything else would work the same.
The query looks like this…
Yup… that’s the query.
And I will be sending in a request to a DBA to update the system stats.
I’d like to confirm that would actually resolve the issue – as I
suspect it will. Additionally, I’ve seen that issue creeping into the
post-dev environments slowly moving from one to the next and would rather it did
not creep into production.
Funny you should bring this up now though.
Until this morning when the login never came back after 10 minutes, it
wasn’t really a high priority for me to look into 
As a result of the finding, knowing other’s have run into various issues
with regards the timing, I figured I’d share it with the group just in
case it wasn’t already identified somewhere.
Roger S.
You probably know this, but for anyone that might not know - it will be slowest
using the ALL_ views, faster if you can select from the DBA_ views, and fastest
if you can select from SYS’s tables directly. In this case, if you can
select from SYS.USER$ and SYS.OBJ$, it will use them.