happydba Rotating Header Image

insert,append,nologging生成redo数量的测试

Apr 13th, 2009
by happydba.

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Prod
PL/SQL Release 10.2.0.3.0 – Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 – Production
NLSRTL Version 10.2.0.3.0 – Production

SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     163
当前日志序列           165

SQL> select count(*) from message;

COUNT(*)
———-
637609

SQL>create table message_bak   as select * from message  where 1=2；

SQL> insert into message_bak select /*+ paralle(message,10)*/* from message;

已创建637609行。

已用时间:  00: 00: 15.61

执行计划
———————————————————-
Plan hash value: 2949668749

—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | INSERT STATEMENT  |         |   593K|    54M|  1891   (1)| 00:00:23 |
|   1 |  TABLE ACCESS FULL| MESSAGE |   593K|    54M|  1891   (1)| 00:00:23 |
—————————————————————————–

统计信息
———————————————————-
18  recursive calls
68964  db block gets
27341  consistent gets
9211  physical reads
73209200  redo size
656  bytes sent via SQL*Net to client
605  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
637609  rows processed

SQL> rollback;

回退已完成。

已用时间:  00: 00: 00.53
SQL>
SQL>
SQL> alter session enable parallel dml;

会话已更改。

已用时间:  00: 00: 00.00
SQL>
SQL> insert into message_bak select /*+ paralle(message,10)*/* from message;

已创建637609行。

已用时间:  00: 00: 11.18

执行计划
———————————————————-
Plan hash value: 2949668749

—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | INSERT STATEMENT  |         |   593K|    54M|  1891   (1)| 00:00:23 |
|   1 |  TABLE ACCESS FULL| MESSAGE |   593K|    54M|  1891   (1)| 00:00:23 |
—————————————————————————–

统计信息
———————————————————-
19  recursive calls
68521  db block gets
27191  consistent gets
9120  physical reads
73187612  redo size
669  bytes sent via SQL*Net to client
605  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
637609  rows processed

生成70多M的redo

SQL> rollback;

回退已完成。

已用时间:  00: 00: 00.28
SQL>
SQL>
SQL>
SQL> insert /*+ append*/ into message_bak select /*+ paralle(message,10)*/* from message;

已创建637609行。

已用时间:  00: 00: 05.09

执行计划
———————————————————-
ERROR:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
———————————————————-
1  recursive calls
9327  db block gets
9131  consistent gets
9120  physical reads
16272  redo size
656  bytes sent via SQL*Net to client
618  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
637609  rows processed

append模式只生成了16k日志，超级减少啊

SQL> rollback;

回退已完成。

已用时间:  00: 00: 00.00
SQL>
SQL> insert /*+ append*/ into message_bak select /*+ paralle(message,10)*/* from message nologgin;

已创建637609行。

已用时间:  00: 00: 03.93

执行计划
———————————————————-
ERROR:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
———————————————————-
1  recursive calls
9327  db block gets
9131  consistent gets
9120  physical reads
16180  redo size
657  bytes sent via SQL*Net to client
627  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
637609  rows processed

语法错误竟然不报错？

SQL> insert /*+ append*/ into message_bak select /*+ paralle(message,10)*/* from message nologging;

已创建637609行。

已用时间:  00: 00: 05.42

执行计划
———————————————————-
ERROR:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
———————————————————-
611  recursive calls
9455  db block gets
9304  consistent gets
9120  physical reads
45132  redo size
646  bytes sent via SQL*Net to client
628  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
7  sorts (memory)
0  sorts (disk)
637609  rows processed

这种方式产生的redo反而多了？


SQL> rollback;

回退已完成。

已用时间:  00: 00: 00.00
SQL>
SQL>
SQL> alter table message_bak nologging;

表已更改。

已用时间:  00: 00: 00.00
SQL>
SQL>
SQL>
SQL> insert /*+ append*/ into message_bak select /*+ paralle(message,10)*/* from message nologging;

已创建637609行。

已用时间:  00: 00: 04.42

执行计划
———————————————————-
ERROR:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
———————————————————-
205  recursive calls
9184  db block gets
9165  consistent gets
9120  physical reads
15764  redo size
650  bytes sent via SQL*Net to client
628  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
6  sorts (memory)
0  sorts (disk)
637609  rows processed

竟然比上一种产生的还要多？

SQL> rollback;

回退已完成。

已用时间:  00: 00: 00.00
SQL>
SQL>
SQL> insert /*+ append*/ into message_bak select /*+ paralle(message,10)*/* from message;

已创建637609行。

已用时间:  00: 00: 04.43

执行计划
———————————————————-
ERROR:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
———————————————————-
1  recursive calls
9184  db block gets
9131  consistent gets
9120  physical reads
15764  redo size
653  bytes sent via SQL*Net to client
618  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
637609  rows processed

这样，和table logging模式下的nologging插入产生的是一样的了。看来在table nologging模式下，insert时不需要再加nologging关键字了。

加上反而产生的redo还要增加 ？ -_-!

非归档模式下，append插入日志生成量和table的logging模式无关，直接就可以减少redo的数量。

####################################################################################################

将数据库设置为归档模式后：

SQL> insert into message_bak select /*+ parallel(message)*/ * from message;

已创建637609行。

已用时间:  00: 00: 10.92

执行计划
———————————————————-
Plan hash value: 3490384975

————————————————————————————————————–
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
————————————————————————————————————–
|   0 | INSERT STATEMENT     |          |   593K|    54M|   262   (1)| 00:00:04 |        |      |         |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |         |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |   593K|    54M|   262   (1)| 00:00:04 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |   593K|    54M|   262   (1)| 00:00:04 |  Q1,00 | PCWC |         |
|   4 |     TABLE ACCESS FULL| MESSAGE  |   593K|    54M|   262   (1)| 00:00:04 |  Q1,00 | PCWP |         |
————————————————————————————————————–

统计信息
———————————————————-
62  recursive calls
69238  db block gets
27690  consistent gets
9120  physical reads
73449316  redo size
675  bytes sent via SQL*Net to client
604  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
2  sorts (memory)
0  sorts (disk)
637609  rows processed

SQL> insert /*+ append*/ into message_bak select /*+ parallel(message)*/ * from message;

已创建637609行。

已用时间:  00: 00: 17.04

执行计划
———————————————————-
ERROR:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
———————————————————-
420  recursive calls
9595  db block gets
9579  consistent gets
9120  physical reads
75163228  redo size
659  bytes sent via SQL*Net to client
617  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
2  sorts (memory)
0  sorts (disk)
637609  rows processed

SQL> insert /*+ append*/ into message_bak select /*+ parallel(message)*/ * from message nologging;

已创建637609行。

已用时间:  00: 00: 10.74

执行计划
———————————————————-
ERROR:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
———————————————————-
2  recursive calls
9435  db block gets
9132  consistent gets
9120  physical reads
75092296  redo size
655  bytes sent via SQL*Net to client
627  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
637609  rows processed

SQL> alter table message_bak nologging;

表已更改。

已用时间:  00: 00: 00.37

SQL> insert into message_bak select /*+ parallel(message)*/ * from message;

已创建637609行。

已用时间:  00: 00: 11.79

执行计划
———————————————————-
Plan hash value: 3490384975

————————————————————————————————————–
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
————————————————————————————————————–
|   0 | INSERT STATEMENT     |          |   593K|    54M|   262   (1)| 00:00:04 |        |      |         |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |         |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |   593K|    54M|   262   (1)| 00:00:04 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |   593K|    54M|   262   (1)| 00:00:04 |  Q1,00 | PCWC |         |
|   4 |     TABLE ACCESS FULL| MESSAGE  |   593K|    54M|   262   (1)| 00:00:04 |  Q1,00 | PCWP |         |
————————————————————————————————————–

统计信息
———————————————————-
46  recursive calls
69092  db block gets
27690  consistent gets
9120  physical reads
73272792  redo size
674  bytes sent via SQL*Net to client
604  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
2  sorts (memory)
0  sorts (disk)
637609  rows processed

SQL>
SQL>
SQL>
SQL> insert /*+ append*/ into message_bak select /*+ parallel(message)*/ * from message;

已创建637609行。

已用时间:  00: 00: 10.39

执行计划
———————————————————-
ERROR:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
———————————————————-
250  recursive calls
9330  db block gets
9479  consistent gets
9120  physical reads
17000  redo size
659  bytes sent via SQL*Net to client
617  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
7  sorts (memory)
0  sorts (disk)
637609  rows processed

看来归档模式下，只有将table nologging之后，使用append方式才能减少redo的产生。

SQL> insert into message_bak select /*+ parallel(message)*/ * from message nologging;

已创建637609行。

已用时间:  00: 00: 07.88

执行计划
———————————————————-
Plan hash value: 2949668749

—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | INSERT STATEMENT  |         |   593K|    54M|  1891   (1)| 00:00:23 |
|   1 |  TABLE ACCESS FULL| MESSAGE |   593K|    54M|  1891   (1)| 00:00:23 |
—————————————————————————–

统计信息
———————————————————-
19  recursive calls
69055  db block gets
27382  consistent gets
9120  physical reads
73211240  redo size
675  bytes sent via SQL*Net to client
614  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
637609  rows processed

SQL> insert /*+append*/ into message_bak select /*+ parallel(message)*/ * from message nologging;

已创建637609行。

已用时间:  00: 00: 03.84

执行计划
———————————————————-
ERROR:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

统计信息
———————————————————-
1  recursive calls
9327  db block gets
9131  consistent gets
9120  physical reads
16136  redo size
659  bytes sent via SQL*Net to client
626  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
637609  rows processed

Posted in: oracle, tuning.
Tagged: · · ·

←
→

Leave a Reply

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Powered by WP Hashcash

Videos, Slideshows and Podcasts by Cincopa Wordpress Plugin