Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 6990910031AAE3 for ; Mon, 22 Mar 2021 11:19:04 +0100 (CET) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 2993640B6C; Mon, 22 Mar 2021 10:19:03 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 1126240009; Mon, 22 Mar 2021 10:19:03 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1616408343; bh=v9n/0NTJqJDLpZGxMqlZ/WZcghLpMhwplp763f24llw=; h=From:Sender:Sender:From; b=dhghXQsgfDUSjhaqC6S6WpVjLe8vGiB9Nh3NG1R/Fd9KTebMTjADsFD4UZRddklMo mGO6sOO3gQftu8Y8eK7YztkR0brDI5iOSt+sgO7LMzlScPTBLahXU/nhHDl9byEgj4 mYtOvaJjwufwXB2F3bwrV1fllSw5LcNvi1sd6Sp8= X-Virus-Scanned: by FreeLists at turing2.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id fMQsDIjPSMbG; Mon, 22 Mar 2021 10:19:03 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 408E340008; Mon, 22 Mar 2021 10:19:00 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1616408341; bh=v9n/0NTJqJDLpZGxMqlZ/WZcghLpMhwplp763f24llw=; h=From:Sender:Sender:From; b=RYJdjq6TeEG99jO6dn+5+7EWXwDd2k8TxLX0guTSz1y04Pq6jzrzr56QDOSoICM8I XwEX7RYjQokq7Za4NwoEOQWOAoz6gB8czWx82fPyy5jpgVSpFNZXmHgwb8MKvKdwK5 ZaPr127WPWw7Ly+qN6hgX4Hvu+T8Kit28lWUoPKw= Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 22 Mar 2021 10:18:58 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id AD55B40005 for ; Mon, 22 Mar 2021 10:18:58 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20161025 header.b=qNhP/61N; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 5cBvfpi8DzJV for ; Mon, 22 Mar 2021 10:18:58 +0000 (UTC) Received: from mail-lf1-f45.google.com (mail-lf1-f45.google.com [209.85.167.45]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 90C0D3FFF8 for ; Mon, 22 Mar 2021 10:18:58 +0000 (UTC) Received: by mail-lf1-f45.google.com with SMTP id b83so20348211lfd.11 for ; Mon, 22 Mar 2021 03:18:58 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=2XZX7o9zwuFMNHcP7Ri20MnlLj54SjDiIN0J1GYaX2M=; b=kA2FgOLb2H94JhvswlgmMvk941PHyzhLAy6oeyDtBwgP/fD0P9AMNP+O/D0bPbGKkI x9R7U/C/xz2StEMny0fK5djbYCzGMnz9zYBvlB5X0ygXmhyFyE4O6XJZ8kWT0aGOCIUR 2UtElXWETddpRm1e+YJZf10h4Y+NSFU804HKFN19e/KSQ1qQecHcwYfJzPXx475y/m7v y9J56IFrqrcWrYh/jYH0oODCYU5BXbF/A1Dc87r2sRx9no/zKHYxTu2gXg4tTxJaZb/k upa/hTUDofcuMs5txi+N99v657jGZtZXGbk2LpoGeOZ4xxpn6Qj/4DJ06HnFy0LnZ+z2 VNHA== X-Gm-Message-State: AOAM530BWatEY0b+cPA0hkmcLYraGvZpy68lT0ZwMpzERnlJ2cbmHcgL 6d2ip269ub/AfKmQTMzF4poHCer1xNzHw6Bo4oU= X-Google-Smtp-Source: ABdhPJy/3WpYHINBeDb9Mr412iYa03XyHXabxwaDD5ZmyVSBDIvCZa0iyZesbicBKU3KFr5gf6Zycvy+jZPisk+4aYw= X-Received: by 2002:ac2:4f84:: with SMTP id z4mr8977383lfs.608.1616408337396; Mon, 22 Mar 2021 03:18:57 -0700 (PDT) MIME-Version: 1.0 References: <495f94ae-3567-1a5c-0813-694ffa65c910@gmail.com> In-Reply-To: From: Hemant K Chitale Date: Mon, 22 Mar 2021 18:18:31 +0800 Message-ID: Subject: Re: UNDO Space Error To: oracle.developer35@gmail.com Cc: gogala.mladen@gmail.com, Oracle L Content-Type: multipart/alternative; boundary="000000000000c3392205be1d6425" X-archive-position: 79349 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: hemantkchitale@gmail.com Precedence: normal Reply-To: hemantkchitale@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --000000000000c3392205be1d6425 Content-Type: text/plain; charset="UTF-8" Look at v$undostat and dba_hist_undostat (if you have the Diagnostic and Tuning Pack licences). If MAXQUERYLEN has increased recently, TUNED_UNDORETENTION would also have increased. Unfortunately, there is no dba_hist_transaaction view so you have to periodically poll v$transaction (I'd put the results into a table with timestamps so that I can monitor sid/serial# with used_urec and used_ublk over time. Your INSERT session may be a victim and not a cause of ORA-30036 if other sessions are generating more undo and/or undo is being retained and not being expired for longer duration. Hemant K Chitale On Sun, Mar 21, 2021 at 1:16 PM Pap wrote: > > Thank you. > > We do use batching for large DML to do it in bulk fashion. However, I got > one of the INSERT queries which has failed and looks something as below. > And I do see this table TAB(masked actual name) has 4 indexes and I think > all the UNDO generated must be because of that direct path load is > happening so table block related UNDO will be zero. And below is the sample > insert query which has failed with Ora-30036. > > I do see the execution plan has changed from past but i can't relate > howcome change in path can result in higher UNDO space consumption for the > INSERT query. It should depend on the number of rows those are loaded to > table only. Please correct me if my understanding is wrong. And also even > if the plan suggests the expected rows are higher in the new plan which is > pointing towards a higher number of rows being loaded to the table too, but > i had verified with the application team and they mentioned the number of > rows loaded were around the same as previous executions which got loaded > during the failure days. So still wondering what must be the cause of this > sudden failure? > > INSERT /*+ append parallel(16) nologging */ INTO USER1.TAB (c1, c2, c3, > c4... c11) SELECT...; > > > > On Sun, Mar 21, 2021 at 6:25 AM Mladen Gogala > wrote: > >> As opposed to ORA-01555 which is a nightmare to debug and resolve, >> ORA-30036 doesn't present such problems because it's thrown by the same >> transaction that causes it. The cause is very simple: the current >> transaction ran out of space in the undo segment. At this point, I'd like >> to remind you of the common saying that disks are cheap, especially is >> someone else is buying them. So, what you need to do is to figure out which >> transaction is causing the problem. Second thing is figuring out what to do >> about that. I've frequently seen this with data purges. If you want to get >> rid of 1/3 of the rows in a billion rows table, based on a date coliumn, it >> is conceivable that you might need few KB of the UNDO space. The solution >> is usually to write a PL/SQL procedure which will clean the rows in batches >> of 100K. >> >> So, what were you doing? Can you post the SQL which has thrown the error? >> Without the exact statement, cardinality of the tables involved and rough >> estimate how much data are you modifying, it's hard to tell you anything >> more. >> On 3/20/21 2:22 PM, Pap wrote: >> >> Hello Listers, Its version 11.2.0.4 of oracle. We suddenly started >> encountering ORA-30036(ORA-30036: unable to extend segment by 8 in undo >> tablespace 'UNDOTBS2') in one of the databases. We don't have any changes >> done to the code. We already increased the UNDO tablespace size from ~100Gb >> to ~190GB but still facing the same and this time we want to find the root >> cause rather than keep on increasing the size of tablespace. >> >> During one of the failures I saw DBA_UNDO_EXTENTS was showing almost all >> of the extent status as UNEXPIRED. dba_free_space was showing zero space >> for that tablespace. We have the data files in the undo tablespace set as >> autoextend ON. And we are using AUTO undo management with UNDO retention >> set as 900. >> >> I understand there are two types of UNDO noted by oracle , one is UNDO >> read which the SELECT query sometimes fails with Ora-01555 (but here we are >> not encountering that). The other one is UNDO generation because of the >> DML(INSERT/UPDATE/DELETE) and in this case our failure is because of the >> same UNDO and each time it's mostly INSERT queries failing while doing data >> load. So is there any way I can track the exact session/sql/user which is >> generating maximum UNDO from any historical AWR views and also during run >> time? >> >> How to debug from history and get the cause of this sudden increase in >> UNDO space consumption? Any other fix other than increasing UNDO space? >> >> Regards >> >> pap >> >> >> -- >> Mladen Gogala >> Database Consultant >> Tel: (347) 321-1217https://dbwhisperer.wordpress.com >> >> -- http://www.freelists.org/webpage/oracle-l > > --000000000000c3392205be1d6425 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Look at v$undostat and dba_hist_undostat (i= f you have the Diagnostic and Tuning Pack licences).=C2=A0 If MAXQUERYLEN h= as increased recently, TUNED_UNDORETENTION would also have increased.
U= nfortunately, there is no dba_hist_transaaction view so you have to periodi= cally poll v$transaction (I'd put the results into a table with timesta= mps=C2=A0so that I can monitor sid/serial# with used_urec and used_ublk ove= r time.

Your INSERT session may be a victim and no= t a cause of=C2=A0ORA-30036 if other session= s are generating more undo and/or undo is being retained and not being expi= red for longer duration.
<= div>

Hemant K Chitale




On Sun, Mar 21, 2021 at 1:16 PM Pap <oracle.developer35@gmail.com> wrote:<= br>

Thank you.

We do use batching for large = DML to do it in bulk fashion. However,=C2=A0 I got one of the INSERT querie= s which has failed and looks something as below. And I do see this table TA= B(masked actual name) has 4 indexes and I think all the UNDO generated must= be because of that direct path load is happening so table block related UN= DO will be zero. And below is the sample insert query which has failed with= Ora-30036.=C2=A0

I do see the execution plan has change= d=C2=A0from past but i can't=C2=A0relate howcome=C2=A0change=C2=A0in pa= th can result in higher UNDO space consumption for the INSERT query. It sho= uld depend on the number of rows those are loaded to table only. Please cor= rect me if my understanding is wrong. And also even if the plan suggests th= e expected rows are higher in the new plan which is pointing towards a high= er=C2=A0number of rows being loaded to the table too, but i had verified wi= th the application team and they mentioned=C2=A0the number of rows loaded w= ere around the same as previous=C2=A0executions which got loaded during the= failure days. So still wondering what must be the cause of this sudden fai= lure?

INSERT /*+ append parallel(16) nolog= ging */ INTO USER1.TAB (c1, c2, c3, c4... c11) SELECT...;=C2=A0



On Sun, Mar 21, 2021 at= 6:25 AM Mladen Gogala <gogala.mladen@gmail.com> wrote:
=20 =20 =20

As opposed to ORA-01555 which is a nightmare to debug and resolve, ORA-30036 doesn't present such problems because it's thrown by the same transaction that causes it. The cause is very simple: the current transaction ran out of space in the undo segment. At this point, I'd like to remind you of the common saying that disks are cheap, especially is someone else is buying them. So, what you need to do is to figure out which transaction is causing the problem. Second thing is figuring out what to do about that. I've frequently seen this with data purges.=C2=A0 If = you want to get rid of 1/3 of the rows in a billion rows table, based on a date coliumn, it is conceivable that you might need few KB of the UNDO space. The solution is usually to write a PL/SQL procedure which will clean the rows in batches of 100K.

So, what were you doing? Can you post the SQL which has thrown the error? Without the exact statement, cardinality of the tables involved and rough estimate how much data are you modifying, it's hard to tell you anything more.

On 3/20/21 2:22 PM, Pap wrote:
=20

Hello Listers, Its version 11.2.0.4 of oracle. We suddenly started encountering ORA-30036(ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2') in one of the databases.= We don't have any changes done to the code. We already increased the UNDO tablespace size from ~100Gb to ~190GB but still facing the same and this time we want to find the root cause rather than keep on increasing the size of tablespace.

During one of the failures I saw DBA_UNDO_EXTENTS was showing almost all of the extent status as UNEXPIRED. dba_free_space was showing zero space for that tablespace. We have the data files in the undo tablespace set as autoextend ON. And we are using AUTO undo management with UNDO retention set as 900.

I understand there are two types of UNDO noted by oracle , one is UNDO read which the SELECT query sometimes fails with Ora-01555 (but here we are not encountering that). The other one is UNDO generation because of the DML(INSERT/UPDATE/DELETE) and in this case our failure is because of the same UNDO and each time it's=C2=A0mostly INS= ERT queries failing while doing data load. So is there any way I can track the exact session/sql/user which is generating maximum UNDO from any historical AWR views and also during run time?

How to debug from history and get the cause of this sudden increase in UNDO space consumption?=C2=A0A= ny other fix other than increasing UNDO space?

Regards

pap



--=20
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbw=
hisperer.wordpress.com
-- htt= p://www.freelists.org/webpage/oracle-l
--000000000000c3392205be1d6425-- -- http://www.freelists.org/webpage/oracle-l