Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Anil.

Asked: February 27, 2005 - 2:17 pm UTC

Last updated: July 07, 2008 - 4:17 pm UTC

Version: 10.1

Viewed 1000+ times

You Asked

Hi Tom

IS there any way to create separate trace file for different alter session set sql_trace=true comamnd. My problem is in a connection pool scenarion . If I wanto trace a purticular user I am not able to cretae a separate file. Oracle always create one file for a connection by its process id. Is there any trick to do this.


and Tom said...

</code> <code>

Kimberly Floss had a very nice write up on dbms_monitor in Oracle magazine not too long ago addressing this in 10g.


  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment


precise to the point

Anil, February 27, 2005 - 2:50 pm UTC

Thanks Tom

That is what I was looking for.


alert message for an application -- 9i

Baqir Hussain, July 05, 2007 - 4:41 pm UTC

We had a situation last night when the database is up and the application "TRAPEZE" was not able to make connection to the database via "CITRIX" application server. This was notified this morning by one of the users.
I would like to know if there is any way of finding (setting up any script or function) that the application is not making connection to the database even though the database is up.

Tom Kyte
July 05, 2007 - 4:46 pm UTC

sure, if that application would like to call such a script - not much we can do from the DATABASE SIDE you understand.

It is sort of like your manager asking you to report all attempts to page you that didn't make it to your pager.

the person doing the paging - they could generate the inputs for that report.
the person who should have gotten paged - they have no clue.

so, talk to the makers of trapeze.

Alexander, July 02, 2008 - 9:42 am UTC

Hi Tom,

Is there anyway in 10.2 that I can see what tracing I may have turned on, like a $view?

I have some rather large trace files showing up in udump every once and awhile and I have no idea where they're coming from.
Tom Kyte
July 07, 2008 - 7:03 am UTC

you say "what tracing I may have turned on", but it seems that the tracing is coming from some other sessions??

have you looked in the trace files, they typically contain tons of stuff that would help you identify what is going on in the session that started tracing.

Alexander, July 02, 2008 - 9:46 am UTC

Nevermind I found it, DBA_ENABLED_TRACES.

Alexander, July 07, 2008 - 11:26 am UTC

It's possible it's coming from another session. But this isn't a sql_trace, it's more of a dump.

Another one popped up, could you tell me where I can find this information? The top is the only piece readable

$ more ocp21d_ora_6852760.trc
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2
System name:    AIX
Node name:      p2a2pdc
Release:        3
Version:        5
Machine:        00CC027C4C00
Instance name: OCP21D
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 6852760, image: oracleOCP21D@p2a2pdc

*** 2008-07-03 15:31:23.122
*** SERVICE NAME:(SYS$USERS) 2008-07-03 15:31:23.120
*** SESSION ID:(315.11474) 2008-07-03 15:31:23.120
Memory Notification: Library Cache Object loaded into SGA
Heap size 157342K exceeds notification threshold (51200K)
LIBRARY OBJECT HANDLE: handle=11041a950 mtx=11041aa80(0) cdp=0
namespace=CRSR flags=RON/CGA/EXP/[00090100]
kkkk-dddd-llll=0000-0001-0000 lock=N pin=X latch#=11 hpc=0000 hlc=0000
lwt=11041a9f8[0,0] ltm=11041aa08[0,0]
pwt=11041a9c0[0,0] ptm=11041a9d0[0,0]
ref=11041aa28[0,0] lnd=11041aa40[0,0]
  LIBRARY OBJECT: object=11041a5d0
  type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
  DEPENDENCIES: count=21 size=32

Tom Kyte
July 07, 2008 - 12:12 pm UTC

read Note 330239.1 on metalink

Alexander, July 07, 2008 - 2:18 pm UTC

That sounds like a bind variable problem to me.

You gave me this query not too long ago,

SQL> edit
Wrote file afiedt.buf

  1  select version_count, count(*)
  2  from v$sqlarea
  3  group by version_count
  4* order by version_count
SQL> /

------------- ----------
            1       5652
            2       1161
            3         88
            4         56
            5         16
            6         25
            7          8
            8          5
            9          4
           10          3
           11          1
           16          1
           17          1
           23          1
           24          1
           28          1
           36          2
           42          1
           52          1

19 rows selected.

That's not good right?
Tom Kyte
July 07, 2008 - 3:08 pm UTC

no, that query would not indicate a bind problem at all.

Alexander, July 07, 2008 - 3:48 pm UTC

I guess maybe I don't know how to interpret the output of the query. What would I see if there was a bind problem? I thought many versions was not good, meaning the sql wasn't being shared so a child cursor was created.
Tom Kyte
July 07, 2008 - 4:17 pm UTC

the versions do mean that, but the sql itself could well, probably is in fact, use binds.

sql might not be shared because

o you optimized all_rows, they optimized first_rows
o you bound a varchar2 bind, they bound a number
o you have a table T, they have a table T and the query is simply "select * from T", but they are different T's
o and so on......

the only row that might signify a bind problem with the query you used would be the first one.

If you have thousands of queries loaded - but yet say to yourself "hmmm, we only issue like 500 different sql statements" - that would mean you have lots of unique sql's out there.

If I gave you a query to find bind issues, it would have probably been:

More to Explore


More on PL/SQL routine DBMS_MONITOR here