system默认:manager
sys默认:change_on_install
使用SQL Plus登录数据库时,system使用密码manager可直接登录。
但如果是sys用户,密码必须加上as sysdba,即完整密码为:change_on_install as sysdba
X$BH中各字段意义
buffer header数据,可以从数据库的数据字典表中查询得到,这张字典表就是x$bh,其中的bh就是指buffer
headers,每个buffer在x$bh中都存在一条记录.
buffer
header中存储每个buffer容纳的数据块的文件号,块地址,状态等重要信息,根据这些信息,结合dba_extents视图,可以很容易地找到每个buffer对应的对象信息:
x$bh中还有一个重要的字段TCH,TCH为Touch的缩写,表示一个Buffer的访问次数,buffer被访问的次数越多,说明该buffer就越抢手,也就可能存在热块竞争的问题
可以结合DUMP
BH来对比X$BH中字段,详见:http://blog.csdn.net/haibusuanyun/article/details/17525523
SYS@ bys3>desc x$bh ----总共57个字段:数据库版本:11.2.0.4
Name Type
-----------------------------
ADDR RAW(4) --Hex address of the Buffer
Header.视图中某行的地址-缓冲区头的十六进制地址
INDX NUMBER --Buffer Header number
INST_ID NUMBER
HLADDR RAW(4) --即hash chain latch
address可以和v$latch_children.addr关联,把latch和数据块关联起来
BLSIZ NUMBER --块大小
NXT_HASH RAW(4) --下一个BH的HASH值
PRV_HASH RAW(4) --前一个BH的HASH值
NXT_REPL RAW(4) --下一个BH的在LRU链上HASH值
PRV_REPL RAW(4) --前一个BH的在LRU链上HASH值
FLAG NUMBER ---块的状态,在BH中可能是buffer_dirty block_written_once
redo_since_read ,详见最后
FLAG2 NUMBER
LOBID NUMBER
RFLAG NUMBER
SFLAG NUMBER
LRU_FLAG NUMBER --- LRU_FLAG,详见最后
TS# NUMBER --tablespace number表空间号
FILE# NUMBER --块在数据库内绝对文件号
DBARFIL NUMBER --块的相对文件号
DBABLK NUMBER --块号--在数据文件上的
CLASS NUMBER --太长,见最下面。
STATE NUMBER --太长,见最下面。
MODE_HELD NUMBER --
CHANGES NUMBER
CSTATE NUMBER
LE_ADDR RAW(4) --Lock Element address (OPS)
DIRTY_QUEUE NUMBER --LRUW上的buffer
SET_DS RAW(4) -- Buffer cache set this buffer is under
OBJ NUMBER --对象号
BA RAW(4) --BUFFER ADDRESS--在内存中的地址
CR_SCN_BAS NUMBER --Consistent Read SCN base 一致读SCN低位
CR_SCN_WRP NUMBER --Consistent Read SCN wrap 一致读SCN高位
CR_XID_USN NUMBER -- CR XID Undo segment no
CR_XID_SLT NUMBER -- CR XID slot
CR_XID_SQN NUMBER --CR XID Sequence
CR_UBA_FIL NUMBER -- CR UBA file
CR_UBA_BLK NUMBER -- CR UBA Block
CR_UBA_SEQ NUMBER --CR UBA sequence
CR_UBA_REC NUMBER --CR UBA record
CR_SFL NUMBER --
CR_CLS_BAS NUMBER
CR_CLS_WRP NUMBER
LRBA_SEQ NUMBER --Lowest RBA needed to recover block in cache
LRBA_BNO NUMBER
HSCN_BAS NUMBER ---SCN的低位
HSCN_WRP NUMBER --SCN的高位
HSUB_SCN NUMBER
US_NXT RAW(4)
US_PRV RAW(4)
WA_NXT RAW(4)
WA_PRV RAW(4)
OQ_NXT RAW(4) --- 对象队列前一个HASH值
OQ_PRV RAW(4) --- 对象队列后一个HASH值
AQ_NXT RAW(4) ---辅助对象队列前一个HASH值
AQ_PRV RAW(4) ---辅助对象队列后一个HASH值
OBJ_FLAG NUMBER
TCH NUMBER --Touch的缩写,表示一个Buffer的访问次数
TIM NUMBER --Touch Time
CR_RFCNT NUMBER
SHR_RFCNT NUMBER
###########################################
附:
flag中,每位代表如下含义:
bit bit
0 buffer_dirty 14 stale
1 notify_after_change 15 deferred_ping
2 mod_started 16 direct_access
3 block_has_been_logged 17 hash_chain_dump
4 temp_data 18 ignore_redo
5 being_written 19 only_sequential_access
6 waiting_for_write 20 prefetched_block
7 multiple_waiters 21 block_written_once
8 recovery_reading 22 logically_flushed
9 unlink_from_lock 23 resilvered_already
10 down_grade_lock 25 redo_since_read
11 clone_being_written 29 plugged_from_foreign_db
12 reading_as_CR 30 flush_after_writing
13 gotten_in_current_mode
class:表示buffer header对应block的类型:
1=data block, 9=2nd level bmb,
2=sort block, 10=3rd level bmb,
3=save undo block, 11=bitmap block,
4=segment header, 12=bitmap index block,
5=save undo header, 13=unused,
6=free list, 14=undo header,
7=extent map, 15=undo block
state:
0, FREE, no valid block image
1, XCUR, a current mode block, exclusive to this instance
正在被当前的instance独占。
2, SCUR, a current mode block, shared with other
instances正在被当前的instance共享
3, CR, a consistent read (stale) block image 一致读
4, READ, buffer is reserved for a block being read from disk
正在从磁盘上读取块
5, MREC, a block in media recovery mode 处于介质恢复模式
6, IREC, a block in instance (crash) recovery mode处于实例恢复模式
lru_flag
0,'free',1,'xcur',2,'scur',
3,'cr', 4,'read',5,'mrec',
6,'irec',7,'write',8,'pi', 9,'memory'
10,'mwrite',11,'donated', 12,'protected',
13,'securefile', 14,'siop',15,'recckpt',
16, 'flashfree', 17, 'flashcur', 18, 'flashna'
一般情况下是含有全表扫描的sql会造成热点块。
1、找到最热的数据块的latch和buffer信息
select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps
from (select * from
(select addr,ts#,file#,dbarfil,dbablk,tch,hladdr from
x$bh order by tch desc)
where rownum <11)
a,
(select addr,gets,misses,sleeps
from v$latch_children
where name= 'cache
buffers chains ')
b
where a.hladdr=b.addr;
2、找到热点buffer对应的对象信息:
col owner for
a20
col segment_name for
a30
col segment_type for
a30
select distinct e.owner,e.segment_name,e.segment_type from
dba_extents e,
(select * from
(select addr,ts#,file#,dbarfil,dbablk,tch from
x$bh order by tch desc)
where rownum <11)
b
where e.relative_fno=b.dbarfil
and e.block_id <=b.dbablk
and e.block_id+e.blocks> b.dbablk;
3、找到操作这些热点对象的sql语句:
break on hash_value
skip 1
select hash_value,sql_text
from v$sqltext
where (hash_value,address)
in (select a.hash_value,a.address
from v$sqltext
a,(select distinct
a.owner,a.segment_name,a.segment_type
from dba_extents
a,
(select dbarfil,dbablk from
(select dbarfil,dbablk
from x$bh order by tch
desc) where
rownum <11) b
where a.relative_fno=b.dbarfil
and a.block_id <=b.dbablk and a.block_id+a.blocks> b.dbablk)
b
where a.sql_text like
'% '||b.segment_name|| '% ' and b.segment_type= 'TABLE ')
order by hash_value,address,piece;