一：序列的nocache导致的log file sync现象

发现数据库大量的log file sync等待，awr报告的top10 wait time如下

 

 

数据库中也是大量的查询操作，少量的dml操作，初步判断是查询大量的nocache的序列导致大量的commit 导致log file sync

 

Cache就是Oracle每次向Sequence进行请求时，分配出的独立数字数量。例如，当我们使用<seq_name>.nextval获取一个独立值时，Oracle需要将sequence对象的数据字典信息更新。如果我们设置cache为10，那么第一次请求nextval的时候，就更新数据字典信息增加10，取出的10个号放在Oracle服务器的缓存中。

在以后每次请求nextval的时候，Oracle就从服务器缓存中去获取序列值。而不需要更新数据字典信息。只有在分配到缓存的10个数字都已经分配完，或者因为缓存刷新操作剩余数字被清理的情况下，才会再次调用sequence分配机制，再次分出cache个数字。

 

查询库里cache小于1000的序列

select 'alter sequence '||sequence_owner||'.'||sequence_name||' cache 1000; ',t.* from dba_sequences t where sequence_owner in (

'XXX'

)  and cache_size<1000 order by max_value

 

 

修改序列的cache

altersequence XXX.x_SEQ_ALARM_ID cache1000;

 

 

 

 

 

二：实验 nocache序列导致产生的redo size 大小

 

实验如下：

 

--创建nocache的序列

SQL> create sequence seq_nocache minvalue 1 maxvalue 999 start with 2 increment by 1 nocache;

 

Sequence created.

 

 

--查询nocache的序列，有 628的redo size出现

SQL> set autotrace traceonly statistics;

SQL> select seq_nocache.nextval from dual;

 

 

Statistics

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

         6  recursive calls

         3  db block gets

         3  consistent gets

         0  physical reads

       628  redosize

       525  bytes sent via SQL*Net toclient

       523  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

 

SQL> SET AUTOTRACE OFF;

 

 

 

 

三：实验 cache序列导致产生的redo size 大小

 

--创建cache的序列(cache 为5，会在第1次取出5条记录 产生redo size，之后会再第6次查询时取出5条记录，产生redosize)

SQL> create sequence seq_cache minvalue 1 maxvalue 999 start with 2 increment by 1 cache 5;

 

Sequence created.

 

 

 

--第一次查询cache的序列，有 676的redo size出现

SQL> set autotrace traceonly statistics;

SQL> select seq_cache.nextval from dual;

 

 

Statistics

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

         3  recursive calls

         3  db block gets

         3  consistent gets

         0  physical reads

       676  redo size

       525  bytes sent via SQL*Net toclient

       523  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

 

SQL> SET AUTOTRACE OFF;

 

 

--第二次查询cache的序列，redo size为0 了，不产生redosize了

SQL> set autotrace traceonly statistics;

SQL> select seq_cache.nextval from dual;

 

 

Statistics

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

         0  recursive calls

         0  db block gets

         0  consistent gets

         0  physical reads

         0  redosize

       525  bytes sent via SQL*Net toclient

       523  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

 

SQL> SET AUTOTRACE OFF;

 

 

--直到第6次查询才再次出现 redosize

SQL> set autotrace traceonly statistics;

SQL> select seq_cache.nextval from dual;

 

 

Statistics

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

         1  recursive calls

         3  db block gets

         1  consistent gets

         0  physical reads

       652  redo size

       525  bytes sent via SQL*Net toclient

       523  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

SQL> SET AUTOTRACE OFF;