oracle read by other session,[Oracle] 性能调优实例 – read by other session | 学步园

这几天每天下午3点开始,开发人员反应Oracle慢,第一等待事件是read by other session

Top 5 Timed Foreground Events

Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

read by other session

4,914,569

9,987

2

45.17

User I/O

db file sequential read

3,176,031

7,473

2

33.80

User I/O

DB CPU

2,128

9.62

db file scattered read

4,104,747

721

0

3.26

User I/O

db file parallel read

10,590

693

65

3.13

User I/O

read by other session 的定义如下:

read by other session Definition: When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait.

In previous versions this wait was classified under the “buffer busy waits” event.

However, in Oracle 10.1 and higher this wait time is now broken out into the “read by other session” wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full-table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.

Confio concludes with a summary that “read by other session waits” are very similar to buffer busy waits

When a session waits on the “read by other session” event, it indicates a wait for another session to read the data from disk into the Oracle buffer cache.

If this happens too often the performance of the query or the entire database can suffer. Typically this is caused by contention for “hot” blocks or objects so it is imperative to find out which data is being contended for. Once that is known this document lists several alternative methods for solving the issue.

总结:两个或者多个会话同时需要把硬盘中的对象装载到data buffer中,当其中一个会话把对象装入后,其他会话就处于read by other session等待状态;这个是oracle 10g 从oracle 9i的buffer busy waits中分离出来的,也是需要一种热块现象

从上面的解释可以知道,read by other session如果伴随着db file sequential read出现,通常表示有索引热块。

导致索引热块的SQL如下:

select count(*) from p95169.order_info o

where o.proxyuseruuid =:1 and proxyuseruuid is not null and proxyuseruuid <> 0

and o.ORDERSTATE in ( 0, 1, 3, 2008, 2009, 3008, 3009, 5002, 5003, 5004, 5006, 7001, 7003, 7002, 7004, 7006 )

and o.ORDERCREATEDTIME >= to_char(sysdate - 15 , 'yyyyMMddHH24mmss') and o.ORDERCREATEDTIME <= to_char(sysdate , 'yyyyMMddHH24mmss')

and o.confirmstate in ( 1 ) and o.paystate in ( 1, 0 )

and o.CLINICALDATE <= to_char(sysdate, 'yyyyMMDD') and o.sourceplatid in ( 20 )

通过分析执行计划,可知它走的是以下索引:

CREATE INDEX "P95169"."IDX_ORDER_HOS_STAT_CLINICAL" ON "P95169"."ORDER_INFO"

("CLINICALDATE" DESC, "ORDERSTATE", "HOSPITALUUID", "ORDERUUID",

"RESERVETIMERANGE", "PATIENTNAME", "CONFIRMSTATE", "SOURCEPLATID", "SEEFLAG")

根据索引的前缀性原则,我们可以知道,上诉索引真正用的的字段是前两列,而前两列在where字句里的过滤条件是固定不变的,如果同时又多个这样的SQL在执行,就会引起索引热块,即产生read by other session

通过观察可知,该SQL语句where字句里变化的字段是proxyuseruuid,因此重建一个索引,包含该字段,就可以避免索引热块:

CREATE INDEX "P95169"."ORDER_INFO_PROXY" ON "P95169"."ORDER_INFO" ("PROXYUSERUUID", "CLINICALDATE", "ORDERCREATEDTIME")

至此,问题解决。

参考文档:http://www.xifenfei.com/1200.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
对于Oracle RMAN(Recovery Manager)的性能,有几个关键的方面需要考虑: 1. 备份策略化:确定适当的备份策略是性能的第一步。考虑到备份窗口、备份级别、保留策略和恢复要求等因素,选择合适的备份类型(完全备份、增量备份或差异备份)和备份频率。 2. 并行备份设置:使用并行备份可以提高备份速度。通过设置RMAN参数PARALLELISM,可以指定并行备份的进程数。根据系统资源和硬件配置,整并行备份的数量,以避免过度消耗资源。 3. 磁盘和通道配置:配置适当的磁盘和通道参数可以改善备份性能。确保备份目标磁盘具有足够的可用空间,并化磁盘I/O性能。此外,选择合适的RMAN通道(例如,备份到磁盘或备份到磁带)以满足性能需求。 4. 压缩和加速备份:使用RMAN提供的压缩功能可以减少备份数据的存储需求,从而提高备份速度。根据硬件支持情况,可以选择使用RMAN的压缩算法(例如,ZLIB、BZIP2或LZ4)。 5. 备份集管理:定期清理过期备份集和归档日志可以提高备份性能,并确保备份目标磁盘上有足够的可用空间。使用RMAN的DELETE命令或配置自动备份集管理策略(例如,利用RMAN的RECOVERY WINDOW选项)来管理备份集。 6. 并行恢复设置:在进行恢复操作时,可以使用并行恢复来加快恢复速度。通过配置RMAN参数PARALLELISM,可以指定并行恢复的进程数。根据系统资源和硬件配置,整并行恢复的数量。 7. 监控和化:使用RMAN的监控命令和报告功能来监视备份和恢复操作的性能。根据监控结果进行整,例如整备份策略、增加并行度或化磁盘配置。 以上是一些常见的Oracle RMAN性能的建议。根据具体情况,可能还需要进一步分析和整其他方面的配置和参数。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值