Possible bug using auto undo_management

Hi list members,

Long time :)

I have a piece of sql that aggregates 1.3 billion rows of one table every month, it reads these rows of the table and joins it to the contents of three global temp tables that have only thousands of rows.

After reading the rows that take about 10 minutes, it used to take six hours of sort/group by/calculation time and completes successfully.

This sql used to run without any problems until the last month.

Suddenly it continues to run for five days where I kill it.

I tried it many times and killed it every time after four to five days.

The execution plan did not change, it the same:

1. read 1.1 billion rows of the big table.
2. hash join it to the temp tables.
3. Group by/Sort.

The only change that happened to the database is changing undo_management to auto (retention = 30000).

I ran it for one more time and watched it carefully:

I found that after the sql read the rows and started the calculations, and kept running for a day,
It's almost like restarting over again and again, after a day I was able to see "direct read from the data files of the big table" And when it's done, it starts the calculation again.

After five days the total number of rows that got read becomes negative number.

To me this looked like a bug in the undo management, that is causing the sql for some reason to restart the execution without giving any errors.

I changed the undo_retention to 50000 (it was 30000), and the sql ran successfully in six hours.

Did you guys hear about any similar issues?

I'm planning to open a TAR (I hate TARs)!!

Thanks

Waleed

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