Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Monitoring questionable long running sessions

Re: Monitoring questionable long running sessions

From: Ryan Gaffuri <>
Date: 15 Jul 2004 07:15:13 -0700
Message-ID: <>

The key to solving this problem is your diagnosis and not guessing at what is wrong. To properly diagnose this problem use what is known as a '10046' trace. This traces all useful information about your session in question to a file. You can then use a reporting tool called 'tkprof' to give you a readable format.

There is a good article on on how to use this. Its relatively painless. After formatting the output is similiar to an explain plan. Once you know 'why', you can then determine 'how' to solve the problem. I'd also recommend checking out 'optizming oracle performance' by the guy who runs that site.

Pete Finnigan also has a very easy to follow step by step article on how to use Oracle tracing. I believe his website is Can't remember.

Don't guess.

Rick Denoire <> wrote in message news:<>...
> Hello
> Last Friday, a query started by a colleague was consuming all eight
> CPUs at nearly 100% for quite a long time (Oracle The OS
> (Linux RHAS 2.1) was showing at times unusually high (I would even say
> exagerated high) harddisk throughput values of more than 160 MB/s.
> He then called me and asked me to investigated why his query was not
> delivering any results - since just one day before, it ran through in
> about 5 min. In fact, I could not recognize any other activities like
> "logical reads" or the like.
> That reminded me similar situations when running 8.1.7 in the past.
> From time to time, sessions started to consume CPU at 100%
> indefinitely even when the DB session was closed. But this is the
> first time this happens on Oracle 9i since migrating 3 months ago.
> I just restarted the instance and asked the colleague to rerun his
> query, which, again, is running since Friday at 100% on two CPUs.
> Don't know why it hijacked only two CPUs this time.
> How can I be sure that the query is not doing some very inneficient
> operations or calculations? How can I find out if the instance or
> session itself is broken? I looked at the execution plan and remember
> a line "granule iteration" with a cost of 28000.
> Of course, there is the command "oradebug", which I actually used to
> do a heapdump, but I myself can't do anything with the trace file. My
> question is how can I quickly diagnose the session.
> Did you have similar experience with this kind of "run away" sessions?
> Thanks a lot
> Rick Denoire
Received on Thu Jul 15 2004 - 09:15:13 CDT

Original text of this message