oracle的表日志删除了,测试oracle插入 删除 更新表的日志开销

###############测试插入 删除 更新表的日志开销

创建表

SYS@orcl >create table ssr(id int);

查询表空间，sys默认system,插入数据后此分配空间

SYS@orcl >select tablespace_name, segment_name from user_segments where segment_name='SSR';

TABLESPACE_NAME            SEGMENT_NAME

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

SYSTEM                   SSR

创建空表

GUOYF@orcl 09:20:01>create table aa as select * from all_objects where 1=0;

查询现有日志大小

select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';

NAME                                      VALUE

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

redo size                                 254388

插入数据

GUOYF@orcl 09:24:01>insert into aa select * from all_objects where rownum<100;

查询日志大小

select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';

NAME                                      VALUE

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

redo size                                 268768

更新数据

GUOYF@orcl 09:27:47>update aa set owner='guoyf' where owner='SYS';

查看日志大小

select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';

NAME                                      VALUE

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

redo size                                294612

删除数据

GUOYF@orcl 09:29:10>delete from aa where owner='guoyf';

查看日志大小

select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';

NAME                                      VALUE

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

redo size                                 330832

通过以上数据表明产生日志量

删除》更新》插入

AUTOTRACE是一个SQL*Plus工具,用于跟踪SQL的执行计划,收集执行时所耗用资源的统计信息

set autotrace off :缺省值，将不生成autotrace 报告

set autotrace on :包含执行计划和统计信息

set autotrace traceonly :等同于set autotrace on，但不显示查询输出的结果

set autotrace on explain :只显示优化器执行路径报告

set autotrace on statistics :只显示执行统计信息

#######测试delete无法释放表空间

创建表

SQL> create table bb as select * from dba_objects;

开启啊autotrace

SQL> set autotrace on ;

##查看I/O

SQL> select count(*) from bb;

COUNT(*)

----------

72573

Execution Plan

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

Plan hash value: 162157452

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

| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |

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

|   0 | SELECT STATEMENT   |      |    1 |   290   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |    1 |           |      |

|   2 |   TABLE ACCESS FULL| BB   | 69590 |   290   (1)| 00:00:04 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

28  recursive calls

0  db block gets

1106  consistent gets

1034  physical reads

0  redo size

528  bytes sent via SQL*Net to client

523  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

关闭啊autotrace

SQL> set autotrace off;

删除表

SQL> delete from bb;

72653 rows deleted.

SQL> select count(*) from bb;

COUNT(*)

----------

0

SQL> commit;

Commit complete.

SQL> set autotrace on;

SQL> select count(*) from bb;

COUNT(*)

----------

0

Execution Plan

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

Plan hash value: 162157452

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

| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |

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

|   0 | SELECT STATEMENT   |      |    1 |   289   (0)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |    1 |           |      |

|   2 |   TABLE ACCESS FULL| BB   |    1 |   289   (0)| 00:00:04 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

0  recursive calls

0  db block gets

1039  consistent gets

0  physical reads

0  redo size

525  bytes sent via SQL*Net to client

523  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> truncate table bb;

Table truncated.

SQL> select count(*) from bb;

COUNT(*)

----------

0

Execution Plan

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

Plan hash value: 162157452

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

| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |

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

|   0 | SELECT STATEMENT   |      |    1 |    2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |    1 |           |      |

|   2 |   TABLE ACCESS FULL| BB   |    1 |    2   (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

20  recursive calls

1  db block gets

10  consistent gets

0  physical reads

96  redo size

525  bytes sent via SQL*Net to client

523  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed