buffercache的調(diào)整與優(yōu)化(一)（緩沖區(qū)緩存的調(diào)整與優(yōu)化（一））

1、buffer cache的調(diào)整與優(yōu)化（一）（緩沖區(qū)緩存的調(diào)整與優(yōu)化（一）adjustment and optimization of buffer cache (part 1)personal classification: oracle performance tuningadjustment and optimization of buffer cache (part 1)classification: oracle performance optimizationadjustment and optimization of buffer cache (part 1)buffer cach

2、e is an important component of sga, which is mainly used to cache data blocks, and its size also directly affects the performance of the system. when buffer cache is too small, will cause morefree buffer waits eve nt. the following detailed descripti on of buffer cache function, adjustment and optim

3、ization.first, all the components of sgathe relevant information was obtained from the sga dynamic view v$sga_dynamic_componentsselectcomponent, current size,min sizefromv$sga dynamic components;component currents i ze min_sizeshared pool 7130316871303168large pool 41943044194304java pool 4194304419

4、4304streams pool 41943044194304defaultbuffer cache 113246208113246208keep buffer cache 00recycle buffer cache 00default2k buffer cache 00default4k buffer cache 00default8k buffer cache 00default16k buffer cache 00default32k buffer cache 00asm buffer cache 00two, buffer cache introductionthe types of

5、 1.buffer cache usually includedefault buffer cachekeep buffer cacherecycle buffer cachenk buffer cachesthe background process for dealing with buffer cache is dbwn, and the corresponding data files are usually located in system tablespace, sysaux tablespace, undo tablespace, datafile, etcbuffer cac

6、he is a part of sga, which is a data block read by the user process from the data file, and all users share these data blocks usually, servicethe process is to improve the performance of i/o, will be a one-time reading a plurality of data blocks buffer cache for dirty data is written to the data fil

7、e dbwn process. similarly, to improve performance, dbwnthe process also writes multiple data blocks at once buffer cache will have multiple copies of a data block, and only one of the latest copies of the current block, and the old or old copies of the block, can be availablecan have multiple copies

8、 for read consistency of blocks buffer cache lru algorithm is used to eliminate the outdated datablock.existing checkpoint queue and lru list buffer cachesome related parameters of 2.buffer cachebuffer cache can consist of several independent buffer pools (buffer pool) with different block sizedb_bl

9、ock_size: parameter determines the block size of the database master block,the size of the block is usually represented by the system table (system, sysaux) space and the main buffer cache (recycle, keep),the use of default buffer cache)determine size of the major parameters of buffer cachedb_cache_

10、sizedb_keep_cache_sizedb_recyc1e_cache_sizefour states of a block in 3.buffer cachepinned: means that many sessions write the same data block at the same time period, and the other sessions wait for access blocksclean: blocks data blocks that are not pinned states and will not be reused the block ma

11、y be in sync with the block on the disk, or it may be a read coherence blockfree/unused:, the block in buffer cache, is in idle or unused state, usually because the instance has just starteddirty: a data block that has changed and has no process to use it again it needs to be written immediately fro

12、m dbwn to the data file before aged outthe server process fills the data block from the data file to the buffer cache when the buffer cache is no longer required to use a replica of the data block, the dbwn process will dirty the datawrite to a data file for the data block from the pinned state to t

13、he free state4. parameter db_block_checksumwhen the parameter is set to true, a specified check code is written to the data block at the same time to prevent disk loss, and i/o system damage leads to data lossthree, the client server process process of data acquisition from buffer cachethe 1. server

14、 process uses a hash function to check whether the required data block is already in the buffer cache if you find the data blocks required in buffer cache, according to the blockfor placing frequency to the specific lru queue position. the read data block is read logic, and not in the need to perfor

15、m subsequent steps if the buffer is not on cache, then go to the next stepthe 2. server process searches for the availability of free space in the lru list to store new data blocks when searching the lru list, the dirty data that has been modified will be placed by the server processcheckpoint queue

16、3. the checkpoint queue length exceeds the preset size of the que or the server process searches the number of preset idle block operations (the value specified by the hidden parameter _db_block_max_scan_pct, which means that it has already been declaredthe number of scanned buffer header accounts f

17、or the total number of buffer header on the entire lru list, and the limit value is 40% in i. then, the server process passes dirty data from dbwnbuffer cache writes data files4. when the available free blocks are found, the server process reads the data file from the data block to the database buff

18、er cache and puts it in the lru queue if the resulting block is not consistent with the read blockthen, from the undo segment server process reconfiguration consistencybuffer cache and dbwn is closely related to the dbwn trigger condition is given belowdirty buffer list reaches the specified thresho

19、ld sizesearch lru idle queue value reached the preset number of quecheckpoint events occurredwhen the database is closedhot backup in tablespacetablespace offlinewhen the segment is deletedmore about the architecture, please refer to: oracle instance and oracle database (oracle architecture)four, bu

20、ffer cache tuning, hit rate, etc1. adjust buffer cache tuning rulesthe goal of tuning: to find data in buffer cache as much as possible, and to reduce the time available for waiting for free blocksdebugging method:wait eventscache hit rationv$db_cache_advice viewadjustment meansreduce the sql comman

21、d requests for data blocks, such as avoiding the use of select * from statementincrease the size of the buffer pooldifferent access methods using different buffer pools (buffer pools)cache common table to memorydo not use cache parallel read or sorting operations, directly read from disk to memory a

22、nd pga2. determine several indicators of buffer cachethe following query list involves several important indicators of the number of cache bufferselectname, valuefrom v$sysstatwhere name in ('session logical reads'), j physical reads，,j physical reads direct','physical reads direct (

23、lob)*,'consistent gets','db block gets','free buffer inspected，,'free buffer requested','dirty buffers inspected，,'pinned buffers inspected，);name valuesession logical reads 139150060175db block gets 274690511consistent gets 139129962467physical reads 21335058151f

24、ree buffer requested 21085155516dirty buffers inspected 156801pinned buffers inspected 432841free buffer inspected 968639physical reads direct 4995527session logical reads: the number of data blocks all logical read.free buffer inspected index:before looking for free buffer check the total number of

25、 blocks, the number of blocks that skip if the value is close to the number of dirty data blocks, the table is still idle block rarely, the value should be as small as possiblethe number of dirty blocksfree buffer waits:this event occurs when session does not find the available data block on the lru

26、 list or the search memory block is paused this is waiting for the dbwn to be dirtythe number of waiting blocks to write to the data file. in addition, when the session is doing consistent reading, it is necessary to construct the pre image (image) of the data block at a certain time, where the appl

27、ication is requiredsave to store these new data block structure, if the memory could not find such a block of memory,this waiting event also happensbuffer busy waits:the user server process has found the required data block, but the block is being used by other processes, or multiple processes are r

28、equired to modify the block at the same time, the time needed to waita buffer busy waits event occurs when a session needs to read a block of data, but this chunk is being read into memory by another session.selecteventtotal_waitsfromv$system_eventwhereeventin ('free buffer waits' , ' bu

29、ffer busy waits，);event total waitsbuffer busy waits 35216021query event event nameselectnameparameterlparameter2parameter3fromv$event_namewherename=，buffer busy waits，;name parameter1 parameter2 parametersbuffer busy waits file# block# idseveral situations of producing buffer busy waitsdata block,

30、the competition of data blocks, is usually based on the competition between the table segment and the index segment, and the following processing methodtry to narrow the query field of sql statements, query range, such as not using select * query, the like clause instead of direct assignment, etcche

31、ck the reasonableness of the index if you use an index generated by sequence, its index keys are usually located in the same block, so you can use the reverse index to avoid this problemuse automatic segment management or add idle lists to avoid multiple processes inserting the same block at the sam

32、e timequery view v$session_wait to obtain the hot block file id, block id, through this information to obtain the object id, further adjust the objectundo headerbased on the undo header competition, if there is no automatic undo segment management mode, more rollback segments need to be addedundo bl

33、ockbased on the undo block competition, if the automatic revocation segment management mode is not used, the larger size should be allocated for rollback segmentsseveral situations of producing free buffer waitsdbwn process is too late to write data to the data file,resulting in the need to wait for

34、 the release of spacei/o system is too s1owensure that the database files are distributed on different disks, or increase the performance of the diskwait for the i/o system resources caused by too slow, such as wait for latchensure that the database files are distributed on different disks, or incre

35、ase the performance of the diskbuffer cache is too small, resulting in dbwn could not be dirty data is written to the data filethe size of buffer cache needs to be increasedbuffer cache is too large, and the single dbwn process requires several times to write data to a filereduce the size of the cac

36、he buffer, or more dbwn processes3. evaluate the hit rate of cachethe idea of calculating hit ratio1- (physical read times total number of requests)calculated hit rateselectround (1- (physical value一direct value一lobs value) /logical, value), 3) *100|'%'"buffer cache hit ratio"from

37、v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logicalwhere physical. name=，physical reads，anddirectname二'physical reads direct'andlobs. name二'physical reads direct (lob)'andlogica ln ame=，session logical reads'physical readsfrom the oracle level, the number of data

38、 blocks read from disk can read multiple blocks at once, and is controlled by the parameter db_file_multiblock_read_count the use of this method of readingdb cacheimage presentation: db_file=>db_cache=>pgaphysical reads directsome data blocks don，t read from hard disk into memory first, then r

39、ead pga from memory and send it to user, but read pga directly from hard disk through sga. for example, parallel queries and empty from temporary tablesread data between. this part of the data block due to cache makes hit ratio will not be increased. it should be deducted when calculating hit ratio.

40、image presentation: db_file =>pgausually, disksort/hash, expdirect二y, reads direct will have physicalscott0rcl> selectnamefromv$statnamewherestatistic#in(54, 55, 56);namephysical readsphysical reads cache 一 the use of buffer cachephysical reads direct 一 cache does not use bufferphysical reads

41、direct (lob)for large value objects, such as lob data types and lob segments, oracle can bypass buffer cache and use pga directly, and its principle is equivalent to physical reads directthe use of physical reads direct, physical reads direct (lob) has the advantages of:for a large operation, a larg

42、e number of data blocks need to be requested, and the parallel execution is assumed, and the number of executions is just one time this time is suitable for the use of the direct method,if you still need to go to buffer cache buffer cache has been in the cache database are emptypay attention to phys

43、ical write/directsession logical readsthe total number of requests issued, here is from the database cache buffer request block for consistent read, the buffer contains data from the rollback segmenthere is another kind of different methods in computing the hit rate, usually used in log and llgselec

44、tname,physical_reads,db_block_gets,consistent_gets,round (1- (physical_reads/ (db_block_gets+consistent_gets) *100) | i'%'ratiofrom v$buffer_pool_statisticswhere namedefault'select (1- (sum (decode (name,jphysical, reads，, value, 0) /(sum (decode (name,' db, block, gets', value,

45、0) +sum (decode (name,，consistent, gets', value, 0) *100 hit ratio"from v$sysstat;consistent gets from cachethe data structure consistency in the rollback segment buffer in the total number of data blocks read the reason is because the other session on the current data block operation, such

46、 as the update operation,however, since our query is called before these modifications, it is necessary to query the previous image of the data block in the rollback section to ensure data consistencythis leads to consistent reading.db block getsthe number of extraction in operation, but not in the

47、number of read consistency under the condition of thethe current block (current, relative to cosistent read, current is always the latest block), the number of requests from buffer cache the current block means to operateblock number just extracted, rather than in the number of read consistency unde

48、r the condition of the usually, a query in the query block is extracted the startthe data block exists at the point where the current block is the block of data that exists at this time, rather than the number of blocks before or after this time pointphysical reads cacheread from disk to buffer cach

49、e in the total number of timesthe main reason is that there are no such blocks in the database cache, full table sean, disk sort, etcthe sum of dbblockgets+consistentgets as the total number of requests, compared with physical_reads, and then get hit ratewith this method the front hit rate calculati

50、on method is not the same, more simple and intuitive4. factors affecting hit ratea full table scan (small standard can, for the big table for i/o performance worse and a full table scan is always placed at the end of the lru, at any time by aged out)effect of different data definition and applicatio

51、n design of the hit raterandom access to large table (non sequential)unbalanced cache hitproblems to be considered in hit ratea. on the same table and index repeat scan is likely to cause a high hit rate regularly check the frequent use and return the result set a large sql statement, to ensure that

52、 these sqlthe sentence uses the optimal execution planb. avoids client programs or middleware that returns the same data query and caches as much of the result set as possiblea full table scan problem of the c table, directly placing it into the lru is easy to aged out (small table usually refers to

53、 a full table scan occupied buffer cache 20% or have a data block).d. for large oltp systems, many rows in the table are accessed only twice or few times, and based on this, these blocks are not easy to occupy the buffer cache for a long timef. has no practical significance in increasing the size of

54、 buffer cache for parallel query or sorting, and the optimization effect is obvious5. buffer caching can be considered in the following casessome waiting events have been optimizedbad sql statements have been optimizedthe operating system level no bad memory page replacementthe last increase of buff

55、er cache is validbased on the above case, and the hit rate is very low, this can increase the buffer cache6. steps to increase buffer cachethe db _ advice in the on state _ cachecheck the dynamic performance view v $_ cache _ db (need to consider increasing the proposal does not affect the operating

56、 system memory page replacement level too much)the dynamic in crease of the value of size (dbproductiondatabase is not recommended to shut down the system and the use of dynamic adjustment of altersystemsetdb _ cache size _ 二 nm;)view the size of the current buffer cacheselectname, the _ buffersfrom

57、v buffer pool size, _ dollars.at present _ size buffers nameinstall default 34564287607. reduce the buffer cache situationin a high percentage under $suggested v db query view cache,cache to weigh whether size will make buffer reduce system i / 0 significantly increase drama, if not, andsize does no

58、t affect performance, but can moderately reduce the size of buffer size, reduce the buffer cache, cacheuse alter size to set _ dba _ cache system8. use advisor to adjust buffer cacheadvisor enables or disables the estimation of the size buffer cache of buffer cache by collecting statistical information, and then adjusts the buffer according to the estimated size and workloadthe size of the cache buffer