Undo-Monitor

1   why undo？

   What made the undo do that?

   What need?

   Undo (Rollback) serves two purposes. It is the fundamental mechanism that allows the Readers don't block writers, writers don't block readers mechanism. Is makes it also possible to rollback a transaction。

2   mock the 1555

SQL> set linesize 2000
SQL> set pagesize 500
SQL> alter session set nls_date_format='dd-mm-yy:hh24:mi:ss';

会话已更改。

SQL> create table a (b number, c varchar2(30));

表已创建。

SQL> insert into a values (1,'hallo');

已创建 1 行。

SQL>  create undo tablespace undo2 datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS02.DBF' SIZE 2M autoextend off;

表空间已创建。

SQL> alter system set undo_tablespace=undo2;

系统已更改。

SQL>  alter system set undo_retention=1;

系统已更改。

SQL> begin
  2  for i in 1..100000 loop
  3  update a set c='michael jackson';
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL 过程已成功完成。

----------another session--------

SQL>  variable i refcursor
SQL> exec open :i for select * from a;

PL/SQL 过程已成功完成。

SQL> print i;
ERROR:
ORA-01555: 快照过旧: 回退段号 14 (名称为 "_SYSSMU14$") 过小

未选定行

3  monitoring undo

SQL>  select begin_time,end_time,undoblks,maxquerylen,ssolderrcnt,nospaceerrcnt from v$undostat;

BEGIN_TIME     END_TIME         UNDOBLKS MAXQUERYLEN SSOLDERRCNT NOSPACEERRCNT
-------------- -------------- ---------- ----------- ----------- -------------
07-4月 -12     07-4月 -12         128548         662           1             0
07-4月 -12     07-4月 -12         124974         356           1             0

---------ssolderrcnt is 1555"snapshot too old" errors

SQL>  select (select max(undoblks)/600* max(maxquerylen) from v$undostat)
  2   *  (select value from v$parameter where name='db_block_size') from dual;

(SELECTMAX(UNDOBLKS)/600*MAX(MAXQUERYLEN)FROMV$UNDOSTAT)*(SELECTVALUEFROMV$PARAMETERWHERENAME='DB_BLOCK_SIZE')
--------------------------------------------------------------------------------------------------------------
                                                                                                    1688414563

-------------- in order to Prevent  1555 errors ，1688414563 is lowest least minimum minimal。 

4  Undo Size：

    Actual Undo Size

SQL> SELECT SUM(a.bytes) "UNDO_SIZE"
  2    FROM v$datafile a,
  3         v$tablespace b,
  4         dba_tablespaces c
  5   WHERE c.contents = 'UNDO'
  6     AND c.status = 'ONLINE'
  7     AND b.name = c.tablespace_name
  8     AND a.ts# = b.ts#;

 UNDO_SIZE
----------
 211812352

 Undo Blocks per Second：

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
  2        "UNDO_BLOCK_PER_SEC"
  3    FROM v$undostat;

UNDO_BLOCK_PER_SEC
------------------
           214.255

DB Block Size ：

SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
  2   FROM v$parameter
  3  WHERE name = 'db_block_size';

DB_BLOCK_SIZE [KByte]
---------------------
                 8192

Optimal Undo Retention：

 

 

    SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
  2         SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
  3         ROUND((d.undo_size / (to_number(f.value) *
  4         g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
  5    FROM (
  6         SELECT SUM(a.bytes) undo_size
  7            FROM v$datafile a,
  8                 v$tablespace b,
  9                 dba_tablespaces c
 10           WHERE c.contents = 'UNDO'
 11             AND c.status = 'ONLINE'
 12             AND b.name = c.tablespace_name
 13             AND a.ts# = b.ts#
 14         ) d,
 15         v$parameter e,
 16         v$parameter f,
 17         (
 18         SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 19                undo_block_per_sec
 20           FROM v$undostat
 21         ) g
 22  WHERE e.name = 'undo_retention'
 23    AND f.name = 'db_block_size'
 24  /

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]                               OPTIMAL UNDO RETENTION [Sec]
------------------------ -------------------------------------------------- ----------------------------
                     202 1                                                                           121

Calculate Needed UNDO Size for given Database Activity ：

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Again, all in one query:

SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
  2         SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
  3         (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
  4         g.undo_block_per_sec) / (1024*1024)
  5        "NEEDED UNDO SIZE [MByte]"
  6    FROM (
  7         SELECT SUM(a.bytes) undo_size
  8           FROM v$datafile a,
  9                v$tablespace b,
 10                dba_tablespaces c
 11          WHERE c.contents = 'UNDO'
 12            AND c.status = 'ONLINE'
 13            AND b.name = c.tablespace_name
 14            AND a.ts# = b.ts#
 15         ) d,
 16        v$parameter e,
 17         v$parameter f,
 18         (
 19         SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 20           undo_block_per_sec
 21           FROM v$undostat
 22         ) g
 23   WHERE e.name = 'undo_retention'
 24    AND f.name = 'db_block_size'
 25  /

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]                               NEEDED UNDO SIZE [MByte]
------------------------ -------------------------------------------------- ------------------------
                     202 1                                                                1.67386719

reference：

http://www.akadia.com/services/ora_optimize_undo.html

------------end-------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13750068/viewspace-720624/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-720624/