普通表与临时表DML操作会产生REDO/UNDO对比与分析(二)
ame='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 813924652
undo change vector size 30676180
提交前后的REDO/UNDO大小变化
BYS@bys1>commit;
Commit complete.
Elapsed: 00:00:00.05
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 813924888
undo change vector size 30676180
查询前后的REDO/UNDO大小变化：
第一次查询产生REDO是因为延迟块清除：
BYS@bys1>set autotrace on
BYS@bys1>select count(*) from test1;
COUNT(*)
----------
6957120
Elapsed: 00:01:38.73
Execution Plan
----------------------------------------------------------
Plan hash value: 3896847026
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26827 (1)| 00:05:22 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST1 | 7495K| 26827 (1)| 00:05:22 |
--------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
----------------------------------------------------------
29 recursive calls
1 db block gets
198000 consistent gets
99253 physical reads
5000 redo size
425 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
BYS@bys1>set autotrace off
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 813932848
undo change vector size 30678540
正常查询并没有产生REDO和UNDO
BYS@bys1>select count(*) from test1;
COUNT(*)
----------
6957120
Elapsed: 00:00:26.95
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 813932848
undo change vector size 30678540
统计情况如下：
create table test1 as select * from dba_objects where 1=0;语句：产生REDO/UNDO分别为： 236780 6736
insert into test1 select * from dba_objects;语句：产生REDO/UNDO分别为： 813686048 30669256
COMMIT语句：产生REDO/UNDO分别为：236和0