Recently I received error on test database:
ORA-01555: snapshot too old: rollback segment number 53 with name "_SYSSMU53_526582059$" too small
simple problem with a simple solution :)
but...
This is a test database and almost nobody use this database. I checked a UNDO utilization and was on very low level.
I changed undo guarantee, undo retention and I added additional datafiles to UNDO tablespace.
Nothing help...
and now the background on how we prepare the test database.
This test database is prepared as I storage snapshot from standby. Before preparing snapshot recovery managed standby had been cancel
recover managed standby database cancel;
and stopped.
So everything should be OK.
I thought that then problem can be with UNDO and I decided to recreate UNDO tablespace. I create new one and switched database to use new UNDO tablespace. After that I dropped old UNDO tablespace and I executed query once again.
and...
the same error but without indicating the UNDO segment.
ORA-01555: snapshot too old: rollback segment number with name "" too small
In original query application use two sub-queries, I wanted to find exactly where is the problem.
In execution plan I found that optimizer for one of the sub-query use index. After eliminating this sub-query everything works :)
BINGO
I tried rebuild this index and once again ORA-01555, so I decided to drop index and create index once again. Successfully...
After that original query works perfectly
ORA-01555: snapshot too old: rollback segment number 53 with name "_SYSSMU53_526582059$" too small
simple problem with a simple solution :)
but...
This is a test database and almost nobody use this database. I checked a UNDO utilization and was on very low level.
I changed undo guarantee, undo retention and I added additional datafiles to UNDO tablespace.
Nothing help...
and now the background on how we prepare the test database.
This test database is prepared as I storage snapshot from standby. Before preparing snapshot recovery managed standby had been cancel
recover managed standby database cancel;
and stopped.
So everything should be OK.
I thought that then problem can be with UNDO and I decided to recreate UNDO tablespace. I create new one and switched database to use new UNDO tablespace. After that I dropped old UNDO tablespace and I executed query once again.
and...
the same error but without indicating the UNDO segment.
ORA-01555: snapshot too old: rollback segment number with name "" too small
In original query application use two sub-queries, I wanted to find exactly where is the problem.
In execution plan I found that optimizer for one of the sub-query use index. After eliminating this sub-query everything works :)
BINGO
I tried rebuild this index and once again ORA-01555, so I decided to drop index and create index once again. Successfully...
After that original query works perfectly
Brak komentarzy:
Prześlij komentarz