oracle学习笔记 使用各种SQL来熟知buffer cache使用情况
这节课我们通过一些sql语句更深入的了解一下我们的buffercache
一）几个sql语句
先对select结果进行一下格式化处理
SQL> set pagesize 1000
SQL> set linesize 120
SQL> column object_name format a10
(1)
select distinct object_name,DBARFIL,DBABLK from x$bh a,dba_objects b
where a.obj=b.object_id and object_name='T2';
将t2这个表这个对象使用内存的情况列出来
使用上节课讲的内容查询
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
order by blocks desc;
结果
OBJECT_NAM STATE BLOCKS
---------- ----- ----------
T2 cr 2
T2 xcur 2
说明t2表在内存有4个块
本节语句执行和输出结果
SQL> select distinct object_name,DBARFIL,DBABLK from x$bh a,dba_objects b
where a.obj=b.object_id and object_name='T2'; 2
OBJECT_NAM DBARFIL DBABLK
---------- ---------- ----------
T2 1 62305
T2 1 62306
结果中列出了t2在内存里面的两个块
对应磁盘上1号文件的62305块和62306块
因为语句中加了唯一约束distinct，结果经过了过滤
将distinct去掉
select object_name,DBARFIL,DBABLK from x$bh a,dba_objects b
where a.obj=b.object_id and object_name='T2';
如果t2表的block块在内存有对应的buffer
则结果输出每个buffer对应的数据文件中的block的对应文件号和块号
执行和结果
SQL> select object_name,DBARFIL,DBABLK from x$bh a,dba_objects b
where a.obj=b.object_id and object_name='T2'; 2
OBJECT_NAM DBARFIL DBABLK
---------- ---------- ----------
T2 1 62305
T2 1 62306
T2 1 62306
T2 1 62306
结果中列出了四个列，说明t2的block块对应有四个buffer块在内存
和上节课讲的块状态查询结果相符。
结果说明实际在x$bh里面t2表占4行
但t2在dbf数据文件只是占两个块
因为dbf中两个block
可以对应buffercache中的两个current还可以有两个cr
这个例子告诉大家
虽然某个表有两个块但在内存它可能有4个5个buffer
(2)
select class, flag, state, lru_flag from x$bh
where dbarfil = 1 and dbablk = 62306;
我知道了内存块对应的磁盘块的DBARFIL(块的相对文件号)和DBABLK(在数据文件上的块号)
如1号文件的62305块在内存里面有
我就可以查这个块在内存的状态
SQL> select class, flag, state, lru_flag from x$bh
where dbarfil = 1 and dbablk = 62305; 2
CLASS FLAG STATE LRU_FLAG
---------- ---------- ---------- ----------
4 0 1 0
4 0 0 4
STATE列有一个1为XCUR，一个0为free
也就是62305的block块在内存中对应1个current一个free块
free实际没有占空间
(3)
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;
把目前buffercache里面的所有对象的内存的占用情况列出来了
这个语句object_name列较长
调整一下结果的输出格式
column object_name format a30
结果内容较长只取其中一部分
OBJECT_NAME STATE BLOCKS
------------------------------ ----- ----------
WRI$_ADV_MESSAGE_GROUPS_PK xcur 1
RULE_MAP$ xcur 1
AQ$_QUEUE_TABLES xcur 1
.
.
REFCON$ xcur 863
VIEWTRCOL$ xcur 863
COL$ xcur 863
436 rows selected.
如结果中
WRH$_SYS_TIME_MODEL xcur 8
对象WRH$_SYS_TIME_MODEL占用了8个xcur状态块
KOTMD$ xcur 9
KOTMD$对象在内存里面占了9个xcur块
这个语句中
where b.obj = o.data_object_id and state <> 0
其中state <> 0说明结果中把free状态的块去掉了
如果没有这个条件结果中会有很多对象对应的free状态块
order by blocks asc；
结果以blocks列值大小升序排列
asc为升序排列,换为desc是降序排列
如结果中最后一个对象
COL$ xcur 863
COL$表在内存里面占用了863个xcur块
从结果中看它占的xcur块数最多
通过这个命令可以将
buffercache里面占用buffer最多的对象给找出来
非常有用
可以知道目前我的buffercache里面有哪些对象
那些对象分别占用了多少的buffer
这是我们关心的
(4)
select decode(wbpd.bp_id,
1,'keep',
2,'recycle',
3,'default',
4,'2k pool',
5,'4k pool',
6,'8k pool',
7,'16k pool',
8,'32k pool',
'unknown') pool,
bh.owner,
bh.object_name object_name,
count(1) numOfBuffers
from x$kcbwds wds,
x$kcbwbpd wbpd,
(select set_ds, x.addr, o.name object_name, u.name owner
from sys.obj$ o, sys.user$ u, x$bh x
where o.owner# = u.user#
and o.dataobj# = x.obj
and x.state != 0
and o.owner# != 0
) bh
where wds.set_id >= wbpd.bp_lo_sid
and wds.set_id <= wbpd.bp_hi_sid
and wbpd.bp_size != 0
and wds.addr = bh.set_ds
--and object_name='T2'
group by decode(wbpd.bp_id,
1,'keep',
2,'recycle',
3,'default',
4,'2k pool',
5,'4k pool',
6,'8k pool',
7,'16k pool',
8,'32k pool',
'unknown'),
bh.owner,
bh.object_name
order by 1, 4, 3, 2;
这个语句和上面的(3)号语句差不多
只不过它做的更详细一些
POOL OWNER OBJECT_NAME NUMOFBUFFERS
-------- ------------------------------ ------------------------------ ------------
default SYSTEM AQ$_QUEUES_CHECK 1
default SYSTEM AQ$_QUEUE_TABLES 1
.
.
default SYSMAN MGMT_METRICS_1HOUR_PK 58
default SYSMAN MGMT_METRICS 81
default SYSMAN MGMT_SEVERITY 113
91 rows selected.
结果较多只列出一部分
如其中一句
default SYSMAN MGMT_METRICS 81
属于SYSMAN用户的MGMT_METRICS对象占了81个buffer
其实用(3)号语句就可以了，就够用了
(5)
SELECT
obj object,
dbarfil file#,
dbablk block#,
tch touches
FROM
x$bh
WHERE
tch > 10
ORDER BY
tch asc;
为寻找热块
这里面有个热块的概念
默认一个buffer在内存里面会发生很多次逻辑读logic read
每发生一次逻辑读这个块的一个状态touch都会增加
LRU链是最近最少使用链
一端挂的是冷块一端挂的是热块
一个块被逻辑读的次数多就在热端，被逻辑读的次数少就在冷端
判读这个块是冷和热就是根据这个块的touch属性
块的touch值越大说明这个块越热
越热说明这个块被频繁的逻辑读
说明这个块是个热块
上面(5)语句会将我们数据库里面目前所有的块给列出来了
并以touch列升序排列
结果较多只取一部分
OBJECT FILE# BLOCK# TOUCHES
---------- ---------- ---------- ----------
40 1 251 11
8796 3 30884 11
96 1 722 11
.
.
237 1 1657 362
237 1 1658 370
338 1 2682 374
210 rows selected.
1号文件的2682块touches值374目前最热
这个块被访问是因为这个对象被访问
我们可以根据1 和 2682 把对象的名字取出来
先格式化
column object_name format a12
然后使用命令：
select object_name,DBARFIL,DBABLK from x$bh a,dba_objects b
where a.obj=b.object_id and DBARFIL=1 and DBABLK=2682;
执行结果
OBJECT_NAME DBARFIL DBABLK
------------ ---------- ----------
SYS_C00648 1 2682
SYS_C00648 1 2682
说明1号文件 2682块 对应的是SYS_C00648对象
我们发现这个对象目前被最频繁的访问
寻找热块可以把最热的块找到
也可以把热块对应的对象的名字找出来
(6)
select
sum(blocks)
from
dba_data_files;
将整个数据库所有数据文件中block的总和取出来
也就是说数据库里面有那么多的数据文件到底有多少个block
执行结果
SUM(BLOCKS)
-----------
116480
从dba_data_files发现数据库有116480个blocks
目前总计有十几万个block
(7)
select decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3,
'BEING USED', state) "BLOCK STATUS",count(*)
from x$bh
group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED',
state);
这个语句将buffercache里面
这三类块
BEING USED 正在使用的，目前这个块是脏的认为被使用了
FREE 是没有使用的
AVAILABLE 是干净的
把它们占的块数给它们列出来
执行结果
BLOCK STATUS COUNT(*)
---------------------------------------- ----------
BEING USED 507
AVAILABLE 5832
FREE 7202
对我们来讲可用的块是
AVAILABLE 5832
FREE 7202
目前我们来看
因为我使用的库是实验没怎么用，FREE占了百分之五十多
目前buffercache里面仍有大量的空间被剩余
我们有时候要判断目前buffercache到底空间够不够
第一看FREE
free是彻底没用的
free的空间最好在10%以内
free太多了没有意义浪费资源，而且还有些负作用不是很明显但是有副作用
第二看AVAILABLE就是clean干净的
他俩加起来是可用的
BEING USED是dirty脏的由DBWn进程写到磁盘变干净后才能被再次使用
(8)
select sum(pct_bufgets) "Percent"
from (select rank() over ( order by buffer_gets desc ) as
rank_bufgets,to_char(100 * ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sqlarea )
where rank_bufgets < 11;
将最浪费内存的前十个sql语句所占有的比例输出
结果
Percent
----------
51.67
最浪费内存的前十个sql语句它占用内存的比例为51.67
sql语句需要访问对象
前十个sql语句访问内存对象占用了总内存读的51%
这个其实没有很大的意义
有这个例子
是因为我们经常出现
数据库当前负载不是很多只有五六个用户在访问
但是因为某个用户执行了一个大的sql语句
导致数据库速度变得很慢
我们就可以执行这个(8)sql语句
如果查出发现这个数字很高如95%
就确认一点
在前十名里面某一个或多个sql语句它占用非常大的资源
进一步找前十个sql语句分别是谁，一个一个的去判断
通过上面的语句可大体判断出是前十个sql语句有问题
有这么一个小意义，有时会用到
(9)
select disk_reads, substr(sql_text,1,4000) from v$sqlarea order by disk_reads asc;
找出消耗物理IO资源最大的的SQL语句
给大家列出来，有时候会用到
语句中disk_reads为物理io
以所有sql语句所发生的所造成的物理io顺序给排一下序
我们一般喜欢用升序asc
结果较多只列出一部分
DISK_READS
----------
SUBSTR(SQL_TEXT,1,4000)
----------------------------------------------------------------------------------------------------
0
SELECT LAST_LOAD_TIME FROM MGMT_TARGETS WHERE TARGET_GUID=:B1
0
SELECT BLACKOUT_GUID, START_TIME, END_TIME, STATUS FROM MGMT_BLACKOUT_WINDOWS WHERE TARGET_GUID=:B2
AND START_TIME <= :B1
.
.
1442
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and ro
w#=:3 order by bucket
3029
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:
2 and version=:3 order by piece#
435 rows selected.
发现最后的sql语句最消耗物理io，消耗了3029个物理io
如果你发现某个sql语句物理io特别的高的话
就找这个sql语句针对这个sql语句找问题
语句中用到的v$sqlarea视图
持续跟踪所有shared pool中的共享cursor，在shared pool中的每一条SQL语句都对应一列
它记录了shared SQL area中语句统计信息，在分析SQL语句资源使用方面非常重要
我们可以看看它的结构
可以执行desc v$sqlarea;语句
结果较多不列了
如BUFFER_GETS为内存读、逻辑读
通过(9)语句你会发现这个数据库物理io非常忙
当然也可以通过linux操作系统的一些命令观察和判断我们目前的一些情况
如前面讲过的常用的一些操作系统的命令：iostat vmstat top free
这里再介绍一个命令mpstat
先执行一下
[oracle@redhat4 ~]$ mpstat
Linux 2.6.9-78.ELsmp (redhat4) 2016年09月22日
11时16分33秒 CPU %user %nice %system %iowait %irq %soft %idle intr/s
11时16分33秒 all 0.28 0.02 0.39 0.50 0.01 0.00 98.81 1025.73
mpstat是Multiprocessor Statistics的缩写，是实时系统监控工具
在多CPUs系统里，其不但能查看所有CPU的平均状况信息，而且能够查看特定CPU的信息
mpstat最大的特点是：可以查看多核心cpu中每个计算核心的统计数据；
而类似工具vmstat只能查看系统整体cpu情况
mpstat的语法如下
mpstat [-P {cpu|ALL}] [internal [count]]
-P {cpu | ALL} 表示监控哪个CPU， cpu在[0,cpu个数-1]中取值
为ALL时每个处理器信息都列出，同时也列出所有处理器的总体信息，
无此参数时只列出所有处理器的总体信息
internal 相邻的两次采样的间隔时间，无此参数时结果只输出一次
count 采样的次数，count只能和internal一起使用
当没有任何参数时，mpstat则显示系统启动以后所有信息的平均值
有参数值时如：
mpstat -P ALL 2 5查看多核CPU所有的核心的和每个核心的当前运行状况信息，
每2秒更新一次，显示5次，最后再输出一次执行命令期间的平均值
结果中值的含义
%user 表示处理用户进程所使用 CPU 的百分比。用户进程是用于应用程序（如Oracle数据库）的非内核进程。
%nice 表示使用 nice 命令对进程进行降级时 CPU 的百分比。
%system 表示内核进程使用的 CPU 百分比
%iowait 表示等待进行 I/O 所使用的 CPU 时间百分比
%irq 表示用于处理系统中断的 CPU 百分比
%soft 表示用于软件中断的 CPU 百分比
%idle 显示 CPU 的空闲时间
%intr/s 显示每秒 CPU 接收的中断总数
这个命令老师用的很多
列一些用法
mpstat 1 10
列出所有处理器的总体信息，1秒输出一次，共输出10次，最后再输出一下这十次的平均值
mpstat -P 0 1
列出0号处理器的信息，1秒输出一次，直到用户把这个命令停止，这里可以使用Ctrl+C完成
我的实验环境有两个cpu
上面我把零号cpu的使用情况列出来了
也可以把1号cpu的使用情况列出来
mpstat -P 1 1
mpstat对多cpu的环境非常有用
如果把语句(9)disk_reads可以改写为BUFFER_GETS
select BUFFER_GETS, substr(sql_text,1,4000) from v$sqlarea order by BUFFER_GETS asc;
找出消耗逻辑读资源最大的SQL语句
结果中最后部分：
BUFFER_GETS
-----------
SUBSTR(SQL_TEXT,1,4000)
------------------------------------------------------------------------------------------------------------------------
.
.
97945
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
101741
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval,
hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
556 rows selected.
最后一句最消耗资源最消耗BUFFER_GETS就是内存读
内存读主要消耗cpu资源，特别产生一些锁(latch)的争用
物理读主要消耗io资源
(10)
SELECT /*+ ORDERED USE_HASH(o u) MERGE */
DECODE(obj#,
NULL,
to_char(bh.obj),
u.name || '.' || o.name) name,
COUNT(*) total,
SUM(DECODE((DECODE(lru_flag, 8, 1, 0) + DECODE(SIGN(tch - 2), 1, 1, 0)),
2,
1,
1,
1,
0)) hot,
SUM(DECODE(DECODE(SIGN(lru_flag - 8), 1, 0, 0, 0, 1) +
DECODE(tch, 2, 1, 1, 1, 0, 1, 0),
2,
1,
1,
0,
0)) cold,
SUM(DECODE(BITAND(flag, POWER(2, 19)), 0, 0, 1)) fts,
SUM(tch) total_tch,
ROUND(AVG(tch), 2) avg_tch,
MAX(tch) max_tch,
MIN(tch) min_tch
FROM x$bh bh, sys.obj$ o, sys.user$ u
WHERE
bh.obj <> 4294967295
AND bh.state in (1, 2, 3)
AND bh.obj = o.dataobj#(+)
AND bh.inst_id = USERENV('INSTANCE')
AND o.owner# = u.user#(+)
-- AND o.owner# > 5
AND u.name NOT like 'AURORA$%'
GROUP BY DECODE(obj#,
NULL,
to_char(bh.obj),
u.name || '.' || o.name)
ORDER BY total desc
/
结果为buffercache的具体使用情况
跟前面的语句很相似
直接执行结果较乱，所以先执行
column name format a20
set linesize 160
set pagesize 1000
对select语句进行格式化
另外list命令可显示最近执行的一个sql命令
结果中name列占用空间较多可以使用
SQL> column name format a20
改变它的显示所占用的屏幕宽度为20个字符
它不会截掉多出的部分而是改为多行输出name列
每行的宽度一样，直至此列所有内容输出完
执行结果较多只列出一部分
NAME TOTAL HOT COLD FTS TOTAL_TCH AVG_TCH MAX_TCH MIN_TCH
-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
SYS.ICOLDEP$ 841 828 13 767 7397 8.8 75 1
SYS.COLTYPE$ 841 828 13 767 7397 8.8 75 1
.
.
SYSMAN.MGMT_NOTIFY_D 1 1 0 0 75 75 75 75
EVICES
SYSMAN.PK_MGMT_JOB_C 1 1 0 0 3 3 3 3
ALLBACKS
SYSMAN.PK_MGMT_JOB_E 1 1 0 0 3 3 3 3
XEC_EVPARAMS
652 rows selected.
它列出了所有的对象以及所有对象的块使用情况
也是进一步通过它来了解buffercache
name列为对象，后面是这个对象使用的各种块的状态
(11)
SELECT t.name AS tablespace_name,
o.object_name,
SUM(DECODE(bh.status, 'free', 1, 0)) AS free,
SUM(DECODE(bh.status, 'xcur', 1, 0)) AS xcur,
SUM(DECODE(bh.status, 'scur', 1, 0)) AS scur,
SUM(DECODE(bh.status, 'cr', 1, 0)) AS cr,
SUM(DECODE(bh.status, 'read', 1, 0)) AS read,
SUM(DECODE(bh.status, 'mrec', 1, 0)) AS mrec,
SUM(DECODE(bh.status, 'irec', 1, 0)) AS irec
FROM v$bh bh
JOIN dba_objects o ON o.data_object_id = bh.objd
JOIN v$tablespace t ON t.ts# = bh.ts#
GROUP BY t.name, o.object_name
order by xcur desc
buffer cache中每一个对象的使用情况
也是把内存的使用情况给列出来
先准备一下这个语句的输出格式
SQL> column object_name format a30
SQL> column TABLESPACE_NAME format a10
SQL> set linesize 120
然后执行语句，执行结果较多只列出一部分
TABLESPACE OBJECT_NAME FREE XCUR SCUR CR READ MREC IREC
---------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
SYSTEM COL$ 254 841 0 0 0 0 0
SYSTEM REFCON$ 254 841 0 0 0 0 0
SYSTEM NTAB$ 254 841 0 0 0 0 0
.
.
SYSAUX I_WRI$_OPTSTAT_TAB_OBJ#_ST 3 0 0 0 0 0 0
SYSAUX WRI$_OPTSTAT_HISTGRM_HISTORY 34 0 0 0 0 0 0
SYSAUX WRI$_DBU_HIGH_WATER_MARK 1 0 0 0 0 0 0
SYSAUX SYS_LOB0000008932C00004$$ 1 0 0 0 0 0 0
696 rows selected.
结果为对象和对象的使用情况
(12)
column c0 heading "Owner" format a12
column c1 heading "Object|Name" format a30
column c2 heading "Object|Type" format a8
column c3 heading "Number of|Blocks in|Buffer|Cache" format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer" format 999
column c5 heading "Buffer|Pool" format a7
column c6 heading "Block|Size" format 99,999
select
buffer_map.owner c0,
object_name c1,
case when object_type = 'TABLE PARTITION' then 'TAB PART'
when object_type = 'INDEX PARTITION' then 'IDX PART'
else object_type end c2,
sum(num_blocks) c3,
(sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
buffer_pool c5,
sum(bytes)/sum(blocks) c6
from
buffer_map,
dba_segments s
where
s.segment_name = buffer_map.object_name
and
s.owner = buffer_map.owner
and
s.segment_type = buffer_map.object_type
and
nvl(s.partition_name,'-') = nvl(buffer_map.subobject_name,'-')
group by
buffer_map.owner,
object_name,
object_type,
buffer_pool
having
sum(num_blocks) > 10
order by
sum(num_blocks) desc
;
把一些段一些对象具体的对buffercache的使用情况给列出来
老师执行这条语句有正确结果输出
但在我的实验环境中的结果是：
buffer_map,
*
ERROR at line 12:
ORA-00942: table or view does not exist
说是没有buffer_map这个表或视图
在我的数据库中寻找名为buffer_map的对象
SQL> select TABLE_NAME from dba_tables where TABLE_NAME like '%BUFFER_MAP%';
no rows selected
数据库中的所有表中没有
SQL> select VIEW_NAME from dba_views where VIEW_NAME like '%BUFFER_MAP%';
no rows selected
数据库的所有视图中没有
SQL> select object_name from dba_objects where object_name like '%BUFFER_MAP%';
no rows selected
甚至数据库中所有的对象中也没有
所以推测这个buffer_map表应该是老师自己建的
我这里就看不到结果了
(13)
REM dbbuffer
select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
o.name object_name,count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds;
with bh_lc as
(select /*+ ORDERED */
lc.addr, lc.child#, lc.gets, lc.misses, lc.immediate_gets,
lc.immediate_misses, lc.spin_gets, lc.sleeps,
bh.hladdr, bh.tch tch, bh.file#, bh.dbablk, bh.class,
bh.state, bh.obj
from
x$kslld ld,
v$session_wait sw,
v$latch_children lc,
x$bh bh
where lc.addr =sw.p1raw
and sw.p2= ld.indx
and ld.kslldnam='cache buffers chains'
and lower(sw.event) like '%latch%'
and sw.state='WAITING'
and bh.hladdr=lc.addr
)
select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
bh_lc.child#, bh_lc.gets,
bh_lc.misses, bh_lc.immediate_gets,
bh_lc.immediate_misses, spin_gets, sleeps
from
bh_lc,
dba_objects o
where bh_lc.obj = o.object_id(+)
union
select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets,
bh_lc.immediate_misses, spin_gets, sleeps
from
bh_lc,
dba_objects o
where bh_lc.obj = o.data_object_id(+)
order by 1,2 desc
/
也是把内存对象使用情况列出来
部分结果：
SUBCACHE OBJECT_NAME BLOCKS
------------ ------------------------------ ----------
DEFAULT RLM$SCHACTIONORDER 1
DEFAULT SYS_IOT_TOP_49769 1
DEFAULT MGMT_TARGET_PROPERTIES 3
DEFAULT MGMT_METRICS_RAW_PK 24
.
.
DEFAULT MGMT_DB_LICENSE_ECM_PK 2
DEFAULT MGMT_HA_RMAN_CONFIG_ECM 4
DEFAULT MGMT_HA_RAC_INTR_CONN_PK 1
389 rows selected.
最后面几个语句没有详细讲，
大家需要将语句列出来执行再详细的看里面的一些结果
上面总共十三个sql语句中老师用的比较多的是(3)号语句，希望大家掌握
讲的不多，只演示一下
只是把语句给大家列出来
同学如果都学过了都熟的话可以直接拿过来用
这里只是告诉大家有这些命令
你也可以知道一些结果
如果没有相关知识现在看结果可能模糊一些
二）dbms_rowid包
dbms_rowid是oracle的一个知识点
有一个表，在磁盘上的一个dbf文件中
表中有三个数据
假设这个表在dbf中占两个块
现在有这么个要求
1、这个表里面的这三行的行地址是多少
行地址就是表中某一行在那个文件的那个块里面的第几行
这叫rowid
就是这个行的物理地址
2、根据表中行只想知道它在那个块里面
实际中有很多这些需求
对表t的操作老师没有实际演示
我在这里对t表的操作也不实际做了只讲一下大概过程
首先创建表t
create table t
( a int,
b varchar2(4000) default rpad('*',4000,'*'),
c varchar2(3000) default rpad('*',3000,'*' )
);
插入一些数据
insert into t (a) values ( 1);
insert into t (a) values ( 2);
insert into t (a) values ( 3);
删一些数据
delete from t where a = 2 ;
插入一些数据
insert into t (a) values ( 4);
发现有三行数据
SQL> select a from t;
A
----------
1
4
3
又插入一些数据
insert into t(a) select rownum from all_users;
commit;
更新一些数据
update t set b=null,c=null;
commit;
insert into t(a) select rownum+1000 from all_users;
前面做的是
建了一个表，然后对表进行大量的增删改查
然后访问t表把t表的所有数据列出来
SQL> select dbms_rowid.rowid_block_number(rowid), a from t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) A
------------------------------------ ----------
42610 1
42611 4
42612 3
42613 1
42613 1017
42614 2
42614 1016
42615 3
42615 1015
42616 4
42616 1014
43785 5
43785 1013
43786 6
43786 1012
43787 7
43787 1011
43788 8
43788 1010
43789 9
43789 1009
43790 10
43790 1008
43791 11
43791 1007
43792 12
43792 1006
43793 13
43793 1005
43794 14
43794 1004
43795 15
43795 1003
43796 16
43796 1002
43797 17
43797 1001
37 rows selected.
因为过程中使用了系统表all_users所以不同的环境结果会有区别
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)列为表某行所在的块的编号
每一行输出为t表中每一行和对应此行所在的block块的编号
数据共有37行占了好多块
这里用到了dbms_rowid包，是借用别人写的一个存储过程一个函数
这个函数的意义
可以根据表里面的每一行的rowid把它所对应的对象号、文件号、块号、行号全部取出来
例子最后删除了t表
drop table t;
现在做个实际的例子
先把DBMS_ROWID函数给建一下
使用语句
create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||
'Relative_fno is :'||to_char(relative_fno)||chr(10)||
'Block number is :'||to_char(block_number)||chr(10)||
'Row number is :'||to_char(row_number);
return ls_my_rowid ;
end;
执行成功函数建立
这里使用我们已有的表t2
SQL> select * from t2;
ID NAME
---------- --------------------
1 xkj
2 jiagulun
SQL> select rowid,t2.* from t2;
ROWID ID NAME
------------------ ---------- --------------------
AAAM7xAABAAAPNiAAA 1 xkj
AAAM7xAABAAAPNiAAB 2 jiagulun
结果t2有两行
AAAM7xAABAAAPNiAAA为第一行的行地址
AAAM7xAABAAAPNiAAB为第二行的行地址
行地址就是rowid
它包含的信息有
这个行所对应的对象的编号、行所在块的文件编号、行所在块的块号、行号
将rowid加入sql语句
select get_rowid('AAAM7xAABAAAPNiAAB') row_id from dual;
执行它
SQL> select get_rowid('AAAM7xAABAAAPNiAAB') row_id from dual;
ROW_ID
--------------------------------------------------------------------------------
Object# is :52977
Relative_fno is :1
Block number is :62306
Row number is :1
根据行的rowid
将它对应的对象编号、文件号、块号和行号给列出来了
结果为
AAAM7xAABAAAPNiAAB的rowid所对应的信息
此行它所在对象的编号Object id是52977
文件号是在1号文件里面
而且在一号文件的62306块里面
还有是第1行
这个包是老师在网上看到的写的不错的一个东西给大家列出来了
三）缓冲区命中率、清空buffer cache 等
还有我们一直关注的关于命中率的概念
命中率高不一定没问题，命中率低一定有问题
查询命中率的语句：
(1)
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;
执行结果
Hit Ratio
----------
99.0659252
现在命中率为99
正常的都在98%以上
数据库长期运行我们希望命中率99%以上
长期运行下命中率在98和99之间差的不小就是效率差的很大
如何找一些有问题的sql语句：
(2)
SELECT executions,
buffer_gets,
disk_reads,
first_load_time,
sql_text
FROM v$sqlarea
ORDER BY disk_reads;
它将sharedpool中所有sql语句的情况都给列出了
再给出两个个查询命中率的语句：
(3)
SELECT (P1.VALUE + P2.VALUE - P3.VALUE) / (P1.VALUE + P2.VALUE)
FROM v$sysstat P1, v$sysstat P2, v$sysstat P3
WHERE P1.name = 'db block gets'
AND P2.name = 'consistent gets'
AND P3.name = 'physical reads';
(4)
SELECT (P1.VALUE + P2.VALUE - P3.VALUE) / (P1.VALUE + P2.VALUE)
FROM v$sesstat P1,
v$statname N1,
v$sesstat P2,
v$statname N2,
v$sesstat P3,
v$statname N3
WHERE N1.name = 'db block gets'
AND P1.statistic# = N1.statistic#
AND P1.sid =141
AND N2.name = 'consistent gets'
AND P2.statistic# = N2.statistic#
AND P2.sid = P1.sid
AND N3.name = 'physical reads'
AND P3.statistic# = N3.statistic#
AND P3.sid = P1.sid;
一个查询磁盘中所有数据dbf文件使用情况的语句：
(5)
SELECT A.file_name, B.phyrds, B.phyblkrd
FROM SYS.dba_data_files A, v$filestat B
WHERE B.file# = A.file_id
ORDER BY A.file_id;
这些语句执行都很正常，不写出结果了
清空buffercache使用语句
alter system flush buffer_cache;
执行
SQL> alter system flush buffer_cache;
System altered.
执行的时候很简单
实际上我们将所有的buffer都成free了
这个在生产环境中很危险的
因为接着会发生大量的物理io
在生产上不要轻易的去执行这个命令
在数据库里面
我们可以设置不同的块大小的一些pool
以及不同类型的pool
这些以后讲
这次讲了block和buffer的一些知识
后面的讲的一些sql语句讲的粗糙一些
只是简单执行一下没有详细的讲
还有些内容只是提前给大家提出来了
最后面一块大家可以先不去理他
2016年9月25日
文字：韵筝