INSERT时 减少REDO 生成

Posted: 九月 8, 2011 in archive

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

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s