From oracle-l-bounce@freelists.org Mon Dec 6 15:58:16 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id iB6LwFe25793 for ; Mon, 6 Dec 2004 15:58:15 -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 iB6Lw7m25769 for ; Mon, 6 Dec 2004 15:58:07 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 29ADC72CF59; Mon, 6 Dec 2004 17:04:30 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21951-07; Mon, 6 Dec 2004 17:04:29 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 436F272D016; Mon, 6 Dec 2004 16:59:25 -0500 (EST) Message-ID: <433A07749711884D8032B6A0AB115262056564A8@conmsx07.corp.acxiom.net> From: Wolfson Larry - lwolfs To: Oracle-L@freelists.org Subject: UNDO question? 4031 bug?? Date: Mon, 6 Dec 2004 15:53:20 -0600 MIME-Version: 1.0 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 13200 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lawrence.wolfson@acxiom.com Precedence: normal Reply-To: lawrence.wolfson@acxiom.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hi, guys! I've got a client running 9.2.0.4 who's system wouldn't allow new connections for about 45 minutes last week. Most of the processes (240 out of 340-360) are from an app using Web Logic which never disconnect. I was monitoring the system with TOAD and about the same time TOAD showed the CPU and I/O activity dropped waay down, but the TOAD connection wasn't lost. And it seemed no one could log on during this time. We tried connect as sysdba and got we were connected to an "idle" instance. All of a sudden we could connect. One of the DBAs noticed at the same time we could get in again a bunch of UNDO segments were created. She went on METALINK and found a couple of potential bugs with UNDO allocation. One of the notes talks about setting an EVENT but didn't give the EVENT number and we're still waiting for them to research her TAR. The problem seemed to go from 8:45 to 9:30 or 14:45 - 15:30 GMT >From alert log, about when we could get in again: ARC0: Completed archiving log 19 thread 1 sequence 31755 Wed Dec 1 15:24:33 2004 Created Undo Segment _SYSSMU50$ Wed Dec 1 15:24:33 2004 Created Undo Segment _SYSSMU51$ Wed Dec 1 15:24:33 2004 Undo Segment 50 Onlined Wed Dec 1 15:24:33 2004 Undo Segment 51 Onlined Wed Dec 1 15:24:33 2004 Created Undo Segment _SYSSMU52$ Undo Segment 52 Onlined Wed Dec 1 15:24:33 2004 Created Undo Segment _SYSSMU53$ Undo Segment 53 Onlined Wed Dec 1 15:25:30 2004 Created Undo Segment _SYSSMU54$ Wed Dec 1 15:25:30 2004 Undo Segment 54 Onlined Wed Dec 1 15:25:30 2004 Created Undo Segment _SYSSMU55$ >From V$UNDOSTAT: MAX S T A R T T I M E UNDOBLKS TXNCOUNT MAXQUERYLEN CON UNXPSTEALCNT -------------------- ------ ------------ ------------ ----- ------------ 02-DEC-2004 14 01 54 3,378 5,113,239 84 12 0 02-DEC-2004 14 11 54 3,489 5,119,315 759 3 0 02-DEC-2004 14 21 54 2,741 5,124,347 595 8 0 02-DEC-2004 14 31 54 3,763 5,131,233 282 6 0 02-DEC-2004 14 41 54 3,461 5,137,022 274 6 0 02-DEC-2004 14 51 54 3,691 5,143,683 292 8 0 02-DEC-2004 15 01 54 3,162 5,149,672 138 13 0 02-DEC-2004 15 11 54 4,420 5,157,174 203 4 0 02-DEC-2004 15 21 54 3,683 5,163,799 316 5 0 02-DEC-2004 15 31 54 4,416 5,171,223 156 8 0 02-DEC-2004 15 41 54 4,010 5,179,075 47 8 0 02-DEC-2004 15 51 54 4,776 5,187,425 130 8 0 02-DEC-2004 16 01 54 4,211 5,195,450 413 6 0 She said someone told her that something happens to UNDO every 12 hours and There may be a performance bug. Anyone familiar with anything like this? TIA Same system - 3 times in the last 4 months they've been incurring 4031 errors that at first appeared to coincide with UNDO segment creation, but now we think is a separate issue. The problem stems from one of 3 Web logic application servers that I don't have access to. The 4031s range from 26-52K in size. Last spring they weren't having any problems like this and the Shared_pool_reserve always showed totally empty. I must say we really weren't monitoring that and it was brought to our attention that the activity had changed. The SP showed 70 to 120M free, but we couldn't get a 52K allocation. We adviced them to make some application changes and haven't gotten a response. That is, sometime next year. The last 2 times we increased the SP size, but I really didn't want to do that. Was 240M then 300 now 420M. The application really isn't that big, but they have lots of cloned code that gets around cursorSharing=force, because of the use of nulls. This is Java app and they don't use bind variables. They use lots of public synonyms and I was wondering if that could be part of the SP fragmentation. We suggested they change the code and that's going nowhere. Anyone have an idea on how to deal with the PSs? Since I'm not getting much assistance from the application group. One of the HOTSOS guys suggested we flush the shared pool once a day and pin everything, but we were already doing that. The pinned objects take around 70M. TIA again. Larry ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- http://www.freelists.org/webpage/oracle-l