oracle buffer cache查询.docxVIP

--?查看?Buffer?cache?的参数 SELECT?component,?current_size,?min_size?from?v$sga_dynamic_components; --?EM?界面? HYPERLINK https://localhost:1158/em https://localhost:1158/em --?Buffer?cache?的大小设置 Alter?system?set?db_cache_size=20M?scope=memory; --?在?OLTP?系统中,?对于DB_CACHE_SIZE?的设置,?推荐的配置是: --?????DB_CACHE_SIZE?=?SGA_MAX_SIZE/2?~?SGA_MAX_SIZE*2/3 --?使用advice?来确认buffer?cache?的大小 --?当前数据库应该设多大,?Buffers?为如果cache?size?为这么大的话,?他应该有多少个?Buffer,?ESTD_PHY_RED?预估的物理读的次数,?ESTD_PHY_RED_t?预估的物理读的时间.?设旳再大,?以后都对物理读的时间没有影响. 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); --?查看Oracle?后台进程 select?program,?status?from?v$session?where?type=BACKGROUND; Show?parameter?writer;?--?有几个DB_writer?进程,?一般是?CPU数量/8. --?改这个参数,?重起生效 alter?system?set?db_writer_processes=2?scope=spfile; --?强制重启? Startup?force;?--?(测试环境下) --?关闭?db_console:? --?emctl?stop?dbconsole;?--?这是个cmd?命令 Alter?system?set?db_cache_size=200M?scope=both;?--?设置的时候只能往大的设,?往小的设没有意义. --?还要增加它的大小,?就只能先增加?SGA(先SGA_MAX_SIZE,?再SGA_TARGET,?再DB_CACHE_SIZE)?的大小. --?desc?x$bh;?--?只有在sqlplus?下才能用 --?一个对象占用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? ????o.object_name=T1??--?这个name?中的英文?一定得大写 group?by?o.object_name,?state? order?by?blocks?desc; delete?from?t1 where?rownum??500; --?查看?Buffer?Cache?中有几种状态的?Buffer Select?distinct?state?from?x$bh; Alter?system?flush?buffer_cache;?--?将整个Buffer?cache全部清空. --?这个对象占用了几个块 Select?object_name,?DBARFIL,?DBABLK?from?x$bh?a,?dba_objects?b?where?a.obj=b.data_object_id?and?object_name=