Re: ORA-1555 - 12.2.0.1 Exa

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 30 Jan 2019 08:53:17 +0000
Message-ID: <CWXP265MB05034C03B8AE53A6ABF41970A5900_at_CWXP265MB0503.GBRP265.PROD.OUTLOOK.COM>

Jack,

The numbers for "rollback changes - undo records applied" are horrendous; do you expect the application to do lots of deliberate rolling back. The "transaction tables consistent reads" figures are also very large - that's a lot of work that Oracle's doing to find "upper bound" commit SCNs

Can you narrow down the time intervals, an average over three hours can be a bit misleading - if you look at the three separete hours (or shorted intervals if available) are they consistent with each other or do you spikes ?

The other stats that can add value to these figures are:

user rollbacks
transaction rollbacks
transaction tables consistent read rollbacks

The last one might tell us whether you're doing a small number of very large transaction table rollbacks or a large number of very small ones The transaction rollbacks might tell us something similar about "real" rollbacks, and a comparison of the user and transaction rollbacks might tell us whether your transaction rollbacks are invoked by users or invoked internally

It's possible that the high volume of rolling back is finding an edge case that results in undo being lost prematurelyI' m trying to work out a scenario where delayed block cleanout can result this happening with unexpired steal count being zero - but I haven't got there yet though I think that that's what's driving this problem; in particular Oracle sampling when gathering index stats and leaving index leaf blocks that need delayed block cleanout in place from a long time back - and then your query hitting such a leaf block. (The problem is less likely to be a table block, since the stat collection on tables is - by default - 100%)

Regards
Jonathan Lewis

P.S. Based on the last 24 hours this post may not get to Oracle-L

---

From: Jack van Zanen <jack_at_vanzanen.com> Sent: 30 January 2019 04:15
To: Daniel Fink; Jonathan Lewis
Cc: oracle-l_at_freelists.org
Subject: Re: ORA-1555 - 12.2.0.1 Exa

_at_Jonathan Lewis<mailto:jonathan_at_jlcomp.demon.co.uk>

1. Do the undo stats report a non-zero "unexpired steal count" Count is Zero for the period where we had 2 Ora-01555
2. What do the "undo records applied" system statistics suggest?

"Data blocks consistent reads - undo records applied 51,547,216/total 4,105.23/s"
"rollback changes - undo records applied 189,571,880/Total 15,097.54/s"
"transaction tables consistent reads - undo records applied 18,953,990/Total 1,509.50/s"

Jack van Zanen

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

Received on Wed Jan 30 2019 - 09:53:17 CET