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] ARSlist: "Where the Answers Are"
