Out of memory error in toad to see the trace file

Toad for Oracle - Beta
1

Dears,
Greetings,

i enabled trace on one of oracle session when after disabling the trace session when it tries to show the trace file it is giving error “Out of memory” How can I resolve the issue so that trace can be shown on toad. file size is over 150 MB.

Regards,
Salahuddin.

2

Toad’s Trace File Browser should be able to easily handle a file of that size, so I am not sure what is wrong. You might try just copying the folder to your PC with FTP or something, then using Trace File Browser to open it locally.

It would help us diagnose your problem if you:

  1. tell us Toad version
  2. give us the call stack from the error dialog (click where it says “click here” in the error dialog, then in the next dialog, check the “copy to clipboard” checkbox, then click OK. Then paste your clipboard contents into your email)
  3. if you don’t see the steps in 2) then a screen shot of where you are in Toad, with the error might help.
3

Dear,
Greetings,
Toad version is 12.11.0.95

OutOfMemError

4

Extraction

Before error it keep extracting file. File size is 550MB

5

were you tracing your own session in Toad or another user’s session?

If you were tracing your own Toad session, then using Toad to transfer its own trace file, sometimes what happens is that the trace file continues to grow as Toad attempts to transfer it (because we use SQL to do that, and then Oracle writes those SQLs into the trace file) and the process never finishes because the end of the file is never reached.

In that case, I would:

  1. forget about this trace file. It now has a lot of data in it that you don’t care about (the SQL that Toad ran to try to trace the file)
  2. Re-do whatever you did to create the trace file in the first place. But when you are done, tell Toad “No” to loading the trace file.
  3. End your connection in Toad and then reconnect. This will make sure that Oracle stops writing to that file.
  4. Open Trace File Browser
  5. Use this button to get your trace file.
    image

If you were trying to load a trace file of a different session, but the file is just very large…it will probably be a lot faster to copy this file to your PC via FTP, then open the Trace File Browser in Toad and use the first toolbar button to open the file from your PC.

6

Dear Jdorlon,
Greetings,

Thanks. For support. I can see after moving to local pc. I have another question. I have some doubt that my trace file is not showing results for just the session which I tried to get the trace. I think it is showing me other statements results than the statements which were executed in traced session. Image attached. I doubt execution such number of sql statments executed in traced session.

SQLsGraph
SQLsGraph.png1210×269 16.5 KB

7

When you execute a statement in oracle, sometimes Oracle has to run SQL in the background to complete the SQL that you gave it. The background SQL is called “recursive SQL”. Sometimes there are a LOT of recursive SQL. The statements that you give Oracle are called “user SQL”. The graph above includes both recursive sql and user sql.

If you want the chart to not combine them, right-click on it and check “Display User and Recursive Statements Separately”.

Also, you can click on any one of those graph bars to see the list of statements below which that bar represents.

1 Like
8

Oh, another thing I just thought of -

If you stop and start the trace on the same session more than one time, Oracle puts all of that trace data into the same file. So that could be another reason that you see more trace data than you expect.

9

Dear Jdorlon,
Greetings,

Thanks. Gr8.

I just divided them as per instructions image follows. I have another question :slight_smile: . I can see that there are lots of Insert, Update and delete statements in recursive sqls. How come they are in recursive SQL why is it not in User SQL. Main execution is execution of a PL/SQL Block which calls other PLSQL Blocks and insert/update and not sure what other is involved because I am not the developer. So I am assuming if a plsql block is executed it will call other SQLs which will be considered as Recursive call as well as recursive calls will include the internal Oracle commands also which are required to perform the activities that were required by this session.

I hope this report contain only my session data.

User_recursive
User_recursive.png1218×319 18.8 KB

10

Oh, well, recursive sql can be in your packages too.

Whatever call the user made directly to oracle (a select statement, a package call, whatever) is considered a user statement. But the sql that the package (or anonymous pl/sql block) executes is considered recursive (even if it not a built-in oracle package). This is just how Oracle works, I have no control over it.

If you go back to the “Statement Detail” tab, all of the user statements are listed there. If you click a “+” to expand a node, those are the recursive statements underneath.

If you want to get SQL for just the user statements, you can right-click in that listing and choose “send user statements to file”.

I am not sure how to separate recursive sql written by you (or a coworker) from recursive sql that is built into the database. Maybe @NormTeamT has an idea on that. He’s a clever guy. :slight_smile:

11

And as if by magic, I’m here! :wink:

Your pl/sql call will be at ‘dep=0’ in the trace file. Sql statements it calls will be ar ‘dep=1’ and will be listed in the trace file before the pl/sql call. System recursive sql to support those sql statements will be at ‘dep=2’ or more.

My TraceMiner2 utility on github can be used to produce an html report for sql in a trace and allows you to specify the maximum depth you wish to see. If I can be forgiven for the shameless plug, it’s at https://github.com/NormanDunbar/TraceMiner2/releases.

I noticed earlier in this thread that your trace file has rather a large number of lines. If your pc is a little short on RAM you might have difficulty loading it all into memory. Give TraceMiner2 a try if Toad’s Trace File Analyser (I’m in the UK!) has problems.

Happy to answer questions about trace files any time.

Cheers,
Norm. [TeamT]

1 Like