problem with UNDO

[Hewlett]

Hi,
I got ORA-01555 when I was trying to run the following query:
select * from (SELECT * FROM dba_role_privs as of TIMESTAMP
to_timestamp('22-DEC-2005:16:07:00','DD-MON-YY HH24: MI: SS')) where
GRANTEE='FCC'
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 16 with name
"_SYSSMU16$"
too small

So I check rollback segemnts:
SQL> select rb.segment_name, rb.status, rb.tablespace_name, sg.bytes
from dba_rollback_segs rb , dba_segments sg where
rb.segment_name=sg.segment_name;

SEGMENT_NAME STATUS TABLESPACE_NAME
BYTES
------------------------------ ----------------
------------------------------ ----------
SYSTEM ONLINE SYSTEM
393216
_SYSSMU1$ ONLINE UNDOTBS1
284745728
_SYSSMU2$ ONLINE UNDOTBS1
99803136
_SYSSMU3$ ONLINE UNDOTBS1
126017536
_SYSSMU4$ ONLINE UNDOTBS1
108126208
_SYSSMU5$ ONLINE UNDOTBS1
133292032
_SYSSMU6$ ONLINE UNDOTBS1
261218304
_SYSSMU7$ ONLINE UNDOTBS1
113434624
_SYSSMU8$ ONLINE UNDOTBS1
89317376
_SYSSMU9$ ONLINE UNDOTBS1
156360704
_SYSSMU10$ ONLINE UNDOTBS1
153280512

SEGMENT_NAME STATUS TABLESPACE_NAME
BYTES
------------------------------ ----------------
------------------------------ ----------
_SYSSMU11$ OFFLINE UNDOTBS1
75685888
_SYSSMU12$ OFFLINE UNDOTBS1
141549568
_SYSSMU13$ OFFLINE UNDOTBS1
131194880
_SYSSMU14$ OFFLINE UNDOTBS1
98689024
_SYSSMU15$ OFFLINE UNDOTBS1
122880
_SYSSMU16$ OFFLINE UNDOTBS1
122880
_SYSSMU17$ OFFLINE UNDOTBS1
122880
_SYSSMU18$ OFFLINE UNDOTBS1
122880
_SYSSMU19$ OFFLINE UNDOTBS1
122880
_SYSSMU20$ OFFLINE UNDOTBS1
122880
_SYSSMU21$ OFFLINE UNDOTBS1
122880

SEGMENT_NAME STATUS TABLESPACE_NAME
BYTES
------------------------------ ----------------
------------------------------ ----------
_SYSSMU22$ OFFLINE UNDOTBS1
122880
_SYSSMU23$ OFFLINE UNDOTBS1
122880
_SYSSMU24$ OFFLINE UNDOTBS1
122880
_SYSSMU25$ OFFLINE UNDOTBS1
122880
_SYSSMU26$ OFFLINE UNDOTBS1
122880

Then I specified a large rollback segement:
SQL> SET TRANSACTION USE ROLLBACK SEGMENT _SYSSMU6$;
SET TRANSACTION USE ROLLBACK SEGMENT _SYSSMU6$
*
ERROR at line 1:
ORA-00911: invalid character

SQL> SET TRANSACTION USE ROLLBACK SEGMENT '_SYSSMU6$'
*
ERROR at line 1:
ORA-02245: invalid ROLLBACK SEGMENT name.

-------------------------------------------------------------------------------------
Question: how or is it possible to choose a larger rollback segment for
a query when Automatic Undo Management is used?

Could anybody help?

Thank you in advance,

Hewlett

[ExpertDba]

Hi,
""" You have absolutely no control over the RBSes in "AUM"."""

Don't know the version of rdbms you are using.
Assuming that you are using 10g:

"Although you use automatic undo retention tuning, you will encounter
1555 because the undo tbs is too small. In which case you need the undo
advisor to compute the right size that depends on the workload. "

Check the following link that details how the above snapshot scenario
happens:
http://www.oracle.com/technology/obe/obe10gdb/manage/undoadv/undoadv.htm

Increase your undo tbspace size!

regards,
Channesh

[Margaret Ruiz De Jesus]

Channesh is correct for the most part. 

 

If you look at the actual error in the alert log you will see how long the query was running when you got the 1555 error.  If this is less than your undo retention setting then your undo tablespace may be too small for the load placed on it at the time of the error.  If it is larger than your undo setting then the undo setting is too small.

 

If it is less than the undo retention setting check to see if  undo segments were stolen at the time of the error. (See v$undostat.)  If so, the undo tablespace is too small.  Increase the undo tablespace.  If not, this should be reported to Oracle as a bug.  There have been a small number of bugs against aum since its inception.

 

To correctly calculate the the size of the undo you must poll the average number of undo bytes consumed throughout a typical business cycle.  I generally advised clients to do this about evey 15 minutes and stuff the results into a table and then take the average and max values.  Ideally you want to be able to accommodate the max but it is not always possible.  You MUST accommodate the average.

 

From there you must multiply the number of bytes consumed per second by your undo retention setting.  It is not unusual to see values ranging from 1 - 2 gig up through several hundred depending on your peak load.  Obviously most folks don't have several hundred to allocate to undo tablespaces so you must set the maximum you can afford and either live with an occaisional ORA-1555 or balance your load.

 

There are several good articles, one of which has an actual query you can poll with, out on metalink.  Search on "size undo tablespace calculate" or log a tar with the RDBMS group and one of them can walk you through it.

 

Hope this helps.

 

MArgaret

 

 

> SQL> select rb.segment_name, rb.status , rb.tablespace_name, sg.bytes

--
==========================================================

Look to the future, because that's where you'll spend the rest of your life.

            - George Burns (1896-1996)

==========================================================

[Hewlett]

Channesh,
Thank you for referring me the link. The undo tablespace configured for
the instance is 3G, which is already very big.

Hewlett

[Hewlett]

Hi MArgaret,
Thank you for the analytical discourse.
Following is the alert log for the query:
---------------
ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN:
0x0001.450fb773):
Mon Dec 26 17:20:43 2005

select * from (SELECT * FROM dba_role_privs as of TIMESTAMP

to_timestamp('23-DEC-2005:01:00:00','DD-MON-YY HH24: MI: SS')) where
GRANTEE='FCC'
--------------

as shown above, the query is returned immediately with ORA-01555. I ran
the query run with reduced flashback period and compared v$UNDOSTAT
output:

-----------------------------------------------------

select * from (SELECT * FROM dba_role_privs as of TIMESTAMP

to_timestamp('23-DEC-2005:3:00:00','DD-MON-YY HH24: MI: SS')) where
GRANTEE='FCC';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
FCC DBA NO YES
FCC CONNECT NO YES
FCC RESOURCE YES YES
FCC AQ_USER_ROLE NO YES
FCC EXP_FULL_DATABASE NO YES
FCC IMP_FULL_DATABASE NO YES
FCC AQ_ADMINISTRATOR_ROLE NO YES

7 rows selected.

SQL> select TO_CHAR(Begin_Time,'DD-MON-YYYY HH24:MI:SS') "Begin Time",
TO_CHAR(End_Time,'DD-MON-YYYY HH24:MI:SS') "End Time",Undoblks,
Txncount, Maxquerylen, Maxconcurrency, Ssolderrcnt, Nospaceerrcnt
from V$UNDOSTAT WHERE ROWNUM <= 10;

Begin Time End Time UNDOBLKS TXNCOUNT
MAXQUERYLEN MAXCONCURRENCY SSOLDERRCNT NOSPACEERRCNT
-------------------- -------------------- ---------- ----------
----------- -------------- ----------- -------------
26-DEC-2005 17:55:14 26-DEC-2005 17:56:15 3 1973658
3 1 0 0
26-DEC-2005 17:45:14 26-DEC-2005 17:55:14 20 1973580
4 1 0 0
26-DEC-2005 17:35:14 26-DEC-2005 17:45:14 21 1972698
3 1 0 0
26-DEC-2005 17:25:14 26-DEC-2005 17:35:14 18 1971769
4 1 0 0
26-DEC-2005 17:15:14 26-DEC-2005 17:25:14 21 1970932
5 1 2 0
26-DEC-2005 17:05:14 26-DEC-2005 17:15:14 18 1969999
3 1 0 0
26-DEC-2005 16:55:14 26-DEC-2005 17:05:14 16 1969182
3 0 2 0
26-DEC-2005 16:45:14 26-DEC-2005 16:55:14 1112 1968299
111 1 0 0
26-DEC-2005 16:35:14 26-DEC-2005 16:45:14 1836 1966702
36 1 0 0
26-DEC-2005 16:25:14 26-DEC-2005 16:35:14 20 1964808
8 1 0 0

10 rows selected.
SQL> select * from (SELECT * FROM dba_role_privs as of TIMESTAMP
to_timestamp('23-DEC-2005:2:00:00','DD-MON-YY HH24: MI: SS')) where
GRANTEE='FCC';

select * from (SELECT * FROM dba_role_privs as of TIMESTAMP

to_timestamp('23-DEC-2005:2:00:00','DD-MON-YY HH24: MI: SS')) where

GRANTEE='FCC'
*
ERROR at line 1:

ORA-01555: snapshot too old: rollback segment number 10 with name
"_SYSSMU10$" too small

SQL> select TO_CHAR(Begin_Time,'DD-MON-YYYY HH24:MI:SS') "Begin Time",
TO_CHAR(End_Time,'DD-MON-YYYY HH24:MI:SS') "End Time",Undoblks,
Txncount, Maxquerylen, Maxconcurrency, Ssolderrcnt, Nospaceerrcnt
from V$UNDOSTAT WHERE ROWNUM <= 10;

Begin Time End Time UNDOBLKS TXNCOUNT
MAXQUERYLEN MAXCONCURRENCY SSOLDERRCNT NOSPACEERRCNT
-------------------- -------------------- ---------- ----------
----------- -------------- ----------- -------------
26-DEC-2005 17:55:14 26-DEC-2005 17:57:05 3 1973721
3 0 1 0
26-DEC-2005 17:45:14 26-DEC-2005 17:55:14 20 1973580
4 1 0 0
26-DEC-2005 17:35:14 26-DEC-2005 17:45:14 21 1972698
3 1 0 0
26-DEC-2005 17:25:14 26-DEC-2005 17:35:14 18 1971769
4 1 0 0
26-DEC-2005 17:15:14 26-DEC-2005 17:25:14 21 1970932
5 1 2 0
26-DEC-2005 17:05:14 26-DEC-2005 17:15:14 18 1969999
3 1 0 0
26-DEC-2005 16:55:14 26-DEC-2005 17:05:14 16 1969182
3 0 2 0
26-DEC-2005 16:45:14 26-DEC-2005 16:55:14 1112 1968299
111 1 0 0
26-DEC-2005 16:35:14 26-DEC-2005 16:45:14 1836 1966702
36 1 0 0
26-DEC-2005 16:25:14 26-DEC-2005 16:35:14 20 1964808
8 1 0 0

10 rows selected.
----------------------------------------

The result seemed to suggest only maximum of 3 undo blocks are needed
for the query, which reach back to 3:00AM (cover 1 business day). but
when the flashback point is extended to '23-DEC-2005:02:00:00', the
ORA-01555 error comes out again. The undo tablespace is 3G with a
retention setting of 270000.

Is there a way to konw before excution how many undo blocks a query
will use?

Hewlett

[fitzj...@cox.net]

No, and Margaret has given you all of the information she possibly
could since you haven't provided any Oracle version information for
this issue. Automatic Undo Management and flashback have been in place
since 9iR1; absent any Oracle version knowing WHY you have a problem or
if that issue has been resolved with a patchset is impossible.

Post your Oracle version, and your operating system and O/S patch level
otherwise any assistance would be merely guesswork.

David Fitzjarrell

[Margaret Ruiz De Jesus]

I did not see it above so will ask again....

 

What was the steal count for the time period in v$undostat?

 

Need to see unxstealcnt, unxpblkrelcny, unxpblkreucnt, expstealcnt, exppblkrelcnt, and expblkreucnt as well as the columns you provided above.  If these all show zero then you MUST log a tar.  You are hitting a bug.  If this is early 9i or 10g there is probably already a fix.

 

Regards,

Margaret

 

[Hewlett]

Hi David,
Here is version info:

Oracle:
-----------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

Solaris:
-----------
Release: 5.8
Kernel architecture: sun4u
Application architecture: sparc
Hardware provider: Sun_Microsystems
Domain:
Kernel version: SunOS 5.8 Generic 117350-11 Sep 2004

Hewlett

[Hewlett]

Margaret,
The result is as you expected.
Thank you for your education.

Best regards,

Hewlett

[fitzj...@cox.net]

You need to patch this to at least 9.2.0.5; I'm running 9.2.0.6 on
Solaris and it's working quite well, without the issue you report and
we have numerous long running transactions at off-peak hours.

David Fitzjarrell