分类：oracle

丢失活动或当前日志文件的恢复
oracle通过日志文件保证提交成功的数据不丢失,可是在故障中,用户可能损失了当前的
(current)日志文件.这又分为两种情况:此时数据是正常关闭的和此时数据库是异常关闭.
1.在损失当前日志时,数据库是正常关闭的.
由于关闭数据库前,oracle会执行全面检查点,当前日志在实例恢复中可以不再需要.
下面进行测试(数据库运行在非归档模式下).在oracle9i及以后版本中,是无法对当前
日志进行clear,需要通过until cancel恢复后再以resetlogs方式打开

[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 00:39:59 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SQL> select * from v$logfile;

GROUP#     STATUS  TYPE    MEMBER                                                      IS_RECOVERY_DEST_FILE
---------- ------- ------- ----------------------------------------------------------- ---------------------
3                  ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log  NO

2                  ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log  NO

1                  ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log  NO

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !mv /u01/app/oracle/product/10.2.0/oradata/jingyong/redo* /u01/app/oracle

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log'
SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> select * from v$log;


GROUP#     THREAD#    SEQUENCE#   BYTES     MEMBERS    ARC STATUS            FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1          1          0   52428800          1          NO  UNUSED           0             06-JAN-13

3          1          0   52428800          1          NO  CLEARING_CURRENT 914164        06-JAN-13

2          1          0   52428800          1          NO  UNUSED           914157        06-JAN-13

 

SQL> recover database until cancel;
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

2.在损失当前日志,数据库是异常关闭的
如果在损失当前日志时,数据库是异常关闭的,那么oracle在进行实例恢复时必须要求当前日志,
否则oracle将无法保证提交成功的数据不丢失(也就是意味着oracle会丢失数据),在这种情况下,
oracle数据库将无法启动.

对于这种情况,通常需要从备份中恢复数据文件,通过应用归档日志文件向前推演,直到最后一个
完好的日志文件,然后可以通过resetlogs启动数据库完成恢复.丢失的数据就是损坏的日志文件
中的数据.

如果没有备份,oracle有一类具有特殊作用的隐含参数,其中一个参数是_allow_resetlogs_corruption,来看一下
这个参数的说明:

SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
2  from x$ksppi x, x$ksppcv y
3  where x.inst_id=USERENV('Instance')
4  and y.inst_id=USERENV('Instance')
5  and x.indx=y.indx
6  and x.ksppinm like '%_allow_resetlogs_%';

NAME                           VALUE          DESCRIB
------------------------------ -------------- --------------------------------------------------
_allow_resetlogs_corruption    FALSE          allow resetlogs even if it will cause corruption

该参数的含义是,允许在破坏一致性的情况下强制重置日志,打开数据库._allow_resetlogs_corruption将
使用所有数据文件中最旧的scn打开数据库,所以通常需要保证system表空间拥有最旧的scn.

在强制打开数据库之后,可能因为各种原因伴随出现ora-00600错误,有些可以依据常规途径解决,看一下下面
的例子:

[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 02:04:02 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

查询当前的日志组

SQL> select * from v$log;

GROUP#     THREAD#    SEQUENCE#   BYTES     MEMBERS    ARC STATUS            FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1          1          2          52428800          1   NO  CURRENT          936058        07-JAN-13

2          1          0          52428800          1  YES  UNUSED           0

3          1          1          52428800          1   NO  INACTIVE         914918        07-JAN-13
SQL> select * from v$logfile;


GROUP#     STATUS  TYPE    MEMBER                                                      IS_RECOVERY_DEST_FILE
---------- ------- ------- ----------------------------------------------------------- ---------------------
3          STALE   ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log  NO

2                  ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log  NO

1                  ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log  NO

删除当前日志组的日志文件

SQL> !rm -rf /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

启动失败,日志文件损坏,在mount状态,可以查询v$log视图,发现此处损坏的是current的日志文件

SQL> select * from v$log;

GROUP#     THREAD#    SEQUENCE#   BYTES     MEMBERS    ARC STATUS            FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1          1          2          52428800   1          NO  CURRENT          936058        07-JAN-13

3          1          1          52428800   1          NO  INACTIVE         914918        07-JAN-13

2          1          0          52428800   1          YES UNUSED           0

由于active和current日志没有完成检查点，在恢复中需要用到,丢失active和current日志情况类似.
如果没有备份,只好使用隐含参数_allow_resetlogs_corruption强制启动数据库,设置此参数之后,
在数据库open过程中,oracle会跳过某些一致性检查,从而使用权数据库可能跳过不一致状态,直接打开.

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.
SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 936059 generated at 01/07/2013 02:01:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/flash_recovery_area/JINGYONG/archivelog/2013_01_0
7/o1_mf_1_2_%u_.arc
ORA-00280: change 936059 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;

Database altered.

如果运气好的话,数据库就可以成功打开了,如果不幸,则可能会遇到一些ora-00600的错误
那么就需要使用其它方法来进行恢复
通过这种方法恢复可以在alert日志中看见类似以下的日志信息:

Mon Jan  7 02:11:19 2013
alter database open resetlogs
Mon Jan  7 02:11:19 2013
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 936059
Resetting resetlogs activation ID 3141718551 (0xbb42d217)
Mon Jan  7 02:11:19 2013

不一致恢复最后恢复到的change号是936059,信息中说明了强制resetlogs不进行一致性检查,
可能会导致数据库损坏,数据库应当重建.

丢失非活动日志组的故障恢复
如果数据库丢失的是非活动(inactive)日志组,由于非活动日志组已经完成检查点,
数据库不会发生数据损失,此时只需要通过clear重建该日志组即可恢复.

先删除一个非活动日志组,模拟一次故障损失:

SQL> !rm /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

此时启动数据库,数据库会提示日志丢失

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log'

此时在mount状态,可以查看各日志组及日志文件的状态:

SQL> select * from v$log;
GROUP#     THREAD#    SEQUENCE#   BYTES     MEMBERS    ARC STATUS            FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1          1          26         52428800            1 NO  INACTIVE         913689        06-JAN-13

3          1          28         52428800            1 NO  CURRENT          913701        06-JAN-13

2          1          27         52428800            1 NO  INACTIVE         913696        06-JAN-13
SQL> select * from v$logfile;

GROUP#     STATUS  TYPE    MEMBER                                                      IS_RECOVERY_DEST_FILE
---------- ------- ------- ----------------------------------------------------------- ---------------------
3                  ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log  NO

2                  ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log  NO

1                  ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log  NO

清除该日志组后即可启动数据库:

SQL>alter database clear logfile group 3;
Database altered

SQL>alter database open;
Database altered

注意,如果数据库处于归档模式下,并且该日志组未完成归档则需要使用如下命令强制清除

alter database clear unarchived logfile group 3;

打开数据库之后,状态为stale的日志文件,在下次正常写入后,状态即可恢复正常:

SQL> select * from v$log;

GROUP#     THREAD#    SEQUENCE#   BYTES     MEMBERS    ARC STATUS            FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1          1          2          52428800            1 NO  INACTIVE         914153        06-JAN-13

2          1          3          52428800            1 NO  INACTIVE         914157        06-JAN-13

3          1          4          52428800            1 NO  CURRENT          914164        06-JAN-13

因为oracle将sql共享之后，截取出来的sql语句是带变量的

创建一个函数 jy_getsql来获得执行时的完整sql语句

CREATE OR REPLACE FUNCTION jy_getsql (my_sql_id in varchar2)
RETURN clob
IS
Result   clob;

cursor jl(p_sql_id in varchar2) is
  select decode(instr(b.BIND_NAME,'SYS'),0,':'||b.BIND_NAME||'',''||chr(58)||chr(34)||b.BIND_NAME||chr(34)||'')  name,

  decode(b.DATATYPE,2,dbms_sqltune.extract_bind(a.bind_data,b.POSITION).value_string ,''''||dbms_sqltune.extract_bind(a.bind_data,b.POSITION).value_string ||'''')
          value_string
  from v$sqlarea a ,v$sql_bind_metadata b
  where a.LAST_ACTIVE_CHILD_ADDRESS = b.ADDRESS
    and a.SQL_ID = p_sql_id
  order by b.POSITION desc;

BEGIN

    select a.SQL_FULLTEXT into Result
    from v$sqlarea a where a.SQL_ID=my_sql_id;

    for r in jl(my_sql_id) loop
       Result := replace(Result,r.name,r.value_string);
    end loop;

RETURN Result;

EXCEPTION
WHEN OTHERS
THEN
RETURN Result;
END;

下面的查询是查询数据库当前正处于等待状态的sql语句

select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait,p.PROGRAM,s.MACHINE,
(select  c.SQL_FULLTEXT from v$sqlarea c where c.SQL_ID=s.SQL_ID) sql_fulltext,
(select  c.BIND_DATA from v$sqlarea c where c.SQL_ID=s.SQL_ID) BIND_DATA,s.SQL_ID,
jy_getsql(s.SQL_ID)
from v$session s,v$session_wait sw,v$process p
where s.username is not null and s.PADDR=p.ADDR
and sw.sid=s.sid and sw.event not like'%SQL*Net%'
order by sw.wait_time desc

但是这还有一缺点就是
select 1 userid from dual
这个1没有在v$sqlarea.bind_data中
但where子句中的绑定变量还是有了

X$BH与Buffer Header

buffer header数据,可以从数据库的数据字典表中查询得到,这张字典表就是x$bh,x$bh中的bh就是
指buffer headers,每个buffer在x$bh中都存在一条记录

buffer header中存储每个buffer容纳的数据块的文件号,块地址,状态等重要信息,根据这些信息,
结合dba_extents视图,可以很容易地找到每个buffer对应的对象信息:

x$bh中还有一个重要的字段TCH,TCH为Touch的缩写,表示一个Buffer的访问次数,buffer被访问的次
数越多,说明该buffer就越抢手,也就可能存在热块竞争的问题

通过对下查询获得当前数据库最繁忙的buffer

SQL> select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc)
where rownum<21;

ADDR                    TS#      FILE#    DBARFIL     DBABLK        TCH
---------------- ---------- ---------- ---------- ---------- ----------
9FFFFFFFBF5AACA8          7         12         12     638244       1722
9FFFFFFFBF5AACA8          7         13         13     598707       1636
9FFFFFFFBF5AAFF8          7         11         11     261996       1629
9FFFFFFFBF5AAFF8          6          8          8     135404       1614
9FFFFFFFBF5AAFF8          7         11         11     655501       1587
9FFFFFFFBF5AAFF8          7         11         11     269628       1568
9FFFFFFFBF5AAFF8          7         11         11    2742315       1568
9FFFFFFFBF5AAFF8          7         11         11     269612       1562
9FFFFFFFBF5AACA8          7         13         13     601755       1538
9FFFFFFFBF5AAFF8          7         13         13     599052       1514
9FFFFFFFBF5AACA8          7         13         13     254900       1506
9FFFFFFFBF5AACA8          7         12         12     261898       1504
9FFFFFFFBF5AAFF8          7         11         11     662797       1491
9FFFFFFFBF5AACA8          7         13         13     610957       1487
9FFFFFFFBF5AAFF8          6          8          8     715684       1465
9FFFFFFFBF5AAFF8          7         11         11     665204       1462
9FFFFFFFBF5AAFF8          7         11         11     132492       1461
9FFFFFFFBF5AACA8          6          8          8    1766500       1458
9FFFFFFFBF5AACA8          7         11         11     273549       1445
9FFFFFFFBF5AAFF8          7         11         11     266099       1441

20 rows selected

再结合dba_extents中的信息,可以查询得到这些热点buffer都来自哪些对象;

select a.owner,a.segment_name,a.segment_type from dba_extents a,
(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc)
where rownum<21) b
where a.relative_fno=b.dbarfil
and a.block_id< =b.dbablk
and a.block_id+a.blocks>b.dbablk;

除了查询x$bh之外,也可以从buffer cache的转储信息中,看到buffer header的具体内容

BH (0x25feb12c) file#: 1 rdba: 0x0040b894 (1/47252) class: 1 ba: 0x25cec000
      set: 3 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 75
      dbwrid: 0 obj: 181 objn: 183 tsn: 0 afn: 1
      hash: [290c0868,290c0868] lru: [25feb230,25feb0d0]
      lru-flags:
      ckptq: [NULL] fileq: [NULL] objq: [25feb124,25feb284]
      st: XCURRENT md: NULL tch: 1
      flags:
      LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
      buffer tsn: 0 rdba: 0x0040b894 (1/47252)
      scn: 0x0000.00074869 seq: 0x01 flg: 0x04 tail: 0x48690601
      frmt: 0x02 chkval: 0xa2b2 type: 0x06=trans data

在oracle10g以前,数据库的等待事件中,所有的latch等待都被归纳为latch free等待
在statspack的report中,如果在top5等待事件中看到latch free这一等待处于较高的位置
那么就要地行研究和解决了

由于latch free是一个汇总等待事件,我们需要从v$latch视图中获得具体的latch竞争主要
是由哪些latch引起的

如果需要具体确定热块对象,可以从v$latch_children中查询到具体的子latch信息

SQL> select * from (
  2  select addr,child#,gets,misses,sleeps,immediate_gets,immediate_misses, spin_gets
  3  from v$latch_children
  4  where name='cache buffers chains'
  5  order by sleeps desc)
  6  where rownum<6;


ADDR                 CHILD#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES  SPIN_GETS
---------------- ---------- ---------- ---------- ---------- -------------- ---------------- ----------
C00000001888BF70      14784  126455972    1469279        636           2832                1    1468752
C0000000187F9940      11786   48496473     345817        216           2948                0     345623
C000000018645EF0       2864   63718005     332144        211           2645                1     331967
C0000000187CEDA8      10911   48514435     329026        207           3791                0     328844
C00000001877CDF0       9232   63325754     341420        202           3223                0     341256

在x$bh中还存在另外一个关键字段HLADDR，即hash chain latch address,这个字段可以和v$latch_children.addr
进行关联,这样就可以把具体的latch竞争和数据块关联起来,再结合dba_extents视图,就可以找到具体的热块竞争
对象,找到具体热点对象后,可以结合v$sqlarea或v$sqltext,找到频繁操作这些对象的sql,然后对其进行优化,
就可以缓解或解决热块竞争的问题了

SQL> select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from (
  2  select * from (
  3  select addr,ts#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc)
  4  where rownum<6) a,
  5  (select addr,gets,misses,sleeps from v$latch_children
  6  where name='cache buffers chains') b
  7  where a.hladdr=b.addr
  8  /

ADDR                    TS#    DBARFIL     DBABLK        TCH       GETS     MISSES     SLEEPS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
C00000001886F4D8          7         12     638244       2273     656301         13          0
C00000001876C900          7         11     261996       2320    2972787         89          0
C0000000186BC960          7         11     269628       2220     495549         10          0
C0000000186A9FB8          6          8     135404       2303    3257192        101          0
C000000018634678          7         11     655501       2257    3640151      30614         26

可以使用下面的语句来找到这些热点buffer的对象信息

select a.owner,a.segment_name,a.segment_type from dba_extents a,
(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc)
where rownum<21) b
where a.relative_fno=b.dbarfil
and a.block_id< =b.dbablk
and a.block_id+a.blocks>b.dbablk;

或

select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from (
select * from (
select addr,ts#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc)
where rownum<6) a,
(select addr,gets,misses,sleeps from v$latch_children
where name='cache buffers chains') b,
dba_extents c
where a.hladdr=b.addr
and c.relative_fno=a.dbarfil
and c.block_id< =a.dbablk
and c.block_id+c.blocks>a.dbablk

再与v$sqltext或v%sqlarea视图关联可以找到操作这些热块对象的相关sql

select  hash_value,sql_fulltext from v$sqlarea
where sql_id  in(
select a.sql_id from v$sqltext a,
(select distinct b.owner,b.segment_name,b.segment_type from dba_extents b,
(select dbarfil,dbablk from(
select dbarfil,dbablk from x$bh order by tch desc)
where rownum<11) c
where b.relative_fno=c.dbarfil
and b.block_id< =c.dbablk
and b.block_id+b.blocks>c.dbablk) d
where a.sql_text like '%'||d.segment_name||'%'
and d.segment_type='TABLE')

找到这些sql语句后,就可以通过优化sql减少数据的访问,避免或优化某些容易引起的争用操作
来减少热块竞争

buffer cache的管理有两个重要的数据结构:

hash bucket和cache buffer chain

1. hash bucket和cache buffer chain
可以想象,如果所有的buffer cache中的所有buffer都通过同一个结构来进行管理,当需要确定某个
block在buffer中是否存在时,将需要遍历整个结构,性能会相当低下.

为了提高效率,oracle引入了bucket的数据结构,oracle把管理的所有buffer通过一个内部的hash算法
运算后,存放到不同的hash bucket中,这样通过hash bucket进行分割之后,众多的buffer被分布到一
定数量的bucket之中,当用户需要在buffer中定位数据是否存在时,只需要通过同样的算法来获得hash
值然后到相应的bucket中查找少理的buffer即可确定.每个buffer存放的bucket由buffer的数据块
地址(DBA,Data Block Address)运算决定.

bucket内部,通过cache buffer chain(cache buffer chain是一个双向链表)将所有的buffer通过
buffer header信息联系起来

buffer header存放的是对应数据块的概要信息,包括数据块的文件号,块地址,状态等.要判断数据块
在buffer中是否存在,通过检查buffer header即可确定.

如果多个会话同时对相同的cache buffer chain进行读取时就会产生cache buffer chain的竞争.
先来进行读取的会话持有这个cache buffer chain的latch

从oracle9i开始,对于cache buffer chain的只读访问,其latch可以被共享,也就是说,如果多个会话
都只是来查阅这个cache buffer chain那么大家可以同时进行查阅,但是如果有会话要对这个
cache buffer chian中的buffer header对应的数据块进行操作时那么就只能独享这个latch了.

这就是buffer cache 与latch的竞争

由于buffer根据buffer header进行散列,从而最终决定存入哪一个hash bucket,那么hash bucket的
数量在一定程度上就决定了每个bucket中buffer数量的多少,也就间接影响了搜索buffer header的
性能.

所以在不同版本中,oracle一直在修改算法,优化hash bucket的数量,可以想象,bucket的数量多一些
那么在同一时间就可以有更多的会话可以对不同的hash bucket进行读取.但是更多的hash bucket,
显然需要更多的存放空间,更多的管理成本,所以优化在什么时候都不是简单的一元方程.

hash bucket的设置受一个隐含参数_db_block_hash_buckets的影响.

SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  2  from  sys.x$ksppi x,sys.x$ksppcv y
  3  where
  4  x.inst_id=userenv('Instance') and
  5  y.inst_id=userenv('Instance') and
  6  x.indx=y.indx and x.ksppinm like '%_db_block_hash_buckets%'
  7  ;

NAME                         VALUE            DESCRIB
---------------------------- ---------------- ----------------------------------------
_db_block_hash_buckets       4194304          Number of database block hash buckets

对于每个hash bucket，只会有一个cache buffer chain ,当用户试图搜索cache buffer chain时
必须先获得cache buffer chain latch.那么cache buffer chain latch的设置就同样值得研究

在oracle8i之前,对于每一个hash bucket,oracle使用一个独立的hash latch来维护,其缺省的
bucket数量为next_prime(db_block_buffers/4)

由于过于严重的热块竞争,从oracle8i开始,oracle改变了这个算法,先是bucket数量开始增加,
_db_block_hash_bucket增加到了2*db_block_buffers,而_db_block_hash_latchs的数量也发生
变化

当cache buffers少于2052个buffers时:
_db_block_hash_latches=power(2,trunc(log(2,db_block_buffers-4)-1))

当cache buffers多于131075个buffers时:
_db_block_hash_latches=power(2,trunc(log(2,db_block_buffers-4)-6))

当cache buffers在2052与131075个buffers之间时:
_db_block_hash_latches=1024

从oracle8i开始,_db_block_hash_buckets的数量较以前增加了8倍,而_db_block_hash_latchs的
数量增加比较有限,这意味着,每个latch需要管理多个bucket,但是由于bucket数量的成倍增加,
每个bucket中的block的数量得以减少,从而使用少量latch管理更多bucket成为可能

从oracle8i开始,bucket的数量比以前大大增加;通过增加bucket的数量来使得每个bucket上的
buffer的数量大大减少.

在oracle8i之前,_db_block_hash_latches的数量和hash bucket的数量是一致的,每一个latch管理
一个bucket,从oracle8i开始每个lath可以管理多个bucket,由于每个bucket中的buffer header数量
大大降低所以latch的性能反而得到提高

每个bucket存在一条cache buffer chain

buffer header上存在指向具体buffer的指针

下面是测试的一情景
db_cache_size为88MB,此时的_db_block_hash_buckets为32768

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 88M

SQL>
SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  2  from  sys.x$ksppi x,sys.x$ksppcv y
  3  where
  4  x.inst_id=userenv('Instance') and
  5  y.inst_id=userenv('Instance') and
  6  x.indx=y.indx and x.ksppinm like '%_db_block_hash_buckets%'
  7  ;

NAME                             VALUE               DESCRIB
-------------------------------- ------------------- --------------------------------------------
_db_block_hash_buckets           32768                Number of database block hash buckets


SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

计算db_cache_size=88MB,有多少个db_block_buffer=11264

SQL> select 88*1024/8 from dual;

 88*1024/8
----------
     11264

查询一下_db_block_hash_latches=1024所以应证了
当cache buffers在2052与131075个buffers之间时:
_db_block_hash_latches=1024

SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  2  from  sys.x$ksppi x,sys.x$ksppcv y
  3  where
  4  x.inst_id=userenv('Instance') and
  5  y.inst_id=userenv('Instance') and
  6  x.indx=y.indx and x.ksppinm like '%_db_block_hash_latches%'
  7  ;

NAME                                   VALUE               DESCRIB
-------------------------------------- ------------------- -----------------------------------------
_db_block_hash_latches                 1024                Number of database block hash latches

计算一下每个bucket中有多少个buffer header
db_cache_size为88MB,此时的_db_block_hash_buckets为32768
db_block_size=8Kb

SQL> select 88*1024/8/32768 from dual;

88*1024/8/32768
---------------
        0.34375

那么说明有的bucket中没有buffer header

SQL> alter session set events 'immediate trace name buffers level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_3341.trc

跟踪文件中的cache buffer chain的数量正好是

[oracle@jingyong udump]$ grep CHAIN jingyong_ora_3341.trc | wc -l
32768

某些chain上可能没有buffer header信息(被标记为null),这些chain的数据类似如下:

[oracle@jingyong udump]$ grep CHAIN jingyong_ora_3341.trc|head -20
CHAIN: 0 LOC: 0x290c0838 HEAD: [NULL]
CHAIN: 1 LOC: 0x290c0840 HEAD: [NULL]
CHAIN: 2 LOC: 0x290c0848 HEAD: [NULL]
CHAIN: 3 LOC: 0x290c0850 HEAD: [NULL]
CHAIN: 4 LOC: 0x290c0858 HEAD: [NULL]
CHAIN: 5 LOC: 0x290c0860 HEAD: [NULL]
CHAIN: 6 LOC: 0x290c0868 HEAD: [25feb12c,25feb12c]
CHAIN: 7 LOC: 0x290c0870 HEAD: [NULL]
CHAIN: 8 LOC: 0x290c0878 HEAD: [24fe6dcc,24fe6dcc]
CHAIN: 9 LOC: 0x290c0880 HEAD: [NULL]
CHAIN: 10 LOC: 0x290c0888 HEAD: [NULL]
CHAIN: 11 LOC: 0x290c0890 HEAD: [NULL]
CHAIN: 12 LOC: 0x290c0898 HEAD: [233f8c7c,233f8c7c]
CHAIN: 13 LOC: 0x290c08a0 HEAD: [NULL]
CHAIN: 14 LOC: 0x290c08a8 HEAD: [NULL]
CHAIN: 15 LOC: 0x290c08b0 HEAD: [NULL]
CHAIN: 16 LOC: 0x290c08b8 HEAD: [NULL]
CHAIN: 17 LOC: 0x290c08c0 HEAD: [NULL]
CHAIN: 18 LOC: 0x290c08c8 HEAD: [21fed2dc,21fee82c]
CHAIN: 19 LOC: 0x290c08d0 HEAD: [NULL]

查看一下chain 6的数据

CHAIN: 6 LOC: 0x290c0868 HEAD: [25feb12c,25feb12c]
    BH (0x25feb12c) file#: 1 rdba: 0x0040b894 (1/47252) class: 1 ba: 0x25cec000
      set: 3 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 75
      dbwrid: 0 obj: 181 objn: 183 tsn: 0 afn: 1
      hash: [290c0868,290c0868] lru: [25feb230,25feb0d0]
      lru-flags:
      ckptq: [NULL] fileq: [NULL] objq: [25feb124,25feb284]
      st: XCURRENT md: NULL tch: 1
      flags:
      LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
      buffer tsn: 0 rdba: 0x0040b894 (1/47252)
      scn: 0x0000.00074869 seq: 0x01 flg: 0x04 tail: 0x48690601
      frmt: 0x02 chkval: 0xa2b2 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x25CEC000 to 0x25CEE000
25CEC000 0000A206 0040B894 00074869 04010000  [......@.iH......]
25CEC010 0000A2B2 00000001 000000B5 00074869  [............iH..]
25CEC020 1FE80000 00031F02 0040B88B 00140001  [..........@.....]
25CEC030 0000006D 00805685 0022002D 00008000  [m....V..-.".....]
25CEC040 00039FF0 0011000A 000000C7 00800A9E  [................]
25CEC050 002600D7 00008000 0007481D 00050400  [..&......H......]
25CEC060 0028FFFF 1E791E70 00001E79 00010001  [..(.p.y.y.......]
25CEC070 00020001 00020000 1F790003 1E701F33  [..........y.3.p.]
25CEC080 1EA21ED5 00000000 00000000 00000000  [................]
25CEC090 00000000 00000000 00000000 00000000  [................]
        Repeat 482 times
25CEDEC0 00000000 00000000 00000000 000D006C  [............l...]
25CEDED0 02444902 001002C1 00000000 00000000  [.ID.............]
25CEDEE0 00000000 02190000 02FF02C1 C20303C1  [................]
25CEDEF0 C1023509 02C20302 FFFFFF1D 006C8001  [.5............l.]
25CEDF00 500B000D 41424F52 494C4942 C1025954  [...PROBABILITY..]
25CEDF10 00001004 00000000 00000000 00000000  [................]
25CEDF20 C1020F00 C102FF02 FFFFFF03 01FFFFFF  [................]
25CEDF30 0D006C80 43530500 0245524F 001003C1  [.l....SCORE.....]
25CEDF40 00000000 00000000 00000000 020F0000  [................]
25CEDF50 02FF02C1 FFFF03C1 FFFFFFFF 006C8001  [..............l.]
25CEDF60 4902000D 02C10244 00000010 00000000  [...ID...........]
25CEDF70 00000000 00000000 02C10219 03C102FF  [................]
25CEDF80 0202C102 C20302C1 FFFF1D02 6C8001FF  [...............l]
25CEDF90 04001500 302E3824 5ECEFA10 4AAA6474  [....$8.0...^td.J]
25CEDFA0 5730E0F6 1016058C 02C20365 05C10209  [..0W....e.......]
25CEDFB0 0104C102 FFFFFF80 FFFFFFFF FFFFFFFF  [................]
25CEDFC0 11FFFFFF F99F5921 C8031661 E625EF53  [....!Y..a...S.%.]
25CEDFD0 03CF388F 0200AC3D 00040004 94B84000  [.8..=........@..]
25CEDFE0 40000000 000094B8 5ECEFA10 4AAA6474  [...@.......^td.J]
25CEDFF0 5730E0F6 1016058C 02C10265 48690601  [..0W....e.....iH]
Block header dump:  0x0040b894
 Object id on Block? Y
 seg/obj: 0xb5  csc: 0x00.74869  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x40b88b ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.014.0000006d  0x00805685.002d.22  C---    0  scn 0x0000.00039ff0
0x02   0x000a.011.000000c7  0x00800a9e.00d7.26  C---    0  scn 0x0000.0007481d

data_block_dump,data header at 0x25cec05c
===============
tsiz: 0x1fa0
hsiz: 0x28
pbl: 0x25cec05c
bdba: 0x0040b894
     76543210
flag=--------
ntab=4
nrow=5
frre=-1
fsbo=0x28
fseo=0x1e70
avsp=0x1e79
tosp=0x1e79
0xe:pti[0] nrow=1 offs=0
0x12:pti[1] nrow=1 offs=1
0x16:pti[2] nrow=0 offs=2
0x1a:pti[3] nrow=3 offs=2
0x1e:pri[0] offs=0x1f79
0x20:pri[1] offs=0x1f33
0x22:pri[2] offs=0x1e70
0x24:pri[3] offs=0x1ed5
0x26:pri[4] offs=0x1ea2
block_row_dump:
tab 0, row 0, @0x1f79
tl: 39 fb: K-H-FL-- lb: 0x0  cc: 2
curc: 4 comc: 4 pk: 0x0040b894.0 nk: 0x0040b894.0
col  0: [16]  fa ce 5e 74 64 aa 4a f6 e0 30 57 8c 05 16 10 65
col  1: [ 2]  c1 02
tab 1, row 0, @0x1f33
tl: 70 fb: -CH-FL-- lb: 0x0  cc: 21 cki: 0
col  0: [ 4]  24 38 2e 30
col  1: [16]  fa ce 5e 74 64 aa 4a f6 e0 30 57 8c 05 16 10 65
col  2: [ 3]  c2 02 09
col  3: [ 2]  c1 05
col  4: [ 2]  c1 04
col  5: [ 1]  80
col  6: *NULL*
col  7: *NULL*
col  8: *NULL*
col  9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: *NULL*
col 13: *NULL*
col 14: *NULL*
col 15: *NULL*
col 16: *NULL*
col 17: *NULL*
col 18: *NULL*
col 19: *NULL*
col 20: [17]  21 59 9f f9 61 16 03 c8 53 ef 25 e6 8f 38 cf 03 3d
tab 3, row 0, @0x1e70
tl: 50 fb: -CH-FL-- lb: 0x0  cc: 13 cki: 0
col  0: [ 2]  49 44
col  1: [ 2]  c1 02
col  2: [16]  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 19
col  3: [ 2]  c1 02
col  4: *NULL*
col  5: [ 2]  c1 03
col  6: [ 3]  c2 09 35
col  7: [ 2]  c1 02
col  8: [ 3]  c2 02 1d
col  9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: [ 1]  80
tab 3, row 1, @0x1ed5
tl: 45 fb: -CH-FL-- lb: 0x0  cc: 13 cki: 0
col  0: [ 5]  53 43 4f 52 45
col  1: [ 2]  c1 03
col  2: [16]  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0f
col  3: [ 2]  c1 02
col  4: *NULL*
col  5: [ 2]  c1 03
col  6: *NULL*
col  7: *NULL*
col  8: *NULL*
col  9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: [ 1]  80
tab 3, row 2, @0x1ea2
tl: 51 fb: -CH-FL-- lb: 0x0  cc: 13 cki: 0
col  0: [11]  50 52 4f 42 41 42 49 4c 49 54 59
col  1: [ 2]  c1 04
col  2: [16]  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0f
col  3: [ 2]  c1 02
col  4: *NULL*
col  5: [ 2]  c1 03
col  6: *NULL*
col  7: *NULL*
col  8: *NULL*
col  9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: [ 1]  80
end_of_block_dump

这个chain中存一个BH信息,其中包含”hash: [290c0868,290c0868] lru: [25feb230,25feb0d0]”
“hash: [290c0868,290c0868] “中的两个数据分别代表X$BH中的NXT_HASH和PRV_HASH,也就是指
同一个hash chain上的下一个BH地址和上一个buffer地址.如果某个chain只包含一个BH,
那么这两个值将同时指向该chain地址

“lru: [25feb230,25feb0d0]”中的两个数据分别代表X$BH中的NXT_REPL和PRV_REPL也就是LRU上的
下一个buffer和上一个buffer

cache buffers lru chain闩锁竞争与解决

当用户进程需要读数据到buffer cache时,或cache buffer根据lru算法进行管理时,就不可避免地要扫描
lru list获取可用buffer或更改buffer的状态,我们知道,oracle的buffer cache是共享内存,可以为众多
并发进程并发访问,所以在搜索的过程中必须获取latch(latch是oracle的一种串行锁机制,用于保护共享内存结构)
,锁定内存结构,防止并发访问损坏内存中的数据.

这个用于锁定lru的latch就是经常见到的cache buffers lru chain.

SQL> select addr,latch#,name,gets,misses,immediate_gets,immediate_misses
  2  from v$latch where name='cache buffers lru chain';

ADDR                 LATCH# NAME                          GETS     MISSES       IMMEDIATE_GETS IMMEDIATE_MISSES
---------------- ---------- ----------------------------- ---------- ---------- -------------- ----------------
C00000000BE23B10        117 cache buffers lru chain        2601887       8060      106765296           597096

cache buffers lru chain latch存在多个子latch,它的数量受隐含参数_db_block_lru_latches控制

SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  2  from sys.x$ksppi x,sys.x$ksppcv y
  3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id=USERENV('Instance')
  5  and x.indx=y.indx
  6  and x.ksppinm like '%_db_block_lru_latches%';

NAME                         VALUE          DESCRIB
---------------------------- -------------  -------------------------------
_db_block_lru_latches        32             number of lru latches

可以从v$latch_children视图查看当前各子latch使用的情况:

SQL> select addr,latch#,name,gets,misses,immediate_gets,immediate_misses
  2  from v$latch_children where name='cache buffers lru chain';

ADDR                 LATCH# NAME                                GETS     MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
---------------- ---------- -------------------------  -------------- ---------- -------------- ----------------
C00000047AB14E80        117 cache buffers lru chain                68          0              0                0
C00000047AB14928        117 cache buffers lru chain                68          0              0                0
C00000047AB143D0        117 cache buffers lru chain                68          0              0                0
C00000047AB13E78        117 cache buffers lru chain                68          0              0                0
C00000047AB13920        117 cache buffers lru chain                68          0              0                0
C00000047AB133C8        117 cache buffers lru chain                68          0              0                0
C00000047AB12E70        117 cache buffers lru chain                68          0              0                0
C00000047AB12918        117 cache buffers lru chain                68          0              0                0
C00000047AB123C0        117 cache buffers lru chain                68          0              0                0
C00000047AB11E68        117 cache buffers lru chain                68          0              0                0
C00000047AB11910        117 cache buffers lru chain                68          0              0                0
C00000047AB113B8        117 cache buffers lru chain                68          0              0                0
C00000047AB10E60        117 cache buffers lru chain                68          0              0                0
C00000047AB10908        117 cache buffers lru chain                68          0              0                0
C00000047AB103B0        117 cache buffers lru chain                68          0              0                0
C00000047AB0FE58        117 cache buffers lru chain                68          0              0                0
C00000047AB0F900        117 cache buffers lru chain                68          0              0                0
C00000047AB0F3A8        117 cache buffers lru chain                68          0              0                0
C00000047AB0EE50        117 cache buffers lru chain                68          0              0                0
C00000047AB0E8F8        117 cache buffers lru chain                68          0              0                0

ADDR                 LATCH# NAME                                 GETS     MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
---------------- ---------- -------------------------  -------------- ---------- -------------- ----------------
C00000047AB0E3A0        117 cache buffers lru chain            658582       2096       27333396           148252
C00000047AB0DE48        117 cache buffers lru chain            659346       2092       27372470           148770
C00000047AB0D8F0        117 cache buffers lru chain            660582       2168       27373352           148620
C00000047AB0D398        117 cache buffers lru chain            657057       1824       27227832           152743
C00000047AB0CE40        117 cache buffers lru chain                68          0              0                0
C00000047AB0C8E8        117 cache buffers lru chain                68          0              0                0
C00000047AB0C390        117 cache buffers lru chain                68          0              0                0
C00000047AB0BE38        117 cache buffers lru chain                68          0              0                0
C00000047AB0B8E0        117 cache buffers lru chain                70          0              2                0
C00000047AB0B388        117 cache buffers lru chain                70          0              3                0
C00000047AB0AE30        117 cache buffers lru chain                70          0              3                0
C00000047AB0A8D8        117 cache buffers lru chain                70          0              2                0

32 rows selected

如果该latch竞争激烈,通常有如下方法可以采用.

适当的增大buffer cache,这样可以减少读数据到buffer cache的机会,减少扫描lru list的竞争

可以适当增加lru latch的数量,修改_db_block_lru_latches参数可以实现,但是该参数通常来说
是足够的,除非在oracle support的建议下或确知是该参数带来的影响,否则不推荐修改

通过多缓冲池技术,可以减少不希望的数据老化和全表扫描等操作对于default池的冲击,
从而可以减少竞争

通过如下命令来转储buffer cache的内容,从而清晰地看到它的数据结构

SQL> alter session set events 'immediate trace name buffers level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d;

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_3294.trc

不同的level转储的内容详细程度不同,此命令的可用级别主要有1-10级,其中和级别
的含义如下:
level 1:仅包含buffer headers信息
level 2:包含buffer headers和buffer概要信息转储
level 3:包含buufer headers和完整的buffer内容转储
level 4: level 1+ latch转储+ lru队列
level 5: level 4+ buffer概要信息转储
level 6和level 7:level 4+完整的buffer内容转储
level 8:level 4+显示users/waiters信息
level 9:level 5+显示users/waiters信息
level 10:level 6+显示users/waiters信息

转储仅限于在测试环境中使用,转储的跟踪文件可能非常巨大,为获取完整的跟踪
文件,建议设置初始化参数max_dump_file_size为UNLIMITED

** 2013-12-27 23:18:36.317
*** SERVICE NAME:(SYS$USERS) 2013-12-27 23:18:36.315
*** SESSION ID:(144.29) 2013-12-27 23:18:36.315
Dump of buffer cache at level 10 for tsn=2147483647, rdba=0
  (WS) size: 0 wsid:  1 state: 0
    (WS_REPL_LIST) main_prev: 29142bbc main_next: 29142bbc aux_prev: 29142bc4 aux_next: 29142bc4curnum: 0 auxnum: 0
cold: 29142bbc hbmax: 0 hbufs: 0
    (WS_WRITE_LIST) main_prev: 29142bd8 main_next: 29142bd8 aux_prev: 29142be0 aux_next: 29142be0curnum: 0 auxnum: 0
    (WS_XOBJ_LIST) main_prev: 29142bf4 main_next: 29142bf4 aux_prev: 29142bfc aux_next: 29142bfccurnum: 0 auxnum: 0
    (WS_XRNG_LIST) main_prev: 29142c10 main_next: 29142c10 aux_prev: 29142c18 aux_next: 29142c18curnum: 0 auxnum: 0
    (WS_REQ_LIST) main_prev: 29142c2c main_next: 29142c2c aux_prev: 29142c34 aux_next: 29142c34curnum: 0 auxnum: 0
  (WS) fbwanted: 0
  (WS) bgotten: 0 sumwrt:  0
  (WS) pwbcnt: 0
MAIN RPL_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN RPL_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY RPL_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY RPL_LST Queue header (PREV_DIRECTION)[NULL]
MAIN WRT_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN WRT_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY WRT_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY WRT_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XOBJ_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN XOBJ_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY XOBJ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY XOBJ_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XRNG_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN XRNG_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY XRNG_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY XRNG_LST Queue header (PREV_DIRECTION)[NULL]
MAIN REQ_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN REQ_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY REQ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY REQ_LST Queue header (PREV_DIRECTION)[NULL]
  (WS) size: 0 wsid:  2 state: 0
    (WS_REPL_LIST) main_prev: 29142f40 main_next: 29142f40 aux_prev: 29142f48 aux_next: 29142f48curnum: 0 auxnum: 0
cold: 29142f40 hbmax: 0 hbufs: 0
    (WS_WRITE_LIST) main_prev: 29142f5c main_next: 29142f5c aux_prev: 29142f64 aux_next: 29142f64curnum: 0 auxnum: 0
    (WS_XOBJ_LIST) main_prev: 29142f78 main_next: 29142f78 aux_prev: 29142f80 aux_next: 29142f80curnum: 0 auxnum: 0
    (WS_XRNG_LIST) main_prev: 29142f94 main_next: 29142f94 aux_prev: 29142f9c aux_next: 29142f9ccurnum: 0 auxnum: 0
    (WS_REQ_LIST) main_prev: 29142fb0 main_next: 29142fb0 aux_prev: 29142fb8 aux_next: 29142fb8curnum: 0 auxnum: 0
  (WS) fbwanted: 0
  (WS) bgotten: 0 sumwrt:  0
  (WS) pwbcnt: 0
MAIN RPL_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN RPL_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY RPL_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY RPL_LST Queue header (PREV_DIRECTION)[NULL]
MAIN WRT_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN WRT_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY WRT_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY WRT_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XOBJ_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN XOBJ_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY XOBJ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY XOBJ_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XRNG_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN XRNG_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY XRNG_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY XRNG_LST Queue header (PREV_DIRECTION)[NULL]
MAIN REQ_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN REQ_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY REQ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY REQ_LST Queue header (PREV_DIRECTION)[NULL]
  (WS) size: 10479 wsid:  3 state: 2
    (WS_REPL_LIST) main_prev: 23fedc20 main_next: 247ee1a0 aux_prev: 207f6bd0 aux_next: 23fedcd0curnum: 10479  auxnum: 1105
cold: 207f6b20 hbmax: 5200 hbufs: 2848
    (WS_WRITE_LIST) main_prev: 291432e0 main_next: 291432e0 aux_prev: 291432e8 aux_next: 291432e8curnum: 0 auxnum: 0
    (WS_XOBJ_LIST) main_prev: 291432fc main_next: 291432fc aux_prev: 29143304 aux_next: 29143304curnum: 0 auxnum: 0
    (WS_XRNG_LIST) main_prev: 29143318 main_next: 29143318 aux_prev: 29143320 aux_next: 29143320curnum: 0 auxnum: 0
    (WS_REQ_LIST) main_prev: 29143334 main_next: 29143334 aux_prev: 2914333c aux_next: 2914333ccurnum: 0 auxnum: 0
  (WS) fbwanted: 0
  (WS) bgotten: 11481 sumwrt:  4081
  (WS) pwbcnt: 112
MAIN RPL_LST Queue header (NEXT_DIRECTION)[247ee1a0,23fedc20]
0x247ee14c=>0x23feee5c=>0x23feedac=>0x23fee61c=>0x23fee1fc=>0x23fef53c=>0x23fef1cc=>0x23fee77c
0x23fee56c=>0x23fee82c=>0x23fee8dc=>0x23fee98c=>0x23feea3c=>0x23feeaec=>0x23fef06c=>0x243eefbc
0x23fef11c=>0x23fef32c=>0x23fef3dc=>0x23fef48c=>0x23fef5ec=>0x23fef74c=>0x23fef7fc=>0x23fef95c
0x23fefa0c=>0x23fefabc=>0x23fefb6c=>0x23fefc1c=>0x23fefccc=>0x23fefd7c=>0x23fefe2c=>0x23fefedc
0x23feff8c=>0x23ff003c=>0x23ff00ec=>0x23ff019c=>0x23ff024c=>0x23ff03ac=>0x23ff045c=>0x23ff050c
0x23ff05bc=>0x23ff066c=>0x23ff071c=>0x23ff07cc=>0x23ff087c=>0x23ff092c=>0x23ff09dc=>0x23ff0a8c
0x23ff0b3c=>0x23ff0bec=>0x23ff0c9c=>0x23ff0d4c=>0x23ff0dfc=>0x23ff0eac=>0x23ff0f5c=>0x23ff10bc
0x23ff116c=>0x23ff12cc=>0x23ff137c=>0x23ff142c=>0x23ff14dc=>0x23ff158c=>0x23ff163c=>0x23ff16ec

SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ

  2  from x$ksppi x, x$ksppcv y
  3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id=USERENV('Instance')
  5  and x.indx=y.indx
  6  and x.ksppinm like '%pool_size%';

NAME                           VALUE                     DESCRIB
------------------------------ ------------------------- ---------------------------------------
_NUMA_pool_size                Not specified             aggregate size in bytes of NUMA pool
__shared_pool_size             1073741824                Actual size in bytes of shared pool
shared_pool_size               1073741824                size in bytes of shared pool
__large_pool_size              117440512                 Actual size in bytes of large pool
large_pool_size                117440512                 size in bytes of large pool
__java_pool_size               134217728                 Actual size in bytes of java pool
java_pool_size                 134217728                 size in bytes of java pool
__streams_pool_size            0                         Actual size in bytes of streams pool
streams_pool_size              0                         size in bytes of the streams pool
_io_shared_pool_size           4194304                   Size of I/O buffer pool from SGA
_backup_io_pool_size           1048576                   memory to reserve from the large pool
global_context_pool_size                                                                                                                                          Global Application Context Pool Size in Bytes
olap_page_pool_size            0                         size of the olap page pool in bytes

13 rows selected

这些由两个下划线开头的参数决定了当前的SGA的分配
这也是动态内存管理调整的参数,这些参数的更改也会
记录到spfile文件当中,在下一次数据库启动时仍然有效
通过create pfile from spfile我们可以看到如下内容:

jingyong.__db_cache_size=88080384
jingyong.__java_pool_size=4194304
jingyong.__large_pool_size=4194304
jingyong.__shared_pool_size=67108864
jingyong.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/jingyong/adump'
*.background_dump_dest='/u01/app/oracle/admin/jingyong/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/product/10.2.0/oradata/jingyong/control01.ctl','/u01/app/oracle/product/10.2.0/oradata/jingyong/control02.ctl','/u01/app/oracle/product/10.2.0/oradata/jingyong/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/jingyong/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='jingyong'
*.db_recovery_file_dest='/u01/app/oracle/product/10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jingyongXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/jingyong/udump'

还可以通过查询v$sga_dynamic_components视图来各动态内存组件的调整信息

SQL> select component,current_size,min_size,last_oper_type,last_oper_mode,last_oper_time from v$sga_dynamic_components;

COMPONENT                                                        CURRENT_SIZE   MIN_SIZE LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME
---------------------------------------------------------------- ------------ ---------- -------------- -------------- --------------
shared pool                                                        1124073472 1073741824 SHRINK         DEFERRED       2012-12-27 16:
large pool                                                          117440512  117440512 STATIC
java pool                                                           134217728  134217728 STATIC
streams pool                                                                0          0 STATIC
DEFAULT buffer cache                                              11442061312 1114007142 GROW           DEFERRED       2012-12-27 16:
KEEP buffer cache                                                    50331648   50331648 STATIC
RECYCLE buffer cache                                                        0          0 STATIC
DEFAULT 2K buffer cache                                                     0          0 STATIC
DEFAULT 4K buffer cache                                                     0          0 STATIC
DEFAULT 8K buffer cache                                                     0          0 STATIC
DEFAULT 16K buffer cache                                                    0          0 STATIC
DEFAULT 32K buffer cache                                                    0          0 STATIC
ASM Buffer Cache                                                            0          0 STATIC

SGA与共享内存

SGA的设置在linux/unix上和一个操作系统内核参数有关,这个参数是shmmax.
不同的操作系统中这个参数据设置的地方一样.在solaris上,这个参数是由
/etc/system文件中的shmsys:shminfo_shmmax来定义的.在linux上,该参数
由/proc/sys/kernel/shmmax参数定义.

shmmax内核参数的作用是系统允许的单个共享内存段的最大值.如果该参数
设置小于oracle sga的大小,那么sga仍然可以创建成功,但是会被分配成多
个共享内存段.通常建议通过调整shmmax的大小来使用sga限制在一个共享
内存段.

在windows系统中,由于系统采用多线程服务器(实际上所有的oracle server procees
都是一个进程中的线程),所以不存在共享内存的问题.无需进行特殊设置.

以32位linux平台为例,来看shmmax参数于数据库的影响.linux上该参数据的缺省值
一般是32M

[root@jingyong ~]# more /proc/sys/kernel/shmmax
33554432

操作系统的版本

[root@jingyong ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)

通过ipcs命令可以查看在shmmax参数为缺省情况下共享内存的分配情况.
可以看到oracle分配了多个共享内存段来满足sga的设置要求:

[root@jingyong ~]# ipcs -sa

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x289516a4 32768      oracle    640        33554432  18
0x289516a4 32778      oracle    640        33554432  18
0x289516a4 32779      oracle    640        33554432  18
0x289516a4 32781      oracle    640        33554432  18
0x289516a4 32784      oracle    640        33554432  18
0x289516a4 32784      oracle    640        4194304  18
0x00000000 65537      gdm       600        393216     2          dest

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x0bbc1610 98304      oracle    640        154

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

从上面可以看到sga为160M(171966464字节)被分成了6个共享内存段.

对于每一个后台进程,使用pmap工具可以看到每个共享内存段的地址空间:

[root@jingyong ~]# ps -ef|grep dbw
oracle    2220     1  0 Dec26 ?        00:00:02 ora_dbw0_jingyong
root      3390  3309  0 02:39 pts/2    00:00:00 grep dbw

[root@jingyong ~]# pmap 2220
2220:   ora_dbw0_jingyong
00110000    344K r-x--  /u01/app/oracle/product/10.2.0/db/lib/libocrutl10.so
00166000     16K rwx--  /u01/app/oracle/product/10.2.0/db/lib/libocrutl10.so
0016a000      4K rwx--    [ anon ]
0016b000   1904K r-x--  /u01/app/oracle/product/10.2.0/db/lib/libnnz10.so
00347000    152K rwx--  /u01/app/oracle/product/10.2.0/db/lib/libnnz10.so
0036d000    432K rwx--    [ anon ]
003d9000     36K r-x--  /lib/libnss_files-2.5.so
003e2000      4K r-x--  /lib/libnss_files-2.5.so
003e3000      4K rwx--  /lib/libnss_files-2.5.so
00458000      4K r-x--    [ anon ]
00459000   1276K r-x--  /lib/libc-2.5.so
00598000      4K --x--  /lib/libc-2.5.so
00599000      8K r-x--  /lib/libc-2.5.so
0059b000      4K rwx--  /lib/libc-2.5.so
0059c000     12K rwx--    [ anon ]
005b4000    436K r-x--  /u01/app/oracle/product/10.2.0/db/lib/libocr10.so
00621000      4K rwx--  /u01/app/oracle/product/10.2.0/db/lib/libocr10.so
00622000   1032K rwx--    [ anon ]
00839000      4K rwx--    [ anon ]
0089f000    880K r-x--  /u01/app/oracle/product/10.2.0/db/lib/libhasgen10.so
0097b000     20K rwx--  /u01/app/oracle/product/10.2.0/db/lib/libhasgen10.so
00980000     12K rwx--    [ anon ]
0098f000      4K r-x--  /u01/app/oracle/product/10.2.0/db/lib/libskgxn2.so
00990000      4K rwx--  /u01/app/oracle/product/10.2.0/db/lib/libskgxn2.so
009fd000     36K rwx--    [ anon ]
00a5a000      4K rwx--    [ anon ]
00ab7000    128K r-x--  /u01/app/oracle/product/10.2.0/db/lib/libskgxp10.so
00ad7000      8K rwx--  /u01/app/oracle/product/10.2.0/db/lib/libskgxp10.so
00b01000     76K r-x--  /lib/libnsl-2.5.so
00b14000      4K r-x--  /lib/libnsl-2.5.so
00b15000      4K rwx--  /lib/libnsl-2.5.so
00b16000      8K rwx--    [ anon ]
00b36000     88K r-x--  /u01/app/oracle/product/10.2.0/db/lib/libdbcfg10.so
00b4c000      8K rwx--  /u01/app/oracle/product/10.2.0/db/lib/libdbcfg10.so
00bfd000    104K r-x--  /lib/ld-2.5.so
00c17000      4K r-x--  /lib/ld-2.5.so
00c18000      4K rwx--  /lib/ld-2.5.so
00c1b000      4K r-x--  /usr/lib/libaio.so.1.0.1
00c1c000      4K rwx--  /usr/lib/libaio.so.1.0.1
00cbc000     32K r-x--  /u01/app/oracle/product/10.2.0/db/lib/libclsra10.so
00cc4000      4K rwx--  /u01/app/oracle/product/10.2.0/db/lib/libclsra10.so
00d67000    148K r-x--  /lib/libm-2.5.so
00d8c000      4K r-x--  /lib/libm-2.5.so
00d8d000      4K rwx--  /lib/libm-2.5.so
00d90000      8K r-x--  /lib/libdl-2.5.so
00d92000      4K r-x--  /lib/libdl-2.5.so
00d93000      4K rwx--  /lib/libdl-2.5.so
00d96000     76K r-x--  /lib/libpthread-2.5.so
00da9000      4K r-x--  /lib/libpthread-2.5.so
00daa000      4K rwx--  /lib/libpthread-2.5.so
00dab000      8K rwx--    [ anon ]
00e33000    268K r-x--  /u01/app/oracle/product/10.2.0/db/lib/libocrb10.so
00e76000      4K rwx--  /u01/app/oracle/product/10.2.0/db/lib/libocrb10.so
00ee0000      4K rwx--    [ anon ]
00f07000      4K rwxs-  /u01/app/oracle/product/10.2.0/db/dbs/hc_jingyong.dat
00f76000      4K rwx--    [ anon ]
00f77000   7028K r-x--  /u01/app/oracle/product/10.2.0/db/lib/libjox10.so
01654000    260K rwx--  /u01/app/oracle/product/10.2.0/db/lib/libjox10.so
01695000      4K rwx--    [ anon ]
048b1000   1556K rwx--    [ anon ]
08048000  77032K r-x--  /u01/app/oracle/product/10.2.0/db/bin/oracle
0cb82000    324K rwx--  /u01/app/oracle/product/10.2.0/db/bin/oracle
0cbd3000    120K rwx--    [ anon ]
0e505000    456K rwx--    [ anon ]
20000000 167936K rwxs-    [ shmid=0x8000 ]
b7e6a000     64K rwx--  /dev/zero
b7e7a000     64K rwx--  /dev/zero
b7e8a000     64K rwx--  /dev/zero
b7e9a000     64K rwx--  /dev/zero
b7eaa000     64K rwx--  /dev/zero
b7eba000     64K rwx--  /dev/zero
b7eca000     64K rwx--  /dev/zero
b7eda000     64K --x--  /dev/zero
b7eea000     24K rwx--  /dev/zero
b7ef0000     64K rwx--  /dev/zero
b7f00000     64K rwx--  /dev/zero
b7f10000     64K rwx--  /dev/zero
b7f20000    128K rwx--  /dev/zero
b7f40000     64K rwx--  /dev/zero
b7f50000     64K rwx--  /dev/zero
b7f60000     40K rwx--  /dev/zero
bffb7000     84K rwx--    [ stack ]
 total   263492K

为了避免给sga分配多个共享内存段,可以修改shmmax内核参数,使用sga存在于一个
共享内存段中,可以通过修改/proc/sys/kernel/shmmax参数可以达到此目的

[root@jingyong ~]# echo 536870912> /proc/sys/kernel/shmmax
[root@jingyong ~]# more /proc/sys/kernel/shmmax
536870912

我修改了512M了,这里对于shmmax参数的修改不会永久的生效,在系统重新启动后
会失效.可以通过修改/etc/sysctl.conf文件来进行永久修改.
在/ect/sysctl.conf文件中增加以下一行这个更改在系统重启后仍然生效
kernel.shmmax=536870912

在修改shmmax参数后，重启数据库使更改生效
在重新启动数据库后我们再来查看共享内存的分配情况:

[root@jingyong ~]# ipcs -sa

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x289516a4 32768      oracle    640        171966464  18
0x00000000 65537      gdm       600        393216     2          dest

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x0bbc1610 98304      oracle    640        154

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

可以看到共享内存分配只分配了一个共享内存段

如果没有修改shmmax参数,oracle在启动过程中就会在alert_.log文件中记录警告:

Starting ORACLE instance(normal)
Thu Nov 21 16:02;02 2012
WARNING:EINVAL creating segment of size 0x0000000033400000
fix shm parameter in /etc/system or equivalent

这是一个警告提示,是建议修改shmmax参数不是强制

修改参数

可以通过alter system或者导入导出来更改spfile的内容
从oracle9i开始，alter system命令增加了一个新的选项scope,scope参数有3个可选值:memory,spfile和both
memory:只改变当前实例运行,重新启动数据库后失效
spfile:只改变spfile的设置,不改变当前实例运行,重新启动数据库后生效
both:同时改变实例及spfile,当前更改立即生效,重新启动数据库后仍然有效.
针对rac环境,alter system还可以指定sid参数,对不同实例进行不同设置.
所以通过spfile修改参数的完整命令如下:

alter system set = scope=memory|spfile|both [sid=]

带有scope=both参数的语句与不带scope参数的效果是一样的

当scope=memory时
修改当前实例的db_cache_advice参数为OFF;

SQL> show parameter db_cache_advice

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON

SQL>alter system set db_cache_advice=off scope=memory;
System altered

SQL> show parameter db_cache_advice

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      OFF

如果观察alert_.log文件,可以发现其中记录了如下一行

alter system set db_cache_advice=off scope=memory;

如果重新启动数据库,这个更改将会丢失

重置spfile参数
当想恢复参数为缺省值是可以使用以下命令:

alter system reset parameter  sid='sid|*';