Re: _rollback_segment_count side effects

From: GG <grzegorzof_at_interia.pl>
Date: Wed, 21 Apr 2021 15:58:21 +0200
Message-ID: <54b04522-edec-720a-660e-af17af40704f_at_interia.pl>

Appologize for kind of takeover but how I'm not seeing this ORA-01628 having below stats (also db 12.1):

SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024/1024 GB, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS
   2  /

Status            GB   COUNT(*) --------- ---------- ----------
UNEXPIRED 1601.99518     306973
EXPIRED   .745910645       2169
ACTIVE    3.40631104        112 SQL>
SQL> SELECT MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(EXPSTEALCNT) FROM V$UNDOSTAT; MAX(TUNED_UNDORETENTION) MAX(MAXQUERYLEN) MAX(NOSPACEERRCNT) MAX(EXPSTEALCNT)

------------------------ ---------------- ------------------ 
----------------

                   863546            61543 0               26

SQL> SELECT BEGIN_TIME, END_TIME, TUNED_UNDORETENTION, MAXQUERYLEN, MAXQUERYID, NOSPACEERRCNT, EXPSTEALCNT, UNDOBLKS, TXNCOUNT FROM V$UNDOSTAT; BEGIN_TIME          Date                TUNED_UNDORETENTION MAXQUERYLEN MAXQUERYID    NOSPACEERRCNT EXPSTEALCNT   UNDOBLKS TXNCOUNT

------------------- ------------------- ------------------- ----------- 

------------- ------------- ----------- ---------- ----------

2021-04-21 09:37:14 2021-04-21 09:46:11              863546 2418 
fcfpwturkq7dm             0          13     153064     193322
2021-04-21 09:27:14 2021-04-21 09:37:14              863062 1813 
fcfpwturkq7dm             0          15     137362     690064

Regards.
Greg

W dniu 2021-04-21 o 12:40, Jonathan Lewis pisze:
> That's not a good question.
>
> It doesn't matter what side effects anyone has seen unless you also
> know what sort of activity their database goes through - so you're
> asking other people to tell you things about their databases that you
> should be telling 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
> 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  max # extents (32765) reached
> for rollback segment
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Tue, 20 Apr 2021 at 05:58, Laurentiu Oprea
> <laurentiu.oprea06_at_gmail.com <mailto:laurentiu.oprea06_at_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 parameter.
>
> Thank you.
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 21 2021 - 15:58:21 CEST