ITPUB论坛 » Oracle数据库管理 » 在BDUMP发现回滚很多扩充，但是回滚表空间很闲呀。什么原因呢？
2008-7-5 20:50
shilei1
在BDUMP发现回滚很多扩充，但是回滚表空间很闲呀。什么原因呢？
系统平台：solaris2.8
oracle:9I
BDUMP文件中有很多Created Undo Segment _SYSSMU53$
Undo Segment 53 Onlined类似动作。但是回滚段8点钟很闲。分析什么原因呢？
BDUMP文件：
。
。
。
。
Sat Jul 5 10:13:31 2008
Thread 1 advanced to log sequence 4709
Current log# 1 seq# 4709 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo01.log
Sat Jul 5 10:13:31 2008
ARC1: Evaluating archive log 3 thread 1 sequence 4708
ARC1: Beginning to archive log 3 thread 1 sequence 4708
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004708.arc'
ARC1: Completed archiving log 3 thread 1 sequence 4708
Sat Jul 5 10:28:11 2008
Thread 1 advanced to log sequence 4710
Current log# 2 seq# 4710 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo02.log
Sat Jul 5 10:28:11 2008
ARC0: Evaluating archive log 1 thread 1 sequence 4709
ARC0: Beginning to archive log 1 thread 1 sequence 4709
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004709.arc'
ARC0: Completed archiving log 1 thread 1 sequence 4709
Sat Jul 5 10:44:48 2008
Thread 1 advanced to log sequence 4711
Current log# 3 seq# 4711 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo03.log
Sat Jul 5 10:44:48 2008
ARC1: Evaluating archive log 2 thread 1 sequence 4710
ARC1: Beginning to archive log 2 thread 1 sequence 4710
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004710.arc'
ARC1: Completed archiving log 2 thread 1 sequence 4710
Sat Jul 5 11:45:01 2008
Thread 1 advanced to log sequence 4712
Current log# 1 seq# 4712 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo01.log
Sat Jul 5 11:45:01 2008
ARC0: Evaluating archive log 3 thread 1 sequence 4711
ARC0: Beginning to archive log 3 thread 1 sequence 4711
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004711.arc'
ARC0: Completed archiving log 3 thread 1 sequence 4711
Sat Jul 5 12:19:36 2008
Thread 1 advanced to log sequence 4713
Current log# 2 seq# 4713 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo02.log
Sat Jul 5 12:19:36 2008
ARC1: Evaluating archive log 1 thread 1 sequence 4712
ARC1: Beginning to archive log 1 thread 1 sequence 4712
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004712.arc'
ARC1: Completed archiving log 1 thread 1 sequence 4712
Sat Jul 5 12:21:46 2008
Created Undo Segment _SYSSMU57$
Undo Segment 57 Onlined
Created Undo Segment _SYSSMU58$
Undo Segment 58 Onlined
Sat Jul 5 12:21:47 2008
Created Undo Segment _SYSSMU59$
Undo Segment 59 Onlined
Sat Jul 5 12:21:50 2008
Created Undo Segment _SYSSMU60$
Undo Segment 60 Onlined
Sat Jul 5 12:21:52 2008
Created Undo Segment _SYSSMU61$
Undo Segment 61 Onlined
Sat Jul 5 12:21:53 2008
Created Undo Segment _SYSSMU62$
Undo Segment 62 Onlined
Sat Jul 5 12:21:53 2008
Created Undo Segment _SYSSMU63$
Undo Segment 63 Onlined
Sat Jul 5 12:21:54 2008
Created Undo Segment _SYSSMU64$
Undo Segment 64 Onlined
Sat Jul 5 12:21:58 2008
Created Undo Segment _SYSSMU65$
Undo Segment 65 Onlined
Sat Jul 5 12:22:04 2008
Created Undo Segment _SYSSMU66$
Undo Segment 66 Onlined
Sat Jul 5 12:22:04 2008
Created Undo Segment _SYSSMU67$
Undo Segment 67 Onlined
Sat Jul 5 12:22:05 2008
Created Undo Segment _SYSSMU68$
Undo Segment 68 Onlined
Sat Jul 5 12:22:05 2008
Created Undo Segment _SYSSMU69$
Undo Segment 69 Onlined
Sat Jul 5 12:22:05 2008
Created Undo Segment _SYSSMU70$
Undo Segment 70 Onlined
Sat Jul 5 12:22:06 2008
Created Undo Segment _SYSSMU71$
Undo Segment 71 Onlined
Sat Jul 5 12:22:07 2008
Created Undo Segment _SYSSMU72$
Undo Segment 72 Onlined
Sat Jul 5 12:22:08 2008
Created Undo Segment _SYSSMU73$
Undo Segment 73 Onlined
Sat Jul 5 12:22:08 2008
Created Undo Segment _SYSSMU74$
Undo Segment 74 Onlined
Sat Jul 5 12:22:09 2008
Created Undo Segment _SYSSMU75$
Undo Segment 75 Onlined
Sat Jul 5 12:22:12 2008
Created Undo Segment _SYSSMU76$
Undo Segment 76 Onlined
Sat Jul 5 12:22:16 2008
Created Undo Segment _SYSSMU77$
Undo Segment 77 Onlined
Sat Jul 5 12:22:17 2008
Created Undo Segment _SYSSMU78$
Undo Segment 78 Onlined
Sat Jul 5 12:22:17 2008
Created Undo Segment _SYSSMU79$
Undo Segment 79 Onlined
Sat Jul 5 12:22:18 2008
Created Undo Segment _SYSSMU80$
Undo Segment 80 Onlined
Sat Jul 5 12:22:20 2008
Created Undo Segment _SYSSMU81$
Undo Segment 81 Onlined
Sat Jul 5 12:22:20 2008
Created Undo Segment _SYSSMU82$
Undo Segment 82 Onlined
Sat Jul 5 12:22:21 2008
Created Undo Segment _SYSSMU83$
Undo Segment 83 Onlined
Sat Jul 5 12:22:21 2008
Created Undo Segment _SYSSMU84$
Undo Segment 84 Onlined
Sat Jul 5 12:22:23 2008
Created Undo Segment _SYSSMU85$
Undo Segment 85 Onlined
Sat Jul 5 12:22:23 2008
Created Undo Segment _SYSSMU86$
Undo Segment 86 Onlined
Sat Jul 5 12:22:25 2008
Created Undo Segment _SYSSMU87$
Undo Segment 87 Onlined
Sat Jul 5 12:22:26 2008
Created Undo Segment _SYSSMU88$
Undo Segment 88 Onlined
Sat Jul 5 12:22:27 2008
Created Undo Segment _SYSSMU89$
Undo Segment 89 Onlined
Sat Jul 5 12:22:27 2008
Created Undo Segment _SYSSMU90$
Undo Segment 90 Onlined
Sat Jul 5 12:22:28 2008
Created Undo Segment _SYSSMU91$
Undo Segment 91 Onlined
Sat Jul 5 12:22:28 2008
Created Undo Segment _SYSSMU92$
Undo Segment 92 Onlined
Sat Jul 5 12:22:30 2008
Created Undo Segment _SYSSMU93$
Undo Segment 93 Onlined
Sat Jul 5 12:22:30 2008
Created Undo Segment _SYSSMU94$
Undo Segment 94 Onlined
Sat Jul 5 12:22:33 2008
Created Undo Segment _SYSSMU95$
Undo Segment 95 Onlined
Sat Jul 5 12:22:33 2008
Created Undo Segment _SYSSMU96$
Undo Segment 96 Onlined
Sat Jul 5 12:22:35 2008
Created Undo Segment _SYSSMU97$
Undo Segment 97 Onlined
Sat Jul 5 12:22:37 2008
Created Undo Segment _SYSSMU98$
Undo Segment 98 Onlined
Sat Jul 5 12:22:39 2008
Created Undo Segment _SYSSMU99$
Undo Segment 99 Onlined
Sat Jul 5 12:22:41 2008
Created Undo Segment _SYSSMU100$
Undo Segment 100 Onlined
Sat Jul 5 12:22:48 2008
Created Undo Segment _SYSSMU101$
Undo Segment 101 Onlined
Sat Jul 5 12:23:00 2008
Created Undo Segment _SYSSMU102$
Undo Segment 102 Onlined
Sat Jul 5 12:23:09 2008
Created Undo Segment _SYSSMU103$
Undo Segment 103 Onlined
Sat Jul 5 13:13:15 2008
Thread 1 advanced to log sequence 4714
Current log# 3 seq# 4714 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo03.log
Sat Jul 5 13:13:15 2008
ARC0: Evaluating archive log 2 thread 1 sequence 4713
ARC0: Beginning to archive log 2 thread 1 sequence 4713
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004713.arc'
ARC0: Completed archiving log 2 thread 1 sequence 4713
Sat Jul 5 14:25:45 2008
Thread 1 advanced to log sequence 4715
Current log# 1 seq# 4715 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo01.log
Sat Jul 5 14:25:45 2008
ARC1: Evaluating archive log 3 thread 1 sequence 4714
ARC1: Beginning to archive log 3 thread 1 sequence 4714
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004714.arc'
ARC1: Completed archiving log 3 thread 1 sequence 4714
Sat Jul 5 15:57:49 2008
Thread 1 advanced to log sequence 4716
Current log# 2 seq# 4716 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo02.log
Sat Jul 5 15:57:49 2008
ARC0: Evaluating archive log 1 thread 1 sequence 4715
ARC0: Beginning to archive log 1 thread 1 sequence 4715
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004715.arc'
ARC0: Completed archiving log 1 thread 1 sequence 4715
Sat Jul 5 17:33:30 2008
SMON offlining US=52
SMON offlining US=53
SMON offlining US=54
SMON offlining US=55
SMON offlining US=56
SMON offlining US=57
SMON offlining US=58
SMON offlining US=59
SMON offlining US=60
SMON offlining US=61
SMON offlining US=62
SMON offlining US=63
SMON offlining US=64
SMON offlining US=65
SMON offlining US=66
SMON offlining US=67
SMON offlining US=68
SMON offlining US=69
SMON offlining US=70
SMON offlining US=71
SMON offlining US=72
SMON offlining US=73
SMON offlining US=74
SMON offlining US=75
SMON offlining US=76
SMON offlining US=77
SMON offlining US=78
SMON offlining US=79
SMON offlining US=80
SMON offlining US=81
SMON offlining US=82
SMON offlining US=83
SMON offlining US=84
SMON offlining US=85
SMON offlining US=86
SMON offlining US=87
SMON offlining US=88
SMON offlining US=89
SMON offlining US=90
SMON offlining US=91
SMON offlining US=92
SMON offlining US=93
SMON offlining US=94
SMON offlining US=95
SMON offlining US=96
SMON offlining US=97
SMON offlining US=98
SMON offlining US=99
SMON offlining US=100
SMON offlining US=101
SMON offlining US=102
SMON offlining US=103
做如下检查：
SQL> SELECT tablespace_name,sum_m,sum_free_m,to_char(100*(sum_m-sum_free_m)/sum_m,'99.9999') ||'%' AS pct_used
2 FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),
3 ( SELECT tablespace_name AS fs_ts_name,sum(bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name )
4 WHERE tablespace_name = fs_ts_name;
TABLESPACE_NAME SUM_M SUM_FREE_M PCT_USED
------------------------------ ---------- ---------- ---------
CWMLITE 20 10.625 46.8750%
DRSYS 20 10.3125 48.4375%
EDMDATA2 1024 986.25 3.6865%
EDMDATA3 1024 76 92.5781%
EDMIDX4 500 404 19.2000%
EXAMPLE 149.375 .5 99.6653%
INDX 25 24.9375 .2500%
ODM 20 10.625 46.8750%
PERFSTAT 6671 1.1875 99.9822%
SYSTEM 470 12.5 97.3404%
TOOLS 10 3.9375 60.6250%
TABLESPACE_NAME SUM_M SUM_FREE_M PCT_USED
------------------------------ ---------- ---------- ---------
UNDOTBS1 8290 8079.9375 2.5339%
UNDOTBS2 12674 12568.6875 .8309%
USERS 11870 9388.1875 20.9083%
XDB 38.125 .1875 99.5082%
15 rows selected.
SQL> shiMaro<[email]yongbo.song@163.com[/email]> 05:09:40
SP2-0734: unknown command beginning "shiMaro<yo..." - rest of line ignored.
SQL> select distinct owner, segment_name, tablespace_name from DBA_UNDO_EXTENTS;
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU1$ UNDOTBS1
SYS _SYSSMU10$ UNDOTBS1
SYS _SYSSMU100$ UNDOTBS2
SYS _SYSSMU101$ UNDOTBS2
SYS _SYSSMU102$ UNDOTBS2
SYS _SYSSMU103$ UNDOTBS2
SYS _SYSSMU11$ UNDOTBS1
SYS _SYSSMU12$ UNDOTBS1
SYS _SYSSMU13$ UNDOTBS1
SYS _SYSSMU14$ UNDOTBS1
SYS _SYSSMU15$ UNDOTBS1
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU16$ UNDOTBS1
SYS _SYSSMU17$ UNDOTBS1
SYS _SYSSMU18$ UNDOTBS1
SYS _SYSSMU19$ UNDOTBS1
SYS _SYSSMU2$ UNDOTBS1
SYS _SYSSMU20$ UNDOTBS1
SYS _SYSSMU21$ UNDOTBS1
SYS _SYSSMU22$ UNDOTBS1
SYS _SYSSMU23$ UNDOTBS1
SYS _SYSSMU24$ UNDOTBS1
SYS _SYSSMU25$ UNDOTBS1
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU26$ UNDOTBS1
SYS _SYSSMU27$ UNDOTBS1
SYS _SYSSMU28$ UNDOTBS1
SYS _SYSSMU29$ UNDOTBS1
SYS _SYSSMU3$ UNDOTBS1
SYS _SYSSMU30$ UNDOTBS1
SYS _SYSSMU31$ UNDOTBS1
SYS _SYSSMU32$ UNDOTBS1
SYS _SYSSMU33$ UNDOTBS1
SYS _SYSSMU34$ UNDOTBS1
SYS _SYSSMU35$ UNDOTBS1
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU36$ UNDOTBS1
SYS _SYSSMU37$ UNDOTBS1
SYS _SYSSMU38$ UNDOTBS1
SYS _SYSSMU39$ UNDOTBS1
SYS _SYSSMU4$ UNDOTBS1
SYS _SYSSMU40$ UNDOTBS1
SYS _SYSSMU41$ UNDOTBS1
SYS _SYSSMU42$ UNDOTBS2
SYS _SYSSMU43$ UNDOTBS2
SYS _SYSSMU44$ UNDOTBS2
SYS _SYSSMU45$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU46$ UNDOTBS2
SYS _SYSSMU47$ UNDOTBS2
SYS _SYSSMU48$ UNDOTBS2
SYS _SYSSMU49$ UNDOTBS2
SYS _SYSSMU5$ UNDOTBS1
SYS _SYSSMU50$ UNDOTBS2
SYS _SYSSMU51$ UNDOTBS2
SYS _SYSSMU52$ UNDOTBS2
SYS _SYSSMU53$ UNDOTBS2
SYS _SYSSMU54$ UNDOTBS2
SYS _SYSSMU55$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU56$ UNDOTBS2
SYS _SYSSMU57$ UNDOTBS2
SYS _SYSSMU58$ UNDOTBS2
SYS _SYSSMU59$ UNDOTBS2
SYS _SYSSMU6$ UNDOTBS1
SYS _SYSSMU60$ UNDOTBS2
SYS _SYSSMU61$ UNDOTBS2
SYS _SYSSMU62$ UNDOTBS2
SYS _SYSSMU63$ UNDOTBS2
SYS _SYSSMU64$ UNDOTBS2
SYS _SYSSMU65$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU66$ UNDOTBS2
SYS _SYSSMU67$ UNDOTBS2
SYS _SYSSMU68$ UNDOTBS2
SYS _SYSSMU69$ UNDOTBS2
SYS _SYSSMU7$ UNDOTBS1
SYS _SYSSMU70$ UNDOTBS2
SYS _SYSSMU71$ UNDOTBS2
SYS _SYSSMU72$ UNDOTBS2
SYS _SYSSMU73$ UNDOTBS2
SYS _SYSSMU74$ UNDOTBS2
SYS _SYSSMU75$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU76$ UNDOTBS2
SYS _SYSSMU77$ UNDOTBS2
SYS _SYSSMU78$ UNDOTBS2
SYS _SYSSMU79$ UNDOTBS2
SYS _SYSSMU8$ UNDOTBS1
SYS _SYSSMU80$ UNDOTBS2
SYS _SYSSMU81$ UNDOTBS2
SYS _SYSSMU82$ UNDOTBS2
SYS _SYSSMU83$ UNDOTBS2
SYS _SYSSMU84$ UNDOTBS2
SYS _SYSSMU85$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU86$ UNDOTBS2
SYS _SYSSMU87$ UNDOTBS2
SYS _SYSSMU88$ UNDOTBS2
SYS _SYSSMU89$ UNDOTBS2
SYS _SYSSMU9$ UNDOTBS1
SYS _SYSSMU90$ UNDOTBS2
SYS _SYSSMU91$ UNDOTBS2
SYS _SYSSMU92$ UNDOTBS2
SYS _SYSSMU93$ UNDOTBS2
SYS _SYSSMU94$ UNDOTBS2
SYS _SYSSMU95$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU96$ UNDOTBS2
SYS _SYSSMU97$ UNDOTBS2
SYS _SYSSMU98$ UNDOTBS2
SYS _SYSSMU99$ UNDOTBS2
103 rows selected.
SQL>
系统平台：solaris2.8
oracle:9I
BDUMP文件中有很多Created Undo Segment _SYSSMU53$
Undo Segment 53 Onlined类似动作。但是回滚段8点钟很闲。分析什么原因呢？
BDUMP文件：
。
。
。
。
Sat Jul 5 10:13:31 2008
Thread 1 advanced to log sequence 4709
Current log# 1 seq# 4709 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo01.log
Sat Jul 5 10:13:31 2008
ARC1: Evaluating archive log 3 thread 1 sequence 4708
ARC1: Beginning to archive log 3 thread 1 sequence 4708
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004708.arc'
ARC1: Completed archiving log 3 thread 1 sequence 4708
Sat Jul 5 10:28:11 2008
Thread 1 advanced to log sequence 4710
Current log# 2 seq# 4710 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo02.log
Sat Jul 5 10:28:11 2008
ARC0: Evaluating archive log 1 thread 1 sequence 4709
ARC0: Beginning to archive log 1 thread 1 sequence 4709
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004709.arc'
ARC0: Completed archiving log 1 thread 1 sequence 4709
Sat Jul 5 10:44:48 2008
Thread 1 advanced to log sequence 4711
Current log# 3 seq# 4711 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo03.log
Sat Jul 5 10:44:48 2008
ARC1: Evaluating archive log 2 thread 1 sequence 4710
ARC1: Beginning to archive log 2 thread 1 sequence 4710
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004710.arc'
ARC1: Completed archiving log 2 thread 1 sequence 4710
Sat Jul 5 11:45:01 2008
Thread 1 advanced to log sequence 4712
Current log# 1 seq# 4712 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo01.log
Sat Jul 5 11:45:01 2008
ARC0: Evaluating archive log 3 thread 1 sequence 4711
ARC0: Beginning to archive log 3 thread 1 sequence 4711
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004711.arc'
ARC0: Completed archiving log 3 thread 1 sequence 4711
Sat Jul 5 12:19:36 2008
Thread 1 advanced to log sequence 4713
Current log# 2 seq# 4713 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo02.log
Sat Jul 5 12:19:36 2008
ARC1: Evaluating archive log 1 thread 1 sequence 4712
ARC1: Beginning to archive log 1 thread 1 sequence 4712
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004712.arc'
ARC1: Completed archiving log 1 thread 1 sequence 4712
Sat Jul 5 12:21:46 2008
Created Undo Segment _SYSSMU57$
Undo Segment 57 Onlined
Created Undo Segment _SYSSMU58$
Undo Segment 58 Onlined
Sat Jul 5 12:21:47 2008
Created Undo Segment _SYSSMU59$
Undo Segment 59 Onlined
Sat Jul 5 12:21:50 2008
Created Undo Segment _SYSSMU60$
Undo Segment 60 Onlined
Sat Jul 5 12:21:52 2008
Created Undo Segment _SYSSMU61$
Undo Segment 61 Onlined
Sat Jul 5 12:21:53 2008
Created Undo Segment _SYSSMU62$
Undo Segment 62 Onlined
Sat Jul 5 12:21:53 2008
Created Undo Segment _SYSSMU63$
Undo Segment 63 Onlined
Sat Jul 5 12:21:54 2008
Created Undo Segment _SYSSMU64$
Undo Segment 64 Onlined
Sat Jul 5 12:21:58 2008
Created Undo Segment _SYSSMU65$
Undo Segment 65 Onlined
Sat Jul 5 12:22:04 2008
Created Undo Segment _SYSSMU66$
Undo Segment 66 Onlined
Sat Jul 5 12:22:04 2008
Created Undo Segment _SYSSMU67$
Undo Segment 67 Onlined
Sat Jul 5 12:22:05 2008
Created Undo Segment _SYSSMU68$
Undo Segment 68 Onlined
Sat Jul 5 12:22:05 2008
Created Undo Segment _SYSSMU69$
Undo Segment 69 Onlined
Sat Jul 5 12:22:05 2008
Created Undo Segment _SYSSMU70$
Undo Segment 70 Onlined
Sat Jul 5 12:22:06 2008
Created Undo Segment _SYSSMU71$
Undo Segment 71 Onlined
Sat Jul 5 12:22:07 2008
Created Undo Segment _SYSSMU72$
Undo Segment 72 Onlined
Sat Jul 5 12:22:08 2008
Created Undo Segment _SYSSMU73$
Undo Segment 73 Onlined
Sat Jul 5 12:22:08 2008
Created Undo Segment _SYSSMU74$
Undo Segment 74 Onlined
Sat Jul 5 12:22:09 2008
Created Undo Segment _SYSSMU75$
Undo Segment 75 Onlined
Sat Jul 5 12:22:12 2008
Created Undo Segment _SYSSMU76$
Undo Segment 76 Onlined
Sat Jul 5 12:22:16 2008
Created Undo Segment _SYSSMU77$
Undo Segment 77 Onlined
Sat Jul 5 12:22:17 2008
Created Undo Segment _SYSSMU78$
Undo Segment 78 Onlined
Sat Jul 5 12:22:17 2008
Created Undo Segment _SYSSMU79$
Undo Segment 79 Onlined
Sat Jul 5 12:22:18 2008
Created Undo Segment _SYSSMU80$
Undo Segment 80 Onlined
Sat Jul 5 12:22:20 2008
Created Undo Segment _SYSSMU81$
Undo Segment 81 Onlined
Sat Jul 5 12:22:20 2008
Created Undo Segment _SYSSMU82$
Undo Segment 82 Onlined
Sat Jul 5 12:22:21 2008
Created Undo Segment _SYSSMU83$
Undo Segment 83 Onlined
Sat Jul 5 12:22:21 2008
Created Undo Segment _SYSSMU84$
Undo Segment 84 Onlined
Sat Jul 5 12:22:23 2008
Created Undo Segment _SYSSMU85$
Undo Segment 85 Onlined
Sat Jul 5 12:22:23 2008
Created Undo Segment _SYSSMU86$
Undo Segment 86 Onlined
Sat Jul 5 12:22:25 2008
Created Undo Segment _SYSSMU87$
Undo Segment 87 Onlined
Sat Jul 5 12:22:26 2008
Created Undo Segment _SYSSMU88$
Undo Segment 88 Onlined
Sat Jul 5 12:22:27 2008
Created Undo Segment _SYSSMU89$
Undo Segment 89 Onlined
Sat Jul 5 12:22:27 2008
Created Undo Segment _SYSSMU90$
Undo Segment 90 Onlined
Sat Jul 5 12:22:28 2008
Created Undo Segment _SYSSMU91$
Undo Segment 91 Onlined
Sat Jul 5 12:22:28 2008
Created Undo Segment _SYSSMU92$
Undo Segment 92 Onlined
Sat Jul 5 12:22:30 2008
Created Undo Segment _SYSSMU93$
Undo Segment 93 Onlined
Sat Jul 5 12:22:30 2008
Created Undo Segment _SYSSMU94$
Undo Segment 94 Onlined
Sat Jul 5 12:22:33 2008
Created Undo Segment _SYSSMU95$
Undo Segment 95 Onlined
Sat Jul 5 12:22:33 2008
Created Undo Segment _SYSSMU96$
Undo Segment 96 Onlined
Sat Jul 5 12:22:35 2008
Created Undo Segment _SYSSMU97$
Undo Segment 97 Onlined
Sat Jul 5 12:22:37 2008
Created Undo Segment _SYSSMU98$
Undo Segment 98 Onlined
Sat Jul 5 12:22:39 2008
Created Undo Segment _SYSSMU99$
Undo Segment 99 Onlined
Sat Jul 5 12:22:41 2008
Created Undo Segment _SYSSMU100$
Undo Segment 100 Onlined
Sat Jul 5 12:22:48 2008
Created Undo Segment _SYSSMU101$
Undo Segment 101 Onlined
Sat Jul 5 12:23:00 2008
Created Undo Segment _SYSSMU102$
Undo Segment 102 Onlined
Sat Jul 5 12:23:09 2008
Created Undo Segment _SYSSMU103$
Undo Segment 103 Onlined
Sat Jul 5 13:13:15 2008
Thread 1 advanced to log sequence 4714
Current log# 3 seq# 4714 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo03.log
Sat Jul 5 13:13:15 2008
ARC0: Evaluating archive log 2 thread 1 sequence 4713
ARC0: Beginning to archive log 2 thread 1 sequence 4713
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004713.arc'
ARC0: Completed archiving log 2 thread 1 sequence 4713
Sat Jul 5 14:25:45 2008
Thread 1 advanced to log sequence 4715
Current log# 1 seq# 4715 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo01.log
Sat Jul 5 14:25:45 2008
ARC1: Evaluating archive log 3 thread 1 sequence 4714
ARC1: Beginning to archive log 3 thread 1 sequence 4714
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004714.arc'
ARC1: Completed archiving log 3 thread 1 sequence 4714
Sat Jul 5 15:57:49 2008
Thread 1 advanced to log sequence 4716
Current log# 2 seq# 4716 mem# 0: /DMCDBS01/DMCEDM01/oradata/DMCEDM02/redo02.log
Sat Jul 5 15:57:49 2008
ARC0: Evaluating archive log 1 thread 1 sequence 4715
ARC0: Beginning to archive log 1 thread 1 sequence 4715
Creating archive destination LOG_ARCHIVE_DEST_1: '/DMCDBS01/DMCEDM01/backup/DMCEDM02/arch0000004715.arc'
ARC0: Completed archiving log 1 thread 1 sequence 4715
Sat Jul 5 17:33:30 2008
SMON offlining US=52
SMON offlining US=53
SMON offlining US=54
SMON offlining US=55
SMON offlining US=56
SMON offlining US=57
SMON offlining US=58
SMON offlining US=59
SMON offlining US=60
SMON offlining US=61
SMON offlining US=62
SMON offlining US=63
SMON offlining US=64
SMON offlining US=65
SMON offlining US=66
SMON offlining US=67
SMON offlining US=68
SMON offlining US=69
SMON offlining US=70
SMON offlining US=71
SMON offlining US=72
SMON offlining US=73
SMON offlining US=74
SMON offlining US=75
SMON offlining US=76
SMON offlining US=77
SMON offlining US=78
SMON offlining US=79
SMON offlining US=80
SMON offlining US=81
SMON offlining US=82
SMON offlining US=83
SMON offlining US=84
SMON offlining US=85
SMON offlining US=86
SMON offlining US=87
SMON offlining US=88
SMON offlining US=89
SMON offlining US=90
SMON offlining US=91
SMON offlining US=92
SMON offlining US=93
SMON offlining US=94
SMON offlining US=95
SMON offlining US=96
SMON offlining US=97
SMON offlining US=98
SMON offlining US=99
SMON offlining US=100
SMON offlining US=101
SMON offlining US=102
SMON offlining US=103
做如下检查：
SQL> SELECT tablespace_name,sum_m,sum_free_m,to_char(100*(sum_m-sum_free_m)/sum_m,'99.9999') ||'%' AS pct_used
2 FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),
3 ( SELECT tablespace_name AS fs_ts_name,sum(bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name )
4 WHERE tablespace_name = fs_ts_name;
TABLESPACE_NAME SUM_M SUM_FREE_M PCT_USED
------------------------------ ---------- ---------- ---------
CWMLITE 20 10.625 46.8750%
DRSYS 20 10.3125 48.4375%
EDMDATA2 1024 986.25 3.6865%
EDMDATA3 1024 76 92.5781%
EDMIDX4 500 404 19.2000%
EXAMPLE 149.375 .5 99.6653%
INDX 25 24.9375 .2500%
ODM 20 10.625 46.8750%
PERFSTAT 6671 1.1875 99.9822%
SYSTEM 470 12.5 97.3404%
TOOLS 10 3.9375 60.6250%
TABLESPACE_NAME SUM_M SUM_FREE_M PCT_USED
------------------------------ ---------- ---------- ---------
UNDOTBS1 8290 8079.9375 2.5339%
UNDOTBS2 12674 12568.6875 .8309%
USERS 11870 9388.1875 20.9083%
XDB 38.125 .1875 99.5082%
15 rows selected.
SQL> shiMaro<[email]yongbo.song@163.com[/email]> 05:09:40
SP2-0734: unknown command beginning "shiMaro<yo..." - rest of line ignored.
SQL> select distinct owner, segment_name, tablespace_name from DBA_UNDO_EXTENTS;
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU1$ UNDOTBS1
SYS _SYSSMU10$ UNDOTBS1
SYS _SYSSMU100$ UNDOTBS2
SYS _SYSSMU101$ UNDOTBS2
SYS _SYSSMU102$ UNDOTBS2
SYS _SYSSMU103$ UNDOTBS2
SYS _SYSSMU11$ UNDOTBS1
SYS _SYSSMU12$ UNDOTBS1
SYS _SYSSMU13$ UNDOTBS1
SYS _SYSSMU14$ UNDOTBS1
SYS _SYSSMU15$ UNDOTBS1
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU16$ UNDOTBS1
SYS _SYSSMU17$ UNDOTBS1
SYS _SYSSMU18$ UNDOTBS1
SYS _SYSSMU19$ UNDOTBS1
SYS _SYSSMU2$ UNDOTBS1
SYS _SYSSMU20$ UNDOTBS1
SYS _SYSSMU21$ UNDOTBS1
SYS _SYSSMU22$ UNDOTBS1
SYS _SYSSMU23$ UNDOTBS1
SYS _SYSSMU24$ UNDOTBS1
SYS _SYSSMU25$ UNDOTBS1
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU26$ UNDOTBS1
SYS _SYSSMU27$ UNDOTBS1
SYS _SYSSMU28$ UNDOTBS1
SYS _SYSSMU29$ UNDOTBS1
SYS _SYSSMU3$ UNDOTBS1
SYS _SYSSMU30$ UNDOTBS1
SYS _SYSSMU31$ UNDOTBS1
SYS _SYSSMU32$ UNDOTBS1
SYS _SYSSMU33$ UNDOTBS1
SYS _SYSSMU34$ UNDOTBS1
SYS _SYSSMU35$ UNDOTBS1
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU36$ UNDOTBS1
SYS _SYSSMU37$ UNDOTBS1
SYS _SYSSMU38$ UNDOTBS1
SYS _SYSSMU39$ UNDOTBS1
SYS _SYSSMU4$ UNDOTBS1
SYS _SYSSMU40$ UNDOTBS1
SYS _SYSSMU41$ UNDOTBS1
SYS _SYSSMU42$ UNDOTBS2
SYS _SYSSMU43$ UNDOTBS2
SYS _SYSSMU44$ UNDOTBS2
SYS _SYSSMU45$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU46$ UNDOTBS2
SYS _SYSSMU47$ UNDOTBS2
SYS _SYSSMU48$ UNDOTBS2
SYS _SYSSMU49$ UNDOTBS2
SYS _SYSSMU5$ UNDOTBS1
SYS _SYSSMU50$ UNDOTBS2
SYS _SYSSMU51$ UNDOTBS2
SYS _SYSSMU52$ UNDOTBS2
SYS _SYSSMU53$ UNDOTBS2
SYS _SYSSMU54$ UNDOTBS2
SYS _SYSSMU55$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU56$ UNDOTBS2
SYS _SYSSMU57$ UNDOTBS2
SYS _SYSSMU58$ UNDOTBS2
SYS _SYSSMU59$ UNDOTBS2
SYS _SYSSMU6$ UNDOTBS1
SYS _SYSSMU60$ UNDOTBS2
SYS _SYSSMU61$ UNDOTBS2
SYS _SYSSMU62$ UNDOTBS2
SYS _SYSSMU63$ UNDOTBS2
SYS _SYSSMU64$ UNDOTBS2
SYS _SYSSMU65$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU66$ UNDOTBS2
SYS _SYSSMU67$ UNDOTBS2
SYS _SYSSMU68$ UNDOTBS2
SYS _SYSSMU69$ UNDOTBS2
SYS _SYSSMU7$ UNDOTBS1
SYS _SYSSMU70$ UNDOTBS2
SYS _SYSSMU71$ UNDOTBS2
SYS _SYSSMU72$ UNDOTBS2
SYS _SYSSMU73$ UNDOTBS2
SYS _SYSSMU74$ UNDOTBS2
SYS _SYSSMU75$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU76$ UNDOTBS2
SYS _SYSSMU77$ UNDOTBS2
SYS _SYSSMU78$ UNDOTBS2
SYS _SYSSMU79$ UNDOTBS2
SYS _SYSSMU8$ UNDOTBS1
SYS _SYSSMU80$ UNDOTBS2
SYS _SYSSMU81$ UNDOTBS2
SYS _SYSSMU82$ UNDOTBS2
SYS _SYSSMU83$ UNDOTBS2
SYS _SYSSMU84$ UNDOTBS2
SYS _SYSSMU85$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU86$ UNDOTBS2
SYS _SYSSMU87$ UNDOTBS2
SYS _SYSSMU88$ UNDOTBS2
SYS _SYSSMU89$ UNDOTBS2
SYS _SYSSMU9$ UNDOTBS1
SYS _SYSSMU90$ UNDOTBS2
SYS _SYSSMU91$ UNDOTBS2
SYS _SYSSMU92$ UNDOTBS2
SYS _SYSSMU93$ UNDOTBS2
SYS _SYSSMU94$ UNDOTBS2
SYS _SYSSMU95$ UNDOTBS2
OWN SEGMENT_NAME TABLESPACE_NAME
--- ------------------------------ ------------------------------
SYS _SYSSMU96$ UNDOTBS2
SYS _SYSSMU97$ UNDOTBS2
SYS _SYSSMU98$ UNDOTBS2
SYS _SYSSMU99$ UNDOTBS2
103 rows selected.
SQL>
2008-7-5 20:55
shilei1
我分析是现在看很闲说明不了任何问题，因为数据库目前很闲，没有什么事务。那么回滚段使用率肯定很低了，因为回滚段在commit后释放掉了。
有可能是在12点左右有个很大的事务，或是很忙，这样就没有提交，这样导致回滚段疯狂扩充。大家帮我分析下
有可能是在12点左右有个很大的事务，或是很忙，这样就没有提交，这样导致回滚段疯狂扩充。大家帮我分析下
2008-7-6 12:01
shilei1
顶上去
2008-7-6 12:07
howard_zhang
v$undostat
beging_time,end_time,undoblks,txncount order undoblks
检查你事务高发期的UNDO使用
beging_time,end_time,undoblks,txncount order undoblks
检查你事务高发期的UNDO使用
2008-7-7 14:30
shilei1
大家帮我看看我理解的好像有错误这里的Created Undo Segment _SYSSMU60$好像不是说创建了回滚段。
2008-7-7 16:20
shilei1
[quote]原帖由 [i]shilei1[/i] 于 2008-7-7 14:30 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10878397&ptid=1017335][img]http://www.itpub.net/images/common/back.gif[/img][/url]
大家帮我看看我理解的好像有错误这里的Created Undo Segment _SYSSMU60$好像不是说创建了回滚段。 [/quote]
明白了，回滚段是自动管理，就是当客户端有个发起个事务可以分配个回顾段，当这个事物完成，那么就会撤销的，例如：Segment _SYSSMU60$分配完成后事务完成时要回收的。下次再有事务再分配。
但是这个问题又别不解了。
select begin_time,end_time,undoblks,txncount from v$undostat order by undoblks
03-JUL-08 03-JUL-08 1894 30672651
07-JUL-08 07-JUL-08 1913 31833724
03-JUL-08 03-JUL-08 1919 30546365
05-JUL-08 05-JUL-08 1922 31172400
06-JUL-08 06-JUL-08 1944 31508141
06-JUL-08 06-JUL-08 2011 31462651
05-JUL-08 05-JUL-08 2016 31209906
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
02-JUL-08 02-JUL-08 2016 30348926
04-JUL-08 04-JUL-08 2141 30875068
07-JUL-08 07-JUL-08 2154 31694714
07-JUL-08 07-JUL-08 2158 31709389
03-JUL-08 03-JUL-08 2177 30551908
07-JUL-08 07-JUL-08 2241 31702830
04-JUL-08 04-JUL-08 2251 30966317
01-JUL-08 01-JUL-08 2322 29958939
03-JUL-08 03-JUL-08 2337 30523888
03-JUL-08 03-JUL-08 2344 30622455
03-JUL-08 03-JUL-08 2379 30587634
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
03-JUL-08 03-JUL-08 2400 30511649
01-JUL-08 01-JUL-08 2441 29969726
07-JUL-08 07-JUL-08 2449 31729782
05-JUL-08 05-JUL-08 2451 31195999
04-JUL-08 04-JUL-08 2462 30916138
03-JUL-08 03-JUL-08 2473 30536939
02-JUL-08 02-JUL-08 2488 30247604
02-JUL-08 02-JUL-08 2501 30241055
06-JUL-08 06-JUL-08 2529 31573117
06-JUL-08 06-JUL-08 2560 31477201
05-JUL-08 05-JUL-08 2566 31270494
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
05-JUL-08 05-JUL-08 2593 31186099
03-JUL-08 03-JUL-08 2593 30654337
07-JUL-08 07-JUL-08 2653 31724106
02-JUL-08 02-JUL-08 2655 30314383
06-JUL-08 06-JUL-08 2731 31502914
01-JUL-08 01-JUL-08 2813 29964830
01-JUL-08 01-JUL-08 2850 30116037
06-JUL-08 06-JUL-08 2890 31453617
05-JUL-08 05-JUL-08 2892 31190954
03-JUL-08 03-JUL-08 2968 30598199
04-JUL-08 04-JUL-08 2998 30866592
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
06-JUL-08 06-JUL-08 3029 31529913
02-JUL-08 02-JUL-08 3054 30321925
03-JUL-08 03-JUL-08 3109 30766327
02-JUL-08 02-JUL-08 3124 30388630
04-JUL-08 04-JUL-08 3126 30939939
04-JUL-08 04-JUL-08 3157 30935585
04-JUL-08 04-JUL-08 3196 30946858
06-JUL-08 06-JUL-08 3198 31524596
06-JUL-08 06-JUL-08 3258 31434989
04-JUL-08 04-JUL-08 3268 31021413
04-JUL-08 04-JUL-08 3328 30897242
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
07-JUL-08 07-JUL-08 3396 31736486
06-JUL-08 06-JUL-08 3414 31428878
01-JUL-08 01-JUL-08 3447 30110744
07-JUL-08 07-JUL-08 3524 31827183
04-JUL-08 04-JUL-08 3602 30884150
06-JUL-08 06-JUL-08 3761 31469841
01-JUL-08 01-JUL-08 3989 29984914
01-JUL-08 01-JUL-08 4051 29976566
01-JUL-08 01-JUL-08 4088 29995820
01-JUL-08 01-JUL-08 4157 30126432
06-JUL-08 06-JUL-08 4183 31484130
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
02-JUL-08 02-JUL-08 4255 30356062
05-JUL-08 05-JUL-08 4462 31204000
07-JUL-08 07-JUL-08 4487 31771849
07-JUL-08 07-JUL-08 5574 31763716
05-JUL-08 05-JUL-08 6016 31259308
04-JUL-08 04-JUL-08 6210 30959715
01-JUL-08 01-JUL-08 6564 30015377
那就是在最高峰的时候才6864个块，一个块8K,那就是最高峰才用53M，怎么可能呢？
请大家帮忙解释下
大家帮我看看我理解的好像有错误这里的Created Undo Segment _SYSSMU60$好像不是说创建了回滚段。 [/quote]
明白了，回滚段是自动管理，就是当客户端有个发起个事务可以分配个回顾段，当这个事物完成，那么就会撤销的，例如：Segment _SYSSMU60$分配完成后事务完成时要回收的。下次再有事务再分配。
但是这个问题又别不解了。
select begin_time,end_time,undoblks,txncount from v$undostat order by undoblks
03-JUL-08 03-JUL-08 1894 30672651
07-JUL-08 07-JUL-08 1913 31833724
03-JUL-08 03-JUL-08 1919 30546365
05-JUL-08 05-JUL-08 1922 31172400
06-JUL-08 06-JUL-08 1944 31508141
06-JUL-08 06-JUL-08 2011 31462651
05-JUL-08 05-JUL-08 2016 31209906
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
02-JUL-08 02-JUL-08 2016 30348926
04-JUL-08 04-JUL-08 2141 30875068
07-JUL-08 07-JUL-08 2154 31694714
07-JUL-08 07-JUL-08 2158 31709389
03-JUL-08 03-JUL-08 2177 30551908
07-JUL-08 07-JUL-08 2241 31702830
04-JUL-08 04-JUL-08 2251 30966317
01-JUL-08 01-JUL-08 2322 29958939
03-JUL-08 03-JUL-08 2337 30523888
03-JUL-08 03-JUL-08 2344 30622455
03-JUL-08 03-JUL-08 2379 30587634
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
03-JUL-08 03-JUL-08 2400 30511649
01-JUL-08 01-JUL-08 2441 29969726
07-JUL-08 07-JUL-08 2449 31729782
05-JUL-08 05-JUL-08 2451 31195999
04-JUL-08 04-JUL-08 2462 30916138
03-JUL-08 03-JUL-08 2473 30536939
02-JUL-08 02-JUL-08 2488 30247604
02-JUL-08 02-JUL-08 2501 30241055
06-JUL-08 06-JUL-08 2529 31573117
06-JUL-08 06-JUL-08 2560 31477201
05-JUL-08 05-JUL-08 2566 31270494
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
05-JUL-08 05-JUL-08 2593 31186099
03-JUL-08 03-JUL-08 2593 30654337
07-JUL-08 07-JUL-08 2653 31724106
02-JUL-08 02-JUL-08 2655 30314383
06-JUL-08 06-JUL-08 2731 31502914
01-JUL-08 01-JUL-08 2813 29964830
01-JUL-08 01-JUL-08 2850 30116037
06-JUL-08 06-JUL-08 2890 31453617
05-JUL-08 05-JUL-08 2892 31190954
03-JUL-08 03-JUL-08 2968 30598199
04-JUL-08 04-JUL-08 2998 30866592
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
06-JUL-08 06-JUL-08 3029 31529913
02-JUL-08 02-JUL-08 3054 30321925
03-JUL-08 03-JUL-08 3109 30766327
02-JUL-08 02-JUL-08 3124 30388630
04-JUL-08 04-JUL-08 3126 30939939
04-JUL-08 04-JUL-08 3157 30935585
04-JUL-08 04-JUL-08 3196 30946858
06-JUL-08 06-JUL-08 3198 31524596
06-JUL-08 06-JUL-08 3258 31434989
04-JUL-08 04-JUL-08 3268 31021413
04-JUL-08 04-JUL-08 3328 30897242
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
07-JUL-08 07-JUL-08 3396 31736486
06-JUL-08 06-JUL-08 3414 31428878
01-JUL-08 01-JUL-08 3447 30110744
07-JUL-08 07-JUL-08 3524 31827183
04-JUL-08 04-JUL-08 3602 30884150
06-JUL-08 06-JUL-08 3761 31469841
01-JUL-08 01-JUL-08 3989 29984914
01-JUL-08 01-JUL-08 4051 29976566
01-JUL-08 01-JUL-08 4088 29995820
01-JUL-08 01-JUL-08 4157 30126432
06-JUL-08 06-JUL-08 4183 31484130
BEGIN_TIM END_TIME UNDOBLKS TXNCOUNT
--------- --------- ---------- ----------
02-JUL-08 02-JUL-08 4255 30356062
05-JUL-08 05-JUL-08 4462 31204000
07-JUL-08 07-JUL-08 4487 31771849
07-JUL-08 07-JUL-08 5574 31763716
05-JUL-08 05-JUL-08 6016 31259308
04-JUL-08 04-JUL-08 6210 30959715
01-JUL-08 01-JUL-08 6564 30015377
那就是在最高峰的时候才6864个块，一个块8K,那就是最高峰才用53M，怎么可能呢？
请大家帮忙解释下
2008-7-7 16:29
howard_zhang
select
to_char(begin_time,'yyyy-mm-dd hh24:mi:ss'),
to_char(end_time,'yyyy-mm-dd hh24:mi:ss'),
undoblks,txncount from v$undostat order by undoblks;
把精确的时间打出来
to_char(begin_time,'yyyy-mm-dd hh24:mi:ss'),
to_char(end_time,'yyyy-mm-dd hh24:mi:ss'),
undoblks,txncount from v$undostat order by undoblks;
把精确的时间打出来
2008-7-7 16:48
howard_zhang
另外检查一下你回滚段的使用情况
SELECT r.tablespace_name,r.segment_name, s.WAITS,s.WRAPS,s.status,s.extents, s.rssize,s.WRITES,s.SHRINKS
FROM v$rollname n,v$rollstat s,dba_rollback_segs r
WHERE n.usn = s.usn and r.segment_name=n.name;
SELECT r.tablespace_name,r.segment_name, s.WAITS,s.WRAPS,s.status,s.extents, s.rssize,s.WRITES,s.SHRINKS
FROM v$rollname n,v$rollstat s,dba_rollback_segs r
WHERE n.usn = s.usn and r.segment_name=n.name;
2008-7-8 08:49
shilei1
select
to_char(begin_time,'yyyy-mm-dd hh24:mi:ss'),
to_char(end_time,'yyyy-mm-dd hh24:mi:ss'),
undoblks,txncount from v$undostat order by undoblks;
。
。
。
。
。
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-02 14:07:53 2008-07-02 14:17:53 2016 30348926
2008-07-04 10:27:53 2008-07-04 10:37:53 2141 30875068
2008-07-07 09:37:53 2008-07-07 09:47:53 2154 31694714
2008-07-07 10:07:53 2008-07-07 10:17:53 2158 31709389
2008-07-03 10:07:53 2008-07-03 10:17:53 2177 30551908
2008-07-07 09:57:53 2008-07-07 10:07:53 2241 31702830
2008-07-04 12:57:53 2008-07-04 13:07:53 2251 30966317
2008-07-01 09:57:53 2008-07-01 10:07:53 2322 29958939
2008-07-03 09:17:53 2008-07-03 09:27:53 2337 30523888
2008-07-03 12:37:53 2008-07-03 12:47:53 2344 30622455
2008-07-03 11:07:53 2008-07-03 11:17:53 2379 30587634
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-03 08:47:53 2008-07-03 08:57:53 2400 30511649
2008-07-01 10:17:53 2008-07-01 10:27:53 2441 29969726
2008-07-07 10:37:53 2008-07-07 10:47:53 2449 31729782
2008-07-05 10:57:53 2008-07-05 11:07:53 2451 31195999
2008-07-04 11:37:53 2008-07-04 11:47:53 2462 30916138
2008-07-03 09:37:53 2008-07-03 09:47:53 2473 30536939
2008-07-02 09:47:53 2008-07-02 09:57:53 2488 30247604
2008-07-02 09:27:53 2008-07-02 09:37:53 2501 30241055
2008-07-06 16:17:53 2008-07-06 16:27:53 2529 31573117
2008-07-06 10:57:53 2008-07-06 11:07:53 2560 31477201
2008-07-05 13:47:53 2008-07-05 13:57:53 2566 31270494
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-05 10:37:53 2008-07-05 10:47:53 2593 31186099
2008-07-03 14:27:53 2008-07-03 14:37:53 2593 30654337
2008-07-07 10:27:53 2008-07-07 10:37:53 2653 31724106
2008-07-02 12:27:53 2008-07-02 12:37:53 2655 30314383
2008-07-06 11:37:53 2008-07-06 11:47:53 2731 31502914
2008-07-01 10:07:53 2008-07-01 10:17:53 2813 29964830
2008-07-01 15:37:53 2008-07-01 15:47:53 2850 30116037
2008-07-06 10:17:53 2008-07-06 10:27:53 2890 31453617
2008-07-05 10:47:53 2008-07-05 10:57:53 2892 31190954
2008-07-03 11:27:53 2008-07-03 11:37:53 2968 30598199
2008-07-04 10:17:53 2008-07-04 10:27:53 2998 30866592
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-06 12:57:53 2008-07-06 13:07:53 3029 31529913
2008-07-02 12:37:53 2008-07-02 12:47:53 3054 30321925
2008-07-03 22:57:53 2008-07-03 23:07:53 3109 30766327
2008-07-02 15:17:53 2008-07-02 15:27:53 3124 30388630
2008-07-04 12:27:53 2008-07-04 12:37:53 3126 30939939
2008-07-04 12:17:53 2008-07-04 12:27:53 3157 30935585
2008-07-04 12:37:53 2008-07-04 12:47:53 3196 30946858
2008-07-06 12:37:53 2008-07-06 12:47:53 3198 31524596
2008-07-06 09:37:53 2008-07-06 09:47:53 3258 31434989
2008-07-04 14:47:53 2008-07-04 14:57:53 3268 31021413
2008-07-04 10:57:53 2008-07-04 11:07:53 3328 30897242
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-07 10:47:53 2008-07-07 10:57:53 3396 31736486
2008-07-06 09:27:53 2008-07-06 09:37:53 3414 31428878
2008-07-01 15:27:53 2008-07-01 15:37:53 3447 30110744
2008-07-07 14:07:53 2008-07-07 14:17:53 3524 31827183
2008-07-04 10:37:53 2008-07-04 10:47:53 3602 30884150
2008-07-06 10:47:53 2008-07-06 10:57:53 3761 31469841
2008-07-01 10:37:53 2008-07-01 10:47:53 3989 29984914
2008-07-01 10:27:53 2008-07-01 10:37:53 4051 29976566
2008-07-01 10:57:53 2008-07-01 11:07:53 4088 29995820
2008-07-01 15:57:53 2008-07-01 16:07:53 4157 30126432
2008-07-06 11:07:53 2008-07-06 11:17:53 4183 31484130
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-02 14:17:53 2008-07-02 14:27:53 4255 30356062
2008-07-05 11:07:53 2008-07-05 11:17:53 4462 31204000
2008-07-07 11:47:53 2008-07-07 11:57:53 4487 31771849
2008-07-07 11:37:53 2008-07-07 11:47:53 5574 31763716
2008-07-05 12:57:53 2008-07-05 13:07:53 6016 31259308
2008-07-04 12:47:53 2008-07-04 12:57:53 6210 30959715
2008-07-01 11:37:53 2008-07-01 11:47:53 6564 30015377
1008 rows selected.
to_char(begin_time,'yyyy-mm-dd hh24:mi:ss'),
to_char(end_time,'yyyy-mm-dd hh24:mi:ss'),
undoblks,txncount from v$undostat order by undoblks;
。
。
。
。
。
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-02 14:07:53 2008-07-02 14:17:53 2016 30348926
2008-07-04 10:27:53 2008-07-04 10:37:53 2141 30875068
2008-07-07 09:37:53 2008-07-07 09:47:53 2154 31694714
2008-07-07 10:07:53 2008-07-07 10:17:53 2158 31709389
2008-07-03 10:07:53 2008-07-03 10:17:53 2177 30551908
2008-07-07 09:57:53 2008-07-07 10:07:53 2241 31702830
2008-07-04 12:57:53 2008-07-04 13:07:53 2251 30966317
2008-07-01 09:57:53 2008-07-01 10:07:53 2322 29958939
2008-07-03 09:17:53 2008-07-03 09:27:53 2337 30523888
2008-07-03 12:37:53 2008-07-03 12:47:53 2344 30622455
2008-07-03 11:07:53 2008-07-03 11:17:53 2379 30587634
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-03 08:47:53 2008-07-03 08:57:53 2400 30511649
2008-07-01 10:17:53 2008-07-01 10:27:53 2441 29969726
2008-07-07 10:37:53 2008-07-07 10:47:53 2449 31729782
2008-07-05 10:57:53 2008-07-05 11:07:53 2451 31195999
2008-07-04 11:37:53 2008-07-04 11:47:53 2462 30916138
2008-07-03 09:37:53 2008-07-03 09:47:53 2473 30536939
2008-07-02 09:47:53 2008-07-02 09:57:53 2488 30247604
2008-07-02 09:27:53 2008-07-02 09:37:53 2501 30241055
2008-07-06 16:17:53 2008-07-06 16:27:53 2529 31573117
2008-07-06 10:57:53 2008-07-06 11:07:53 2560 31477201
2008-07-05 13:47:53 2008-07-05 13:57:53 2566 31270494
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-05 10:37:53 2008-07-05 10:47:53 2593 31186099
2008-07-03 14:27:53 2008-07-03 14:37:53 2593 30654337
2008-07-07 10:27:53 2008-07-07 10:37:53 2653 31724106
2008-07-02 12:27:53 2008-07-02 12:37:53 2655 30314383
2008-07-06 11:37:53 2008-07-06 11:47:53 2731 31502914
2008-07-01 10:07:53 2008-07-01 10:17:53 2813 29964830
2008-07-01 15:37:53 2008-07-01 15:47:53 2850 30116037
2008-07-06 10:17:53 2008-07-06 10:27:53 2890 31453617
2008-07-05 10:47:53 2008-07-05 10:57:53 2892 31190954
2008-07-03 11:27:53 2008-07-03 11:37:53 2968 30598199
2008-07-04 10:17:53 2008-07-04 10:27:53 2998 30866592
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-06 12:57:53 2008-07-06 13:07:53 3029 31529913
2008-07-02 12:37:53 2008-07-02 12:47:53 3054 30321925
2008-07-03 22:57:53 2008-07-03 23:07:53 3109 30766327
2008-07-02 15:17:53 2008-07-02 15:27:53 3124 30388630
2008-07-04 12:27:53 2008-07-04 12:37:53 3126 30939939
2008-07-04 12:17:53 2008-07-04 12:27:53 3157 30935585
2008-07-04 12:37:53 2008-07-04 12:47:53 3196 30946858
2008-07-06 12:37:53 2008-07-06 12:47:53 3198 31524596
2008-07-06 09:37:53 2008-07-06 09:47:53 3258 31434989
2008-07-04 14:47:53 2008-07-04 14:57:53 3268 31021413
2008-07-04 10:57:53 2008-07-04 11:07:53 3328 30897242
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-07 10:47:53 2008-07-07 10:57:53 3396 31736486
2008-07-06 09:27:53 2008-07-06 09:37:53 3414 31428878
2008-07-01 15:27:53 2008-07-01 15:37:53 3447 30110744
2008-07-07 14:07:53 2008-07-07 14:17:53 3524 31827183
2008-07-04 10:37:53 2008-07-04 10:47:53 3602 30884150
2008-07-06 10:47:53 2008-07-06 10:57:53 3761 31469841
2008-07-01 10:37:53 2008-07-01 10:47:53 3989 29984914
2008-07-01 10:27:53 2008-07-01 10:37:53 4051 29976566
2008-07-01 10:57:53 2008-07-01 11:07:53 4088 29995820
2008-07-01 15:57:53 2008-07-01 16:07:53 4157 30126432
2008-07-06 11:07:53 2008-07-06 11:17:53 4183 31484130
TO_CHAR(BEGIN_TIME, TO_CHAR(END_TIME,'Y UNDOBLKS TXNCOUNT
------------------- ------------------- ---------- ----------
2008-07-02 14:17:53 2008-07-02 14:27:53 4255 30356062
2008-07-05 11:07:53 2008-07-05 11:17:53 4462 31204000
2008-07-07 11:47:53 2008-07-07 11:57:53 4487 31771849
2008-07-07 11:37:53 2008-07-07 11:47:53 5574 31763716
2008-07-05 12:57:53 2008-07-05 13:07:53 6016 31259308
2008-07-04 12:47:53 2008-07-04 12:57:53 6210 30959715
2008-07-01 11:37:53 2008-07-01 11:47:53 6564 30015377
1008 rows selected.
2008-7-8 08:54
shilei1
SELECT r.tablespace_name,r.segment_name, s.WAITS,s.WRAPS,s.status,s.extents, s.rssize,s.WRITES,s.SHRINKS
FROM v$rollname n,v$rollstat s,dba_rollback_segs r
WHERE n.usn = s.usn and r.segment_name=n.name;
TABLESPACE_NAME SEGMENT_NAME WAITS WRAPS STATUS EXTENTS RSSIZE WRITES SHRINKS
------------------------------ ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- ----------
SYSTEM SYSTEM 0 5 ONLINE 6 385024 281726 0
UNDOTBS2 _SYSSMU42$ 1190 9310 ONLINE 8 6414336 3430464132 1305
UNDOTBS2 _SYSSMU43$ 615 6627 ONLINE 5 3268608 1755168420 746
UNDOTBS2 _SYSSMU44$ 510 6388 ONLINE 6 4317184 2781862826 714
UNDOTBS2 _SYSSMU45$ 558 6203 ONLINE 6 4317184 2300743578 692
UNDOTBS2 _SYSSMU46$ 736 6443 ONLINE 5 3268608 2662837360 732
UNDOTBS2 _SYSSMU47$ 622 6563 ONLINE 8 6414336 4125811450 751
UNDOTBS2 _SYSSMU48$ 652 6442 ONLINE 6 4317184 92337620 735
UNDOTBS2 _SYSSMU49$ 603 6456 ONLINE 8 6414336 3491453306 729
UNDOTBS2 _SYSSMU50$ 600 6257 ONLINE 8 6414336 2335411918 735
UNDOTBS2 _SYSSMU51$ 536 6217 ONLINE 4 2220032 2007386770 723
11 rows selected.
SQL>
FROM v$rollname n,v$rollstat s,dba_rollback_segs r
WHERE n.usn = s.usn and r.segment_name=n.name;
TABLESPACE_NAME SEGMENT_NAME WAITS WRAPS STATUS EXTENTS RSSIZE WRITES SHRINKS
------------------------------ ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- ----------
SYSTEM SYSTEM 0 5 ONLINE 6 385024 281726 0
UNDOTBS2 _SYSSMU42$ 1190 9310 ONLINE 8 6414336 3430464132 1305
UNDOTBS2 _SYSSMU43$ 615 6627 ONLINE 5 3268608 1755168420 746
UNDOTBS2 _SYSSMU44$ 510 6388 ONLINE 6 4317184 2781862826 714
UNDOTBS2 _SYSSMU45$ 558 6203 ONLINE 6 4317184 2300743578 692
UNDOTBS2 _SYSSMU46$ 736 6443 ONLINE 5 3268608 2662837360 732
UNDOTBS2 _SYSSMU47$ 622 6563 ONLINE 8 6414336 4125811450 751
UNDOTBS2 _SYSSMU48$ 652 6442 ONLINE 6 4317184 92337620 735
UNDOTBS2 _SYSSMU49$ 603 6456 ONLINE 8 6414336 3491453306 729
UNDOTBS2 _SYSSMU50$ 600 6257 ONLINE 8 6414336 2335411918 735
UNDOTBS2 _SYSSMU51$ 536 6217 ONLINE 4 2220032 2007386770 723
11 rows selected.
SQL>
2008-7-8 09:30
shilei1
SQL> SELECT r.tablespace_name,r.segment_name, s.WAITS,s.WRAPS,s.status,s.extents, s.rssize,s.WRITES,s.SHRINKS
2 FROM v$rollname n,v$rollstat s,dba_rollback_segs r
3 WHERE n.usn = s.usn and r.segment_name=n.name;
TABLESPACE_NAME SEGMENT_NAME WAITS WRAPS STATUS EXTENTS RSSIZE WRITES SHRINKS
------------------------------ ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- ----------
SYSTEM SYSTEM 0 5 ONLINE 6 385024 281726 0
UNDOTBS2 _SYSSMU42$ 1190 9316 ONLINE 13 10674176 3434578138 1305
UNDOTBS2 _SYSSMU43$ 615 6630 ONLINE 8 6414336 1757570998 746
UNDOTBS2 _SYSSMU44$ 510 6392 ONLINE 9 6479872 2784788976 714
UNDOTBS2 _SYSSMU45$ 558 6204 ONLINE 7 5365760 2301996466 692
UNDOTBS2 _SYSSMU46$ 736 6444 ONLINE 7 5365760 2664629658 732
UNDOTBS2 _SYSSMU47$ 622 6566 ONLINE 8 6414336 4127228502 751
UNDOTBS2 _SYSSMU48$ 652 6444 ONLINE 8 6414336 94061884 735
UNDOTBS2 _SYSSMU49$ 603 6462 ONLINE 13 11657216 3496039942 729
UNDOTBS2 _SYSSMU50$ 600 6259 ONLINE 10 8511488 2337784514 735
UNDOTBS2 _SYSSMU51$ 536 6220 ONLINE 6 4317184 2008796298 723
11 rows selected.
2 FROM v$rollname n,v$rollstat s,dba_rollback_segs r
3 WHERE n.usn = s.usn and r.segment_name=n.name;
TABLESPACE_NAME SEGMENT_NAME WAITS WRAPS STATUS EXTENTS RSSIZE WRITES SHRINKS
------------------------------ ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- ----------
SYSTEM SYSTEM 0 5 ONLINE 6 385024 281726 0
UNDOTBS2 _SYSSMU42$ 1190 9316 ONLINE 13 10674176 3434578138 1305
UNDOTBS2 _SYSSMU43$ 615 6630 ONLINE 8 6414336 1757570998 746
UNDOTBS2 _SYSSMU44$ 510 6392 ONLINE 9 6479872 2784788976 714
UNDOTBS2 _SYSSMU45$ 558 6204 ONLINE 7 5365760 2301996466 692
UNDOTBS2 _SYSSMU46$ 736 6444 ONLINE 7 5365760 2664629658 732
UNDOTBS2 _SYSSMU47$ 622 6566 ONLINE 8 6414336 4127228502 751
UNDOTBS2 _SYSSMU48$ 652 6444 ONLINE 8 6414336 94061884 735
UNDOTBS2 _SYSSMU49$ 603 6462 ONLINE 13 11657216 3496039942 729
UNDOTBS2 _SYSSMU50$ 600 6259 ONLINE 10 8511488 2337784514 735
UNDOTBS2 _SYSSMU51$ 536 6220 ONLINE 6 4317184 2008796298 723
11 rows selected.
页: [1]
查看完整版本: 在BDUMP发现回滚很多扩充，但是回滚表空间很闲呀。什么原因呢？
Powered by ITPUB论坛