Cannot run tkprofs on Unix due to space limitations

I have a 6 gb trace file but keep getting space errors when trying to run tkprofs against it.

Note: this is a follow-on to
- see my follow-on actions in subsequent comment.

There is around 50gb free space, here:

Filesystem    1024-blocks      Free     %Used    Iused %Iused Mounted on
/dev/slice6lv   150208512  70837372   53%    25447     1%   /slice6

but when I run tkprofs, I get this error:

[/slice6/bpeck] tkprof edwdev02_ora_1429584.trc nasco_md_tkprof.txt explain=/ sort='(prsela,exeela,fchela)'

TKPROF: Release - Production on Wed Mar 3 20:03:37 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

could not allocate space of size 360

It seems like it's some "internal" size to Oracle's ability to process the file rather than raw disk space being the issue, but that's just a guess.
Alaska CowboyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Franck PachotOracle DBACommented:

6 gb trace file is huge. You sould activate trace only for a short period.
Or manually search within the raw trace file the portion you need to tune (you can see a timestamp in it)

Alaska CowboyAuthor Commented:
I got my ulimited -a upgraded to unlimited for most settings

[/slice6/bpeck] ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) unlimited
memory(kbytes) unlimited
coredump(blocks) 4194303
nofiles(descriptors) 4096
still ran out of space
then I took off the sort option (as suggested in the first post) - still ran out of space
space in /tmp is only 5% used
Any ideas ? I didn't try splitting files yet, but if there is nothing else will have to come up with other options.
Alaska CowboyAuthor Commented:
How would I turn it off in the middle of a job ?
I have a Unix script that
- turns trace on
- calls the job to load the data for which I want stats
- turns trace off after the job is completed
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

What is your df -k for /tmp?
Alaska CowboyAuthor Commented:
5% used for /tmp
How much space is available?  5Gb , 10GB?
johnsoneSenior Oracle DBACommented:
One way to disable trace in a session is to log in as sys and run

exec dbms_system.set_sql_trace_in_session(sid, serial#, false);

You need to supply the sid and serial# of the session that is running the trace.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alaska CowboyAuthor Commented:
space avail
Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
/dev/hd3 524288 503208 5% 3589 4% /tmp
Johnsone - ok, thanks
That's only 500Mb.  If it is using this area for sorting that may not be enough.  There was another discussion on this in another thread.  I did not know tkprof used /tmp but if it does, this is probably not enough for the file you are analyzing.
Can you verify whether or not /tmp is running out of space while your tkprof is running?
Alaska CowboyAuthor Commented:
Wietman, I can try, not until Sat.
Franck PachotOracle DBACommented:
You can enable/disable trace from another session with dbms_system.set_sql_trace_in_session package or in 10g with dbms_monitor.
If you already have the trace files, maybe you can just extract the begining (trace file are witten sequentially). 'head - 10000' for exemple  to get first 10000 lines on unix.

I did a little searching and I am unsure as to whether or not tkprof makes use of /tmp or not without the user putting the output there, explicitly.  I do not have a large tracefile to test this on one of my systems currently.  So I am curious about what you determine.
Alaska CowboyAuthor Commented:
Franck - good suggestion, I'll try that. In my earlier question on this same issue, someone suggested splitting the files, so I guess this is along those Li es.

Mark, ok , will let you know.

Alaska CowboyAuthor Commented:
I ran it today, checking /tmp periodically (the job took 1.5 hours)
but today . . . it completed successfully, so ???
I'll have to talk it over with the unix admin, but I guess I am ok.
this was all on a test box, plus I need to verify the prod box . . . will keep this open for a day or two while I get try to run the same thing on the prod box
For my information s well as any others,  did space utilization change significantly in /tmp as a result of this run, while you were monitoring?
Alaska CowboyAuthor Commented:
oh sorry, I touched on that but didn't specify.
no, /tmp did not change, it remained at 5% the whole time.
I checked it frequently at the beginning, then periodically after that (it ran for 1.5 hours)
Franck PachotOracle DBACommented:
Does someone has information about the fact that tkprof uses /tmp.
It is not a good idea to use /tmp for big files. A full /tmp is something to avoid. Unis has /var/tmp for temporary files that can have big size.
It would be surprising that oracle uses /tmp for big temporary files.
I've traced (strace on linux) tkprof and I've seen no /tmp usage.
Alaska CowboyAuthor Commented:
FYI, the DBA reported a huge hit on this box this morning when I ran the tkprofs.
She reported: "I think it either used tons of memory or cpu…….some people could not even sign into Unix while it was running.  I saw that happen last week too when I was testing it out."
I am going to run  this as well on the Prod box to see if it works, but cannot do that until Saturday.
Alaska CowboyAuthor Commented:
FYI, I moved my large file (6GB) to our Prod Unix box and ran tkprofs . . . and it ran in around 10 minutes, no problem.
When I was finally able to generate the tkprofs successfully on the test Unix box, it took 1.5 hours. I had my ulimit -a all set to unlimited for this.
- although, after getting the ulimit set to unlimited, I still had a couple of attempts that failed due to "could not allocate space of size 360"
- but finally, on like the 3rd attempt after getting ulimit increased, the tkprofs output generated.
As I mentioned above, /tmp did not increase during the tkprofs utility on the test box, and I did not track it on the Prod box.
So it obviously is a resource constraint I had that caused problems on the test box, and this doesn't seem to be the case on our Prod box
Thanks for the help in this, I'll leave this open for a day or two.
Next week for our month-end processing, I am going to capture stats for the actual prod month-end job, and hopefully generate stats and be able to determine why our job runs so long.
Alaska CowboyAuthor Commented:
I was able to get my tkprof generated on test box as well as prod box.

I will post updates this weekend for our month-end processing but this is closed out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.