From oracle-l-bounce@freelists.org Tue Nov 23 11:34:01 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id iANHY0I03471 for ; Tue, 23 Nov 2004 11:34:00 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id iANHY0B03464 for ; Tue, 23 Nov 2004 11:34:00 -0600 Received: from localhost (freelists-183.iquest.net [206.53.239.183]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3E03972D751; Tue, 23 Nov 2004 12:40:20 -0500 (EST) Received: from turing.freelists.org ([206.53.239.180]) by localhost (misc.avenirtech.net [206.53.239.183]) (amavisd-new, port 10024) with ESMTP id 06038-86; Tue, 23 Nov 2004 12:40:18 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 74AE972ECCE; Tue, 23 Nov 2004 11:34:15 -0500 (EST) From: "Mark W. Farnham" To: , , , "New DBA" Subject: RE: Important: Oracle processes taking lots of CPU Date: Tue, 23 Nov 2004 11:03:22 -0500 Message-ID: MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 In-Reply-To: <200411231401.iANE1TFh022420@webmail.nexlink.net> X-archive-position: 12626 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-To: mwf@rsiz.com X-list: oracle-l X-Virus-Scanned: amavisd-new at avenirtech.net Give that man a cigar! But I'll toss in a few bits anyway. The man page section on the system call or library reference "select" is often not loaded on various derivatives of Dennis Ricthie's operating system. In brief, last time I checked the arguments were (nfds, readfds, writefds, exceptfds, timeout), where nfds is the size of the structure (makes some sense that it is a constant), and the "fds" are file descriptors. If you're reading, then read and except should be non-zero, if you're writing then write and except should be non-zero. That looks like a really big number for timeout. I wonder if this is a zombie (shadow process, two_task, pick your name for it) that is disconnected and something else is now inadvertantly using the same descriptor, causing this process to repeatedly be falsely woken. Then its activity would cause it to survive any timeout and keep sitting on the select if it is in a spot where Oracle wants it to wait for a reply. Heh. A truss daemon rotating through Oracle back ends might be a lightweight way to check for candidate zombies to kill. Then again, you might be really waiting in a competitive race for access, but I'm don't grok why both read and write are filled in and a number that looks like a file descriptor is in the timeout argument. Regards, mwf -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Stephane Faroult Sent: Tuesday, November 23, 2004 9:01 AM To: Tony.Adolph@o2.com; ORACLE-L@freelists.org; New DBA Subject: Re: Important: Oracle processes taking lots of CPU I know that it may seem confusing on oracle-l, but 'select' doesn't ONLY refer to the SQL language - in that case, it has to do with I/O multiplexing - try 'man select'. Identifying what your file descriptors are pointing to might help. In any case, you are more likely to see things with sar or iostatthat the V$ views, as you pointed. Regards, Stephane Faroult RoughSea Ltd http://www.roughsea.com On Tue, 23 Nov 2004 05:25 , New DBA sent: Tony, Yes if Oracle is not waiting but working no wait will be registered. But it should atleast be reflected in "CPU used by this session" stat. It doesn't. I traced a few processes, but the trace files show no SQL which takes lots of CPU. Moreover, the CPU utlization in the trace file, or in v$sesstat don't match with the actual CPU taken by the process as seen from the OS commands like "top" Thats why I believe its some kind of O/S issue. So I did a truss on the process. And I saw the following line repeating infinitely. select(2048, 0x800003fffdffb3d0, 0x800003fffdffb4d0, 0x800003fffdffb5d0, 0x800003fffdffb6d0) = 0 I'm not sure how to interpret the output of truss, so I posted it in this forum since there are many experts out here, who might be able to interpret it! Is there any further information I can gather at the O/S level which throws some light on the problem? As far as statspack is concerned, we haven't implemented statspack, but I did run utlbstat/utlestat and uploaded the output to oraperf.com. It didn't suggested or detect excess CPU/LIOs, since those stats are pretty acceptable in the trace files. Regards New DBA --- Tony.Adolph@o2.com[1] wrote: >I'm no expert here, but here *may be* a few things >to think about: > >When Oracle is actually doing something it isn't >recorded as a wait event, >e.g. getting a datablock that is in cache doesn't >generate a wait event. >If your query is "horrible" you could be using loads >of CPU without >generating many waitevents. > >A little more dodgy info: "db file sequential >read" is normally >accociated with datafile access by rowid, ie. after >an index lookup. > >I think I'd try to find out which queries are >running during the >performance problem times and explaining the >queries. > >Also, have you run spreport for this time period? > >Told you I wasn't an expert, but I hope that prompts >other readers to fill >in the gaps and give you better hints, > >Good luck >Tony __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com[2] -- http://www.freelists.org/webpage/oracle-l[3] --- Links --- 1 javascript:parent.opencompose('Tony.Adolph@o2.com','','','') 2 modules/refer.pl?redirect=http%3A%2F%2Fmail.yahoo.com 3 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle- l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l