ORA-01555: snapshot too old Error
============================
Consider following error in alert log:
ORA-12012: error on auto execute of job 4593
ORA-12048: error encountered while refreshing materialized view "USER_A"."MVIEW_A"
ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4_1209447629$" too small
ORA-02063: preceding line from MASTER_DB7
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at "USER_A.REFRESH_PKG", line 17
ORA-06512: at line 1
In line 17 of package USER_A.REFRESH_PKG there is:
DBMS_REFRESH.REFRESH('MY_GROUP');
From Tom Kyte:
An ORA-1555 is NOT about running out of rollback.
It is about rollback that was generated being overwritten.
A select statement will not cause rollback to be "held".
It is about rollback that was generated being overwritten.
A select statement will not cause rollback to be "held".
As soon as the transaction that generated the rollback commits - that rollback may be
reused and if it is and it is needed by some query, you will get an ORA-1555.
The way to avoid ORA-1555 is to size your rollback segments adequately
The ORA-1555 happens when people try to save space.
They will have small rollback segments that could grow if they needed, and will shrink using OPTIMAL.
So rollback segments would start with say 10Mb or so.
These rollback segments COULD grow to 100Mb each if we let them, however in a system with small often committed transactions, they will NEVER grow unless you get a big transaction.
Now, someone needs to run a query that will take 5 minutes.
On the system however, the rollback wraps every 2 minutes due to lots of little transactions going on.
In this system, ORA-1555's would happen frequently.
What you need to do here is size rollback so that it wraps less frequently.
Rollback Segment (RBS) allocation
RBS extents are allocated in a round robin manner.
When there is a request for more space, Oracle would do one of two things:
A. Go to the next extent in the ring, only if the next extent has no active transactions.
B. Allocate a new extent and insert it into the RBS ring at this point.
============================
Rollback Segment Management.
============================
Manual Undo Management - for 8i
============================
Manual Undo Management - for 8i
The way before Oracle 9G
The undo space is managed through rollback segments, and no undo tablespace is used.
The undo space is managed through rollback segments, and no undo tablespace is used.
Increase the size for UNDO in Oracle manual mode.
A. Modify the Rollback Extends, so that they allocate 100Mb when extents are created.
B. Increase the number of Rollback Extends.
B. Increase the number of Rollback Extends.
Automatic Undo Management
Introduced in Oracle 9, default from Oracle 11.
The undo space is managed in an undo tablespace.
UNDO_MANAGEMENT Parameter
Parameter UNDO_MANAGEMENT determines the behavior of Undo Management.
Prior to Oracle 11, the default was manual undo management mode.
To change to automatic undo management, you must first create an undo tablespace and then change the UNDO_MANAGEMENT initialization parameter to AUTO.
A null UNDO_MANAGEMENT initialization parameter defaults to automatic undo management mode in Release 11g and later, but defaults to manual undo management mode in earlier releases.
UNDO Tablespace
An auto-extending undo tablespace named UNDOTBS1 is automatically created when you create the database with Database Configuration Assistant (DBCA).
UNDO TABLESPACE can also be explicitly created, as part of the CREATE DATABASE command.
CREATE DATABASE rbdb1
CONTROLFILE REUSE
.
.
.
UNDO TABLESPACE undotbs_01 DATAFILE '/u01/oracle/rbdb1/undo0101.dbf';
Optionally create additional UNDO Tablespace:
CREATE UNDO TABLESPACE undotbs_02 DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 2M REUSE AUTOEXTEND ON;
Multiple UNDO Tablespaces
You can create multiple undo tablespaces, but only one of them can be active at any one time.
If no undo tablespace is available, the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace. This is not recommended, and an alert message is written to the alert log file.
If the database contains multiple undo tablespaces, you can optionally specify at startup that you want to use a specific undo tablespace.
This is done by setting the UNDO_TABLESPACE initialization parameter,
UNDO_TABLESPACE = undotbs_01
To switch to another undo tablespace:
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
The old undo tablespace enters into a PENDING OFFLINE mode (status).
In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.
Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode.
ALTER SYSTEM SET UNDO_TABLESPACE = '';
This command causes so that the current undo tablespace is switched out and the next available undo tablespace is switched in.
Use this statement with care because there may be no undo tablespace available.
Resource Management
By default users are allowed unlimited undo space.
Use Resource Manager UNDO_POOL directive to limit resource usage.
Increase the size for UNDO in Oracle 11
A. Increase the size of UNDO Tablespace.
ALTER DATABASE DATAFILE '/path/to/data/file/undo.dbf' AUTOEXTEND ON MAXSIZE 2000M;
ALTER DATABASE DATAFILE '/path/to/data/file/undo.dbf' RESIZE 2000M;
or, set to unlimited:
ALTER DATABASE DATAFILE '/path/to/data/file/undo.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;
C. Increase UNDO_RETENTION above the default value of 900 (sec)
============================
View current settings
============================
SELECT name, value
FROM V$PARAMETER WHERE name like '%undo%'
NAME VALUE
------------------------------ ------------------------------
undo_management AUTO
undo_tablespace UNDOTBS
undo_retention 3600
undo_retention - is in seconds.
============================
SELECT name, value
FROM V$PARAMETER WHERE name like '%undo%'
NAME VALUE
------------------------------ ------------------------------
undo_management AUTO
undo_tablespace UNDOTBS
undo_retention 3600
undo_retention - is in seconds.
============================
Monitor UNDO Usage
============================
Monitor UNDO Usage
============================
V$UNDOSTAT Table
This table got entries every 10 minutes, and data going back for 7 days.
It got some useful columns:
Represents the total number of undo blocks consumed.
This can be used to estimate the size of the undo tablespace needed to handle the workload on your system. (UNDOBLKS*8192)
MAXQUERYLEN
Identifies the length of the longest query (in seconds) executed in the instance during the period.
This can be used to estimate the proper setting of the UNDO_RETENTION initialization parameter.
sql_id f the longest running SQL statement in the period.
The number of times the error ORA-01555 occurred.
Increasing the value of UNDO_RETENTION can reduce the occurrence of this error.
NOSPACEERRCNT
The number of times space was requested in the undo tablespace and there was no free space available.
The corrective action is to add more space to the undo tablespace.
============================
Who is using UNDO Tablespace
============================
Who is using UNDO Tablespace
============================
Option A - see V$SESSTAT and V$STATNAME
SELECT SESSIONS.sid,
SESSIONS.username,
SESSIONS.machine,
SESSIONS.program,
ROUND(SUM(SESSTAT.value) / 1024 / 1024) AS UNDO_SIZE_MB
FROM V$SESSTAT SESSTAT,
V$SESSION SESSIONS,
V$STATNAME STATNAME
WHERE SESSIONS.sid = SESSTAT.sid
AND STATNAME.statistic# = SESSTAT.statistic#
AND STATNAME.name = 'undo change vector size'
AND SESSIONS.type <> 'BACKGROUND'
AND SESSIONS.username IS NOT NULL
GROUP BY SESSIONS.sid, SESSIONS.username, SESSIONS.machine,SESSIONS.program
HAVING ROUND(SUM(SESSTAT.value) / 1024 / 1024) > 100
ORDER BY ROUND(SUM(SESSTAT.value) / 1024 / 1024) DESC;
SID USERNAME MACHINE PROGRAM UNDO_SIZE_MB
----- ---------------- -------------------------- ----------------- ---------------
498 LAB_QANFV_ALLQQ ipn-08-cajste.starhome.com JDBC Thin Client 627
197 LAB_QANFV_ALLQQ spx-02-di7ykx.starhome.com JDBC Thin Client 301
418 LAB_QANFV_ALLQQ spx-02-di7ykx.starhome.com JDBC Thin Client 246
881 LAB_QANFV_ALLQQ spx-02-di7ykx.starhome.com JDBC Thin Client 240
1086 LAB_QANFV_ALLQQ spx-03-j39pv7.starhome.com JDBC Thin Client 237
1169 LAB_QANFV_ALLQQ ipn-01-esj736.starhome.com JDBC Thin Client 227
223 LAB_QANFV_ALLQQ ipn-01-esj736.starhome.com JDBC Thin Client 222
1268 LAB_QANFV_ALLQQ spx-02-di7ykx.starhome.com JDBC Thin Client 187
1366 LAB_QANFV_ALLQQ spx-02-di7ykx.starhome.com JDBC Thin Client 179
986 LAB_QANFV_ALLQQ spx-02-di7ykx.starhome.com JDBC Thin Client 178
Option B - see V$UNDOSTAT
SELECT V$SQL.sql_text,
UNDOSTAT.maxqueryid,
SUM(undoblks)
FROM V$UNDOSTAT UNDOSTAT,
V$SQL
WHERE maxqueryid IS NOT NULL
AND UNDOSTAT.maxqueryid = V$SQL.sql_id
GROUP BY UNDOSTAT.maxqueryid, V$SQL.sql_text
ORDER BY SUM(undoblks) DESC;
How to size the UNDO Tablespace
SELECT ROUND(((UR * UPS * DBS) + (DBS * 24))/1024/1024) AS "MBytes"
FROM (SELECT value AS UR FROM V$PARAMETER WHERE name = 'undo_retention'),
(SELECT (SUM(UNDOBLKS)/SUM(((end_time - begin_time)*86400))) AS UPS
FROM V$UNDOSTAT),
(SELECT block_size as DBS
FROM DBA_TABLESPACES WHERE tablespace_name =
(SELECT value FROM V$PARAMETER WHERE name = 'undo_tablespace'));
Reference: Sizing your undo tablespace
No comments:
Post a Comment