ORALE11g Direct Path Read可能影响性能

Oracle 11g引入了Direct Path Read (DPR) 特性,该特性允许数据库通过绕过Buffer Cache直接从磁盘读取数据进行全表扫描。此功能旨在减少对Buffer Cache的影响,并提高对大表进行全表扫描时的性能。通过一些隐藏参数,如_small_table_threshold和_very_large_object_threshold,可以进一步定制DPR的行为。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

在Oracle Database 11g中有一个新特性,全表扫描可以通过直接路径读的方式来执行(Direct Path Read),这是一个合理的变化,如果全表扫描的大量数据读取是偶发性的,则直接路径读可以避免大量数据对于Buffer Cache的冲击。

当然对于小表来说,Oracle允许通过Buffer Cache来进行全表扫描,因为这可能更快,也对性能影响不大。
小表受到隐含参数:_small_table_threshold 影响。如果表大于 5 倍的小表限制,则自动会使用DPR替代FTS。
可以设置初始化参数: _serial_direct_read 来禁用串行直接路径读。

当然,Oracle通过一个内部的限制,来决定执行DPR的阈值。
可以通过设置10949事件屏蔽这个特性,返回到Oracle 11g之前的模式上:

alter session set events '10949 trace name context forever, level 1';

还有一个参数 _very_large_object_threshold 用于设定(MB单位)使用DPR方式的上限,这个参数需要结合10949事件共同发挥作用。
10949 事件设置任何一个级别都将禁用DPR的方式执行FTS,但是仅限于小于 5 倍 BUFFER Cache的数据表,同时,如果一个表的大小大于 0.8 倍的 _very_large_object_threshold  设置,也会执行DPR。

这些限定的目标在于:
对于大表的全表扫描,必须通过Direct Path Read方式执行,以减少对于Buffer Cache的冲击和性能影响。

但是我们可以通过参数调整来决定执行DPR的上限和下限。



Before 11gr2, full table scan access path read all the blocks of a table (or index fast full scan) under high water mark into the buffer cache unless either "_serial_direct_read" hidden parameter is set to true or the table/index have default parallelism set.

In 11gR2, Oracle will automatically decide whether to use direct path reads bypassing buffer cache for serial full table scans.

For using direct path reads, Oracle first has to write any dirty blocks of the table to disk by issuing object level checkpoint and then read all the blocks from disk into the PGA of server process. If it has to undo any uncommitted transactions to provide read consistency or to do delayed block clean out, Oracle will use server process PGA to construct read consistent block buffers.

If block clean out has to be performed by the server process using direct path reads for full table scans, it won't write those modified blocks back to disk, so every time you perform full table scan using direct path reads it'd have to keep doing the same work of cleaning out the blocks for every execution. For this reason, it is recommended to manually clean out those blocks by performing full table scan without using direct path reads.

Following behavior was observed with my testing on 11.2.0.1:

Hidden parameter "_small_table_threshold" defines the number of blocks to consider a table as small. Any table having more blocks (about 5 times the value of "_small_table_threshold" if you leave it at default value) will automatically use direct path reads for serial full table scans (FTS).

Hidden parameter "_very_large_object_threshold" defines the upper limit of object size in MB for using direct path reads. There is no effect on FTS behavior just by setting this parameter alone.

Event 10949 set to any level will disable direct path reads for serial scans only if the size of an object is less then 5 times the size of buffer cache.

Combination of event 10949 and "_very_large_object_threshold" parameter will disable direct path reads for serial scans if the size of an object is less than 5 times the size of buffer cache or the value of "_very_large_object_threshold" is less than about 0.8 times the size of an object.

So, if you want to disable direct path reads for serial scans for any object, then set event 10949 at any level and set "_very_large_object_threshold" to greater than the size of largest object in MB.

Hidden parameter "_serial_direct_read" (or event 10355 set at any level) set to TRUE will enable direct path reads for all serial scans, unless the table is considered as small table and it's caching attribute is set (by issuing alter table xxxx cache). Remember that any sql statement already parsed and not using direct path reads will continue to do so unless hard parse is forced after setting these parameters. For this reason, it is better not to set these parameters.

It is not recommended to set any of the above mentioned hidden parameters if you want direct path reads to be used for serial scans, let Oracle decide dynamically based on the size of an object.


转载自eygle的博客:http://www.eygle.com/archives/2012/05/oracle_11g_direct_path_read.html  

资源下载链接为: https://pan.quark.cn/s/1bfadf00ae14 “STC单片机电压测量”是一个以STC系列单片机为基础的电压检测应用案例,它涵盖了硬件电路设计、软件编程以及数据处理等核心知识点。STC单片机凭借其低功耗、高性价比和丰富的I/O接口,在电子工程领域得到了广泛应用。 STC是Specialized Technology Corporation的缩写,该公司的单片机基于8051内核,具备内部振荡器、高速运算能力、ISP(在系统编程)和IAP(在应用编程)功能,非常适合用于各种嵌入式控制系统。 在源代码方面,“浅雪”风格的代码通常简洁易懂,非常适合初学者学习。其中,“main.c”文件是程序的入口,包含了电压测量的核心逻辑;“STARTUP.A51”是启动代码,负责初始化单片机的硬件环境;“电压测量_uvopt.bak”和“电压测量_uvproj.bak”可能是Keil编译器的配置文件备份,用于设置编译选项和项目配置。 对于3S锂电池电压测量,3S锂电池由三节锂离子电池串联而成,标称电压为11.1V。测量时需要考虑电池的串联特性,通过分压电路将高电压转换为单片机可接受的范围,并实时监控,防止过充或过放,以确保电池的安全和寿命。 在电压测量电路设计中,“电压测量.lnp”文件可能包含电路布局信息,而“.hex”文件是编译后的机器码,用于烧录到单片机中。电路中通常会使用ADC(模拟数字转换器)将模拟电压信号转换为数字信号供单片机处理。 在软件编程方面,“StringData.h”文件可能包含程序中使用的字符串常量和数据结构定义。处理电压数据时,可能涉及浮点数运算,需要了解STC单片机对浮点数的支持情况,以及如何高效地存储和显示电压值。 用户界面方面,“电压测量.uvgui.kidd”可能是用户界面的配置文件,用于显示测量结果。在嵌入式系统中,用
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值