Oracle-OCP学习笔记:Database buffer cache 性能优化 
Q：current块和cr块区别？
A：
cr块被构造后被读出，就马上没意义了。。马上就可以被覆盖了。。
要修改一个块，只能修改current块。
Q：查看一个对象占用了多少不同状态的buffer？
注：实验的时候可以先执行
alter system flush buffer_cache; |
#将buffer cache的内容清空 |
SYS AS SYSDBA@ORCL>SYS AS SYSDBA@ORCL>selecto.object_name,decode(state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pi')state, count(*) blocks from x$bh b, dba_objects o where b.obj =o.data_object_id and o.object_name = 'T2' group by o.object_name, state orderby blocks desc;
OBJECT_NAME STATE BLOCKS
-------------------- -------------------- ----------
T2 free 2
#2表示t1表还占用2个free状态的块，这是因为虽然系统清空了buffer cache中的数据块的内容，但原链上还有一个指针对向这个块，所有还会显示占有2个块，但这些块当中没有数据。
这时如果再次读取这张表时，就可以看到实际占用的块了：
SYS AS SYSDBA@ORCL>select * from t2;
ID NAME
---------- --------------------
1 r
2 s
SYS AS SYSDBA@ORCL>select o.object_name,decode(state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pi')state, count(*) blocks from x$bh b, dba_objects o where b.obj =o.data_object_id and o.object_name = 'T2' group by o.object_name, state orderby blocks desc;
OBJECT_NAME STATE BLOCKS
-------------------- -------------------- ----------
T2 xcur 2
T2 free 2
下面查看不同的进程读取同一个块的情况：
在当前会话中修改一个块，但不提交：
SYS AS SYSDBA@ORCL>update t2 set name='rsc' whereid=2;
新开一个会话，查看同一行内容：
SYS AS SYSDBA@ORCL>select * from t2; #因为没有显示的提交，所有这个会话查不到任何内容
no rows selected
再回到原会话中，查看情况：
SYS AS SYSDBA@ORCL>select o.object_name, decode(state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pi')state, count(*) blocks from x$bh b, dba_objects o where b.obj =o.data_object_id and o.object_name = 'T2' group by o.object_name, state orderby blocks desc;
OBJECT_NAME STATE BLOCKS
-------------------- -------------------- ----------
T2 free 2
T2 xcur 2
T2 cr 2 #出现的CR块
这时将产生CR块，这是数据库为了确保另一个会话不会读到没有提交的数据。
7.使用各种sql熟知buffer cache使用情况
select distinct object_name, dbarfil, dbablk from x$bh a,dba_objects b
2 where a.obj=b.object_id and object_name='T2';
OBJECT_NAME DBARFIL DBABLK
-------------------- ---------- ----------
T2 6 2393
T2 6 2392
上面表示t2占用两个块(buffer),一个块在6号文件的2393块，一个块在6号文件的2392块。
查看具体的情况：
select object_name, dbarfil, dbablk from x$bh a, dba_objects b
2 where a.obj=b.object_id and object_name='T2';
OBJECT_NAME DBARFIL DBABLK
-------------------- ---------- ----------
T2 6 2392
T2 6 2392
T2 6 2393
T2 6 2393
T2 6 2393
T2 6 2393
T2 6 2393
虽然t2表中物理文件中只有两个块，但在内存中却有7个块，这是因为在构造CR块时，要占用内存的资源，一个块将会被复制多份。所有多个相同的块。
查看上面数据文件和块在内存中的状态：
SYS AS SYSDBA@ORCL>Select class,flag,state,lru_flagfrom x$bh where dbarfil=6 and dbablk=2393;
CLASS FLAG STATE LRU_FLAG
---------- ---------- ---------- ----------
1 524288 ########## 0
1 524288 ########## 0
1 35651584 ########## 0
1 524288 ########## 2
1 0 ########## 4
Elapsed: 00:00:00.02
Q：将buffer cache中占用buffer最多的对象给找出来？
select
o.object_name,
decode(state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',
6,'irec',7,'write',8,'pi') state,
count(*) blocks
from x$bh b, dba_objects o
where b.obj = o.data_object_id and state<>0
group by o.object_name, state
order by blocks asc;
OBJECT_NAME STATE BLOCKS
对象名 对象的状态 占用的内存块
-------------------- -------------------- ----------
SYS_C0015530 xcur 1
MGMT_COLLECTION_TASK xcur 1
S_IDX01
MGMT_JOB_PK xcur 1
STREAMS$_PROPAGATION xcur 1
_PROCESS
AQ$_QUEUE_TABLES_PRI xcur 1
MARY
Q：寻找热块
A：
Select obj object, dbarfil file#, dbablk block#, tchtouches from x$bh where tch>10
2 order by tch asc;
OBJECT FILE# BLOCK# TOUCHES
---------- ---------- ---------- ----------
287 1 2008 59 #每发生一次逻辑读，TOUCHES的值将会增加
287 1 2009 59
注：块被逻辑读的次数多，就是热端，根据块的tch属性判断。
以上表示块2008被访问了59次。
根据文件号和块号，可以取出对象名：
SYS AS SYSDBA@ORCL>select object_name, dbarfil, dbablkfrom x$bh a, dba_objects b where a.obj=b.object_id and dbarfil=1 anddbablk=2009;
OBJECT_NAME DBARFIL DBABLK
-------------------- ---------- ----------
JOB$ 1 2009
JOB$ 1 2009
Q：块的总数
select sum(blocks) from dba_data_files; |
|
SUM(BLOCKS)
-----------
176640
Q：查询当前空闲空间的比例，最好控制在10%以内
select decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEINGUSED'),3,'BEING USED', state) "BLOCK STATUS",count(*) from x$bh groupby decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3,'BEINGUSED',state);
BLOCK STATUS COUNT(*)
---------------------------------------- ----------
BEING USED 74
AVAILABLE 5385
FREE 10696
BEING USED:表示脏块
AVAILABLE:干净的块
FREE：没有使用的块
可用空间=FREE+AVAILABLE
Q：最浪费内存的前10个语句占所有的比例，建议控制在5%以内
A：
select sum(pct_bufgets) "Percent"
from (select rank() over (order by buffer_gets desc) asrank_bufgets, to_char(100 * ratio_to_report(buffer_gets)over(),'999.99')pct_bufgets from v$sqlarea)
3 where rank_bufgets < 11;
Percent
----------
45.85
Q：找出消耗物理I/O资源最大的SQL语句
A：
select disk_reads, substr(sql_text,1,4000) from v$sqlarea order by disk_reads asc;
BEGINordsys.CARTRIDGE.dbms_feature_multimedia(:feature_boolean, :aux_cnt, :feat
ure_info); END;
21827
这个语句消耗21827个物理IO
最消耗内存读的语句：
select buffer_gets, substr(sql_text,1,4000) from v$sqlarea order by buffer_gets asc;
Q：常见的操作系统命令，查看
A：
iostat 1 10
vmstat 1 10
mpstat 1 10
mpstat -P 0 1 #列出0号CPU的使用情况
mpstat -P 1 1 #列出1号CPU的使用情况
top
free
内存读：消耗CPU资源,锁的争用
物理读：消耗I/O资源
ROWID:
表中数据行的物理地址
通过ROWID,可以查到行，对象，ID，文件编号，块号，行号
通过ROWID，可以知道这行数据在那个数据文件的那个块里面。
SYS AS SYSDBA@ORCL>create table t (a int,bvarchar2(4000) default rpad('*',4000,'*'),c varchar2(3000) defaultrpad('*',3000,'*'));
Table created.
Elapsed: 00:00:00.12
SYS AS SYSDBA@ORCL>desc t;
Name Null? Type
------------------------------------------------------------------------------------------- --------------------------------------------------------
A NUMBER(38)
B VARCHAR2(4000)
C VARCHAR2(3000)
SYS AS SYSDBA@ORCL>insert into t(a) values(1);
1 row created.
Elapsed: 00:00:00.01
SYS AS SYSDBA@ORCL>insert into t(a) values(2);
1 row created.
Elapsed: 00:00:00.00
SYS AS SYSDBA@ORCL>insert into t(a) values(3);
1 row created.
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
SYS AS SYSDBA@ORCL>insert into t(a) values(4);
1 row created.
Elapsed: 00:00:00.02
SYS AS SYSDBA@ORCL>select a from t;
A
----------
1
4
3
Elapsed: 00:00:00.02
SYS AS SYSDBA@ORCL>insert into t(a) select rownum+1000from all_users;
39 rows created.
Elapsed: 00:00:00.05
SYS AS SYSDBA@ORCL>selectdbms_rowid,rowid_block_number(rowid),a from t;
select dbms_rowid,rowid_block_number(rowid),a from t
*
ERROR at line 1:
ORA-00904: "ROWID_BLOCK_NUMBER": 标识符无效
Elapsed: 00:00:00.08
SYS AS SYSDBA@ORCL>selectdbms_rowid.rowid_block_number(rowid),a from t;
显示A行对应的数据块
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) A
------------------------------------ ----------
2401 1
2402 4
2403 3
2404 1001
2405 1002
2406 1003
2407 1004
2408 1005
2409 1006
2410 1007
2411 1008
2412 1009
2413 1010
2414 1011
2415 1012
2416 1013
2417 1014
2418 1015
2419 1016
2420 1017
2421 1018
2422 1019
2423 1020
2424 1021
2425 1022
2426 1023
2427 1024
2428 1025
2429 1026
2430 1027
2431 1028
2432 1029
2433 1030
2434 1031
2435 1032
2436 1033
2437 1034
2438 1035
2439 1036
2440 1037
2441 1038
2442 1039
42 rows selected.
查看表中行的ROWID:
SYS AS SYSDBA@ORCL>select rowid from t;
ROWID
------------------
AAAUfpAAGAAAAlhAAA
AAAUfpAAGAAAAliAAB
AAAUfpAAGAAAAljAAA
AAAUfpAAGAAAAlkAAA
AAAUfpAAGAAAAllAAA
关于buffer cache各种SQL语句缓冲区命中率:
SYS AS SYSDBA@ORCL>select(1-(sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db_block_gets',value,0))+sum(decode(name,'consistent_gets',value,0)))))* 100 "Hit Ratio" from v$sysstat;
select (1-(sum(decode(name,'physicalreads',value,0))/(sum(decode(name,'db_block_gets',value,0))+sum(decode(name,'consistent_gets',value,0)))))* 100 "Hit Ratio" from v$sysstat
查看物理读最多的sql语句：
select executions,buffer_gets,disk_reads,first_load_time,sql_text from v$sqlarea order by disk_reads
