Home » RDBMS Server » Performance Tuning » Enabling Trace file generation. (Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production)
Enabling Trace file generation. [message #314715] Thu, 17 April 2008 10:21 Go to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Hi,

I'm having some trouble generating trace files on a relatively new oracle instance.

If I connect to the db in sqlplus I can enable trace from within the session, and a trace file is generated fine.

However, I'm having trouble when trying to enable trace from outside the session.

I've tried (as sys) using both toads "enable trace" thingy within it's sessions browser, and just plain old calling dbms_system.set_sql_trace_in_session, but no trace file is being generated.

Anyone aware of any particular oracle param which needs to be set, to allow trace file generation to be enabled from outside a session, when enabling from within a session works fine?

Cheers,
Michael.
Re: Enabling Trace file generation. [message #314716 is a reply to message #314715] Thu, 17 April 2008 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Pete Finnigan, How to set trace for others sessions, for your own session and at instance level

Regards
Michel
Re: Enabling Trace file generation. [message #314725 is a reply to message #314715] Thu, 17 April 2008 11:06 Go to previous message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Thanks Michel, thats a great link, which has allowed me to reframe my little mystery ...

Actually, now I can see that my problem is nothing to do with enabling trace outside the session versus inside the session, but rather can be given my this example which is trying to show that
I'm getting different behaviour when I set the tracefile_identifier and when I don't.

D:\Documents and Settings\m134>sqlplus ilimrep@tmstest

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 17 16:46:17 2008

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

16:46:20 SQL> ALTER SESSION SET SQL_TRACE=TRUE;
Session altered.
16:46:41 SQL> SELECT * FROM DUAL;

D
-
X

16:46:41 SQL> ALTER SESSION SET SQL_TRACE=FALSE;
Session altered.

--Note, at this stage I check the udump area and there's no trace file

16:46:41 SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='';
Session altered.
16:47:12 SQL>
16:47:12 SQL> ALTER SESSION SET SQL_TRACE=TRUE;
Session altered.
16:47:12 SQL>
16:47:12 SQL> SELECT * FROM DUAL;
D
-
X
16:47:12 SQL>
16:47:12 SQL> ALTER SESSION SET SQL_TRACE=FALSE;
16:47:12   2  /
Session altered.
16:47:12 SQL>

--Now however there is a trace file!! The only difference is that I used SET TRACEFILE_IDENTIFIER. Hardly!?



I've run this test a whole bunch of times, and am mightly confused by why I seem to be getting different behaviour in terms of generating the trace file depending on whether I've used SET TRACEFILE_IDENTIFIER or not.

Any suggestions?

Cheers,
Michael.
Previous Topic: Query taking time to execute in front end(Oracle 10g R2)
Next Topic: how can i execute this query faster
Goto Forum:
  


Current Time: Thu Dec 08 18:48:36 CST 2016

Total time taken to generate the page: 0.11214 seconds