OraFAQ Forum: Performance Tuning » Tracing long running program

Home » RDBMS Server » Performance Tuning » Tracing long running program (10.2.0.4, Windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tracing long running program [message #528535]

Tue, 25 October 2011 10:22

sant_new
Messages: 165
Registered: June 2008

Senior Member

Hi friends,

We have a program that is taking about 13-14 hours to run and we need to generate traces to see where it is taking so long. I usually use 10046 for the tracing, I'm wondering if the traces can be built incremently so that it doesn't become one huge trace file. Or if there is any other efficient way to do this..

Thanks so much

Report message to a moderator

Re: Tracing long running program [message #528541 is a reply to message #528535]

Tue, 25 October 2011 11:11

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I'm wondering if the traces can be built incremently so that it doesn't become one huge trace file.

If you can stop and start a new trace in the middle of your treatment then it is possible, you have to set trace_identifier between each trace.

But is this not more efficient to instrumentalize your code.
That is put at different points in your code log of context information so that you will know how much time each part lasts.

You can also have a look at the dbms_profiler package.

Regards
Michel

Report message to a moderator

Re: Tracing long running program [message #528543 is a reply to message #528541]

Tue, 25 October 2011 11:17

sant_new
Messages: 165
Registered: June 2008

Senior Member

Thanks Michael. Since the program already started running, I will try to use set trace_identifier in between each trace and see if it generates a new trace file. But, I'm generating trace for a different session though since the program runs through the application...

so I tried this:

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV(181,1720,10046,8,'');

Session altered.

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV(181,1720,10046,0,'');

Session altered.

SQL> ALTER SESSION set tracefile_identifier='sql1.trc';

Session altered.

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV(181,1720,10046,8,'');

Session altered.

But the trace is still updating in the same old trace file instead of generating a new trace file...
Can you please tell me what should I set trace_identifier to?

Thanks a lot

[Updated on: Tue, 25 October 2011 11:25]

Report message to a moderator

Re: Tracing long running program [message #528546 is a reply to message #528543]

Tue, 25 October 2011 11:42

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

dbms_system set the event in session 181, the alter session set the current session identifier.
By the way, the trace identifier is NOT the trace file name, it is a tag you will find in the trace file name.

Do you want to trace the current session or another one?

Regards
Michel

[Updated on: Tue, 25 October 2011 11:46]

Report message to a moderator

Re: Tracing long running program [message #528547 is a reply to message #528546]

Tue, 25 October 2011 11:47

sant_new
Messages: 165
Registered: June 2008

Senior Member

Not the current session... I'm tracing another session that is running the program....

Report message to a moderator

Re: Tracing long running program [message #528548 is a reply to message #528547]

Tue, 25 October 2011 11:50

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So you cannot use ALTER SESSION.
You can try:

exec sys.dbms_system.SET_INT_PARAM_IN_SESSION(181,1720,'tracefile_identifier',<a number>);

It allows you to set the client_identifier as a number. You cannot give it a string.

Regards
Michel

Report message to a moderator

Re: Tracing long running program [message #528549 is a reply to message #528548]

Tue, 25 October 2011 11:59

sant_new
Messages: 165
Registered: June 2008

Senior Member

I tried this.. while 10046 is running, I stopped it. Then ran SET_INT_PARAM_IN_SESSION and then started the 10046 trace again.
It did not generate new trace, just started updating the old trace again...

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV(181,1720,10046,8,'');

PL/SQL procedure successfully completed.

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV(181,1720,10046,0,'');

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_system.SET_INT_PARAM_IN_SESSION(181,1720,'tracefile_identifier',2);

PL/SQL procedure successfully completed.

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV(181,1720,10046,8,'');

PL/SQL procedure successfully completed.

Please help... thanks

Report message to a moderator

Re: Tracing long running program [message #528551 is a reply to message #528549]

Tue, 25 October 2011 12:14

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Did you stop the second trace?

Regards
Michel

Report message to a moderator

Re: Tracing long running program [message #528552 is a reply to message #528551]

Tue, 25 October 2011 12:18

sant_new
Messages: 165
Registered: June 2008

Senior Member

No... there is only one trace running db01_ora_892.trc in the server (this is 10046 trace). I stopped this trace by running below

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV(181,1720,10046,0,'');

PL/SQL procedure successfully completed.

Then I ran the below, hoping it would start a new trace file...

SQL> exec sys.dbms_system.SET_INT_PARAM_IN_SESSION(181,1720,'tracefile_identifier',2);

PL/SQL procedure successfully completed.

SQL> EXEC SYS.DBMS_SYSTEM.SET_EV(181,1720,10046,8,'');

PL/SQL procedure successfully completed.

But, this just started updating the same trace file db01_ora_892.trc (right now the filesize is 971MB.. worried if it will be difficult to open the trace file when done..)

Any suggestions please? I just need to generate multiple trace files instead of one...

THanks a lot

Report message to a moderator

Re: Tracing long running program [message #528553 is a reply to message #528552]

Tue, 25 October 2011 12:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I just need to generate multiple trace files instead of one...
NO, you do not need to generate multiple trace files.

Report message to a moderator

Re: Tracing long running program [message #528555 is a reply to message #528553]

Tue, 25 October 2011 12:22

sant_new
Messages: 165
Registered: June 2008

Senior Member

It will be easier to open/interpret the files and besides when the program runs for 13 hours the trace file will probably be like 10GB..

Report message to a moderator

Re: Tracing long running program [message #528556 is a reply to message #528555]

Tue, 25 October 2011 12:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>It will be easier to open/interpret the files
quantify easier
post proof above is true.
You are trying to solve a problem that does NOT exist!

Report message to a moderator

Re: Tracing long running program [message #528557 is a reply to message #528556]

Tue, 25 October 2011 12:37

sant_new
Messages: 165
Registered: June 2008

Senior Member

Oh no. So there is no way to generate multiple trace files for better reading.... For some reason, I'm unable to open the trace file db01_ora_892.trc now... It says access is denied its in use. but I've stopped 10046 tracing and its not updating the trace anymore but I'm not able to open or copy the trace file....

Any idea why?

thanks

Report message to a moderator

Re: Tracing long running program [message #528561 is a reply to message #528557]

Tue, 25 October 2011 12:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Any idea why?
WINDOWS prevent 2 threads opening same file concurrently.

which consumes less space
a) single 10GB file
b) ten 1GB files

Report message to a moderator

Re: Tracing long running program [message #528562 is a reply to message #528557]

Tue, 25 October 2011 12:49

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

sys.dbms_system.SET_INT_PARAM_IN_SESSION was just a try as the procedure is designed to work for an integer parameter and indeed it does not work for a string one even if you give it a number.

Quote:

So there is no way to generate multiple trace files for better reading

It seems in your case, yes.

Regards
Michel

[Updated on: Tue, 25 October 2011 12:50]

Report message to a moderator

Previous Topic:	Index on Range-List Subpartitions Table using template
Next Topic:	executions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 16:08:44 CST 2024