oracle dml操作生成redo、undo测试

最新推荐文章于 2022-05-10 15:33:49 发布

zpil321

最新推荐文章于 2022-05-10 15:33:49 发布

阅读量847

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/waitlonelycherry/article/details/9746665

版权

本文通过一系列的DML操作，包括INSERT、UPDATE、DELETE，测试并分析了这些操作如何影响Oracle数据库的redo和undo日志的大小。实验结果显示，INSERT、UPDATE和DELETE操作分别导致redo和undo大小显著变化，揭示了不同操作对数据库日志资源的影响。

摘要由CSDN通过智能技术生成

1.建立查询redo、undo大小的视图：

测量redo脚本：

create or replace view v_measure_redo_size

as select name, value

from v$mystat, v$statname

where v$mystat.statistic# = v$statname.statistic#

and v$statname.name = 'redo size';

测量redo、undo脚本：

create or replace view v_measure_redo_undo_size

as select name, value

from v$mystat, v$statname

where v$mystat.statistic# = v$statname.statistic#

and (v$statname.name = 'redo size' or

v$statname.name = 'undo change vector size');

2.创建基于session与事务的临时表

drop table t_session purge;

drop table t_transaction purge;

create global temporary table t_session on commit preserve rows

as select * from dba_objects where 1<>1;

create global temporary table t_transaction on commit delete rows

as select * from dba_objects where 1<>1;

3.insert操作生成日志大小测试

select * from v_measure_redo_undo_size;

SQL> select * from v_measure_redo_undo_size;

NAME VALUE
---------------------------------------------------------------- ----------
redo size 20244
undo change vector size 5452

SQL> insert into t_session select * from dba_objects;

已创建50338行。

SQL> select * from v_measure_redo_undo_size;

NAME VALUE
---------------------------------------------------------------- ----------
redo size 293836
undo change vector size 185864

SQL> select (185864-5452)/1024/1024 undo,(293836-20244)/1024/1024 redo from dual;

UNDO REDO
-------------- ----------
.172054291 .260917664

SQL> select * from v_measure_redo_undo_size;

NAME VALUE
---------------------------------------------------------------- ----------
redo size