ORA-01555超长的Query Duration时间
来源:互联网 发布:webpack java 编辑:程序博客网 时间:2024/04/28 06:33
ORA-01555超长的Query Duration时间
现象:
Alert日志报ORA-01555错误,但让人感到不可思议的是超长的Query Duration(1382909202 sec)。
Mon Oct 28 05:26:42 2013
ORA-01555 caused by SQL statement below (Query Duration=1382909202 sec, SCN: 0x0011.0514ee8f):
Mon Oct 28 05:26:42 2013
ANALYZE TABLE "SAPPRD"."COSP" ESTIMATE STATISTICS SAMPLE 1 PERCENT FOR TABLE FOR ALL COLUMNS SIZE 1 FOR ALL INDEXES
Mon Oct 28 05:26:45 2013
ARC0: Completed archiving log 23 thread 1 sequence 1075683
Mon Oct 28 05:30:37 2013
Completed checkpoint up to RBA [0x1069e4.2.10], SCN: 0x0011.058bf650
Mon Oct 28 05:31:05 2013
分析:
我的参数UNDO_RETENTION = 900,
通常来说,ORA-01555意味着我们的查询超过了900秒,并且很有可能查询结果集中的某一行在900秒前被更新并提交。
(在NOGUARANTEE UNDO表空间中也存在另一可能:UNDO空间不够,未过期的UNDO信息被覆盖)。
对于这种情况的解决办法有几种:
1、优化查询SQL,使其更快执行完成;
2、增加UNDO_RETENTION大小,并且设置UNDO表空间为GUARANTEE模式,保护未过期的事物槽,但这种方式需要更大的UNDO空间。
但对于我们这种Query Duration大得离谱的现象,很有可能是遇到了某个BUG。
查询MOS,发现现象跟Note-ORA-1555 Errors with Unrealistic Query Duration (Doc ID 1574399.1)描述的一致。
解决方法:
Oracle建议安装one-off patch 10415967
该BUG在11GR2和10.2.0.5中被修复。
This has been discussed in Bug:10415967 - ORA-1555 SHOWS UNREALISTIC QUERY DURATION (AS IF RUNNING SINCE 01.01.1970) and Bug 7264172 : TB:SH STATUS DISPLAY AS "UNKNOWN" ON V$ROLLSTAT
Bug 7264172 deals with two issues. The v$rollstat view shows status as 'Unknown' and bad query duration. Since the former requires a modification for fixed view, this cannot be provided as a one-off patch. Hence the latter part is tracked in Bug 10415967.
Unrealistic query duration is reported when the start time is not initialized within the cursors
Apply one-off patch 10415967 if available for your DBversion and platform.
To download the patch, please follow the steps below:
1) Go To MOS
2) Click on Patches & Updates
3) In the Patch Search Section, enter your patch number in the Patch Name or Number field.
4) Click Search
5) Click the patch that meets your DbVersion and platform
6) Read any applicable notes before downloading, then click the download button.
This issue is fixed in 11gR2 and above. The fix is included in 10.2.0.5 also (but not in 11gR1).
现象:
Alert日志报ORA-01555错误,但让人感到不可思议的是超长的Query Duration(1382909202 sec)。
Mon Oct 28 05:26:42 2013
ORA-01555 caused by SQL statement below (Query Duration=1382909202 sec, SCN: 0x0011.0514ee8f):
Mon Oct 28 05:26:42 2013
ANALYZE TABLE "SAPPRD"."COSP" ESTIMATE STATISTICS SAMPLE 1 PERCENT FOR TABLE FOR ALL COLUMNS SIZE 1 FOR ALL INDEXES
Mon Oct 28 05:26:45 2013
ARC0: Completed archiving log 23 thread 1 sequence 1075683
Mon Oct 28 05:30:37 2013
Completed checkpoint up to RBA [0x1069e4.2.10], SCN: 0x0011.058bf650
Mon Oct 28 05:31:05 2013
分析:
我的参数UNDO_RETENTION = 900,
通常来说,ORA-01555意味着我们的查询超过了900秒,并且很有可能查询结果集中的某一行在900秒前被更新并提交。
(在NOGUARANTEE UNDO表空间中也存在另一可能:UNDO空间不够,未过期的UNDO信息被覆盖)。
对于这种情况的解决办法有几种:
1、优化查询SQL,使其更快执行完成;
2、增加UNDO_RETENTION大小,并且设置UNDO表空间为GUARANTEE模式,保护未过期的事物槽,但这种方式需要更大的UNDO空间。
但对于我们这种Query Duration大得离谱的现象,很有可能是遇到了某个BUG。
查询MOS,发现现象跟Note-ORA-1555 Errors with Unrealistic Query Duration (Doc ID 1574399.1)描述的一致。
解决方法:
Oracle建议安装one-off patch 10415967
该BUG在11GR2和10.2.0.5中被修复。
This has been discussed in Bug:10415967 - ORA-1555 SHOWS UNREALISTIC QUERY DURATION (AS IF RUNNING SINCE 01.01.1970) and Bug 7264172 : TB:SH STATUS DISPLAY AS "UNKNOWN" ON V$ROLLSTAT
Bug 7264172 deals with two issues. The v$rollstat view shows status as 'Unknown' and bad query duration. Since the former requires a modification for fixed view, this cannot be provided as a one-off patch. Hence the latter part is tracked in Bug 10415967.
Unrealistic query duration is reported when the start time is not initialized within the cursors
Apply one-off patch 10415967 if available for your DBversion and platform.
To download the patch, please follow the steps below:
1) Go To MOS
2) Click on Patches & Updates
3) In the Patch Search Section, enter your patch number in the Patch Name or Number field.
4) Click Search
5) Click the patch that meets your DbVersion and platform
6) Read any applicable notes before downloading, then click the download button.
This issue is fixed in 11gR2 and above. The fix is included in 10.2.0.5 also (but not in 11gR1).
0 0
- ORA-01555超长的Query Duration时间
- ORA-01555 caused by SQL statement below (Query Duration=38751 sec, SCN: 0x0000.fe5b584a)
- ORA-1555 Reported with Query Duration = 0 , or a Few Seconds
- VBR的MP3的播放时间(duration)计算方法
- ORA-21779: duration not active
- Java的日期与时间(十一)java.time.Duration
- ORA-21779: duration not active问题处理
- Duration
- Parallel Query 导致的ORA-04031
- 使用Java8提供的Duration类制作字幕时间轴调整工具
- CSS3中transition-duration参数对hover前后两种过渡时间的影响
- LoadRunner中负载时间Duration与迭代次数Iteration的联系
- 动画--过渡所需时间 transition-duration
- CSS3的【transition-duration】属性
- Solaris TELNET ftp时间超长
- oracle sql 超长报ORA-01460错误
- Ora-01135 DML/query访问的文件 处于脱机状态
- 超长短信的发送
- nyoj-222 整数中的1
- Android去掉标题栏和全屏
- Get 与Post 的区别
- C++重载——重载概述
- Java解析XML汇总
- ORA-01555超长的Query Duration时间
- android 禁用和开启四大组件的方法(setComponentEnabledSetting )
- 二分查找
- fedora 19(KDE)配置NFS
- layout、permission、test
- String to Integer (atoi)
- java中文件加锁机制
- TrustZone——硬件环境—SOC如何支持TrustZone
- 4G时代来临,百度何以大展拳脚?