S000常用的SQL语句

S001A:

使用advice分析buffer cache size大小：

SELECT size_for_estimate "Cache Size (MB)",size_factor,buffers_for_estimate "Buffers",
estd_physical_read_factor est_read_factor,
estd_physical_reads estd_phy_red,estd_physical_read_time est_phy_red_t
FROM  v$db_cache_advice
WHERE NAME='DEFAULT' AND block_size=(SELECT VALUE FROM v$parameter WHERE NAME='db_block_size');

一般以物理读时间为尺度进行考量。

S001C:

所有对象占用内存情况

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

and o.object_name='TB'    --查询TB表占block的响应状态，如果注释掉此行，就是查询所有对象占用buffer的情况；

group by o.object_name, state
order by blocks asc;