oracle数据库性能优化实务第6讲db cache分析与

Oracle数据库性能优化第六讲：DB

CACHE分析与优化主讲人：白鳝Oracle的DB

CACHE存储最近使用过的数据块通过LRU算法管理（8i开始引入了tch）DB_BLOCK_SIZE定义了标准的数据块大小SGABuffer

Head第3页每个BUFFER都有一个头BH和BUFFER存储在相同的GRANULE里BH包含以下主要内容Buffer

headers

includeLRU链Hash链指向BUFFER的指针BufferHeadersGranuleBuffers多缓冲技术缓冲池的分类DEFAULT

POOL：通常使用的缓冲池KEEP

POOL：存放热点数据的缓冲池RECYCLE

POOL：临时统计使用的缓冲池使用多缓冲的好处提供缓冲池的效率减少数据库操作之间的相互影响减少LRU链相关闩锁的争用NK缓冲NK缓冲的目的是为不同块大小的表空间提供独立缓冲池NK缓冲池的优点配合独立块大小的表空间使用更为合理的将数据存储在不同块大小的表空间中减少热块冲突减轻LRU链的争用提高缓冲池的效率注意事项在创建NK表空间前先要配置好NK缓冲池热块冲突较为严重的数据可以通过减少BLOCKSIZE来优化行长度较大的表适合放到BLOCKSIZE较大的数据块中LOB字段适合使用较大的数据块使用KEEP

POOLKEEP

POOL的主要作用是最大限度减少IO存放最常用的对象减少DEFAULT

POOL对敏感数据的影响什么对象适合放入KEEP

POOL常用的表和索引相对静态的数据如何启用KEEP

POOL设置db_keep_cache_sizealert

table|index

...

storage

(buffer_pool

keep);DB

CACHE的优化要点要配置足够大的DB

CACHEDB

CACHE过小的迹象DB

CACHE命中率不高CACHE

BUFFER

CHAINS闩锁争用严重CACHE

BUFFER

LRU

CHAINS争用严重物理IO较大DBWR十分繁忙出现大量CHECKPOINT的等待双刃剑：DB

CACHE越大，CPU开销越大多缓冲池是一项十分优秀的技术NK

CACHE技术与NK表空间关于DB

CACHE的命中率DB

CACHE的命中率直接反映出DB

CACHE的效率一般针对OLTP系统，DB

CACHE的命中率越高越好OLAP系统的DB

CACHE命中率往往不高加大DB

CACHE提高命中率减少IO开销会消耗更多的物理内存和CPU资源关于DB

CACHE命中率的几个问题DB

CACHE的命中率并不是优化的核心所在优化DB

CACHE的核心所在是平衡IO量和物理内存之间的平衡资源紧张情况下优化DB

CACHECPU资源紧张时加大DBCACHE可能会消耗更多的CPU资源在某些情况下减少某些资源的IO等待可能会降低CPU资源IO资源紧张时加大DB

CACHE可以减少IO开销内存紧张时使用KEEPPOOLDB

CACHE分析分析DB

CACHE的总体性能分析DB

CACHE设置是否合理分析DB

CACHE调整的策略DB

CACHE分析小技巧（1）通过AWR报告查看LOAD

PROFILE和命中率指标~~~~~~~~~~~~Load

ProfilePerSecondPerTransactionRedosize:1,030,352.772,106.74Logicalreads:790,444.411,616.20Blockchanges:7,245.2014.81Physicalreads:18,130.4337.07Physical

writes:200.460.41Usercalls:25,398.8051.93Parses:6,705.1513.71Hard

parses:38.140.08Sorts:12,338.3125.23Logons:1.750.00Executes:11,005.2022.50Transactions:489.07%

Blocks

changed

per

Read:Rollback

per

transaction

%:0.920.34Recursive

Call

%:Rows

per

Sort:34.333.81Instance

Efficiency

Percentages

(Target100%)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~BufferNowait%:99.94RedoNoWait%:100.00Buffer

Hit%:97.71In-memory

Sort%:100.00Library

Hit%:99.90SoftParse%:99.43Execute

to

Parse%:39.07Latch

Hit%:99.89Parse

CPU

to

Parse

Elapsd

%:73.51%

Non-Parse

CPU:97.11DB

CACHE分析小技巧（2）通过AWR报告查看DB

CACHE相关闩锁Pct

Avg

Wait

PctGet

Get

Slps

Time NoWait

NoWaitLatch Requests Miss /Miss (s) Requests Misscachebuffer

handles765,5610.10.000cachebuffers

chains886,820,5190.10.1119823,647,9730.0cachebuffers

lru

chain931,5140.20.1023,741,5040.2DB

CACHE分析小技巧（3）通过AWR报告查看BUFFER

CACHE相关情况PNumber

of

CacheBuffers

Hit

%BufferGetsPhysicalReadsPhysicalWritesBuffer

CompleteWaits

WaitsBusyWaits2DK6,203,125744,37597.7

531,142,401012,077,0910119,319000

317,98800DB

CACHE分析小技巧（3）通过AWR报告查看DB

CACHE

AdvisoryPSize

forEstimate

(M)SizeFactrBuffersforEstimateEst

PhysicalReadFactorEstimatedPhysical

ReadsD29,952.63,715,9201.571,777,770,246D34,944.74,335,2401.401,583,288,243D39,936.84,954,5601.241,411,786,927D44,928.95,573,8801.111,261,570,495D49,9201.06,193,2001.001,136,170,566D50,0001.06,203,1251.001,134,324,766D54,9121.16,812,5200.911,030,197,173D59,9041.27,431,8400.83941,461,891D64,8961.38,051,1600.76863,666,000D69,8881.48,670,4800.70797,738,505D74,8801.59,289,8000.65742,123,542D79,8721.69,909,1200.61694,454,835D84,8641.710,528,4400.58652,546,848D89,8561.811,147,7600.54614,028,920D94,8481.911,767,0800.51578,615,501D99,8402.012,386,4000.48547,054,847DB

CACHE分析小技巧（4）通过图表分析1.510.5043.532.5200.511.522.5物理读物理读DB

CACHE分析小技巧（5）SELECT

size_for_estimate,

buffers_for_estimate,

estd_physical_read_factor,

estd_physical_readsFROM

V$DB_CACHE_ADVICEWHERE

nameANDblock_size=

'DEFAULT'=

(SELECT

value

FROM

V$PARAMETER

WHERE

name

=

'db_block_size')AND

advice_status

='ON';SIZE_FOR_ESTIMATE

BUFFERS_FOR_ESTIMATE

ESTD_PHYSICAL_READ_FACTOR

ESTD_PHYSICAL_READS3664

439451

3.4933 7.6854E

2DB

CACHE优化小技巧（6）查找放入KEEP

POOL的对象放入KEEP

POOL的对象最好从业务角度考虑通过X$BH中的TCH字段可以进行辅助筛选经验分享：分析X$BH的脚本Selectdecode(pd.bp_id,1,

'KEEP',

2,'RECYCLE',

3,'DEFAULT',

4,

'2KSUBCACHE',5,'4K

SUBCACHE',

6,'8K

SUBCACHE',7,

'16K

SUBCACHE',

8,'32K

SUBCACHE',

'UNKNOWN')

subcache,bh.object_name

object_name,bh.blocks,tchfromx$kcbwds

ds,x$kcbwbpd

pd,(select

/*+

use_hash(x)

*/

set_ds,

object_name,

count(*)

BLOCKS,tchfrom

obj$

o,

x$bh

x

where

o.dataobj#

=

x.objand

x.state!=0

and

o.owner#

!=0group

by

set_ds,,tch)

bh

where

ds.set_id

>=

pd.bp_lo_sidand

ds.set_id

<=

pd.bp_hi_sid

andpd.bp_size

!=

0

and

ds.addr=bh.set_dsorder

by

subcache,object_name;例子SUBCACHEOBJECT_NAMEBLOCKSTCHDEFAULTIDX_EXECORDER_BUREAUNO1023879DEFAULTIDX_EXECORDER_BUREAUNO1113243DEFAULTIDX_FILEWFPU_PUSTATE2293038DEFAULTIDX_FILEWFPU_PUSTATE2313148DEFAULTIDX_PREPAY_FAV_PLAN_USER2931145DEFAULTIDX_STAFF_STAFFNO101107DEFAULTIDX_TBA_ASKLIST_ASKDATE1,68119122DEFAULTIDX_TBA_ASKLIST_ASKDATE2,18413636DEFAULTIDX_TCH_FEELIST_LIST_PAYTIME3933005DEFAULTIDX_TCH_FEELIST_LIST_PAYTIME4173554DEFAULTIDX_TCM_USER1,7326747DB

CACHE相关的闩锁cache

buffer

handlescache

buffers

chainscache

buffers

lru

chainmultiblockread

objectsDB

CACHE优化与共享内存自动管理SGA_TARGET足够的情况下，使用共享内存自动管理具有较好的性能SGA_TARGET不足的情况下，需要设置DBCACHE的最小值以确保DB

CACHE的效率情况严重时关闭共享内存自动管理关注V$SGA_RESIZE_OPSDB

CACHE和热块冲突DB

CACHE配置不合理会加大热块冲突的机会热块冲突也会影响DB

CACHE的性能什么是热块冲突热块冲突在OLTP系统中十分常见并发会话同时访问相同的数据块会产生热块冲突热块冲突严重时会导致严重的系统性能问题在RAC环境下的热块冲突可能导致系统性能急剧下降，甚至HANG住实例热块冲突的主要原因IO系统性能不佳大并发量的数据访问索引根节点的热块冲突索引叶节点的热块冲突SEGMENT

HEADER的冲突ITL参数设置问题解决热块冲突的主要思路打散数据分布使用较小的块大小使用HASH簇表使用HASH分区表反转键索引较大的SEQUENCE

CACHE（RAC下）优化表存储参数PCTFREEINITRANSFREELISTS/FREELIST

GROUPS提高DB

CACHE命中率优化IO使用物化视图KEEP热表优化应用查找热块的技巧（1）AWR报告Segments

by

Buffer

Busy

Waits DB/Inst:

SFOSS/sfoss1 Snaps:

10825-10826->

%

of

Capture

shows

%

of

Buffer

Busy

Waits

for

each

top

segment

compared->

with

total

Buffer

Busy

Waits

for

all

segments

captured

by

the

SnapshotOwnerSubobjectNameObj.TypeBufferBusy %

ofWaits

CaptureEXP5EXP5EXP5EXP5EXP5TablespaceName Object

NameEXP5_TE