但行好事 莫问前程

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|*';

当遇到ora错误时需要查询错误信息是其实可以在数据库服务器上来查看

[root@jingyong ~]# su - oracle

[oracle@jingyong ~]$ cd $ORACLE_BASE

[oracle@jingyong oracle]$ oerr ora 30012
30012, 00000, "undo tablespace '%s' does not exist or of wrong type"
// *Cause:   the specified undo tablespace does not exist or of the
//           wrong type.
// *Action:  Correct the tablespace name and reissue the statement.

oracle先会使用spfile.ora文件作为启动参数文件

如果该文件不存在就使用查找spfile.ora文件
如果spfile.ora与spfile.ora文件都不存在的话
就会使用init.ora文件
如果上述三个文件都没有就没有办法启动oracle实例
先通过spfile文件来创建pfile文件

SQL> create pfile from spfile;

File created.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db/dbs/spfilejingyong.ora
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

删除spfilejingyong.ora文件

[oracle@jingyong dbs]$ mv spfilejingyong.ora spfilejingyong.ora.bak

SQL> startup nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              75499764 bytes
Database Buffers           88080384 bytes
Redo Buffers                2973696 bytes
SQL> show parameter spfile

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
spfile                               string

从上面可以看去现在使用的是initjingyong.ora参数文件

ora_rowscn

对于每一行数据,ora_rowscn返回每一行最近被修改的大概时间.这对于判断一行数据
大概是在什么时间被修改的还是有用的.因oracle是通过事务提交对行所在数据块来
进行scn的跟踪的所以说它不精确.可以通过在创建表时使用行级别的依赖跟踪来获得
一个更加精确的scn.create table … norowdependencies|rowdependencies

在对视图进行查询时不能使用ora_rowscn.但对于视图的基表是可以使用ora_rowscn.
也能在update或delete语句中的where子句中使用ora_rowscn

ora_rowscn不能用于回闪查询,但是可以用回闪版本查询来代替ora_rowscn.

ora_rowscn也不能用于外部表

获取行被修改的大根的scn

SQL> select ora_rowscn,t.test_id from test_jy t;

ORA_ROWSCN               TEST_ID
---------- ---------------------
    625591                     3

通过scn来获取修改行记录大概的时间

SQL> select scn_to_timestamp(ora_rowscn),t.test_id from test_jy t;

SCN_TO_TIMESTAMP(ORA_ROWSCN)                                                                   TEST_ID
-------------------------------------------------------------------------------- ---------------------
20-12月-12 06.23.22.000000000 下午                                                                   3

也可以通过时间来获得scn

SQL> select timestamp_to_scn(scn_to_timestamp(ora_rowscn)),t.test_id from test_jy t;

TIMESTAMP_TO_SCN(SCN_TO_TIMEST               TEST_ID
------------------------------ ---------------------
                        625590                     3

在使用序列的currval和nextval时的限制 创建一个序列

create sequence test_seq minvalue 1 maxvalue 10000000 start with 1 increment by 1 cache 20;

在delete,select,update语句的子查询中不能使用sequence的值

SQL> delete from test_jy where test_id < (select test_seq.currval from dual); 
delete from test_jy where test_id <(select test_seq.currval from dual) 
ORA-02287: 此处不允许序号 

SQL> select * from test_jy where test_id < (select test_seq.currval from dual); 
select * from test_jy where test_id <(select test_seq.currval from dual) 
ORA-02287: 此处不允许序号 

SQL> update test_jy set test_id=0 where test_id < (select test_seq.currval from dual); 
update test_jy set test_id=0 where test_id <(select test_seq.currval from dual) 
ORA-02287: 此处不允许序号 

在查询视图或物化视图时

SQL> select a.* from test_v a where a.userid
带有distinct操作符的select语句不能使用 
SQL> select distinct a.*,test_seq.currval from test_v a ; 
select distinct a.*,test_seq.currval from test_v a 
ORA-02287: 此处不允许序号 

有group by,order by操作的select语句不能使用 
SQL> select test_jy.*,test_seq.currval from test_jy group by test_jy.test_id; 
select test_jy.*,test_seq.currval from test_jy group by test_jy.test_id 
ORA-02287: 此处不允许序号 

SQL> select test_jy.*,test_seq.currval from test_jy order by test_jy.test_id; 
select test_jy.*,test_seq.currval from test_jy order by test_jy.test_id 
ORA-02287: 此处不允许序号 

有UNION, INTERSECT, MINUS操作符的语句不能使用
 
SQL> select test_jy.*,test_seq.currval from test_jy where test_id=1 
2 union 
3 select test_jy.*,test_seq.currval from test_jy where test_id=2;
select test_jy.*,test_seq.currval from test_jy where test_id=1 
union 
select test_jy.*,test_seq.currval from test_jy where test_id=2 
ORA-02287: 此处不允许序号 

SQL> select test_jy.*,test_seq.currval from test_jy where test_id=1 
2 intersect 
3 select test_jy.*,test_seq.currval from test_jy where test_id=2; 
select test_jy.*,test_seq.currval from test_jy where test_id=1 
intersect 
select test_jy.*,test_seq.currval from test_jy where test_id=2 
ORA-02287: 此处不允许序号 

SQL> select test_jy.*,test_seq.currval from test_jy where test_id=1 
2 minus 
3 select test_jy.*,test_seq.currval from test_jy where test_id=2; 
select test_jy.*,test_seq.currval from test_jy where test_id=1 
minus 
select test_jy.*,test_seq.currval from test_jy where test_id=2 
ORA-02287: 此处不允许序号 

在select语句中的where子句中 
SQL> select test_jy.* from test_jy where test_id
在create table或alter table语句的中default值是不能使用sequence 
SQL> alter table test_jy modify test_id number(20) default test_seq.currval; 
alter table test_jy modify test_id number(20) default test_seq.currval 
ORA-00984: 列在此处不允许 

还有就在check约束中不能使用