系统数据存储性能优化方案__sitech--crm6.0

1. 避免全表扫描

       在数据库中，对无索引的表进行查询一般称为全表扫描。全表扫描是数据库服务器用来搜索表的每一条记录的过程，知道所有符合给定条件的记录返回为止。

        全表扫描的成本 = 表的数据块总数 / 多块读取。

        一般来说，数据库在进行全表扫描时进行的是多块读取，也就是说每次从 buffer cache 中读取多块，而不是一次一块的读。另外，全表扫描的成本中并不包括对数据进行过滤和计算的成本。也就是说全表扫描的成本仅仅是读取数据的成本，而不包含对数据进行计算、过滤的 CPU 成本。

      【适合使用全表扫描】

          (1). 单表查询：

               ※  表很小，索引可能就比表还大。

               ※  访问的数据占全表数据的百分比很大，索引访问的总成本大于全表扫描的成本。

               ※  相对于索引来说，表中的数据排列过于凌乱，表现出来就是索引的 clustering_factor 很大，导致索引的啊访问成本剧增。

           (2). 多表连接：

                ※  hash join的时候，内层表和外层表都可以使用全表扫描（对于某个单独的表的访问是否使用全表扫描，这个又回到了单表查询的情况）。

                ※  nest loop的时候，外层表可以使用全表扫描，内层表一般不用全表扫描。

2. 尽量少使用排序

       排序是 SQL语句中一个小的方面，但对调优很重要，在 Oracle 的调整中，它常常被忽略。排序分为私有排序区域和磁盘排序；私有排序区域的大小是由 init ora 中的 sort_area_size 参数决定的。每个排序所占用的大小由 init ora 中的 sort_area-size 参数决定的。当排序不能在分配的空间中完成时，就会使用磁盘排序的方式，即在 Oracle 实力中的临时表空间中进行。

       当使用 create index、order by 或者 group by 的语句时，Oracle数据库将会自动执行排序的操作。通常，在以下的情况下，Oracle会进行排序的操作：

              使用 Order by 的 SQL 语句；

              使用 Group by 的 SQL 语句；

       磁盘排序的开销是很大的，有几个方面的原因。首先，和内存排序相比较，它们特别慢，而且磁盘排序会消耗临时表空间中的资源。Oracle 还必须分配缓冲池块来保持临时表空间中的块；还有，过多的磁盘排序将会令 free buffer waits 的值变高，从而另其他任务的数据块由缓冲中移走。

3. 重新组织表结构

       在优化的过程中经常发现，部分数据实体作为应用逻辑的触发源，当表中存在数据时，调用其他组件完成业务逻辑，然后更新触发表对应记录为已处理或删除对应记录。导致数据检索慢或者表争用严重。性能不满足要求。举例如下：

       客户预警接口表的设计如下：

英文名称	中文名称	数据类型	是否主键	是否索引
SEQ_ID	记录的唯一编号	NUMBER(14)	Y
ID_NO	用户ID	NUMBER(14)	N	Y
CRT_TIME	创建时间	DATE	N
STATUS	状态	CHAR(1)	N	Y
。。。	。。。
UPT_TIME	更新时间		N
ERRMSG	错误描述

       进程 dAlterMsg 循环读取该表中 STATUS=0 记录，如果存在调用 dUpdateCustMsg 进程完成客户资料修改，更新对应记录的状态为 STATUS=1；

       该进程总共起了 10 个进程，每个进程读取 ID_NO 左后一位和进程号匹配的记录。检索语句如下：

              select SEQ_ID form RD_CBInterFace_Info where mod(id_no, 10)= 进程号 order by crt_time；

       问题：

              检索语句对 ID_NO 索引使用了函数取模操作，不能用上索引；

              检索语句使用 ORDER BY 操作，消耗大量的 CPU 资源；

              此方案导致接口表数据不断增加，随着数据量的增加，导致数据检索和更新效率低下。

       调整方案：

              修改数据模型，增加地区代码，并在新增则断增加索引，按地市启动进程；

              修改数据模型，增加冗余字段，存在 ID_NO 最后一位，按 0 ~ 9 启动进程；

              当然还有其他较为盒实的方案，更加不同的业务场景需要进行调整。

4. 采用冗余式数据模型

       在NG的数据模型设计时，为了体现数据的梳妆关系（比如产品目录、功能目录、组织机构等），数据实体采用了父子字段设计，为了展现父子关系的数据，需要采用 Start with ... Connect By 字句递归查询，这种语法虽然解决了数据展现问题，但是非常消耗数据库资源。

       举例：

       根据需求需要构筑 DB 的表结构如下（ORG_RANK）

中文字段名称	英文字段名称	数据类型
组织 (PK)	ORG_ID	Varchar2(10)
上位组织ID	HIGH_ORG_ID	Varchar2(10)

       根据上面的结构， 使用 Oracle 的树查询语句 (start with 和 connect by) 来创建 SQL 语句，如下：

       查询指定组织的直属下层组织：

select ORANK.ORG_ID from ORG_RANK ORANK
where (level - 1) = 1
start with ORANK.ORG_ID = #orgId#
connect by prior ORANK.ORG_ID = ORANK.HIGH_ORG_ID

       对以上 SQL 做性能评定时发现出现严重性能问题，（10层组织，3000条数据时）查询时间1分钟多，下面进行了优化：

       ①、分析执行计划，发现由 Full Table，说明使用索引失败，优化的方法是对 HIGH_ORG_ID 加上索引。

       ②、虽然只是查询直属下层的组织，但是上面 SQL 实际执行时，先查询出指定组织的所有下层组织，然后再从结果中过滤出直属下层的组织 (where (level - 1)=1)。

       上面的分析可以得到证明，因为输入倒数第二层组织的执行时间会比输入最上层组织的执行时间少得多。

       【优化方法1：】

       增加 connect by 语句的条件 (and (level - 1)<= 1)，不满足条件的子树不会被查询，会省去很多没用的递归查询。

select ORANK.ORG_ID from  ORG_RANK ORANK
where (level - 1)=1
start with ORANK.ORG_ID = #orgId#
connect by prior ORANK.ORG_ID = ORANK.HIGH_ORG_ID
and (level - 1)<= 1

       【优化方法2：】

       增加数据模型，存储父子关系以及数据层级关系，在检索时直接通过新增模型进行查询，不使用 Connect by 语句。

       增加数据模型属性如下：

中文字段名称	英文字段名称	数据类型
组织 (PK)	ORG_ID	Varchar2(10)
上位组织ID	HIGH_ORG_ID	Varchar2(10)
上下级层级	CLEVEL	NUMBER(6)

5. 避免使用过大的视图

6. 避免使用 UNIION

7. 动态查询使用绑定变量

8. 避免频繁访问系统表

9. 优化方案总结

(1) 优化方案总结：

步骤	方法
1	找到需要调优的SQL语句。 从SPOOL_AREA中查看等待事件是找出需调优语句的极好办法。
2	查看其执行计划。 解SQL运行方式，如全表读、索引读、HASH连接、分区扫描等，还可看出大致代价及物理读逻辑读
3	检查表和索引的分析情况。 检查关键谓词是否有索引，及表和索引是否有分析过；
4	检查表内部块的情况。 检测是否存在行迁移及高水平位问题，目的是为了避免访问过多不必要的块。
5	重新组件索引或增加CACHE存储 将表和索引CACHE到内存KEEP区中，将物理IO降低。
6	如果以上发放还无效，利用分区特性、利用ROWID和ROWNUM特性、利用中间表转化、利用物化视图等
7	调整设计方案，改变设计思路，改变数据模型

(2) 优化使用的关键视图：通过 Oracle 数据库的关键动态视图观察数据库语句的性能。

序号	概述	ORACLE动态视图表
1	System 的 over view	v$sysstat , v$system_event , v$parameter
2	某个session 的当前情况	v$process , v$session , v$session_wait ,v$session_event , v$sesstat
3	SQL 的情况	v$sql , v$sqlarea , v$SQL_PLAN , V$SQL_PLAN_STATISTICS, v$sqltext_with_newlines
4	Latch / lock /ENQUEUE	v$latch , v$latch_children , v$latch_holder , v$lock ,V$ENQUEUE_STAT ,V$ENQUEUE_LOCK
5	IO 方面	v$segstat , v$filestat , v$tempstat ,v$datafile , v$tempfile
6	shared pool / Library cache	v$Librarycache , v$rowcache , x$ksmsp
7	advice	v$db_cache_advice , v$PGA_TARGET_ADVICE, v$SHARED_POOL_ADVICE