实验一：延迟块清除的产生
1.先构建一个1万条数据的表
create table test as select * from dba_objects where rownum<10001;
2.使用循环来插入数据。
初始数据1W条，则每次循环后表内数据量依次是2，4 ，8 ，16 ，32 ，64 ，128， 256，512，1024，需要10次循环。
begin
for i in 1 .. 10 loop
insert into test select * from test;
commit;
end loop;
end;
3.执行查询，执行计划中可以看到 2204 redo size
SQL> select count(*) from test;
COUNT(*)
———-
5120000
SQL> select count(1) from dba_indexes where table_name=’TEST’; —没有索引
COUNT(1)
———-
0
SQL> alter system flush buffer_cache; —清空buffer_cache
System altered.
SQL> set autotrace on
SQL> select count(*) from test where object_name like ‘%gc%’;
COUNT(*)
———-
0
Execution Plan
省略。。。
Statistics
———————————————————-
5 recursive calls
0 db block gets
101073 consistent gets
67716 physical reads
2204 redo size
421 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
实验二：延迟块清除的避免
因为延迟块清除有可能会导致ORA-01555错误(操作大数据时），所以应该避免。
在操作大数据量的DML操作后做一个select 操作。
建表并使用存储过程插入数据
SQL> create table test as select * from dba_objects where rownum<10001;
Table created.
SQL>
declare
v_select clob;
begin
for i in 1 .. 5 loop
insert into test select * from test;
commit;
select count(*) into v_select from test;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> set autotrace on;
SQL> alter system flush buffer_cache;
从查询结果中可以看到， 0 redo size，没有产生REDO。
SQL> select count(*) from test where object_name like ‘%gc%’;
COUNT(*)
———-
0
Execution Plan
省略。。。
Statistics
———————————————————-
5 recursive calls
0 db block gets
4212 consistent gets
4363 physical reads
0 redo size
421 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
