| ORA-01555: snapshot too old: rollback segment number with name "" too small [message #546575] |
Wed, 07 March 2012 08:45  |
 |
yavvie
Messages: 6
Registered: March 2012
Location: Prague
|
Junior Member |
|
|
We are getting error "ORA-01555: snapshot too old: rollback segment number with name "" too small" in procedure inserting data from view (connected to remote DB) into local table. There should be approx 10 000 000 rows inserted. At first the procedure ran for about 4 hours (one year ago), now it runs 18 hours and sometimes fails with this error.
Is there any way to
1) check how the rollback segments are set and which are used by this procedure
2) calculate necessary rollback segment
3) modify the rollback segment for this procedure only (if possible)
I am not the DBA so I don't know what is set in the database or where it can be checked, so I'd need scripts that tell me what is there and what I should set and how.
The code in the procedure is as follows:
execute immediate 'alter index TARGET_TABLECREDATE unusable';
delete from TARGET_TABLE;
insert into TARGET_TABLE
select
col1
,col2
,col3
,col4
,col9
from SOURCE_VIEW oi
where oi.change_date between (sysdate - 10) and sysdate
or oi.creation_date between (sysdate - 10) and sysdate;
commit;
execute immediate 'alter index TARGET_TABLECREDATE rebuild';
execute immediate 'analyze table TARGET_TABLE compute statistics';
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-01555: snapshot too old: rollback segment number with name "" too small [message #546593 is a reply to message #546591] |
Wed, 07 March 2012 09:25  |
John Watson
Messages: 8985
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Here's a test routine that will prove to you how much more efficient truncate and diurect load are than delete and conventional insert:conn / as sysdba
drop user jon cascade;
grant dba to jon identified by jon;
set timing on
conn jon/jon
create table t1 as select * from all_objects;
--test using DELETE
conn jon/jon
delete from t1;
select name,value from v$mystat natural join v$statname where statistic# in (178,290);
--test conventional load
conn jon/jon
insert into t1 select * from all_objects;
select name,value from v$mystat natural join v$statname where statistic# in (178,290);
--test using TRUNCATE
conn jon/jon
truncate table t1;
select name,value from v$mystat natural join v$statname where statistic# in (178,290);
--test direct load
connect jon/jon
insert /*+ append */ into t1 select * from all_objects;
select name,value from v$mystat natural join v$statname where statistic# in (178,290);
|
|
|
|
|
|
|
|
| Re: ORA-01555: snapshot too old: rollback segment number with name "" too small [message #546596 is a reply to message #546592] |
Wed, 07 March 2012 09:29 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Roachcoach wrote on Wed, 07 March 2012 15:20Slowness would depend on the relative complexity of the view and the size of the underlying tables surely?
True, I kinda assumed it's a simple view, probably shouldn't
Roachcoach wrote on Wed, 07 March 2012 15:20
Plus if there's mass DML going on at the time that can really slow things down.
Yup, though if it's taking 18 hours a mass of DML becomes a given on most systems.
|
|
|
|
|
|
| Re: ORA-01555: snapshot too old: rollback segment number with name "" too small [message #546599 is a reply to message #546593] |
Wed, 07 March 2012 09:33 |
|
yavvie
Messages: 6
Registered: March 2012
Location: Prague
|
Junior Member |
|
|
John Watson wrote on Wed, 07 March 2012 16:25Here's a test routine that will prove to you how much more efficient truncate and diurect load are than delete and conventional insert:
--test conventional load
conn jon/jon
insert into t1 select * from all_objects;
select name,value from v$mystat natural join v$statname where statistic# in (178,290);
--test direct load
connect jon/jon
insert /*+ append */ into t1 select * from all_objects;
select name,value from v$mystat natural join v$statname where statistic# in (178,290);
Thanks for the code, I'll have to do some reading on this.
I'm not sure what the difference here is, clause /*+ append */ in the select statement? Isn't that commented out?
Looking at the original procedure this part of code is in the insert statement, I just didn't write it here since I didn't know what it does, so my bad.
[Updated on: Wed, 07 March 2012 09:36] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: ORA-01555: snapshot too old: rollback segment number with name "" too small [message #546606 is a reply to message #546603] |
Wed, 07 March 2012 10:03 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
18 hours isn't a 'bad' duration to get an ora-01555.
It's caused by Oracle trying to get a read consistent view of a changed block (i.e. someone altered a record after your query started and the DB no longer has the information to work out what it was at the time the query started).
I'd be surprised if they are not using auto undo management and as mentioned 18 hours is a pretty good length to run for. Basically the DBA is unlikely to offer much assistance in that particular area.
I'd start by looking at that view and working out if you can apply the data in a more efficient way. Many a time I've seen slow queries accessing a view and it turns out the view is only accessed for convenience/laziness - if you only need a few tables worth of data, going through a view hitting a dozen is usually inefficient.
If this is a 'batch' style job and the server has capacity, it may be advantageous to look at parallel execution (if available) but use this with care, it is not a silver bullet.
Finally, what might be more a productive conversation with them, is to ask the DBAs what data replication options are available to your environment, perhaps there is a better way?
To be honest though, your quickest gain will be binning the delete and using a truncate.
PS: Use dbms_stats.gather_table_stats for the stats job
PPS: All of this assumes you are on 10g as mentioned.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
