关于Oracle x$bh视图.doc
40墨值下载
X$BH
Hello everyone. When we covered Oracle9i in previous issues, we mainly focused on X$BH to further
understand the structure of database buffer. In this issue, I will start a new topic focusing on X$BH.
First of all, I check if there is any change in the status of the object on database buffer when object is
being searched or updated in a single instance environment. Next, I check the status of the object in
RAC environment.
Hopefully, I will talk about SGA management in Oracle10g.
Now, I briefly review the X$BH.
What is X$BH?
X$BH is a source table of dynamic performance view where you can query as SYS user. X$BH
helps
you examine the type and the status of the object in database buffer.
Following query will return a list of tables in database buffer.
!
"#$
$
%&'()*+,-(&./01&.%)2/
33333333333333333333333333333333333333333333333333
)4/*%-(5678989!
)4/*%-(5&,:0:;67899<
0*(-678977
/*%)2678977
/*%)2=2(>678977
0*(-=2(>67898
+(?%5:(5=2(>6789@
%5:(5.0+(=2(>6789A
%5:(5=2(>6789B
For example, the list above indicates that the table CUSTOMER occupies the database buffer in large quantity. In
this case, SQL statements referring to the table CUSTOMER can be a tuning target.
Why? Because this may prove that index is not performed efficiently and full scan or inefficient range scan is
performed. If the table CUSTOMER is extremely larger than other tables, this is not a problem.
Next, I focus on a single object and view STATE column.
Following query will return the status of each object. (Query is narrowed down to a table TEST.)
!CC7CC9CC<CCBCCACC
@CCDCC6C#C
!
C*(/*C
"#$
$
%&'()*+,-(/*,*(&./01&.%)2/
3333333333333333333333333333333333333333333333333333333
*(/*67899<
*(/*6789A
STATE column indicates the following:
FREE: not currently in use
XCUR: exclusive
SCUR: shared current
CR: CR block
READ: being read from disk
MREC: in media recovery mode
IREC: in instance recovery mode
WRITE: writing to disk
PI: past image block involved in cache fusion block transfer
I will explain each of the categories in future issues.
of 45
40墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论