Re: Strange undo behavior

On Oct 17, 1:48 pm, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> hardball wrote:
> >> How do you explain the used space in the undo tablespace going from < 10
> >>> meg to almost 10 gig in a space of two hours where v$undostat shows that
> >> < 50m of undo blocks have been written?
>
> > Because you left the datafile(s) in the undo tablespace of interest set
> > to autoextend?

> There are two datafiles in the tablespace. Both have autoextend on,
> maxsize of 5g, and have been at the max of 5g for probably over a year.
>
> How does that explain a growth in undo extents from 10m to 9.5g when
> only 38m have been written according to v$undostats.

If the datafiles had been created smaller and not set to autoextend, they would not have grown so large. I'm explaining the symptom, not the cause.

You're still running 9.2.0.5. There exist known issues with undo extent management in 9.2 which may or may not be fixed in a subsequent patchset.
I don't know, as I don't maintain any 9.2.x databases, just 10.1 and 10.2 databases.
The fewer releases one has to support, the less spread out one's knowledge might be ...

You could research this behavior with the hypothesis that its a bug and might even find a doc referring to the behavior being "fixed in 10.2". You could then determine if a patch has been backported to your release on your platform. If not, you could request a backport.

You could clone the existing database, apply the latest patchset and/or patches (such as 9.2.0.8 if that is out) and retest. Considering that this is likely an extremely difficult problem to reproduce - you might not be able to verify that the condition does or does not occur on the newly patched clone.

How about you open a service request with Oracle Support and see what they come up with.

Again, the datafiles would not have grown, had they not been allowed to.
Don't let them.

-bdbafh

> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.5 (MingW32)
>
> iEYEARECAAYFAkU1F3MACgkQzIf+rZpn0oRw7ACePobVhA0TbabuqI5OYy5YJvVr
> dNsAoIQt2cc2kyaxiV8IajFEouFJX6Zm
> =En0n
> -----END PGP SIGNATURE-----
Received on Tue Oct 17 2006 - 13:40:35 CDT