Oracle两个使用细节知识点

最新推荐文章于 2022-12-08 16:19:37 发布

转载最新推荐文章于 2022-12-08 16:19:37 发布 · 646 阅读

本文介绍了Oracle数据库中两个关键优化点：慎用forupdate大规模数据操作，以及数据列默认值设置的影响。对于forupdate，指出其在锁定数据时带来的性能损耗及redo日志消耗，建议合理使用避免数据表过大时的性能瓶颈。关于默认值设置，通过对比不同操作顺序的性能表现，强调了生产环境中均衡化负载的重要性，避免一次性操作导致的风险。

大量的Oracle知识点都是需要在实际中不断发现实验获得的。本篇中介绍两个实际中发现的问题，权当记录。

1、慎用for update大规模数据

Select for update是我们有时候会使用的一种语句类型。它的特点优势是能够将筛选出的数据进行独占锁定。通常情况下的Oracle select操作，是不会对数据表进行锁定，同时也不会被其他操作所阻塞。

在实际应用中，我们的确是经常使用for update进行数据锁定。常用的场景是，确定符合条件的数据是否存在，如果存在就立即锁定该条记录独占。之后对独占的数据进行修改。在存在多会话并发访问的时候，for update可以起到资源/信号量抢夺的作用。

但是，for update的使用是有相应的代价的。在之前的系列文章中，我们探讨过for update要消耗更多的时间，主要在于需要在每个数据行进行加锁操作。在Oracle[url=]11g[/url]中，CBO的Query Optimizer采用了一些优化[url=]技术[/url]进行性能提升。但是，总的来说，for update是要消耗更多的资源的。

除了上面提到的方面，在redo size上for update也是有独特之处。通常我们继续DDL和更改类型操作的时候，才会生成redo log片段。普通的select是不会生成redo log记录的。如果使用for update，我们是可以发现select会生成redo size。

SQL> select * from v$version;

BANNER

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

Oracle Database11gEnterpriseEdition Release11.2.0.1.0 - Production

PL/SQL Release11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version11.2.0.1.0 - Production

NLSRTL Version11.2.0.1.0 – Production

使用v$mystat可以看到当前会话累计的各种指标统计量。

SQL> select a.statistic#, name, [url=]value[/url] from v$mystat a left join v$statname b on a.STATISTIC#=b.STATISTIC# where b.NAME = 'redo size';

STATISTIC# NAME VALUE

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

169 redo size 0

当前，redo size生成的消耗量是0。

SQL> select bytes, blocks from dba_segments where segment_name='T' and wner='SYS';

BYTES BLOCKS

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

9437184 1152

SQL> select count(*) from t;

COUNT(*)

----------

72349

此时，我们使用一个for update语句。

SQL> select * from t where wner='SCOTT' for update;

（结果集合省略……）

此时，redo size的消耗可以查询到。

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

STATISTIC# NAME VALUE

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

169 redo size 0

SQL> commit;

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

STATISTIC# NAME VALUE

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

169 redo size 2628

一个一般的for update之后，进行commit，没有发生本质上的数据变化。但是之后，我们发现了redo size指标增加到了2628。一般的select操作，就不会产生redo size消耗。

SQL> select * from t where wner='SCOTT';

（结果集合省略……

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

STATISTIC# NAME VALUE

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

169 redo size 2628

SQL> commit;

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

STATISTIC# NAME VALUE

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

169 redo size 2628

可见，一般的select的操作是不会产生redo记录的。这个问题我们也可以使用autotrace工具来查看到。

SQL> set autotrace traceonly;

SQL> select * from t for update;

已选择72349行。

执行计划

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

Plan hash value: 250223868

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

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

| 0 | SELECT STATEMENT | | 72349 | 6853K| 282 (1)| 00:00:04 |

| 1 | FOR UPDATE | | | | | |

| 2 | BUFFER SORT | | | | | |

| 3 | [url=]TABLE[/url] ACCESS FULL| T | 72349 | 6853K| 282 (1)| 00:00:04 |

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

统计信息

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

46 recursive calls

73649 db block gets

1066 consistent gets

1026 physical reads

15086508 redo size

5649675 bytes sent via SQL*Net to client

53429 bytes received via SQL*Net from client

4825 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

72349 rows processed

SQL> select * from t;

已选择72349行。

执行计划

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 72349 | 6853K| 282 (1)| 00:00:04 |

| 1 | TABLE ACCESS FULL| T | 72349 | 6853K| 282 (1)| 00:00:04 |

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

统计信息

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

1 recursive calls

0 db block gets

5788 consistent gets

0 physical reads

0 redo size

3286309 bytes sent via SQL*Net to client

53429 bytes received via SQL*Net from client

4825 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

72349 rows processed

从统计量信息看，使用for update在处理相同数据行数据的时候，要产生相当数据量的redo size和嵌套[url=]SQL[/url]。

分析原因，从Oracle角度看，实现数据行的锁定依靠Select语句是不能做到的。所以，在for update操作的时候，Oracle会启动一个事务过程，将筛选出的数据行作出修改动作。

在这个过程中，Oracle会在条件记录对应的数据块上的事务槽作出标记，表示对应的数据行已经进行修改。这样才可能以独占的方式获取数据行。在Oracle中，只要对数据块进行了修改，无论这个修改是否最终被commit/rollback，都会生成出redo log信息，写入到online redo log中。

数据表越大，涉及的数据越多，for update的损耗和生成redo就越大。所以，如果数据表很大，同时select出的数据集合也很大，频繁的select for update就引起Oracle产生出大量的redo blocks。在归档模式下，过于频繁的对大数据进行for update操作，会带来额外的归档日志存储要求。如果没有预先的规划，容易引起[url=]数据库[/url]hange住。

2、数据列默认值：先加后加，大不相同

数据列默认值，也是我们经常使用的一种数据库对象技术。通过数据列默认值的设置，当插入数据的时候，如果不对数据列进行指定，那么默认值就会加入其中。

如果对一个已经存在数据的数据表加列，同时确定默认值和之后设置默认值有什么差别呢？

SQL> create table t (id number, name varchar2(10));

Table created

SQL> select * from t;

ID NAME

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

SQL> insert into t select object_id, null from dba_objects where rownum<10;

9 rows inserted

SQL> commit;

Commit complete