oracle数据库优化报告

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、数据库优化部分
从2018年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.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
SQL的100%的数据库时间在CPU、I/O和集群等待，此delete语句消耗大量的CPU资源和产生大量的IO。

delete from CASE_RESULT_INFO where t_time < to_date('2018-04-01','yyyy-mm-dd')
分析排查：
1、表中一共1千万条数据
2、运行时间过长
3、表为普通表，未根据时间t_time分区
修改意见：
根据时间字段t_time按照每个月一个分区的方式来创建一个范围分区
partition by range(t_time)
(
partition table_name_2018_1 values less than(to_date('01/01/2018','dd/mm/yyyy')),
partition table_name_2018_2 values less than(to_date('01/01/2018','dd/mm/yyyy')),
partition table_name_2018_3 values less than(to_date('01/01/2018','dd/mm/yyyy')),
partition table_name_2018_4 values less than(to_date('01/01/2018','dd/mm/yyyy')),
partition table_name_2018_5 values less than(to_date('01/01/2018','dd/mm/yyyy')),
partition table_name_2018_6 values less than(to_date('01/01/2018','dd/mm/yyyy')),
partition table_name_2018_7 values less than(to_date('01/01/2018','dd/mm/yyyy')),
partition table_name_2018_8 values less than(to_date('01/01/2018','dd/mm/yyyy')),
partition table_name_2018_9 values less than(to_date('01/01/2018','dd/mm/yyyy')),
partition table_name_2018_10 values less than(to_date('01/01/2018','dd/mm/yyyy')),
partition table_name_2018_11 values less than(to_date('01/01/2018','dd/mm/yyyy')),
partition table_name_2018_12 values less than(to_date('01/01/2018','dd/mm/yyyy'))
);
这样按照月份删除数据的时候，我们可以：
alter table table_name truncate partition table_name_2018_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列的V ARCHAR2类型修改为NUMBER型，因为里面只放0和1两个取值，其中=1仅返回10条以内，0几乎返回所有记录。

适合用索引!IS_RECOMMEND_CLERK列有索引，只是因为是V ARCHAR2类型的，产生了类型转换，用不到索引。

如果实在无法修改类型，则只有将and IS_RECOMMEND_CLERK = 1改为and IS_RECOMMEND_CLERK='1'
3、数据库优化总结
本次针对数据库在环境、设计及SQL的三方面优化建议，更多的是给出一些合理化的建议和意见，毕竟是生产库，所以在得到甲方领导与厂商ISV的许可，并且我们在测试库中做过测试，而且制定回退计划的情况下才能对生产数据库和系统进行优化操作。