direct path read新特性导致buffer_pool keep失效

最新推荐文章于 2023-08-09 15:21:31 发布
最新推荐文章于 2023-08-09 15:21:31 发布
阅读量982 收藏 1
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/goolenblog/article/details/13022213
版权
最近朋友遇到一个问题，有个表迁到11g后，之前设置buffer_pool keep失效了，每次查询都有很大的物理读
经过检查发现是11g的新特性direct path read导致的
这个特性其实就是oracle在扫描一个大表的时候，会将数据直接读取到pga中，而不经过sga，所以就造成了buffer_pool keep没有生效的问题
oracle通过参数_small_table_threshold 来评估一个表是大表还是小表，单位是块，大于这个值的表，oracle就认为是大表

下面我们模拟一下这种情况
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table goolen as select * from dba_objects;
Table created.
SQL> alter table scott.goolen storage(buffer_pool keep);
Table altered.
SQL> insert into goolen select * from goolen;
SQL> /
/....
--这个sql可以查看buffer_cache中的缓存对象
SQL> SELECT o.object_name, COUNT(1) number_of_blocks 
  2  FROM DBA_OBJECTS o, V$BH bh                   
  3  WHERE o.object_id  = bh.objd                   
  4  AND o.owner != 'SYS'
  5  group by o.object_name                
  6  ORDER BY count(1);
OBJECT_NAME                         NUMBER_OF_BLOCKS
----------------------------------- ----------------
GOOLEN                                         31560
--查看参数_small_table_threshold的值
SQL> col name for a30
SQL> col value for a20
SQL> col DESCRIB for a60
SQL> set linesize 140 pagesize 1400
SQL>  
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2  FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3  WHERE x.inst_id = USERENV ('Instance')
  4  AND y.inst_id = USERENV ('Instance')
  5  AND x.indx = y.indx
  6  AND (x.ksppinm ='_small_table_threshold'or x.ksppinm='_serial_direct_read');
NAME                           VALUE                DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_small_table_threshold         500                 lower threshold level of table size for direct reads
_serial_direct_read            FALSE                enable direct read in serial

SQL> alter system flush buffer_cache;
SQL> select count(*) from goolen;
  COUNT(*)
----------
   1155424
Execution Plan
----------------------------------------------------------
Plan hash value: 1270414684
---------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |  4771   (2)| 00:00:58 |
|   1 |  SORT AGGREGATE    |        |     1 |            |          |
|   2 |   TABLE ACCESS FULL| GOOLEN |    19M|  4771   (2)| 00:00:58 |
---------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      16499  consistent gets
      16489  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select count(*) from goolen;
  COUNT(*)
----------
   1155424
Execution Plan
----------------------------------------------------------
Plan hash value: 1270414684
---------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |  4771   (2)| 00:00:58 |
|   1 |  SORT AGGREGATE    |        |     1 |            |          |
|   2 |   TABLE ACCESS FULL| GOOLEN |    19M|  4771   (2)| 00:00:58 |
---------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      16499  consistent gets
      16489  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--每次查询都有很大的物理读

--我们用10046事件跟踪一下这个sql
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_2194.trc
SQL> alter session set events '10046 trace name context forever,level 8';
Session altered.
SQL> select count(*) from scott.goolen;
  COUNT(*)
----------
   1155424
--查看trace文件发现wait都是direct path read
=====================
PARSING IN CURSOR #2 len=33 dep=0 uid=0 oct=3 lid=0 tim=1382688719747413 hv=1981856655 ad='db29f650' sqlid='4ub9jxdv21fwg'
select count(*) from scott.goolen
END OF STMT
PARSE #2:c=8998,e=9807,p=270,cr=72,cu=0,mis=1,r=0,dep=0,og=1,plh=1270414684,tim=1382688719747412
EXEC #2:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1270414684,tim=1382688719747526
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=5352 tim=1382688719747576
WAIT #2: nam='direct path read' ela= 128 file number=4 first dba=171 block cnt=13 obj#=74754 tim=1382688719748396
WAIT #2: nam='direct path read' ela= 138 file number=4 first dba=185 block cnt=15 obj#=74754 tim=1382688719748590
WAIT #2: nam='direct path read' ela= 64 file number=4 first dba=201 block cnt=15 obj#=74754 tim=1382688719748779
WAIT #2: nam='direct path read' ela= 52 file number=4 first dba=217 block cnt=15 obj#=74754 tim=1382688719748954
WAIT #2: nam='direct path read' ela= 52 file number=4 first dba=233 block cnt=15 obj#=74754 tim=1382688719749128
WAIT #2: nam='direct path read' ela= 53 file number=4 first dba=249 block cnt=15 obj#=74754 tim=1382688719749305
WAIT #2: nam='direct path read' ela= 62 file number=4 first dba=265 block cnt=15 obj#=74754 tim=1382688719749488
WAIT #2: nam='direct path read' ela= 52 file number=4 first dba=281 block cnt=15 obj#=74754 tim=1382688719749660
WAIT #2: nam='direct path read' ela= 1069 file number=4 first dba=386 block cnt=126 obj#=74754 tim=1382688719750861
WAIT #2: nam='direct path read' ela= 1058 file number=4 first dba=514 block cnt=126 obj#=74754 tim=1382688719752056
WAIT #2: nam='direct path read' ela= 411 file number=4 first dba=642 block cnt=126 obj#=74754 tim=1382688719753315
WAIT #2: nam='direct path read' ela= 421 file number=4 first dba=770 block cnt=126 obj#=74754 tim=1382688719754560
WAIT #2: nam='direct path read' ela= 413 file number=4 first dba=898 block cnt=126 obj#=74754 tim=1382688719755788
WAIT #2: nam='direct path read' ela= 405 file number=4 first dba=1026 block cnt=126 obj#=74754 tim=1382688719757010
WAIT #2: nam='direct path read' ela= 400 file number=4 first dba=1154 block cnt=126 obj#=74754 tim=1382688719758225
WAIT #2: nam='direct path read' ela= 416 file number=4 first dba=1282 block cnt=126 obj#=74754 tim=1382688719759478
WAIT #2: nam='direct path read' ela= 410 file number=4 first dba=1410 block cnt=126 obj#=74754 tim=1382688719760723

--我们可以通过调大此参数的值，来避免oracle将这个表视为大表
SQL> alter session set "_small_table_threshold"=9999999;
Session altered.
--当然也可以通过设置10949事件来禁止这个特性
-- ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';
SQL> select count(*) from goolen;
  COUNT(*)
----------
   1155424
SQL> set autot on
SQL> select count(*) from goolen;
  COUNT(*)
----------
   1155424
Execution Plan
----------------------------------------------------------
Plan hash value: 1270414684
--------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |  4771   (2)| 00:00:58 |
|   1 |  SORT AGGREGATE    |        |     1 |            |          |
|   2 |   TABLE ACCESS FULL| GOOLEN |    19M|  4771   (2)| 00:00:58 |
---------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      16506  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select count(*) from goolen;
  COUNT(*)
----------
   1155424
Execution Plan
----------------------------------------------------------
Plan hash value: 1270414684
---------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |  4771   (2)| 00:00:58 |
|   1 |  SORT AGGREGATE    |        |     1 |            |          |
|   2 |   TABLE ACCESS FULL| GOOLEN |    19M|  4771   (2)| 00:00:58 |
---------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      16506  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)

          1  rows processed



_Goolen
关注
mysql5.7性能提升一百倍调优宝典
lifetragedy的专栏
05-06 1万+
全文中一共有常用的（事实上你如果花1-2周阅读、理解、自己动手设一下后是需要这么多参数的）76个参数，我是按照： 每一个参数干吗？ 在某些典型硬件配置下的db上参数该设多少？ 设会怎么样？ 不设会怎么样？ 有什么坑如何填坑？ 有些参数怎么算、算法又如何 这种style来写的，具体请看文档！ 一千个DBA就有一千种配置方式! 大家一定记得不要轻易去看网上，要看只看官网！网上很多博客都...
Oracle数据库实例之内存架构（二）
Sebastien23的博客
06-18 659
Oracle数据库实例之内存架构（二）系统全局区：SGA数据库 buffer cacheIn-Memory Area重做日志缓冲器共享池Large池Java池Streams池固定SGA 系统全局区：SGA SGA是一块可读写内存区域，与Oracle后台进程（background processes）一起构成了数据库实例。所有代表用户执行的服务器进程都能读取实例SGA里的信息。有一些进程能在数据库运行时写入SGA。需要注意的是，服务器和后台进程本身并不在SGA中，而是存在于独立的内存空间中。 每个数据库实例都
oracle 设置keep池后没有生效的一个参数设置
weixin_33994429的博客
02-26 135
修改隐含参数 _serial_direct_read 为never后再检查。 转载于:https://blog.51cto.com/383610/1615490
深入分析direct path read（11G）
cotchte0421的博客
09-27 343
[i=s] 本帖最后由 wei-xh 于 2013-9-13 09:32 编辑 深入解析direct path read 传统读取数据的方式是服务器进程通过读取磁盘，然后把数据加载到共享内存...
关于direct path read
wish503的博客
11-30 3482
描述：用户反应数据库任何操作都很慢，然后我查询到当前有大量的direct path read等待事件。数据库版本是oracle 11G。 诊断与解决步骤： 1查询当前等待事件，主要是direct path read select event, count(1)   from v$session_wait  WHERE EVENT NOT IN        (select E.
direct path read 及 log file sync 等待事件分析过程及优化方法案例一则
05-28 279
5月20号下午4－5点，某项目组进行数据入库作业，作业人员反映入库速度很慢。在16:30和16:50分别采集了快照，并根据两个快照得到AWR报告，即打包文件中的ruku1.html。 直接看TOP 5 EVEN...
Oracle Buffer Cache 中 Keep Pool 说明
ijavagos
07-03 306
一. Keep Pool 说明 在我之前的Blog里对DB buffer 进行了一个说明，参考： Oracle Buffer Cache 原理 http://blog.csdn.net/tianlesoftware/article/details/6573438 Oracle的buffer cache 由三个部分组成： default pool，keep pool 和Recycle po...
AWR报表解读
wbj19890107的专栏
03-13 2504
---awr ----------报表头信息 报表的第一部门包含了数据库本身的信息，包括数据库的名称，ID，版本号以及主机等信息。 随后是快照的开始时间和结束时间，以及有多少活动会话的信息。 缓存尺寸部分显示了缓冲区缓存的值（初始化文件中的db_cache_size）;共享池的尺寸（shared_pool_size）;标准数据块的尺寸（db_block_size）;日志缓冲区（log_bu
V$SQLAREA
道行尚浅（老道）的博客
09-28 4128
v$SQLAREA  是一张描述shared pool 父游标信息的视图。其含义为 Column Datatype Description 含义 SQL_TEXT VARCHAR2(1000) First thousand characters of the SQL text for the current cursor SQL语句前1000字符
Java架构专家
最新发布
星月IWJ
08-09 424
java架构专家
一则 Oracle 等待事件 “direct path read" 排查思路
glogin
05-07 752
Oracle 等待事件 “direct path read" 排查思路
警惕Oracle数据库性能“隐形杀手”——Direct Path Read
Hehuyi_In的博客
05-17 6057
Oracle 的11g版本正式发布到今天已经10年有余，最新版本也已经到了20c，但是Direct Path Read（直接路径读）导致性能问题的案例仍时有发生，很多12c的用户还是经常遇到这个问题，所以有必要把这个事情再跟大家讲一遍，通过2个典型案例加深理解。 早在2012年，盖国强大师就撰写文章，介绍了direct path read这个11g版本推出的新特性： https://www.eygle.com/archives/2012/05/oracle_11g_direct_p...
模拟direct path read 等待事件
落落的专栏 专注SQL调优 性能调优
11-26 4331
 引起direct path read这个等待事件 主要有两个原因，一个是磁盘排序，另外一个是并行查询。这里先做一个磁盘排序引起的direct path read.另外磁盘排序也会记录direct path write 等待事件。下面是实验步骤:SQL> select * from v$version;BANNER----------------------------------
keep buffer cache
落落的专栏 专注SQL调优 性能调优
10-22 3727
    如果某个表是热点表，经常被访问，就应该考虑将其放入 keep buffer cache.防止其被挤出default pool 。从而减少physical read所带来的I/O开销。默认的情况下 db_keep_cache_size=0,未启用，如果想要启用，需要手工设置db_keep_cache_size的值，设置了这个值之后db_cache_size 会减少。    并不是
Buffer Cache（缓冲区缓存）篇：keep pool（保留池）
pb_watercuizhen的博客
06-04 440
http://www.2cto.com/database/201309/244325.html Buffer Cache（缓冲区缓存）篇：keep pool（保留池） 默认缓冲区池 keep缓冲区池 recycle缓冲区池 --保留池和回收池可以独立于sga中的其他缓存分配内存。创建表的时候可以在storage子句中使用buffer_pool keep和buffer_pool recy...
oracle keeppool 设置,Source of Oracle参数解析(buffer_pool_keep) - django-\/\/ i K |
weixin_34466348的博客
04-12 128
前面介绍了Oracle的基本参数，从这节开始讲其他的参数，参数从v$parameter中提取基本参数请看如下链接:[http://www.zhaibibei.cn/oralce/oracle-parameter/](http://www.zhaibibei.cn/oralce/oracle-parameter/)如无特殊说明数据库版本为11.2-------**buffer_pool_keep**...
oracle+buffer_pool,create table中的buffer pool 设置 KEEP 及CACHE 参数的联系及区别
weixin_30316931的博客
04-07 570
CREATE TABLESPACE LOG_DATA DATAFILE'/ocfs_data/mxdell/log_data01.dbf' SIZE 4001M AUTOEXTEND OFF,'/ocfs_ctrl_redo/mxdell/log_data2.dbf' SIZE 2048064K AUTOEXTEND OFFLOGGINGONLINEPERMANENTEXTENT MANAGEME...
Oracle 建表语句的关键字详解
热门推荐
帝上无天的博客
05-08 1万+
目录 一、建表语句 二、关键字详解 1. ENABLE 和 DISABLE 2. 创建主键约束 3. USING INDEX 4. PCTFREE 5. PCTUSED 6. INITRANS 和 MAXTRANS 1）INITRANS 2）MAXTRANS 7. COMPUTE STATISTICS 8. STORAGE 1）表空间管理机制 2）STORAGE 内部参数 3）FREELISTS 和FREELIST GROUPS 4）BUFFER_POOL 5) SE...
DirectSound API 9.0 新特性与功能解析
"DirectSound帮助文档提供了关于DirectSound API 9.0的详细信息，包括其新特性、功能以及与DirectMusic的关系。" DirectSound API 9.0 的增强功能： 1. **缓冲区频率上限提升**：在操作系统支持的情况下，Direct...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值