Archive for the ‘archive’ Category

 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

Advertisements

Mon Sep 05 14:43:45 CST 2011
Errors in file /app/admin/semis/bdump/semis1_arc1_14462.trc:
ORA-16038: log 1 sequence# 15810 cannot be archived
ORA-19504: failed to create file “”
ORA-00312: online log 1 thread 1: ‘+DATA/semis/onlinelog/group_1.262.746634879’
ORA-00312: online log 1 thread 1: ‘+RECOVERY/semis/onlinelog/group_1.260.746634885’
Mon Sep 05 14:44:44 CST 2011
ARCH: Archival stopped, error occurred. Will continue retrying

今天遭遇数据库归档错误,原因为:近期业务比较多,以至于产生的大量的归档日志,从而导致快速恢复区迅速填满,数据库停止服务.

对策:

删除较前日期的归档:

rm -rf 2011_08_16/ 2011_08_17/ 2011_08_18/ 2011_08_19/ 2011_08_20/  2011_08_21/ 2011_08_22/ 2011_08_23/

SQL> select name,free_mb from v$asm_diskgroup;

NAME                                                            FREE_MB
———————————————————— ———-
DATA                                                             806064
RECOVERY                                                         412881

数据库正常对外服务:

[oracle@db1 bdump]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Mon Sep 5 17:13:13 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select status from v$instance;

STATUS
————
OPEN

SQL> select open_mode from v$database;

OPEN_MODE
———-
READ WRITE

SQL>