oracle数据库优化改进报告.docx

oracle数据库
优化汇报

目录
1、概述 3
2、数据库优化部分 3
2.1、环境优化 3
2.1.1 统计信息收集被关闭 3
2.1.2 部分索引失效 4
2.2、设计优化 4
2.2.1 设计类问题概述 4
2.2.2 设计类问题优化建议 5
2.3、SQL优化 5
2.3.1 SQL_ID= 7gf3typgc469a 5
2.3.2 SQL_ID= bdcfdz26x5hm9 6
3、数据库优化总结 7

1、概述
伴随应用软件用户负载增加和愈来愈复杂应用环境，操作系统各项性能参数、数据库使用效率、用户响应速度、系统安全运行等性能问题逐步成为系统必需考虑指标之一。性能测试和优化通常经过自动化测试工具模拟多个正常、峰值和异常负载条件来对系统各项性能指标进行测试，用来检测系统是否达成用户提出性能指标，立即发觉系统中存在瓶颈，最终起到优化系统目标。
伴随需求不停增加，尤其是复杂逻辑需求，一旦出现高并发量时，也将可能造成数据库主机无法承载，所以数据库优化亟待处理。
2、数据库优化部分
从1月份开始跟踪及分析，发觉托管区数据库在环境、设计及SQL三方面，全部存在不少问题。在SQL类优化中，当地化代码编写和设计不良，是比较显著问题。下面将分成环境、设计、SQL优化三类进行连续分析，并给出相关提议、整改方案、整改善度。
2.1、环境优化
2.1.1 被关闭
zonghe托管区数据库统计信息未自动搜集，假如未打开搜集，会对系统性能造成较大影响。

需要开启统计信息
开启方法以下：
--实施           
BEGIN
 dbms_auto_task_admin.enable(client_name => 'auto optimizer statscollection',
                              operation   => NULL,
                              window_name =>NULL);
END;
2.1.2 部分索引失效

需要将索引进行删除。删除命令参考以下：
drop index index_name;
2.2、设计优化
2.2.1 设计类问题概述
序号
类型
问题描述
1
表
ZJ_KZH_DATE、ZJ_CRM_S_ORDER_GATHER等当地表，设计了大量V1，V2，需要开发人员查对需求
2
索引
索引定义较混乱，常和其它表进行连接表，在连接字段上没有建立索引
3
sql语句
sql语句不规范，SELECT子句常常使用' * '
4
关键字
表中查询常常使用in，not in，对数据库性能产生影响
2.2.2 设计类问题优化提议
1、对于表创建开发人员需要和业务人员确定后再定义
2、常常和其它表进行连接表，在连接字段上应该建立索引
3、索引应该建在选择性高字段上。比如：表示性别数据列，因为只有男女两种值，就属于选择性低
4、SELECT子句中避免使用' * '：ORACLE在解析过程中，会将' * '依次转换成全部列名， 这个工作是经过查询数据字典完成，这意味着将花费更多时间
5、sql语句用大写：因为oracle总是先解析sql语句，把小写字母转换成大写再实施。另外，在java代码中尽可能少用连接符“＋”连接字符串！
6、用EXISTS替换IN、用NOT EXISTS替换NOT IN：在很多基于基础表查询中，为了满足一个条件，往往需要对另一个表进行联接.在这种情况下， 使用EXISTS(或NOT EXISTS)通常将提升查询效率。 在子查询中，NOT IN子句将实施一个内部排序和合并。不管在哪种情况下，NOT IN全部是最低效 (因为它对子查询中表实施了一个全表遍历)。 为了避免使用NOT IN ，我们能够把它改写成外连接(Outer Joins)或NOT EXISTS。
2.3、SQL优化
2.3.1 SQL_ID= 7gf3typgc469a
SQL100%数据库时间在CPU、I/O和集群等候，此delete语句消耗大量CPU资源和产生大量IO。
delete from CASE_RESULT_INFO where t_time < to_date('-04-01','yyyy-mm-dd')
分析排查：
1、表中一共1千万条数据
2、运行时间过长
3、表为一般表，未依据时间t_time分区
修改意见：
依据时间字段t_time根据每个月一个分区方法来创建一个范围分区
partition by range(t_time)
(
partition table_name__1 values less than(to_date('01/01/','dd/mm/yyyy')),
partition table_name__2 values less than(to_date('01/01/','dd/mm/yyyy')),
partition table_name__3 values less than(to_date('01/01/','dd/mm/yyyy')),
partition table_name__4 values less than(to_date('01/01/','dd/mm/yyyy')),
partition table_name__5 values less than(to_date('01/01/','dd/mm/yyyy')),
partition table_name__6 values less than(to_date('01/01/','dd/mm/yyyy')),
partition table_name__7 values less than(to_date('01/01/','dd/mm/yyyy')),
partition table_name__8 values less than(to_date('01/01/','dd/mm/yyyy')),
partition table_name__9 values less than(to_date('01/01/','dd/mm/yyyy')),
partition table_name__10 values less than(to_date('01/01/','dd/mm/yyyy')),
partition table_name__11 values less than(to_date('01/01/','dd/mm/yyyy')),
partition table_name__12 values less than(to_date('01/01/','dd/mm/yyyy'))
);
这么根据月份删除数据时候，我们能够：
　　alter table table_name truncate partition table_name__1;
2.3.2 SQL_ID= bdcfdz26x5hm9
该语句仅节点+1在7天内就实施+255130+次，平均每次2.2+秒，两节点累计7天实施50多万次
select key_word
from cust_zj_declaration a
where REASON_SUB_TYPE is not null
and IS_RECOMMEND_CLERK = 1
and rownum <=10
order by CLERK SORT;
分析排查：
1、CUST_ZJ_DECLARATION表统计有100万条
2、IS_RECOMMEND_CLERK类型为varchar2类型
3、IS_RECOMMEND_CLERK列有索引

修改意见：
将IS_RECOMMEND_CLERK列VARCHAR2类型修改为NUMBER型，因为里面只放0和1两个取值，其中=1仅返回10条以内，0几乎返回全部统计。适适用索引!IS_RECOMMEND_CLERK列有索引，只是因为是VARCHAR2类型，产生了类型转换，用不到索引。假如实在无法修改类型，则只有将 and IS_RECOMMEND_CLERK = 1改为and IS_RECOMMEND_CLERK='1'
3、数据库优化总结
此次针对数据库在环境、设计及SQL三方面优化提议，更多是给出部分合理化提议和意见，毕竟是生产库，所以在得到甲方领导和厂商ISV许可，而且我们在测试库中做过测试，而且制订回退计划情况下才能对生产数据库和系统进行优化操作。