星空网 > 软件开发 > 数据库

ORACLE调整SGA_TARGET值耗费时间长案例

在一数据库版本为(标准版)Oracle Database 10g Release 10.2.0.4.0 - 64bit Production 的服务器上调整 sga_target时,遇到命令执行了非常久都没有执行完成的异常情况,觉得非常诧异、不解,因为一般调整sga_targt命令非常快速,检查了告警日志,并没有任何异常错误,等了好几分钟都没有执行完成,于是执行了CTRL+C命令结束了SQL命令,具体过程如下:

$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Sep 3 22:35:59 2016
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
 
SQL> show parameter sga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 8G
sga_target                           big integer 6G
 
SQL> 
SQL> alter system set sga_target=8g scope=both;
 
^Calter system set sga_target=8g scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01013: user requested cancel of current operation
 
 
 
SQL> 
SQL> 


 

 

后面检查时,在$ORACLE_BASE/admin/$ORACLE_SID/bdump目录下发现scm2_mmon_16798.trc跟踪文件,在里面看到大量“SGA POLICY: Cache below reserve request pending 1”这里错误信息

/u01/app/oracle/admin/SCM2/bdump/scm2_mmon_16798.trc
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      xxxxxxxxx
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Instance name: SCM2
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 16798, image: xxxxxx (MMON)
 
*** 2016-09-03 22:36:49.845
*** SERVICE NAME:(SYS$BACKGROUND) 2016-09-03 22:36:49.836
*** SESSION ID:(931.1) 2016-09-03 22:36:49.836
SGA POLICY: Cache below reserve getting from component1
SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
*** 2016-09-03 22:37:19.880
SGA POLICY: Cache below reserve getting from component1
SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
*** 2016-09-03 22:37:49.892
SGA POLICY: Cache below reserve getting from component1
SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
SGA POLICY: Cache below reserve request pending 1
*** 2016-09-03 22:38:19.909


ORACLE调整SGA_TARGET值耗费时间长案例

 

当时查了一下资料,觉得有可能与db_cache_size被手工设定有关(这个参数被同事手工设置过),当时检查了一下V$SGA_RESIZE_OPS,发现并没有SGA组件重定义大小的操作失败的记录。由于这个不是急于调整,手头还有正事处理,于是当时就搁置下来。

 

今天晚上去检查、处理时,居然无法重现这个错误了, 调整sga_target_size一下子就成功了,没法重现这个错误了,于是我又特意检查了一下V$SGA_RESIZE_OPS,发现期间出现了shared pool 收缩, DEFAULT buffer cache的增长, 还有一个DEFAULT buffer cache的增长从2016-09-03 22:45:21持续到了2016-09-04 21:13:26,而且状态为INACTIVE。

ORACLE调整SGA_TARGET值耗费时间长案例

ORACLE调整SGA_TARGET值耗费时间长案例

 

后面分析了这个时间段的ASH报告,发现这个时间点出现了“SGA: allocation forcing component growth”等待事件,

ORACLE调整SGA_TARGET值耗费时间长案例


The SGA: allocation forcing component growth indicates that you can Automatic Memory Management (AMM) turned-on

This is an AMM message indicating that the size of sga_target or memory_target is too small, causing excessive AMM resize operations.

The solution is to turn off AMM and allocate your own db_cache_size and shared_pool_size and un-setting sga_target and memory_target.


分析至此,能确定一些东西:当调整SGA_TARGET大小时,操作系统的内存也会将内存里面用作cache或buffers的部分数据踢出内存, 另外,由于che_size和shared_pool_size等SGA相关组件大小剧烈调整,导致了SGA: allocation forcing component growth等待事件,导致SQL语句耗费了非常长的时间。由于当时SQL执行时间长,我取消了调整sga_target的SQL语句




原标题:ORACLE调整SGA_TARGET值耗费时间长案例

关键词:oracle

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

Daniel:B2B工厂转B2C电商,你需要了解的几件事!:https://www.ikjzd.com/articles/137030
B2B工厂转B2C电商 你需要了解的几件事!:https://www.ikjzd.com/articles/137031
惊!旺季物流还降价!便宜1块是1块~:https://www.ikjzd.com/articles/137032
月销百万美金,两位95后送你八条创业建议:https://www.ikjzd.com/articles/137033
70亿次搜索,80%的用户都买了这类产品!:https://www.ikjzd.com/articles/137034
涨了60%!做户外运动产品,都有一颗“初心”:https://www.ikjzd.com/articles/137035
长治婚庆女司仪和主持人:https://www.vstour.cn/a/366176.html
北京丰台区水上乐园哪家好玩?:https://www.vstour.cn/a/366177.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流