[计算机软件及应用]Buffercache课件.ppt
- 【下载声明】
1. 本站全部试题类文档,若标题没写含答案,则无答案;标题注明含答案的文档,主观题也可能无答案。请谨慎下单,一旦售出,不予退换。
2. 本站全部PPT文档均不含视频和音频,PPT中出现的音频或视频标识(或文字)仅表示流程,实际无音频或视频文件。请谨慎下单,一旦售出,不予退换。
3. 本页资料《[计算机软件及应用]Buffercache课件.ppt》由用户(三亚风情)主动上传,其收益全归该用户。163文库仅提供信息存储空间,仅对该用户上传内容的表现方式做保护处理,对上传内容本身不做任何修改或编辑。 若此文所含内容侵犯了您的版权或隐私,请立即通知163文库(点击联系客服),我们立即给予删除!
4. 请根据预览情况,自愿下载本文。本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
5. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007及以上版本和PDF阅读器,压缩文件请下载最新的WinRAR软件解压。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 计算机软件及应用 计算机软件 应用 Buffercache 课件
- 资源描述:
-
1、Buffer Cache Waits#.2In This Section1.latch:cache buffers chains 2.latch:cache buffers lru chain 3.latch:cache buffer handles4.Free Buffer Wait5.Buffer Busy Wait6.Write Complete Wait7.Buffer Exterminate#.3Buffer Cache#.4Oracle Memory Structures#.5Buffer Cache AccessBuffer Cache ManagementLocating Fr
2、ee blocksFinding data blocksManaging LRU listsCleaning Dirty BlocksBuffer Cache management can cause contentionDifferent from IO(reading blocks of disk)#.6Query0.Parse statement1.Find object information in data dictionary2.Calculate execution plan3.If full table scanLook at all blocks of table4.If i
3、ndex find root of index and follow to key5.Data Dictionary will have info about table or index blockFile#Block#6.Once you know the block DBA(file#+block#)Select ename from emp where empno=12;#.7Is Block in cache?Now you have a file#and block#How do you know if a block is cached?Do you search all the
4、 blocks?Could be 1000s of blocks to search.Buffer caches are in the multi Gig#.8Buffer CacheFind a block by:1)Hash ofData file#Block#2)Result =Bucket#3)Search linked list for that bucket#What is a hash valueWhat are Buckets What is the linked list?#.9ConceptsTo understand contention on the buffer ca
5、che,need to understand:1.Linked Lists2.Hashing3.Buckets#.10Double Linked Lists 03C3900003C3947803C38F6003C3900003C3900003C38F6003C39478AddressNextPrevious#.11Hashing Function Simple hash could be a Mod function1 mod 4=12 mod 4=23 mod 4=34 mod 4=05 mod 4=16 mod 4=27 mod 4=38 mod 4=0 Using“mod 4”as a
6、hash funtion creates 4“buckets”to store things#.12Hash Bucket FillData BlockData BlockHash Blocks Hash Blocks file#file#block#sblock#sResult in a bucket#Result in a bucket#Put Block in bucketPut Block in bucket?Hash Blocks Hash Blocks 1 file#1 file#437 block#s437 block#s(1+437)mod 4=2(1+437)mod 4=2#
7、.13Latches Protect Bucket ContentsBufferHeadersData BlocksHashbucketlatchesBuffer Headers contents described by X$BH#.14X$bhDescribes Contents of Buffer HeadersSQL desc x$bh Name Type-ADDR RAW(4)DBARFIL NUMBER DBABLK NUMBER OBJ NUMBER HLADDR RAW(4)NXT_HASH RAW(4)PRV_HASH RAW(4)much more ADDR DBARFIL
8、 DBABLK OBJ HLADDR NXT_HASH PRV_HASHA each buffer header contains Information about the data block It points to and the previous and next Buffer header in a linked list#.15Cache 03C3900003C3947803C38F6003C3900003C3900003C38F6003C39478ADDRNXT_HASHPRV_HASH#.16X$BH describes HeadersBufferHeadersData Bl
9、ocksHashbucketlatchesHLADDRNXT_HASHPRV_HASHADDR ADDRDBARFILDBABLKOBJx$bh ADDR DBARFIL DBABLK OBJ HLADDR NXT_HASH PRV_HASH#.17To Find a Block1.Hash the block address2.Get Bucket latch3.Look for header4.Found,read block in cache5.Not Found Read block off diskBufferHeadersData BlocksHashbucketlatches#.
10、18Cache Buffers ChainsHash BucketslatchesBlockHeadersCache Buffer ChainDataBlocks#.19ExamplesS1S2S3S41.Look up Table2.Nested LoopsSelect t1.val,t2.valfrom t1,t2where t1.c1=value and t2.id=t1.id;t1Index_t2t2#.20CBC SolutionsFind SQL(Why is application hitting the block so hard?)Nested loops,possiblyH
11、ash PartitionUses Hash Join Hash clustersLook up tables(“select language from lang_table where.”)Change applicationUse plsql functionSpread data out to reduce contentionSelect from dualPossibly use x$dualHow do you find the SQL?#.21CBC:Statspack 9iTop 5 Timed Events%TotalEvent Waits Time(s)Ela Time-
12、latch free 21,428 1,914 81.37CPU time 360 15.29PL/SQL lock timer 16 48 2.04SQL*Net message from dblink 4,690 14 .58db file sequential read 1,427 5 .19Top 5 Timed Events%TotalEvent Waits Time(s)Ela Time-latch free 21,428 1,914 81.37CPU time 360 15.29PL/SQL lock timer 16 48 2.04SQL*Net message from db
13、link 4,690 14 .58db file sequential read 1,427 5 .19Latch Sleep breakdown for DB:CDB Instance:cdb Snaps:1-2-ordered by misses descLatch Name Requests Misses Sleeps Sleeps 1-4-cache buffers chains 12,123,500 608,415 15,759 0/0/0/0/0library cache pin 12,027,599 173,446 2,862 172694/743/8/1/0library ca
14、che 12,072,503 98,065 2,373 97739/279/47/0/0simulator lru latch 606 436 434 6/426/4/0/0Fails to find SQL#.22CBC:Statspack 10gTop 5 Timed Events Avg%Total wait CallEvent Waits Time(s)(ms)Time-CPU time 35 54.3latch:cache buffers chains 46 11 243 17.6latch:library cache pin 35 8 229 12.6latch:library c
15、ache 27 6 231 9.8log file sequential read 15 1 60 1.4Top 5 Timed Events Avg%Total wait CallEvent Waits Time(s)(ms)Time-CPU time 35 54.3latch:cache buffers chains 46 11 243 17.6latch:library cache pin 35 8 229 12.6latch:library cache 27 6 231 9.8log file sequential read 15 1 60 1.4Fails to find SQL#.
16、23CBC:ASH select count(*),sql_id,nvl(o.object_name,ash.current_obj#)objn,substr(o.object_type,0,10)otype,CURRENT_FILE#fn,CURRENT_BLOCK#blocknfrom v$active_session_history ash ,all_objects owhere event like latch:cache buffers chains and o.object_id(+)=ash.CURRENT_OBJ#group by sql_id,current_obj#,cur
17、rent_file#,current_block#,o.object_name,o.object_typeorder by count(*)/CNT SQL_ID OBJN OTYPE FN BLOCKN-84 a09r4dwjpv01q MYDUAL TABLE 1 93170SQL Statement:SuccessExtra:Hot block#.24CBC:OEM#.25CBC:ADDMProblemSQL StatementSolution?#.26CBC Further Investigationselect*from v$event_namewhere name=latch:ca
18、che buffers chains EVENT#NAME -58 latch:cache buffers chains PARAMETER1 PARAMETER2 PARAMETER3-address number tries NOTE:_db_block_hash_buckets=#of hash buckets _db_blocks_per_hash_latch=#of hash latches#.27CBC:whats the hot blockCan get it from ASH Current_file#Current_block#Where event=latch:cache
19、buffers chains”Sometimes file and block=0Seems to happen for Nested LoopsGet the hot block real timeUse Hash Latch Address Ash.p2=x$bh.hladdr#.28Hot Block:X$BH.TCHUpdated when block readUpdated by no more than 1 every 3 secondsCan be used to find“hot”blocksNote:set back to zero when block cycles thr
20、ough the buffer cache#.29CBC Real Timeselect count(*),lpad(replace(to_char(p1,XXXXXXXXX),0),16,0)laddrfrom v$active_session_historywhere event=latch:cache buffers chainsgroup by p1;select o.name,bh.dbarfil,bh.dbablk,bh.tchfrom x$bh bh,obj$owhere tch 100 and hladdr=00000004D8108330 and o.obj#=bh.objo
21、rder by tch COUNT(*)LADDR-4933 00000004D8108330NAME DBARFIL DBABLK TCH-EMP_CLUSTER 4 394 120#.30Putting into one Queryselect name,file#,dbablk,obj,tch,hladdr from x$bh bh ,obj$o where o.obj#(+)=bh.obj and hladdr in(select ltrim(to_char(p1,XXXXXXXXXX)from v$active_session_history where event like lat
22、ch:cache%group by p1 having count(*)5)and tch 5order by tch NAME FILE#DBABLK OBJ TCH HLADDR-BBW_INDEX 1 110997 66051 17 6BD91180IDL_UB1$1 54837 73 18 6BDB8A80VIEW$1 6885 63 20 6BD91180VIEW$1 6886 63 24 6BDB8A80DUAL 1 2082 258 32 6BDB8A80DUAL 1 2081 258 32 6BD91180MGMT_EMD_PING 3 26479 50312 272 6BDB
23、8A80This can be misleading,as TCH gets set to 0 ever rap around the LRU and it only gets updated once every 3 seconds,so in this case DUAL was my problem table not MGMT_EMD_PING#.31Consistent Read BlocksCurrentBlock(XCUR)Update Select ConsistentRead(CR)Clone&Undo Both have same file#and block#and ha
24、sh to same bucket#.32latchesCBC:Consistent Read BlocksCache Buffer ChainHash BucketsBlockHeadersMax length:_db_block_max_cr_dba10g=6#.33Consistent Read Copiesselect count(*),name ,file#,dbablk ,hladdr from x$bh bh ,obj$owhere o.obj#(+)=bh.obj and hladdr in(select ltrim(to_char(p1,XXXXXXXXXX)from v$a
25、ctive_session_history where event like latch:cache%group by p1)group by name,file#,dbablk,hladdrhaving count(*)1order by count(*);CNT NAME FILE#DBABLK HLADDR-14 MYDUAL 1 93170 2C9F4B20#.34CBC:Solution Fine the SQL causing the problemChange Application Logic Eliminate hot spotsLook up tablesUses pl/s
展开阅读全文