Oracle 11G 性能优化一例

按：别人做的一个系统（基本都是本人做的，当然产品是某公司的），在用户使用时，发现在中午1.30 左右，性能出现下降，记得当时特地跑到现场去解决。最后发现是Oracle 优化做成的，处理过程记录如下。

全文转帖如下

某某Oracle系统性能调整参考命令

system 登录SQLus 或者第三方工具，例如 plsql developer

使用下列命令查询每小时执行的SQL语句数目

Select count(*)，--统计数目
substr(b.FIRST_LOAD_TIME,1,13)—sql语句首次装载时间
from v$sqlarea b
where b.FIRST_LOAD_TIME between '201 5-06-22/21:00:00'
and '2015-06-25/22:00:00'--时间范围，可以根据实际情况调整
and b.PARSING_SCHEMA_NAME='NOTAM_ADMIN'--通告Oracle用户
group by substr(b.FIRST_LOAD_TIME,1,13)--按小时分组统计，13 是截取到小时的位置
order by 1--按照统计数排序
desc --降序排列

 以北京时间查询显示，参考使用下列命令查询每小时执行的SQL语句数目
注：下面是按照小时查询hh24表示转换为24小时制

Select count(*) Total ,to_Char(to_date(b.FIRST_LOAD_TIME,'yyyy-MM-dd/hh24:Mi:ss')+8/24,'yyyy-mm-dd hh24') BJTIME

From v$sqlarea b
where to_Char(to_date(b.FIRST_LOAD_TIME,'yyyy-MM-dd/hh24:Mi:ss')+8/24,'yyyy-mm-dd hh24')  
between '2015-06-24 13' and '2015-06-25 15'
and b.PARSING_SCHEMA_NAME='NOTAM_ADMIN'
group by to_Char(to_date(b.FIRST_LOAD_TIME,'yyyy-MM-dd/hh24:Mi:ss')+8/24,'yyyy-mm-dd hh24')
order by 2
desc

说明：oracle时间可以进行加减运算，单位是天，因此8小时要表示成8/24

 根据简单统计，发现系统在性能下降的时刻，数据库SQL语句执行很多。这些语句基本都是Oracle系统优化的语句。过多的优化SQL可能导致了系统负载加重，从而在客户端表现为查询时间变长，甚至形成假死现象。目前根据有关资料，调整了如下两个参数

cursor_sharing

shared_pool_size

第一个参数（cursor_sharing）用于指示Oracle优化器的优化行为以及是否加载SQL语句并执行“硬解析”,修改前的系统参数是exact，含义为只有完全相同的SQL语句才不进行硬解析，否则进行加载和硬解析。现在修改为similar，含义为大致相似的SQL语句就不再进行硬解析。

第二个参数用于指定共享池的大小，根据资料，这个值不是越大越好，如果该值过大，会占用Oracle其他进程的内存空间，从而是系统性能下降。系统原来的参数值是6G，目前调整为4G。

以上参数的查询和修改方法
使用PL-sql工具，打开file-New-Command Window, 输入
show parameters cursor_sharing；
或者
show parameters shared_pool_size；
红色部分为查询的参数，可以简写进行模糊查询，例如可以写成

show parameters pool_size；

修改方法：

alter system set cursor_sharing=similar;

alter system set shared_pool_size=4096M;

说明shared_pool_size参数可以根据实际情况调整，但不要超过6G，最小在500M以上。

其他参数：

optimizer_dynamic_sampling

目前设置为： 2
该参数用于Oracle系统进行优化的程度和频度，修改为0后，系统将不进行优化。
如果系统继续出现相应迟滞现象，尝试将该参数改为0，方法如下

Alter system set optimizer_dynamic_sampling=0;

上述文档是2017年7月份，为了防止忘记，记录如上。

MaraSun BJFWDQ
生命诚可贵，快乐价更高。若为自由故，二者不能抛。