There is a bug with oracle 9i and materialized views whereby there are numerous snapshot too old errors. This bug is around the source database whereby the optimizer is giving an incorrect execution plan, when the parameter star_transformation_enabled is set to TRUE.
This bug is fixed in oracle 10.0. The workaround is to change the parameter to temp_disable.
Showing posts with label UNDO. Show all posts
Showing posts with label UNDO. Show all posts
Tuesday, March 20, 2007
Wednesday, March 14, 2007
undo created at db startup
It appears undo at database startup is dependent on
- transactions_per_rollback_segment
- max_sessions
oracle (9.2.0 at least) uses the following formula to work out how many undo segments it uses when the undo tablespace is created.
max_rollback_segments = (max_sessions * 1.1) / transactions_per_rollback_segment
oracle will always have at least this number. We had the problem that max_sessions was changed and the undo tablespace was not recreated. Consequently the database was only ever creating what the old session level was set at.
We were also getting some weird errors around ORA-1555, snapshot too old errors when we kick a number of jobs off at once and they fail immediately with ORA-1555 errors.
We are in the process of making this change to production so time will tell !!!
- transactions_per_rollback_segment
- max_sessions
oracle (9.2.0 at least) uses the following formula to work out how many undo segments it uses when the undo tablespace is created.
max_rollback_segments = (max_sessions * 1.1) / transactions_per_rollback_segment
oracle will always have at least this number. We had the problem that max_sessions was changed and the undo tablespace was not recreated. Consequently the database was only ever creating what the old session level was set at.
We were also getting some weird errors around ORA-1555, snapshot too old errors when we kick a number of jobs off at once and they fail immediately with ORA-1555 errors.
We are in the process of making this change to production so time will tell !!!
UNDO SQL
Below is some sql I used to get an idea about how undo is being managed in an oracle database.
Firstly some simple stuff ...
Look at the segments in the UNDO tablespace.
select segment_name, sum(bytes)/(1024*1024)
from dba_extents
where tablespace_name = 'tablespace_name'
group by segment_name
order by segment_name;
LOOK at current rollback segments, by status, either ONLINE or OFFLINE.
select status, count(*) from dba_rollback_segs
where owner = 'PUBLIC' group by status;
MERGE the above two statements together ... This can take a minute or so to run, but is quite useful as it shows the total number of segments that can be used. Bearing in mind that the database will try and add some more.
select a.segment_name, b.status, sum(a.bytes)/(1024*1024)
from dba_extents a, dba_rollback_segs b
where a.segment_name = b.segment_name AND b.owner = 'PUBLIC'
group by a.segment_name, b.status;
Firstly some simple stuff ...
Look at the segments in the UNDO tablespace.
select segment_name, sum(bytes)/(1024*1024)
from dba_extents
where tablespace_name = 'tablespace_name'
group by segment_name
order by segment_name;
LOOK at current rollback segments, by status, either ONLINE or OFFLINE.
select status, count(*) from dba_rollback_segs
where owner = 'PUBLIC' group by status;
MERGE the above two statements together ... This can take a minute or so to run, but is quite useful as it shows the total number of segments that can be used. Bearing in mind that the database will try and add some more.
select a.segment_name, b.status, sum(a.bytes)/(1024*1024)
from dba_extents a, dba_rollback_segs b
where a.segment_name = b.segment_name AND b.owner = 'PUBLIC'
group by a.segment_name, b.status;
Tuesday, March 13, 2007
UNDO, Some Discussion
OK, below are issues relating to the undo problems with oracle 9.2.0.3.
Seems as though there are a few bugs. The problem we seem to have is when we do either materialized view refreshes and truncate table type commands.
The problem seems to be that oracle can not create enough undo segments. When running these again, often straight after the failure it works. This generally seems to be the case.
The way undo seems to normally work is that it creates segments when required. There are particular rules around when it grabs new extents, how much space it grabs etc. The smon process is responsible for dropping undo segments that are not required any more. It generally does this every 12 hours. There is an event to stop this from happenning.
Seems as though there are a few bugs. The problem we seem to have is when we do either materialized view refreshes and truncate table type commands.
The problem seems to be that oracle can not create enough undo segments. When running these again, often straight after the failure it works. This generally seems to be the case.
The way undo seems to normally work is that it creates segments when required. There are particular rules around when it grabs new extents, how much space it grabs etc. The smon process is responsible for dropping undo segments that are not required any more. It generally does this every 12 hours. There is an event to stop this from happenning.
Subscribe to:
Posts (Atom)
