Toad World® Forums

SQL Trace

I have one of my packages getting compiled slow. Can I turn on the SQLTrace while compiling the package to understand what piece of code is making the compile slower.

If so, how can I turn it on and how should I interpret the Trace file…

Kindly suggest!

Morning ypranathi,

I have one of my packages getting compiled slow. Can I turn
on the SQLTrace while compiling the package to understand
what piece of code is making the compile slower.
Does it eventually compile? I’m wondering if someone (or something) is
using the package already and you are hanging on a latch (probably
library cache latch) waiting to compile it?

If so, how can I turn it on
Find the session in Toad that is ABOUT to compile the package (you can
do “alter package x compile;” and/or “alter package x compile body;” in
the editor if it makes things easier) then start tracing, turn on WAITS,
you will not need BINDS. Once tracing, execute the command to compile
the package.

When done, turn trace back off. Go to the server and find the latest
trace file (in user_dump_dest) and look at it in an editor.

and how should I interpret the Trace file…
In Toad, Database->Diagnose->Trace File Browser.

Look at the “total time” column - click it to sort - and find the one
with the highest values.

Expand that statement using the ‘+’ if necessary to get the nested
statements making up that big time.

Repeat the above until you find the most expensive statement.

Click on it.

Click onto the WAITS tab in the bottom of the screen. You may wish to
view the Waits Summary tab as well.

See where you spent the most time.

Have fun.

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

Hi!

Is there any whitepaper how big trace files could be loaded in Trace File browser.

I have i7 8 core, 16 GB workstation and upload 5.000.000 lines. But then Toad hangs (Task manager is showing activity, but after 3 hours I give up).

Please let me understand treshold in this way

Damir

There is no determined limit and the longest I have ever waited is a few minutes for large files.

If you zip up your file, will it be small enough to email to me? I would love to try to find the problem and fix it. If it is too big to email, perhaps
you could put it somewhere that I could pull by FTP?

If you can email it, please send to john.dorlon@quest.com

Thanks.

From: damir.vadas_531 [mailto:bounce-damirvadas_531@toadworld.com]

Sent: Monday, July 15, 2013 11:05 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] SQL Trace

RE: SQL Trace

Reply by damir.vadas_531

Hi!

Is there any whitepaper how big trace files could be loaded in Trace File browser.

I have i7 8 core, 16 GB workstation and upload 5.000.000 lines. But then Toad hangs (Task manager is showing activity, but after 3 hours I give up).

Please let me understand treshold in this way

Damir

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.