Re: Negative numbers in undo advisor

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Thu, 26 Oct 2006 17:39:24 GMT
Message-ID: <gp60h.8747$bb.4188@trnddc03>

joel garry wrote:
> Chuck wrote:

>> Anyone else seen this or have a fix for it? In the 10g Grid Control
>> (10.2.0.1) when connecting to a 9.2 database, I am seeing negative
>> numbers in the undo advisor. Click link below if you want to see the
>> actual image.
>>
>> http://mysite.verizon.net/hampal/charts.gif
>>
>> Also tried to use the 9i EM console and the undo advisor there is not
>> even showing the "maximum undo rate" line, presumably because it too is
>> trying to display a negative # but the 9i undo advisor doesn't go below 0.

>
> Are you in archivelog mode? I've noticed my test db which is in
> noarchivelog mode doesn't show the maximum or current lines, while my
> prod db does. The testdb shows about -200M /minute for maximum rate
> (I've been testing some ETL in it), retention set for 3 hours. I'm
> using 9i EM standalone.
>
> jg
> --
> @home.com is bogus.
> http://www.monkeychops.karoo.net/mistertweakathon.html
>

I'm seeing it happen in some archivelog and some noarchivelog databases, but not on all databases. Very strange. Never saw this with the 10.1 version of OEM. Is there a bug? Maybe soemthing is using a signed int intead of an unsigned int? My max undo generation is about 2.9g in 10 min. but that is a *very* short burst. Over the entire 4 hr undo retention period it's maybe 9g.

SQL> select u.undoblks * t.BLOCK_SIZE / 1048576 megabtyes_per_10_minutes from dba_tablespaces t,
( select max(undoblks) undoblks from v$undostat) u where t.tablespace_name = 'UNDO1'

MEGABTYES_PER_10_MINUTES

---

              2896.89063

1 row selected. Received on Thu Oct 26 2006 - 12:39:24 CDT