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 BF5FC1003271C4 for ; Thu, 29 Apr 2021 06:59:15 +0200 (CEST) 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 3A1F44254D; Thu, 29 Apr 2021 04:59:14 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 2F30341336; Thu, 29 Apr 2021 04:59:14 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1619672354; bh=VkNqc3me8LsHGEE3sOYQdzZG2B0wer2EpSZXgNk3zmg=; h=From:Sender:Sender:From; b=m/LouCqcLlMHBmWtau/N+5o9jAHNEsgpviVdJaCgypz+I0oXOIpVNE0LlokgpM4r0 SwA65wQ20hwgTUP18PUK3oJ6RyZWgGZIzmQGeMxNUGKaZ2LqIXFDq/fdNcu4uTyw5Z ywwv8EFCCtgI8fLqoIdXXV2nMjPao2la+dLasFEY= 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 E48W93LKK_Js; Thu, 29 Apr 2021 04:59:14 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id B340A41335; Thu, 29 Apr 2021 04:59:11 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1619672352; bh=VkNqc3me8LsHGEE3sOYQdzZG2B0wer2EpSZXgNk3zmg=; h=From:Sender:Sender:From; b=rh16x/2m+U7xKfDOJgayj6xPVVR8kH0ggCmoyFTbokoeJO3Z0vh9E5FMxMXTW/p6L gorTlvHnGXXd42wW/MEtfhGciVEQy61IbGx5lElUa3H3mSlBlGdeFWqZlTTIEiIhha 4lWTbY/4BJsVuvVu0cB9pn5CjrH+RWuJ17pXt1DE= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 29 Apr 2021 04:59:10 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 4602740A16 for ; Thu, 29 Apr 2021 04:59:10 +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=GM2BKb0B; 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 zPnrbC_KPoth for ; Thu, 29 Apr 2021 04:59:10 +0000 (UTC) Received: from mail-io1-f45.google.com (mail-io1-f45.google.com [209.85.166.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 0F4BC41332 for ; Thu, 29 Apr 2021 04:59:07 +0000 (UTC) Received: by mail-io1-f45.google.com with SMTP id b10so6780740iot.4 for ; Wed, 28 Apr 2021 21:59:07 -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=fMMCZhwsIepfZmedmFxOVxR0KVBOOn5oJNdzwJ4HFYU=; b=Q0YmYI7qfQhM12/NAuaO5NdktHD9opvqYFirTGkgBUxHmlL6YozhcHx/i6ZARYh8NO M+2ivvjH5PkKkb4m9DT4bDFO+YojJyef2kciUOdUppHwisx9xxQRTp+zpvxbRH1L3WcU bVr8Qo7i+dMSXjzGd5eUOD9D44IqVjOC69tHtWXWB5eY5dKdIP5I7DEcz3hol0n9OZ/V K5IUtwWzazPxXIpDNZWnfXSUsKXv8uf3hgqxOy0FMBb3wLoOmI8U/VKQCqBzm1NcozV1 qSW6RJvHWZvfKJ1sZoFK6b83ZjVG2fUZThcaGHKoxmAcXCMSM+lp608KjRbDdRaboG0f TkqA== X-Gm-Message-State: AOAM532CKN/Hn4pjIQNdXm8g1LwPyTob6YMK1smlNA/qqPS01g75iEkD Ybk7D3tyo2FE3xZogkmxkXb2BHQwkPkgWzFFkCA= X-Google-Smtp-Source: ABdhPJzn5K/FGhMwGus70CkiannzIRPddNBMcAPGszO6LtOhnkGVb56ev1GqNQX41nxq6a9Sw6cDzp1c3w4ldFygpsw= X-Received: by 2002:a05:6602:24d1:: with SMTP id h17mr8307596ioe.99.1619672347306; Wed, 28 Apr 2021 21:59:07 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Laurentiu Oprea Date: Thu, 29 Apr 2021 07:58:19 +0300 Message-ID: Subject: Re: _rollback_segment_count side effects To: Jonathan Lewis Cc: "ORACLE-L (oracle-l@freelists.org)" Content-Type: multipart/alternative; boundary="000000000000e9ee6e05c1155a6e" X-archive-position: 79729 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: laurentiu.oprea06@gmail.com Precedence: normal Reply-To: laurentiu.oprea06@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 --000000000000e9ee6e05c1155a6e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks a lot for the detailed answer. I was looking indeed at v$resource_limit and found these numbers on the instance with highest values: RESOURCE_NAME/CURRENT_UTILIZATION/MAX_UTILIZATION/INITIAL_ALLOCATION/ LIMIT_VALUE transactions 142 533 5596 UNLIMITED So based on the above, just to be on the safe side I should consider changing _rollback_segment_count to around 500? I managed to extract the session stats also. Does it look like this is an issue with both read and write consistency? rollback changes - undo records applied 19651 data blocks consistent reads - undo records applied 126936562 transaction tables consistent reads - undo records applied 151042001 transaction tables consistent read rollbacks 165714 undo change vector size 1.0182E+11 Thank you. =C3=8En vin., 23 apr. 2021 la 11:39, Jonathan Lewis a scris: > If you do decide to learn more about how undo works then you should get a > copy of my book "Oracle Core". > > When you saw the large numbers of single block reads did you also check > the session (or system) activity statistics (v$sesstat/v$sysstat) for > "transaction tables consistent read rollbacks" and "transaction tables > consistent reads - undo reacords applied", The former is the number of > times you had to take an undo segment header block into the past to check= a > commit SCN, and the latter is the number of undo records (which may be fa= r > more than physical block reads) that you needed to use to get to the > correct point in the past. > If the average undo records per rollback here is large then that would > explain why Oracle Support suggested setting this parameter. > > The suggestion of 50 to 100 is not a threat. It's when you get to values > in the region of a couple of thousand that increase the risk of ORA-01628= . > In fact there's an easy guideline for setting this value if you are havin= g > the problem that Oracle thinks you have. > > select * from V$resource_limit where resource_name =3D 'transactions"; > > The *maximum_utilization* column will tell you the largest number of > concurrent transactions that has appeared since database startup. That's > the largest number of rollback segments you would have needed - so a good > guideline for that hidden parameter. (Technically if you also had incomi= ng > XA transactions I think you might also need to consider maximum_utilizati= on > for "branches" and add that as well - but that's speculation that I haven= 't > tested.) > > I wrote a short note about this view recently at > https://jonathanlewis.wordpress.com/2021/03/25/vresource_limit/ > > > The threat that this fix addresses is the case where Oracle has only got = a > small number of undo segments online to meet what it sees as a low level = of > concurrent transactions, and you suddenly get a sustained burst of much > higher concurrency. Generally Oracle tries to keep one undo segment per > transaction and one transaction per undo segment - but the delay between > concurrency going up and more segments coming online may be too long, so > you end up with lots of transactions per rollback segment at the same tim= e. > This has two effects: each undo segment transaction table slot gets > overwritten many times, and (b) if the concurreny level goes over 5 > transactions per rollback segment you start "wasting" space in undo block= s > so you can end up using the undo segment much more rapidly than normal. > > I wrote a blog note a few years ago that describes and models the > particular situation that you may be seeing. The model does some bizarre > things that won't look anything like your production application, but I h= ad > to do that to reproduce the production symptoms from a very small model: > https://jonathanlewis.wordpress.com/2017/04/21/undo-understood/ > > > > Regards > Jonathan Lewis > > > > On Thu, 22 Apr 2021 at 15:22, Laurentiu Oprea > wrote: > >> Thanks a lot Jonathan for your anwer. Your answer is actually exactly >> what I was searching for, because to be honest I don't have a full >> understanding of the issue I face. >> >> In short: I have an insert base on select that is executed around 150 >> times per in a window of about 3-4 hours and just one or 2 executions >> sometimes are taking a few good hours, although all other executions are >> under 3 minutes. All the waits are mainly cell single blocks wait events >> (when doing a range scan) on UNDO tablespace. I know for sure data is >> loaded on the source tables all the time, and is very strange why one or >> maximum 2 executions take so much time. >> >> I opened an SR and the recommendation was to set _rollback_segment_count >> to a value like 50/100. At this point I`m in the process of >> understanding the issue from a business perspective because the describe= d >> waits are on 3 different indexes sometimes (on 3 different tables - agai= n I >> know for sure data is loaded all the time in those tables) and maybe I c= an >> arrange the order of these jobs. Even so the impact of what I believe is= a >> read consistency issue is just too dramatic. >> >> If this is expected behavior and _rollback_segment_count can mitigate i= t >> is clear to me I might need to learn more about how UNDO works. >> >> Thanks again. >> >> =C3=8En mie., 21 apr. 2021 la 13:40, Jonathan Lewis >> a scris: >> >>> That's not a good question. >>> >>> It doesn't matter what side effects anyone has seen unless you also kno= w >>> what sort of activity their database goes through - so you're asking ot= her >>> people to tell you things about their databases that you should be tell= ing >>> them about your database before you ask the question. >>> >>> I can think of a couple of *possible *side effects - but they may be >>> completely irrelevant to you, and if I don't give you a context your ne= xt >>> question will probably be to ask for an explanation of how or why the s= ide >>> effects night appear. >>> >>> a) You waste buffer cache unnecessarily >>> b) you increase the risk of ORA-01628 max # extents (32765) reached fo= r >>> rollback segment >>> >>> >>> Regards >>> Jonathan Lewis >>> >>> >>> >>> On Tue, 20 Apr 2021 at 05:58, Laurentiu Oprea < >>> laurentiu.oprea06@gmail.com> wrote: >>> >>>> Hello, >>>> >>>> DB Version: 12.1 >>>> >>>> Is someone played around with parameter _rollback_segment_count ? I`m >>>> most interested in what are the side effects of increasing this parame= ter. >>>> >>>> Thank you. >>>> >>> --000000000000e9ee6e05c1155a6e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks a lot for the detailed=C2=A0answer.

<= div>I was looking indeed at v$resource_limit and found these numbers on the= instance with highest values:
RESO= URCE_NAME/CURRENT_UTILIZATION/<= span style=3D"color:rgb(26,24,22);font-family:"Courier New",Couri= er,monospace;font-size:14px">MAX_UTILIZATION/INITIAL_ALLOCATION/LIMIT_VALUE<= /span>

transactions=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 142=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 533=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 5596=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 UNLIMITED


So based on the a= bove, just to be on the safe side I should consider changing=C2=A0_rollback_seg= ment_count=C2=A0 to around 500?


I managed=C2=A0t= o extract the session stats also. Does it look like this is an issue with b= oth read and write consistency?=C2=A0


rollback changes - undo records applied=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 19651

data blocks consistent reads - undo records applied=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 126936562=C2=A0=C2=A0=C2=A0=C2=A0

transaction tables consistent reads - undo records applied=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 151042001=C2=A0=C2=A0=C2=A0=C2=A0

transaction tables consistent read rollbacks=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 165714=C2=A0=C2=A0=C2=A0=C2=A0

undo change vector size=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 1.0182E+11=C2=A0 =C2=A0 =C2=A0


Thank you.

<= /div>

=C3=8En vin., 23 apr. 2021 la 11:39, Jonathan Lewis <jlewisoracle@gmail.com> a scris:
I= f you do decide to learn more about how undo works then you should get a co= py of my book "Oracle Core".

When y= ou saw the large numbers of single block reads did you also check the sessi= on (or system) activity statistics=C2=A0 (v$sesstat/v$sysstat) for=20 "transaction tables consistent read rollbacks"=C2=A0 and "tr= ansaction tables consistent reads - undo reacords applied", The former= is the number of times you had to take an undo segment header block into t= he past to check a commit SCN, and the latter is the number of undo records= (which may be far more than physical block reads) that you needed to use t= o get to the correct point in the past.
If the average undo recor= ds per rollback here is large then that would explain why Oracle Support su= ggested setting this parameter.

The suggestion of = 50 to 100 is not a threat. It's when you get to values in the region of= a couple of thousand that increase the risk of ORA-01628.
In fac= t there's an easy guideline for setting this value if you are having th= e problem that Oracle thinks you have.

select * fr= om V$resource_limit where resource_name =3D 'transactions";
<= div>
The maximum_utilization column will tell y= ou the largest number of concurrent transactions that has appeared since da= tabase startup. That's the largest number of rollback segments you woul= d have needed - so a good guideline for that hidden parameter.=C2=A0 (Techn= ically if you also had incoming XA transactions I think you might also need= to consider maximum_utilization for "branches" and add that as w= ell - but that's speculation that I haven't tested.)

=
I wrote a short note about this view recently at=C2=A0 https://jonathanlewis.wordpress.com/2021/03/25/vresource_limit/=


The threat that this fix addre= sses is the case where Oracle has only got a small number of undo segments = online to meet what it sees as a low level of concurrent transactions, and = you suddenly get a sustained burst of much higher concurrency. Generally Or= acle tries to keep one undo segment per transaction and one transaction per= undo segment - but the delay between concurrency going up and more segment= s coming online may be too long, so you end up with lots of transactions pe= r rollback segment at the same time. This has two effects: each undo segmen= t transaction table slot gets overwritten many times, and (b) if the concur= reny level goes over 5 transactions per rollback segment you start "wa= sting" space in undo blocks so you can end up using the undo segment m= uch more rapidly than normal.

I wrote a blog note = a few years ago that describes and models the particular situation that you= may be seeing. The model does some bizarre things that won't look anyt= hing like your production application, but I had to do that to reproduce th= e production symptoms from a very small model: https://j= onathanlewis.wordpress.com/2017/04/21/undo-understood/



Regards
Jonathan Lewis



On Thu, 22 Apr 2021 at 15:22, La= urentiu Oprea <laurentiu.oprea06@gmail.com> wrote:
Thanks a lot Jonathan f= or your anwer. Your answer=C2=A0is actually exactly what I was searching fo= r, because to be honest I don't have a full understanding of the issue = I face.

In short: I have an insert base on select that i= s executed around 150 times per in a window of about 3-4 hours and just one= or 2 executions sometimes are taking a few=C2=A0good hours, although all o= ther executions are under 3 minutes. All the waits are mainly cell single b= locks wait events (when doing a range scan) on UNDO tablespace. I know for = sure data is loaded on the source tables all the time, and is very strange= =C2=A0why one or maximum 2 executions take so much time.=C2=A0
I opened an SR and the recommendation was to set _rollback_seg= ment_count to a value like 50/100. At this point I`m in the process of unde= rstanding=C2=A0the issue from a business perspective because the described = waits are on 3 different=C2=A0indexes sometimes (on 3 different tables - ag= ain I know for sure data is loaded all the time in those tables) and maybe= =C2=A0I can arrange the order of these jobs. Even so the impact of what I b= elieve is a read consistency issue is just too dramatic.

If this is expected behavior and=C2=A0 _rollback_segment_count can mitigate it is clear to me=C2=A0I might need to= learn more about how UNDO works.

Thanks again.

=C3=8En mie., 21 apr. 2021 la 13:40, Jonathan Lewis <jlewisoracle@gmail.com> = a scris:
That's not a good question.

It = doesn't matter what side effects anyone has seen unless you also know w= hat sort of activity their database goes through - so you're asking oth= er people to tell you things about their databases that you should be telli= ng them about your database before you ask the question.

I can think of a couple of possible side effects - but= they may be completely irrelevant to you, and if I don't give you a co= ntext your next question will probably be to ask for an explanation of how = or why the side effects night appear.

a) You waste= buffer cache unnecessarily
b) you increase the risk of ORA-01628= =C2=A0 max # extents (32765) reached for rollback segment


Regards
Jonathan Lewis



On Tue, 20 Apr 2021 at 05:58, Laurentiu Oprea <laurentiu.oprea06= @gmail.com> wrote:
Hello,

DB Version: 12.1

Is someone played around with parameter=C2=A0_rollback= _segment_count ? I`m most interested in what are the side effects of increa= sing this parameter.=C2=A0

Thank you.=C2=A0
<= /div>
--000000000000e9ee6e05c1155a6e-- -- http://www.freelists.org/webpage/oracle-l