在Buffer Cache中自动大表缓存

约翰,在A银行做DBA。凯特琳,新的首席技术官,她正在为数据库性能问题由于长的I/O响应时间而沮丧。为了提高性能,她希望增加数据库实例的缓冲区缓存,以便在缓存中缓存更多的数据,从而减少了频繁需要去读磁盘。戴比,系统开发工程师,经历了很多这样的问题在她的职业生涯中,她解释说,“我们的应用有很多全表扫描,数据库的全表扫描使用的是直接路径读取(DPR)在表中的数据块,不做缓冲区高速缓存。”因此,添加物理内存到服务器后,增加缓冲区缓存,是不会帮助,因为缓冲区没有用。应用程序可以通过加hits跳过DPRs,使用缓冲区高速缓存,但在所有的应用需要修改成千上万的查询,这将是一个非常昂贵和耗时的过程,没有人愿意接受这种解决方案。为了提高全表扫描性能的唯一途径,是提高I/O子系统的吞吐量,例如使用闪存存储,但是闪存比较昂贵。
约翰向他们提出建议:在Oracle数据库12c(12.1.0.2)发布一个新功能可以使全表扫描被加载和保存在一个大的缓冲区高速缓存中。

为什么不缓存全表扫描?

在约翰解释新的功能之前,办公室里的每个人都想知道为什么一个完整的表扫描不会使用缓冲区缓存。当一个连接到数据库实例的会话从表中选择数据时,数据库服务器进程从磁盘读取适当的数据块,并默认地将它们放入缓冲区缓存中。每个块进入缓冲区中的缓冲中。原因很简单:如果另一个会话要访问这些块中的一些数据,它可以从缓存区查询,速度远远快于从磁盘上读取。缓冲区的缓存大小是有限的,通常小于整个数据库,所以当缓存满了,一个新的数据块要进来,数据库迫使旧的缓冲区,很长一段时间没有被访问的块被覆盖。约翰继续解释,考虑选择表的所有块的全表扫描查询的情况。如果该表非常大,它的块将消耗缓冲区缓存的很大一部分,迫使其他表的块被覆盖。这是不可能的,一个大表的所有块将被定期访问,所以在缓存中的那些块实际上并没有帮助性能。但是,强制讲其他表的块,尤其是受欢迎的块,挤出缓存区,会降低数据库中的应用程序的整体性能。这就是为什么,数据库不加载全表扫描到缓冲区缓存中的原因。

Temperature
大家对约翰为什么全表扫描不缓存高速缓存的原因的解释感到非常满意,但是,凯特琳,有另一个问题:如何决定Oracle数据库缓冲区高速缓存中的数据块的去或留?

约翰解释说,传统的数据库实例使用最近最少使用(LRU)列表来跟踪多久前在缓冲区高速缓存缓冲区的访问。当一个缓冲区的块第一次访问,它被放在LRU列表的头部。随着时间的推移,当缓冲区中的其他块被会话访问时,他们会移动到LRU列表头部,这会导致原始的缓冲区块从列表的头部向下滑动。当原始缓冲区块再次被访问时,它会移动到LRU列表的头部,但如果它被不访问,它一路下降到底部,并最终被从列表中移除掉。在这一点上,缓冲区的块将从缓存中移除。实际过程中,约翰赶忙补充,稍微复杂一点,因为实例还考虑其他因素,如多少次缓冲被触动的(通过一个叫触摸计数的计数器)。

但在Oracle数据库12c(12.1.0.2)中, 全表扫描可以缓存到缓冲区中:数据库有一个新的度量,温度,用于识别一个对象的受欢迎度。访问的对象的块越多,该对象的温度越高。而且由于温度是基于对象的,而不是在块上,它是一个更准确的衡量表的受欢迎度。
Big table

现在,利用新的特性,戴比可以缓存大表,这个新的功能,约翰描述为“自动大表缓存”。

但戴比看起来持怀疑态度。数据库是巨大的,缓冲区高速缓存尽管较大,仍然比数据库大小小得多。事实上,有一些表是相当大的。当他们被加载到缓冲区缓存中,他们将迫使很多流行的缓冲区的块被覆盖。所以,她沉思着,仍然存在风险。

风险仍然潜伏,约翰同意。因此,而不是冒险整个缓冲区缓存,数据库只是使一部分被用于缓存大表。这是由初始化参数控制的。戴比想把高达百分之40的用于此目的的缓冲区,所以约翰用以下SQL语句:
SQL>alter system set db_big_table_cache_percent_target = 40;
这将留出百分之40的缓冲区缓存用于大表缓存。其他百分之60将不会被大表的全表扫描所使用,所以流行的缓冲区块仍然可以存在在缓冲区中。这是一个动态参数,所以约翰不用重启数据库,即可重复效果演示。

Code Listing 1: Checking big table cache statistics (at first setup)

select * from v$bt_scan_cache;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP CON_ID
—————————————— ——————————————— ———————————— ———————————————— ——————————————— ——————
    .210158085              40            0                0            1000      0

select * from v$bt_scan_obj_temps;

no rows selected


两个动态性能试图显示如何缓存全表扫描的工作方式,v$bt_scan_cache显示了全表扫描的概述。
列的含义如下:


BT_CACHE_ALLOC. Even though John allocated 40 percent to the big table cache, not all of that cache might be required. As the demand for buffers in the big table cache rises due to incoming big tables, more space will be allocated. This column shows the ratio of the buffer cache used by the big table cache right now to the overall size of the buffer cache; in our example, it’s 0.210158085, or about 21 percent, because there is no big table in the buffer cache yet.

BT_CACHE_TARGET. This column shows the target allocation percentage, 40 percent, as set by John earlier.

OBJECT_COUNT. This column shows how many objects are in the big table cache. Because John just set up this cache, there are no objects yet; hence, this shows 0.

MEMORY_BUF_ALLOC. This column shows how many buffers are allocated to the objects in the big table cache right now. Again, because John just set up the big table cache, this shows 0.

MIN_CACHED_TEMP. As John explained earlier, temperature is a new way to designate the usefulness of an object (for example, a table) in the big table cache. The more often a table is accessed, the higher its temperature and, hence, the more beneficial it is for the table to be in this cache. This column shows the minimum temperature of objects that will be considered for this cache. It shows 1,000; therefore, objects with temperatures below 1,000 will not be considered.

CON_ID. This column shows the container ID for a database in a multitenant environment.

The second view, V$BT_SCAN_OBJ_TEMPS, shows the details of the big table cache. The V$BT_SCAN_OBJ_TEMPS view includes the following columns:

TS#. This column shows the tablespace number an object resides in. You can join this view with the TS$ table to get the tablespace name.

DATAOBJ#. This column shows the data object number of the object. You can join this view with DBA_OBJECTS to get the object name.

SIZE_IN_BLKS. This column shows the number of blocks of this object that were considered for the big table cache in this database instance.

TEMPERATURE. This column shows the temperature of the object.

POLICY. This column shows how the object was cached: either partially or in its entirety.

CACHED_IN_MEM. This column shows how many blocks of this object are in the big table cache.

CON_ID. This column shows the container ID for a database in a multitenant environment.

约翰指出,v$bt_scan_obj_temps视图没有返回任何行。这并不奇怪,他补充说,因为他只是设置了大表的缓存,但并没有表已被缓存。为了证明这个新的缓存特性,约翰从数据库中选择一个大的表名为T1。首先,他用以下SQL查询,查询下该表多少块由该表使用:

select blocks 
from user_tables 
where table_name = 'T1';
 
    BLOCKS
———————————————
    335952


T1表有 335,952 数据块—这个表相当大,执行一个全表扫描:

select count(1) from t1;

 

执行全表扫描后, 这时两个动态视图显示的数据和前期的不一样了. 我们Code Listing 2. 试图 V$BT_SCAN_CACHE 的 BT_CACHE_ALLOC 列现在显示0.400012911, 表明大约有 40 % 的数据缓存在 buffer cache 中.
Code Listing 2: Checking big table cache statistics (after full table scan operation)

select * from v$bt_scan_cache;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP CON_ID
—————————————— ——————————————— ———————————— ———————————————— ——————————————— ——————
    .400012911              40            1            49570            1000      0

select * from v$bt_scan_obj_temps;

   TS#    DATAOBJ# SIZE_IN_BLKS TEMPERATURE POLICY  CACHED_IN_MEM    CON_ID
——————  —————————— ———————————— ——————————— ——————— ————————————— —————————
196612       95956       335952        1000 MEM_PART        49570         0


 

接下来约翰指着V $ bt_scan_obj_temps视图输出结果,讲述了如何使用缓存的具体细节。dataobj #列显示95956。约翰使用以下查询来查找是哪个对象:

select object_name
from dba_objects
where data_object_id = 95956;

OBJECT_NAME
—————————————————
T1


对象是T1表,约翰迫使执行了全表扫描。该表现在处于缓冲区缓存中。然而,他指出,该表共335952块,只有49570是在缓存中,在cached_in_mem列。他解释说,原因是,缓存大表的缓冲区缓存太小,不能够容纳表的所有块。他通过POLICY的列的值,表明mem_part,表明只有一个表的一部分,而不是整个表加载到内存中。

 

“But we have additional memory now,” observes Debbie. “Sure,” agrees John. “That enables us to increase the total system global area [SGA] size, which will increase the buffer cache as well.” He increases the SGA size, using the following SQL statement:

“但我们现在有更多的内存,”戴比说。“当然,”约翰同意。他增加了SGA的大小,使用下面的SQL语句:


alter system set sga_target = 3G;

他还增加了大表缓存的比例为百分为90:

alter system set db_big_table_cache_percent_target = 90;


这之后他再次执行T1表全表扫描,在Code Listing 3中有几个有趣的数据输出,他指出。首先,大表缓存分配现在是缓冲区缓存的86.5%。在大表缓存中的对象的温度是4000,之前是1000。由于在表上的全表扫描的数目增加。cached_in_mem列显示为335952,与表中的块的数量相同,表明整个表缓存了。

Code Listing 3: Checking big table cache statistics (after increasing cache size)

select * from v$bt_scan_cache;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP CON_ID
—————————————— ——————————————— ———————————— ———————————————— ——————————————— 
    .865177265              90            1           175625            1000      0

select * from v$bt_scan_obj_temps;

   TS#    DATAOBJ# SIZE_IN_BLKS TEMPERATURE POLICY  CACHED_IN_MEM    CON_ID
——————  —————————— ———————————— ——————————— ——————— ————————————— —————————
196612       95956       335952        4000 MEM_ONLY       335952         


policy列这表明mem_only,这意味着表所有的块缓存在内存中。在这之后所有对T1的访问,即使表是通过全表扫描访问,只会从内存中读取,不会读磁盘。

Debbie still looks a bit unconvinced, though. The database doesn’t have just one big table as John showed, she points out. It has many such big tables, and the pattern of access to them will be unpredictable. The memory can’t just grow infinitely, so how will the database system decide how much of which of those tables to cache, she asks.


戴比看起来还是有点不服气,他指出,数据库并没有像约翰所显示的那样,只有一个大表。会有许多这样的大的表。“数据库的内存是有限的,不能无限增长,所以如何将数据库中其他的表缓存到缓存中?”

这是很容易通过演示显示,约翰的答复。他执行全表扫描表T2:

SQL> select count(1) from t2;

然后,他再次检查视图,如Code Listing 4:所示。他指着v$bt_scan_obj_temps,试图其中的dataobj #列显示一个新的对象:95964。用之前的查询,这个对象是表T2,这正是他在第二次全表扫描的对象。然而,policy列显示的是mem_part,这意味着内存中缓存的是表的一部分,不像表T1,完全在内存中。但表T2是最近访问,戴比指出,数据库T1表块的缓存不应该为T2的所有块的腾出空间吗?

Code Listing 4: Checking big table cache statistics (after adding second table)

select * from v$bt_scan_cache;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP CON_ID
—————————————— ——————————————— ———————————— ———————————————— ——————————————— ———
    .866228351              90            2           277731            1000      0

select * from v$bt_scan_obj_temps;

   TS#    DATAOBJ# SIZE_IN_BLKS TEMPERATURE POLICY  CACHED_IN_MEM    CON_ID
——————  —————————— ———————————— ——————————— ——————— ————————————— —————————
196612       95956       335952        4000 MEM_ONLY       335952         0
196612       95964       334149        1000 MEM_PART       102106         0
select object_name
from dba_objects
where data_object_id = 95964;

OBJECT_NAME
—————————————————
T2

不,回答约翰,这正是TEMPERATURE列的作用。T1比T2被访问更频繁(如图所示的温度,T1(4000)T2(1000) )。因此,T2比T1的优先级低了。

继续演示,约翰执行几个全表扫描的T2。之后,他检查了2个试图缓存统计数据,如Code Listing 5:所示。他指出表T1(95956 # dataobj)的温度4000,这是低于T2(95964 # dataobj),T2现在已经加热到温度5000。这改变了表的优先级。因为T2具有较高的温度。事实上,表T2现在都在内存中,Policy列的值是mem_only。表T1的缓冲区只有部分在缓存中,Policy列的值是mem_part。

Code Listing 5: Checking big table cache statistics (after temperature change)

select * from v$bt_scan_obj_temps;

   TS#    DATAOBJ# SIZE_IN_BLKS TEMPERATURE  POLICY CACHED_IN_MEM    CON_ID
——————  —————————— ———————————— ——————————— ——————— ————————————— —————————
196612       95964       334149        5000 MEM_ONLY       334149         0
196612       95956       335952        4000 MEM_PART       107376         0


戴比现在完全相信了。这不仅利用了额外的内存,减少磁盘上的负担,并且没有修改任何应用程序或数据库对象的变化。这个新特性的高速缓存内存量也可以动态调整,而不需要重启数据库。凯特琳听完后欣喜若狂。大家都非常感谢约翰。

结论

Oracle数据库12c(12.1.0.2)发布之前,只有通过提高IO系统的吞吐量才能达到提高全表大表扫描。

大表缓存功能在Oracle数据库12c(12.1.0.2)的版本中,DBA可以通过分配缓冲区的一部分空间为全表扫描提供缓存,可以减少磁盘访问和显著提高性能。

原文来自oracle官方,本人翻译的有不准确的地方,欢迎大家一起交流。
http://www.oracle.com/technetwork/issue-archive/2016/16-jul/o46dba-nanda-3076580.html
参考文档
http://docs.oracle.com/database/121/VLDBG/GUID-A553169D-C6CD-443E-88C3-B746D5E32923.htm

彭小波

性别:男: 现在定居:大连

DBA+东北社区 创始人

Oracle 用户组年轻专家

数据库技术爱好者

+10Oracle数据库使用经验

+2Oracle培训讲师

手机:150 4113 1262



 

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。

MySQL案例-mysqld got signal 11 - 2016-08-09 14:08:35

-------------------------------------------------------------------------------------------------正文--------------------------------------------------------------------------------------------------------------- 背景: MySQL-5.7.12, debian 8核16G虚拟机 , 业务方反馈在某一
今天的技术问答是刘晨兄的一个问题,提问来自于我新书中的一个实验,刘晨兄非常认真,对我书中的很多细节都进行了测试。 看到这个错误,如果出现end-of-file这类的错误信息,基本可以断定数据库实例是宕了。 找到刘晨兄提到的页码标示,原来和我书中的测试结果有一些差别。 我书中的结果类似这样的形式: 错误代码也完全不同,这个问题该怎么解释呢,这个应该是一个很细节的问题。 首先网络上关于这个错误有很多种说法,很多我不认同。 我们先来复现一下问题,找了一套11.2.0.3的环境测试了一下。 先初始化数据 然后复现问
【故障处理】分布式事务 ORA-01591 错误解决 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ①  分布式事务的简单概念         ②  ORA-01591 错误解决   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.cnblogs.com/lh
我们有几个项目使用了Windows Server 2008+ ROSE HA+Oracle 的组合方案,但是最近遇到了一个不大不小的麻烦。 甲方在进行故障测试时,断掉了一个网络交换机 的电源 (共有两个网络交换机,对应两个冗余的业务网络),Oracle服务竟然停止了,导致所有的客户端数据库连接中断。 首先,这个测试结果甲方是无法接受的; 其次,这个结果也出乎我们的意料,在另外一个交换机正常工作的情况下,ROSE HA 停止了 Oracle服务。 那么,如何给甲方一个交代呢?在和 ROSE售后经过多次沟通后,
故障现象: 用服务器上面的(客户端)sql server management  stutio 去连接本服务器上的sql server 数据库,如下图:点击连接不报任何错,就是连接不上,一直处于等待状态; 但是通过windows 身份验证是可以连接的,并且是可以正常操作的,如下图:服务器名字不在是个IP,要选择local. 通过这个现象可判断出 数据库本身的服务(通过任务管理器中也可以看出来)是没有问题,只是连接方面出现问题:网络,端口或者是监听之类的问题。 这是网上查到的一篇文档:经验证我的这里是 没有
《 Oracle DBA 工作笔记》第二章 常用工具和问题分析   一.1    BLOG 文档结构图     一.2    本文简介 建荣的新书《 Oracle DBA 工作笔记》第二章的目录如下图,主要讲解了 SQL*Plus 、 exp/imp 、 expdp/impdp 以及常见的问题分析,第二章的目录如下:     下边小麦苗将自己阅读完第二章后整理的一些内容分享给大家。 一.3    第一章内容修改 一.3.1    删除数据库的几种方式 这个内容是第一章( http://blog.itpub
工作中可能需要某一天各个时间段的ash报告或awr报告,手动一个一个生成太费力了.利用 dbms_workload_repository 包再配合sqlplus的spool 可以使这件事情简单一些. 以下示例: 一.批量生成一天的ash报告 1.生成查询语句 #此处是按照15分钟的间隔时间,生成前一天所有的ash报告  的查询语句 select 'spool ash_'||db_unique_name||'_'||inst_id||'_'||to_char(trunc(sysdate-1)+level/9
分区索引分为本地索引和全局索引,但对于在分区表上建索引,一般用的比较多的还是普通索引和本地分区索引,而全局分区索引相对用的比较少. 以下测试为验证:分区表上 的本地分区索引 因为查询条件引起跨分区,是否改为普通索引更合适. 以下测试: oracle version:11.2.0.4 建测试表: drop table SCOTT.TB_TEST01; create table SCOTT.TB_TEST01 partition by range (CREATED) (   partition P_2015
关于半自动化搭建Data Guard,自己花了一些时间,总算是把这件事情继续推进了一下,还是再啰嗦一句,为什么不自动化,因为安全。主库就是主库,任何变更都要手工检查审核,自动化的工作在备库和中控端来完成。我希望自己的脚本能够只知道主库的IP,不用一次又一次连过去配置和检查,当然要完成自动化还是半自动化,有些网友也提醒的极是,那就是规范和标准。 预先条件: 1.目前的设计是基于11.2.0.4的版本,当然这个很容易定制,在此是作为一个基本的标准,作为环境的初始化和Data Guard对的搭建的基线。 2.默认
   前段时间有个开发的同事向我咨询一个问题,     开发同事:Oracle会存在一个用户插入数据,已经提交了;但是另外一个用户还查询不到吗?都是同一张表     jeanron:   不会的。     开发同事: 我们现在一个用户写入,程序日志是说已经写入;可是读取的用户还读取不到,在线延迟5分钟可能的问题在哪儿?或者你帮忙监控一下?     jeanron:   是Oracle吗,MySQL还可能有这种情况     开发同事: Oracle,MySQL是什么情况下会这样?     jeanron: