Based on the sql being executed, you can speculate which queue is executing the sql. As for a systematic way to tie an oracle session to a remedy thread to a remedy queue/remedy user, I know of none. It would be nice if the thread logging collected this information.
Axton Grams The opinions, statements, and/or suggested courses of action expressed in this E-mail do not necessarily reflect those of BMC Software, Inc. My voluntary participation in this forum is not intended to convey a role as a spokesperson, liaison or public relations representative for BMC Software, Inc. On Wed, Feb 3, 2010 at 9:02 AM, Grooms, Frederick W < [email protected]> wrote: > I do it by having select access to a couple of system views in Oracle. Not > all DBA(s) will give access to these views, so you may have to ask nicely ;) > > The following will show you what SQL has taken time to complete (I forget > the threshold on Oracle that makes it show up in this view) > select * from V$SESSION_LONGOPS Order By Start_Time DESC > If it is not in there and is still running you could also use > select * from V$SESSION where Status='ACTIVE' Order By Logon_Time DESC > > Either one of those should give you the SQL_ID. With that you should be > able to see what is actually being executed. > select * from v$SQL where SQL_ID = '7wj5xv851kq9c' -- use the SQL_ID from > one of the above queries > > The V$SESSION_LONGOPS view will also give you the time remaining and > elapsed_seconds of that SQL statement. > > Fred > > -----Original Message----- > From: Action Request System discussion list(ARSList) [mailto: > [email protected]] On Behalf Of Coleman, Gavin > Sent: Wednesday, February 03, 2010 8:23 AM > To: [email protected] > Subject: Oracle / ARS thread linking > > ** > Hi List - we've been having a few problems with our Remedy systems and I > could do with some advice. We have recently experienced a massive slowdown > on two consecutive days of our production system. We have tied this to a > database problem. There has been an Oracle process that has been taking up > 60-70% of our DB CPU. This process was triggered by ARADMIN user. During > this slowdown, our Application Servers have shown no significant jump in CPU > usage. > > Is there a way in Oracle to tie the Oracle "thread ID" back to an ARS > thread ID? I.e. If I switch on logging by thread on my application servers, > this gives me the thread ID and everything that is being processed by that > thread. Can I identify that thread number in Oracle in anyway? If I could, > then I could potentially narrow down what could have caused this slowdown. > > Remedy 6.3 Patch 18 (windows 2003) > Oracle 9.2.0.6.4 (AIX) > > Thanks for your help, > > Gavin Coleman > Senior Analyst/Programmer > Computacenter (UK) Ltd > Services & Solutions > Hatfield Avenue > Hatfield, Hertfordshire, AL10 9TW, United Kingdom > T: +44 (0) 1707 631662 > E: [email protected] > W: www.computacenter.com > > > ********************************************************************** > COMPUTACENTER PLC is registered in England and Wales with the registered > number 03110569. Its registered office is at Hatfield Business Park, > Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW > COMPUTACENTER (UK) Limited is registered in England and Wales with the > registered number 01584718. Its registered office is at Hatfield Business > Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW > COMPUTACENTER (Mid-Market) Limited is registered in England and Wales with > the registered number 3434654. Its registered office is at Hatfield Business > Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW > COMPUTACENTER (FMS) Limited is registered in England and Wales with the > registered number 3798091. Its registered office is at Hatfield Business > Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW > > The contents of this email are intended for the named addressee only. > It contains information which may be confidential and which may also be > privileged. > Unless you are the named addressee (or authorised to receive mail for the > addressee) you may not copy or use it, or disclose it to anyone else. > If you receive it in error please notify us immediately and then destroy > it. > Computacenter information is available from: http://www.computacenter.com > ********************************************************************** > > _Platinum Sponsor: [email protected] ARSlist: "Where the Answers > Are"_ > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum > Sponsor:[email protected]<sponsor%[email protected]>ARSlist: > "Where the Answers Are" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
