Tuning Oracle at the Block Level

Maclean Liu(刘相兵 发表于 2012-2-9 22:56:03

This paper is not for beginners.  It is a look at Oracle moving at the block level. It is not meant to be an all encompassing detailed paper.  That would take weeks or even months to write.  But, it is meant to give you a taste of some of what I’ve seen so that you can look into it and take it to the next level.  This subject is only meant for advanced DBAs with several years of experience.  This paper will review some of the concepts concerning how blocks move from disk to memory, how they become current blocks, how they become Consistent Read (CR) versions and other interesting information. This presentation specifically covers dumping blocks of data, reading block dumps, the cache buffer operation, general block level concepts, the current and CR version of blocks, delayed block cleanout, the hot/cold side of the LRU and some useful scripts along the way.
Why you should understand some block level concepts?  You wouldn’t use this for everyday use, but when you really need to dig deep to solve a difficult problem that is absorbing much or most of your system.  Another reason to better your understanding is the large increase in hardware memory and the move toward memory resident databases for some of the smaller databases out there.  Also, with a move toward RAC/Grid computing, problems with blocks moving through the interconnect could be where your future problems will be hiding. Lastly, when you can visually see how something works at the micro level, it can help you understand it better at the macro level.

Terminology:
Since everyone looks at terminology differently, I want to define how I’ll use some terms within the paper.  Within the “Instance Activity” section of a statspack report or AWR report (Automatic Workload Repository Report – very similar to statspack but uses different internal tables/views), you can find several items that tell you how much information is passing through your system.  The “Session Logical Reads” are all reads which are cached in memory.  This includes both the consistent gets and also the db block gets which are also in the report.  The “Consistent Gets” are the reads of a block that are in the cache.  They should NOT be confused with a consistent read (cr) version of a block in the buffer cache which is a block at a consistent point in time or SCN (note that usually the current version of a block is the one in the buffer cache and usually your buffer cache is about 95% current blocks.).  Also in a statspack or AWR report you’ll see the “Db block gets” which are the blocks gotten to be changed.  You MUST use the CURRENT (curr) version of a block and not a cr block when you are going to change it.  There is also listed the “Db block changes” which are the db block gets that were actually changed (a lot of rollbacks may cause a lot more db block gets than db block changes). The “Physical Reads” are the blocks that are not read from the cache.  These can be either blocks that are read from disk, the disk cache or O/S cache.  The fact that these reads are not all truly read from disk is why people are in constant debate on how fast memory or disk is.  People think that they’re reading blocks very fast from disk, giving them the allusion that it’s almost as fast as reading them from memory, when in fact they ARE reading them from memory (the disk cache memory or the O/S cache memory).  To Oracle though, it’s a disk read, since it’s a read outside Oracle’s buffer cache, causing the debate to go on.  There are also “physical reads direct” which bypass cache using things like Parallel Query (these are also not in hit ratios). Here’s an example of the Instance Activity section:

zhuqibs 发表于 2012-2-11 14:02:03

块级调优，有挑战，有兴趣，看看啊

casper0511 发表于 2012-12-5 09:35:47

学习！！！！！！！！！！！！！！！！！

albert 发表于 2012-12-6 14:45:01

看介绍就很吸引人

fsm 发表于 2012-12-12 07:25:31

刊名字不错！

keer7737 发表于 2012-12-28 23:20:15

先看下哦非常感谢

ziyoo0830 发表于 2013-11-14 08:58:19

看看。。。

zengkefu 发表于 2015-4-27 17:07:02