利用tom的SHOW_SPACE脚本实验验证表插入删除带来的redo和工作原理

来源：互联网发布：ip 网络层编辑：程序博客网时间：2024/04/29 03:39

之前创建的一个大表，现在拿来用一下：

big_table源程序脚本:

BIG_TABLE

create table big_table

select rownum id, a.*

from all_objects a

where 1=0

alter table big_table nologging;

declare

l_cnt number;

l_rows number := &1;

begin

insert /*+ append */

into big_table

select rownum, a.*

from all_objects a

where rownum <= &1;

l_cnt := sql%rowcount;

commit;

while (l_cnt < l_rows)

loop

insert /*+ APPEND */ into big_table

select rownum+l_cnt,

OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID,DATA_OBJECT_ID,

OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP,STATUS,

TEMPORARY, GENERATED, SECONDARY, NAMESPACE,EDITION_NAME

from big_table

where rownum <= l_rows-l_cnt;

l_cnt := l_cnt + sql%rowcount;

commit;

end loop;

end;

alter table big_table add constraint

big_table_pk primary key(id);

exec dbms_stats.gather_table_stats( user,'BIG_TABLE', estimate_percent=> 1);

15:51:14 scott@felix SQL>update big_table setowner = lower(owner)

15:51:30 2 where rownum<=1000;

1000 rows updated.

15:51:34 scott@felix SQL>@mystat2

NAME VDIFF

-------------------------------------------------- --------------------------------

redo size 621028 621,028

redo size for lost write detection 0 0

redo size for direct writes 0 0

注意：上面是更新1000行，所产生的redo，但是redosize for direct writes的值却是0，说明没有执行直接路径加载（比如：append操作）

现在，我创建一张表MYTEST：

、

15:58:03 scott@felix SQL>create table mytest asselect * from dba_objects;

Table created.

查看表产生的redo：

15:58:38 scott@felix SQL>@/home/oracle/mystat "redo size";

NAME VALUE

--------------------------------------------------

redo size 9629724

redo size for lost write detection 0

redo size for direct writes 8837136

注意：此时redo size for direct writes的值8837136。说明CTAS操作也是直接路径加载的操作；

15:58:45 scott@felix SQL>commit;

Commit complete.

15:59:02 scott@felix SQL>@mystat2

NAME VDIFF

---------------------------------------- ------------------------------------------

redo size 9629724 792,588

redo size for lost write detection 0 -8,837,136

redo size for direct writes 8837136 0

重做大小的写检测增大了，为什么？？

接着看一下实验：

15:59:17 scott@felix SQL>executeshow_space('MYTEST');

Unformatted Blocks ..................... 0

FS1 Blocks (0-25) ...................... 0

FS2 Blocks (25-50) ..................... 0

FS3 Blocks (50-75) ..................... 0

FS4 Blocks(75-100)..................... 0

Full Blocks............................ 1,074 --所有数据都是满块；

Total Blocks............................ 1,152

Total Bytes............................. 9,437,184

Total MBytes............................ 9

Unused Blocks........................... 52

Unused Bytes............................ 425,984

Last Used Ext FileId.................... 4

Last Used Ext BlockId................... 1,664

Last Used Block......................... 76

Unformatted Blocks=0 --说明表中高水位以下数据都已使用，即该表可能未做update、delete等操作，升高水位；

此时，FS4还是0，

• Unformatted Blocks–为表分配的位于高水位线之下但未用的块数。把未格式化和未用的块加在一起，就是已为表分配但从未用于保存ASSM对象数据的总块数。

• FS1 Blocks-FS4 Blocks–包含数据的格式化块，项名后的数据区间表示各块的“空闲度”。

• Full Blocks –已满的块数，不能再对这些块执行插入。

• Total Blocks, Total Bytes, Total Mbytes–为所查看的端分配的总空间量，单位分别是数据库块、字节、兆字节。

• Unused Blocks, Unused Bytes–表示未用的空间所占的比例（未用空间量）。这些块已经分配给所查看的段。但目前在段的高水位线之上。

• Last Used Ext FileId–包含最后一个区块（其中包含数据）的文件的文件ID。.

• Last Used Ext BlockId–最后一个区段开始处的块ID，这是最后使用的文件中块的ID。

• Last Used Block–最后一个区段中最后一个块的偏移量。

然后接着做插入操作：

16:01:30 scott@felix SQL>insert into mytestselect * from mytest;

75465 rows created.

16:03:37 scott@felix SQL>/

150930 rows created.

16:03:56 scott@felix SQL>/

301860 rows created.

再查看表空间情况：

16:05:03 scott@felix SQL>executeshow_space('MYTEST');

Unformatted Blocks ..................... 496

FS1 Blocks (0-25) ...................... 1

FS2 Blocks (25-50) ..................... 1

FS3 Blocks (50-75) ..................... 0

FS4 Blocks (75-100)..................... 20

Full Blocks ............................ 8,559

Total Blocks............................ 9,216

Total Bytes............................. 75,497,472

Total MBytes............................ 72

Unused Blocks........................... 0

Unused Bytes............................ 0

Last Used Ext FileId.................... 4

Last Used Ext BlockId................... 9,984

Last Used Block......................... 1,024

PL/SQL procedure successfully completed.

经过多次插入，可以发现，有FS4 Blocks(75-100)= 20，20个块的空闲度是在75~100%的，Unformatted Blocks = 496说明数据库为该段又另外分配了一个区（extent），还有496个空闲块可用；

16:06:01 scott@felix SQL>@mystat2

NAME V DIFF

-------------------------------------------------- --------------------------------

redo size 71041768 62,204,632

redo size for lost write detection 0 -8,837,136

redo size for direct writes 8837136 0

再观察redo size for directwrites = 8837136，大小未变，说明isnert select 操作不是直接路径加载。

并且redo size for lostwrite detection= -8,837,136，也未有改变，说明产生的redo是全部写入在线redo。

再继续往下做实验：

在此，我们删除部分数据从mytest表中：

16:08:34 scott@felix SQL>delete from mytest where rownum<=150930;

150930 rows deleted.

16:09:13 scott@felix SQL>commit;

Commit complete.

因为在未删除数据之前数据都是直接按时间插入表中的，所以几乎每个块都是满的，因此在此我们做实验，验证删除之后应该有大量的未满的块产生：

16:09:19 scott@felix SQL>execute show_space('MYTEST');

Unformatted Blocks ..................... 496

FS1 Blocks (0-25) ...................... 1

FS2 Blocks (25-50) ..................... 2

FS3 Blocks (50-75) ..................... 0

FS4 Blocks (75-100)..................... 2,162

Full Blocks ............................ 6,416

Total Blocks............................ 9,216

Total Bytes............................. 75,497,472

Total MBytes............................ 72

Unused Blocks........................... 0

Unused Bytes............................ 0

Last Used Ext FileId.................... 4

Last Used Ext BlockId................... 9,984

Last Used Block......................... 1,024

PL/SQL procedure successfully completed.

16:09:37 scott@felix SQL>

由上面结果可知，在0~25和0~50%的空闲块数量很少，这个事为什么呢？

由于oracle在执行插入数据时，数据在磁盘的分布是按照插入时间顺序，逐步写的，因为我删除时是按照rownum<1000进行删除的，所以，该部分数据在块上的分布几乎是均匀的，所以，75~100%空闲块的数量占很多；

0 0