Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Creating snapshots to avoid ORA-01555?
larionov_at_yahoo.com wrote:
> The worst thing about ORA-01555 is that for some reason it takes
> quite a few minutes for Oracle to "decide" that "snapshot too old".
> During this "a few" minutes the entire server becomes very slow.
> Is it a known effect of ORA-01555?
This happens because the ORA-01555 isn't returned until the query comes upon a data block that cannot be reconstructed from the rollback segments. If the server is becoming slow for this period, it probably means that the long-running query is rather poorly optimized SQL; or it could point to other problems with your setup.
> By the way, can it help if I forse all the small inserts
> to use one and the same rollback seg?
It might. You must make certain that the rollback segment is large enough that the small insert transactions will not cause any extents of the rollback segment to be overwritten! This can be quite difficult.
One option which you may want to pursue: you could cause the long-running query to start a transaction assigned to the same rollback segment, before the small inserts begin. Since the transaction isn't complete, the rollback information for that transaction is /active/ -- Oracle will not overwrite it. What I have in mind is something like:
create table sticky (col1 char);
to create the table that you will be using to keep the rollback segment from wrapping)
set transaction use rollback segment big1;
insert into sticky values ('A');
/* DO NOT COMMIT! */ select /* ...long running query(-ies) go here! */ yadda, yadda, yadda from table_name;
commit; /* after all queries are complete! */
If you ensure that all update activity on the selected tables is performed on rollback segment big1, you should not get an ORA-01555 because of the traditional "inactive, in-use rollback data" problem. If you are doing a fetch across commits, you may still get the ORA-01555.
-bn Received on Wed May 20 1998 - 13:02:49 CDT
![]() |
![]() |