12 积分	0 好友	2 主题

发消息

[SQL调优] oracle 11G的自动统计分析是否会引起业务暂时查询超时？

1^#

发表于 2013-3-13 17:58:32 | 查看: 4851| 回复: 3

本帖最后由 david058 于 2013-3-13 17:59 编辑

刚刚接手一套新系统，发现业务系统有时在 22:00 ~ 22:01 会抽筋（前前后后出现3次），系统在这60秒内的操作失败次数会增多，过了这1分钟又恢复到正常，查看了一下系统 Schedules 有个默认的 GATHER_STATS_JOB 在22点启动，不知道是否与这个有关，系统是oltp，主要是查询，极少有插入和更改操作？

SQL> select owner,
2 table_name,
3 partition_name,
4 subpartition_name,
5 stats_update_time,
6 stats_update_time - lag(stats_update_time, 1, null) over(partition by owner, table_name order by stats_update_time) interval
7 from DBA_TAB_STATS_HISTORY
8 where owner = 'SDU'
9 -- and table_name = 'r_onenumber_base'
10 order by owner, table_name, stats_update_time ;
OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
黄帆(黄帆) 17:29:53
INTERVAL
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------
SDU TOTO_INFO2 26-2月 -13 10.00.50.839888 下午 +08:00
SDU TOTO_SUBSCRIBER 01-3月 -13 10.01.59.440197 下午 +08:00
SDU TOTO_SUBSCRIBERRELA 01-3月 -13 10.03.01.897714 下午 +08:00

复制代码

2.26号出现问题时间与统计时间吻合，但是 3.1 没出现问题

在alert.log 中 SYS_AUTO_SQL_TUNING_TASK 后，有时就出现 ORA-3136 错误

-- 2.26 问题时间
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Tue Feb 26 22:00:40 2013
WARNING: inbound connection timed out (ORA-3136)
Tue Feb 26 22:00:40 2013
WARNING: inbound connection timed out (ORA-3136)
Tue Feb 26 22:00:40 2013
WARNING: inbound connection timed out (ORA-3136)
Tue Feb 26 22:00:40 2013
WARNING: inbound connection timed out (ORA-3136)
Tue Feb 26 22:00:46 2013
WARNING: inbound connection timed out (ORA-3136)
Tue Feb 26 22:00:56 2013
End automatic SQL Tuning Advisor run for special tuning task "c"
Wed Feb 27 02:00:00 2013
-- 2.27正常时间
Wed Feb 27 22:00:03 2013
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Wed Feb 27 22:00:29 2013
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Thu Feb 28 02:00:00 2013
Clearing Resource Manager plan via parameter
Thu Feb 28 02:03:22 2013
ALTER SYSTEM ARCHIVE LOG
Thu Feb 28 02:03:22 2013
Thread 1 advanced to log sequence 280 (LGWR switch)
Current log# 4 seq# 280 mem# 0: /dev/vx/rdsk/vgora/lvredo4
Archived Log entry 279 added for thread 1 sequence 279 ID 0xf8b55ac3 dest 1:
Thu Feb 28 22:00:00 2013
Setting Resource Manager plan SCHEDULER[0x2C56]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Thu Feb 28 22:00:02 2013
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
-- 3.11 再次出现问题
Mon Mar 11 22:00:00 2013
Setting Resource Manager plan SCHEDULER[0x2C53]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Mon Mar 11 22:00:02 2013
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Mon Mar 11 22:00:39 2013
WARNING: inbound connection timed out (ORA-3136)
Mon Mar 11 22:00:39 2013
WARNING: inbound connection timed out (ORA-3136)
Mon Mar 11 22:00:39 2013
WARNING: inbound connection timed out (ORA-3136)
Mon Mar 11 22:00:39 2013
WARNING: inbound connection timed out (ORA-3136)
Mon Mar 11 22:00:45 2013
WARNING: inbound connection timed out (ORA-3136)

复制代码

还有 sqlnet.log 中

***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
Time: 26-FEB-2013 22:00:40
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
Time: 11-MAR-2013 22:00:45
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=191.168.11.3)(PORT=40484))

复制代码

数据库版本
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

操作系统版本
cat /etc/SuSE-release
SUSE Linux Enterprise Server 10 (x86_64)
VERSION = 10
PATCHLEVEL = 2

我还有几个疑问：
1. 这个有个语句没绑定变量，导致v$sql 的98%语句都是这个引起的，这个语句会导致 library cache lock 是可以理解的，但是为什么会导致 row cache lock，是否与oracle 11g的AMM相关，需要把AMM关掉？
2. GATHER_STATS_JOB 会导致 row cache lock 问题出现吗？
3.目前开发商不愿意修改绑定变量，除了cursor_sharing=force外，是否还有其他方式把代码改为绑定变量？以前系统cursor_sharing=force后出现过ORA-600错误，公司有规定不让这样操作了。

（附件中：awr （3.11 的 21点、22点）， alert.log sqlnet.log)

err.zip

82.67 KB, 下载次数: 1091

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2013-3-13 21:45:23

1. 这个有个语句没绑定变量，导致v$sql 的98%语句都是这个引起的，这个语句会导致 library cache lock 是可以理解的，但是为什么会导致 row cache lock，是否与oracle 11g的AMM相关，需要把AMM关掉？

==>

gather stats job 确实可能引发一些library cache lock，特别是有dblink、text index 的时候

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2013-3-13 21:45:40

2. GATHER_STATS_JOB 会导致 row cache lock 问题出现吗？

==》不是没有可能的

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2013-3-13 21:46:23

3.目前开发商不愿意修改绑定变量，除了cursor_sharing=force外，是否还有其他方式把代码改为绑定变量？以前系统cursor_sharing=force后出现过ORA-600错误，公司有规定不让这样操作了。

==》比起系统不可用出1、2个 600没什么可怕的。

开发商连基本的绑定变量都不愿意做，可以开掉了

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册