Toad World® Forums

TOAD Weird behavior (?)


#1

I use TOAD to connect to several Production and Test databases. The production databases are spreaded all around the world, and my main test database is right next door. When I open a connection to this test database, it takes around 3+ minutes to answer back; after that, any operation with this database behaves ok; when I open a connection to any other database, across the globe, or even another test database on the same server, the connection time is reasonably good. I noticed (using OEM) that the connection on the database side opens almost immediately, but why does it take so much time to appear on the client side? Is there any parameter (on the database or in TOAD) I should review?

Thanks for your time,
Ignacio.


#2

Toad does a lot of querying once you connect.

Do you see any performance hints detailed under Help > Toad Advisor?


#3

Hi Jeff.
Thanks for your prompt response. I didn’t know this feature even existed.
I checked that on 2 or 3 databases and, besides the other test database on the same server, which I am the only user, there is 1 warning and 2 alerts:
Warning: Incompatible client and server; my client is 11.1.0.6, and the database is 11.2.0.1.0, but I don’t really think that’s the problem, because it was happening before the upgrade. What do you think?
Alerts:

  • Server is not a terminal release (?).
  • Untested Oracle server version (?).
    Any idea how this may affect the performance?
    The Hints and Performance suggestions shouldn’t be a problem because all are related to my Toad settings which are the same from db to db.

Thanks for any feedback,
Ignacio.


#4

The Hints and Performance suggestions shouldn’t be a problem because all are
related to my Toad settings which are the same from db to db.

That’s only true if the dbs are identical, are they?

Also, check the stats of your data dictionary objects in your test db, Bert has
an article on AskToad about this.


#5

Yes – the stats issue is most often a reason for such – and worth
eliminating before looking elsewhere J

Here are ask toad and toad world items to read on this (where says 10g most;ly
applies under 11g as well):

http://asktoad.com/DWiki/doku.php/faq/answers/database_versions#why_can_toad_some
times_seem_to_run_slower_on_10g

http://www.toadworld.com/BLOGS/tabid/67/EntryID/135/Default.aspx


#6

Thanks Bert and Jeff.
The article really makes sense to me. Today, after all developers leave (or I kick them out ;)), I’ll run the stats and will post the results here.
Regarding Jeff’s comment: the dbs are just similar, not identical; basically, the production servers are more powerful, handle a lot more users, etc., and the related init parameters are set to handle the extra hardware and the load.

I’ll let you know the result of my test after the stats gathering.

Thanks again,
Ignacio.


#7

All of a sudden I started getting the following error trying to open a file in
SE:

Is it a known problem?
image001.jpeg


#8

We need your Toad.elf file.

Michael
image001.jpeg


#9

[ Attachment(s) from George.Meltser@aigfpc.com included below]
image001.jpeg


#10

[ Attachment(s) from George.Meltser@aigfpc.com included below]
image001.jpeg


#11

I don’t recall seeing that one before, but I recommend restarting your
computer and Toad once in a while! 6 days and 6 hours of up time before that
error occurred. The developers here can’t get through an hour of uptime
with Delphi without killing the process and restarting it. If I can make it 2
days without requiring a PC restart its amazing.

Michael
image001.jpeg


#12

Results:
There was some improvement on the response time. Now, it takes 2+ minutes instead of 3+, but I still think it is pretty slow compared to the rest of the databases. As you suggest, I strongly believe it has something to do with the Oracle stats. As far as you know, are there any new stats in 11GR2 that may affect Toad? This is our only database in 11GR2, so I can’t make any comparisons now, but we’ll upgrade the remaining ones in the next 2-3 months.
Are there any logs, views, trace files, etc. that may give me some insights into this issue?

Thanks for your help,
Ignacio.


#13

I do most of my work these days using 11gR2 64-bit on Linux and 32-bit on
Windows (still in beta). I’ve had zero performance issues – but that
could be just dumb luck on my part. If you can run the sql monitor tool in toad
(actually separate exe bundled with toad) and report what queries are taking the
longest?


#14

Afternoon Ignacio,

Results:
There was some improvement on the response time. Now, it
takes 2+ minutes instead of 3+, but I still think it is
pretty slow compared to the rest of the databases.

I’m not running 11g yet however, can you create a (sys owned) after
logon trigger that looks for your logon and Toad, and if found runs an
“alter session” to set event 10046 with waits (and binds) turned on -
similar to the code below (apologies for formatting in advance) which
will create a trace file on the database server.

When you connect as your user and Toad, you will be able to see exactly
where your session is delayed or held up (and you can use Toad’s Trace
File Analyser to investigate as well!)

CREATE OR REPLACE TRIGGER sys.norms_logon_trigger
AFTER LOGON
ON DATABASE
BEGIN
– Look for your user logon here, plus any other checks you need to
do.
IF (USER IN (‘IGNACIO’)) THEN
execute immediate 'alter session set events ‘‘10046 trace name
context forever, level 12’’ ';
END IF;
EXCEPTION
– I know, I know, but in this case it is valid. Norm.
WHEN others THEN NULL;
END;
/

Once you have a trace file, you can drop this trigger to prevent any
possibility of it getting copied to production etc.

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


#15

Now that you’ve tried the stats, I’d start looking at your Toad options. Start by turning off the options to highlight object names. See View–>Options–>Editor–>Display. Uncheck the 3 “Highlight X names” options under the Syntax Highlighting group box. Does that make a difference?

Michael


#16

You can also provide a name prefix for the trace file so that it sticks out
better amongst the many oracle files in the server’s log/trace directory

Here’s how I do these items (the code below works just fine –but
there are supported package calls now days for these):

PL/SQL trigger code …

EXECUTE IMMEDIATE ‘ALTER SESSION SET tracefile_identifier =
‘’’||v_file_name||’’’’;

EXECUTE IMMEDIATE ‘ALTER SESSION SET max_dump_file_size =
‘’’||to_char(v_max_dump_size)||‘M’’’;

DBMS_SESSION.SESSION_TRACE_ENABLE (waits => FALSE, binds => TRUE);


#17

Thanks Bert, Jeff, Michael, Norman; you are the best.
I will do my tests this afternoon (I’m in the MDT zone), and let you know my results.
Again, thank you very much for your quick responses,
Ignacio.


#18

6 days and 6 hours of up time

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?

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.

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 :wink:

Roger S.


#19

Now that you’ve tried the stats, I’d start looking at your Toad options.

Caveat: this is just an FYI to bring awareness to the Quest team of a potential
new possibility, not to try and resolve my issue currently. In other words,
don’t look into it on account of my post :wink:

Additionally, it’s probably a bit winded.

There appears to be something else slowing things down. I can’t really
explain much at this point. I’m still trying to nail something down so I
can follow up with our DBA’s first.

What I’ve noticed: All our databases that I’m working with are on
10g. Specifically 10.2.0.1.0. I used to be able to log in to any of them
quickly. For example, with production it currently takes about 3 seconds from a
login to get the SQL Editor in a ready state. However, as the DBA’s are
handling their maintenance – and I suspect it’s a rather specific
task – the length of time jumps. Currently the Dev environment takes some
39/40 seconds from login to Editor ready state.

The particular task that I suspect it’s related to has to do with the file
system. For a while there, we were having frequent notifications sent about a
change in the file folder structure. Something about moving the Oracle folder
structure to bring it into standard’s compliance. Of course, it was a high
level summary with no specific details. Unfortunately at this time they appear
to have gone into hibernation on that task. Only the QAT and Prod environments
are left.

What’s really strange is that SQL Plus still gets in as fast as it ever
has. I can’t really see a change in the folder structure impacting Toad
without impacting a lot of other Tools as well. It just doesn’t make sense
that the Database would only respond slowly to a particular tool on a file
system change. Unfortunately at the moment, although it’s most likely
coincidence, it appears tied in to that particular task. Perhaps the DBA
handling the task is new and is re-running the “wrong” statistics or
something. Whatever the case, I need to nail down when they’re going to
proceed with QAT so I can follow up with some very specific testing to confirm
if there’s a relation or just a coincidence.

I was going to give an idea of the summary explanation but I can’t
currently find it in my emails. Ah well… as I said, just an FYI in case
someone else runs into something that ends up being confirmed outside the
currently expected sources.

One final note: I tried to track the login through SQL Monitor using the Launch
process. I selected the shortcut I normally use as well as the Toad executable
directly. No parameters selected. In both cases, Toad fails to get passed a
popup error that says “Cannot open AVI”. I had originally been
hoping that might give me some clue as to which point during the login process
was taking forever but I can’t use it because I need to be logged in first
for it to track the SQL. Ah well… hopefully the new version (if I remember
correctly, Quest is working on one) of SQL Monitor will be able to handle the
task.

Roger S.


#20

6 days and 6 hours of up time

Ouch… 6 days without a reboot… I don’t go one day without a reboot

18d 16h 58m so far.

Many things puke on me, including the venerable Sysinternals Process
Explorer (looks like a memory leak), but most things are as stable as I can
expect for 32-bit XP. Without any empirical evidence, I guess that
suspending my laptop saves me at least 10m/workday from the boot/logon
process.

My $.02,
Rich – [TeamT]

Disclaimer: They’re always after me Lucky Charms!