oracle 数据库优化
发布日期:2021-09-16 04:36:22 浏览次数:13 分类:技术文章

本文共 6508 字,大约阅读时间需要 21 分钟。

1、I/O优化

  • 降低HWM:尽量用TRUNCATE代替DELETE、重构表
  • 减少因内存不足导致的等待:减少union、distinct、减少orderby,这些占内存
  • 减少网络传输等待:减少dblink,将要访问的远程的表接过来一次,以后直接访问这个表

2、执行计划(数据库访问数据的路径)

执行计划简单的讲就是数据库如何访问数据的路径,从数据库访问到一条数据的方法有多种,执行计划会从众多方案中通过各种比较选出开销最小(CBO模式)一个访问路径,它会因很多因素的改变受到影响。

3、oracle连接

连接发生在一对表或数据行源之间,当在from子句中存在多张表时,优化器将决定哪种连接运算对于每一张表来说效率最高。

常见连接方法:嵌套循环连接、散列连接、排序-合并及笛卡尔,每种方法都有一定的适合条件。每个连接方式都有两个分支,访问的第一张表叫驱动表,访问的第二张表叫被驱动表,通常优化器预估返回行最小的表通常作为驱动表。

嵌套循环用在一个表大一个表小的情况,哈希循环用在等值连接的情况,merge join用在不等值连接。

nested loop(嵌套循环):(关联条件的列要有索引,驱动表的数据远小于被驱动表)

存在着两个循环,一个是外部循环,提取驱动表中符合条件的每条记录。另外一个是内部循环,根据外循环中提取的每条记录对内部表进行连接查询相应的记录。由于这两个循环是嵌套进行的,故此种连接方法称为嵌套循环连接。

特点:

1.一个大表和一个小表(驱动表)连接,连接方式可以是等值或者是不等值

2.驱动表数据较小或者内部表已连接的列有唯一性索引或者高度可选的非唯一性索引,效率很高

3.能快速读取结果集中第一批记录而不必等待整个结果集完全确定下来

嵌套循环连接返回前几行的记录是非常快的,这是因为使用了嵌套循环后,不需要等到全部循环结束再返回结果集,而是不断地将查询出来的结果集返回。在这种情况下,终端用户将会快速地得到返回的首批记录,且同时等待Oracle内部处理其他记录并返回。如果查询的驱动表的记录数非常多,或者被驱动表的连接列上无索引或索引不是高度可选的情况,嵌套循环连接的效率是非常低的。

hash join(散列连接)(只适合出现在等值连接的情况下):

哈希连接分为两个阶段,如下。

1、 构建阶段:优化器首先选择一张相对较小的表做为驱动表,运用哈希函数对连接列进行计算产生一张哈希表。通常这个步骤是在内存(hash_area_size)里面进行的,因此运算很快。

2、 探测阶段:优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话,那么这些记录将会被丢弃而不进行探测

特点:

1.一般两张相同大小的表连接,初始参数hash_join_enable=true

2.只能是等价连接,只能是CBO模式

3.只有一张源表需要排序,可能比merge join更快,因为只需要对一张源表排序;

也可能比nested loop更快,因为处理内存中的hash表比处理b-tree索引更快

4.可能会使用到临时表空间,所以最好pag_aggregate_target设置的比较大

哈希连接比较适用于返回大数据量结果集的连接。

merge join(排序-合并):两个互相连接的表按连接列的值先排序,排序完后形成的结果集再互相进行合并连接提取符合条件的记录(用在>、>=、<=等情况下的连接)

特点:

1.首先对2张表的连接列进行排序后再连接

2.当缺乏数据选择性或者有效索引时,或者2个表都比较庞大,可能比nested loop更有效

3.只能是等值连接,可能使用temp表空间

排序合并连接比较适用于返回大数据量的结果。 

排序合并连接在数据表预先排序好的情况下效率是非常高的,也比较适用于非等值连接的情况,比如>、>=、<=等情况下的连接(哈希连接只适用于等值连接)

笛卡尔积:笛卡尔积连接发生在当一张表的所有行与另一张表的所有行连接的时候,因此这种连接的结果集等于两个表的数据行数相乘。在实际应用中不使用或者避免出现这种连接。

根据执行计划进行优化的一般步骤

将瓶颈sql块单独取出,查看其执行计划,

首先检查其中使用了全表扫描的对象,判断其是否合适。引发错误使用的情形通常是:

1)  没有建立合适的索引列导致全表扫描

2)  非函数索引列使用了函数引发全表扫描

3)   对象统计信息过旧或未收集导致全表扫描、

4)  对于位图索引,直方图信息的缺失有时也会导致错误的全表扫描

然后检查其中无用的表,确认无用的表可以直接去掉减少访问步骤减小系统开销。

接着检查其中是否有重复访问的表,查看是否可以减少访问次数一般可以通过使用with或者建立中间表来优化。

优化访问路径,数据库优化器也有不那么聪明的时候,有时候它生成的访问路径可能并不是最优的,可尝试使用hint来改变访问路径进行优化

3.      统计信息
10g的统计信息自动收集策略
Oracle10g中统计信息默认可以自动收集,由GATHER_STATS_JOB作业收集得到,只有当数据库对象没有统计信息或者统计信息已经过期(Oracle 10G中是否过期的标准是数据库对象被修改的记录行数超过10%,该信息由Modification Monitoring来追踪完成)时才对该对象进行信息统计,该作业在数据库创建或升级时由Scheduler自动创建,

4.      分区表

简述

RANGE(范围)分区

例如查询某个月的数据

LIST(列表)分区

例如查询某个地区的数据

HASH(哈希)分区

数据分配到每个分区的量是均衡的

组合分区

分为(range-list,range-hash)

 

4.分区表优点与缺点

优点

缺点

1.  分区消除:减少访问路径

2.  记录清理:删除记录高效(truncate)

3.  分区转移(exchange partition 分区名 with table tabname);将某分区数据与表互换

1.分区表的缺点:分区过多,会加大Oracle对段的管理。同时内部会产生大量的递归调用。一般数据记录在100万以下的不

建议创建分区表。

分区表的维护

       分区的增加:在10G中没有自动增加分区的功能因此本轮优化中采取了手动维护的方式,通过脚本在每月1号增加下个月的分区。

       分区统计信息收集:分区表的统计信息主要是通过数据库自动收集统计信息的策略实现的,但在日常执行过程中出现了每月2号(sysdate时间)引用了部分分区表的sql执行缓慢的情况,是因为新增的分区的统计信息是空的,因此在分区插入数据后导致了优化器没有准确的统计信息可供使用出现执行计划变差。目前采取的补救方案是每月3号早上在自动收集统计信息完成后将分区的统计信息备份一份,在下月2号之前恢复给新建分区上。使优化器有统计信息可以使用避免了执行计划变差。

       分区数据接入:采取分区表后可以对分区进行截断操作即减少了执行时间也能避免之前delete而产生的高水位线。因此在对按月接入数据的分区表采取截断操作后再接入数据。

5、索引:一种数据库结构,用来快速查找数据,一般包括根节点,叶节点。叶子节点存储索引条目,一般条目里包含索引键值(单列索引是一个值多列索引是多个值组合)与rowid
B树索引
目前数据库中最常用的索引,构造类似于二叉树,能根据键值提供一行或一个行集的快速访问,其中的’B’代表平衡, 通常使用在频繁使用查询谓词的列上,一般这类列的选择度都较高。

使用场景

1、当我们希望从表中只返回少量的数据(占比很小,这个比例通常经验值是5%,不过根据表的不同也有不用,一个瘦表(通常只有几列)可能在20%-30%,一个胖表(列很多或列很宽)可能在2%-3%)时会使用索引。如下例:

T1表插入从1到10w的数字只有一列,t3_2利用下例中的t3建表,建表sql

createtable t3_2asselectid,name,name||name||name||name c3,name||name||name||name||name c4 fromt3

分别在id列建立索引,收集统计信息

以上两个查询中的数值分别是全表扫描与索引范围查询的临界值

2、当我们想要查询大量数据,但是只要返回索引的列或者只通过索引列就能得到结果的话,索引也会起到作用

3、数据在磁盘上的物理组织也会对索引的使用有影响,如以下的例子,我们创建两个实验表t2/t3,向t2中顺序的插入10w条数据,同时生成一组随机数据。将t2按照随机数排序插入到t3中,目的是打乱数据的物理存储位置。

总结

1、通过索引访问表中的数据占比越少越有效

2、如果能使用索引列回答问题(只用到索引列不用访问表)那么返回数据占比很大索引也是有效的

3、数据的物理组织有时未按照索引列或主键列有序的填充表,会影响索引的使用

4、空值会影响索引的使用,在有空值的列上通过与虚拟列建立组合索引,可以使优化器选择索引。而且索引的大小并没有明显变化

5、B树索引经过大量的插入删除操作以后一个是容易使树不平衡,再一个是删除后空间不回收。所以定期重建索引非常有必要。

 

位图索引
在B树索引中,索引键值与行之间存在一种一对一的关系,一个索引键值引向一行,而在位图索引中,一个索引键值则对应多行,位图索引通常适用于高度重复(相对于很多的行数,列值可能只有几个,列值/行数越接近0则越适合使用位图索引)而且经常只读的列,通常查询这种列返回的数据占比很大,因此也不适合使用B树索引。对比来看,B树索引通常是选择性的,位图索引位通常不是选择性的。位图索引的键值使用0,1存储,相较B树索引节省很大的空间另外位图索引可以存储NULL值。

使用场景

1、一个查询条件包含多个列,并且要创建索引的列只有几个不同的值及大量的聚合统计查询where条件中使用and/or/in

如截图中的查询例子。如果建立B树索引,为了高效的满足查询要求,就要建立2或者更多的索引组合来实现,这将会占用大量的数据库空间如果后期条件有调整维护起来也比较麻烦。

如果建立位图索引,oracle会对3个索引的位图使用and、or或not得到合并后的位图,如果有必要可以将位图中的‘1’转换成rowid来访问数据,如果是计数则直接统计1的个数。

总结

1. 位图索引使用于低基数的列(比如说性别列,数据仓库中的维表的主键),相对于B树索引,它的count,and,or操作更有效

2. 位图索引存放的是0,1的比特位,相对于B树索引,占字节数特别少

使用位图索引要特别注意

1. 列的基数比较多,不适合位图索引,因为它会占用更多的存储空间

2.索引列DML频繁的列,不适合位图索引,容易造成死锁,原因是一个位图索引键值指向多行,如果一个会话修改了一行数据,大多数情况下这个键值所对应的所有行都会被锁定。大大影响到系统并发性。数据仓库项目中对于位图索引的维护一般建议先删掉索引加载完完数据后再建立索引

3.关于列偏态或称列倾斜、倾斜列对使用索引的影响,这种列的特点是数据大多集中在某几个值。这种情况下一般会影响索引的使用,通常情况下需要收集表的直方图信息来使优化器决定是否使用索引。

分区表索引
 在分区表中经常使用的两种索引,本地索引和全局索引。一般使用LOCAL索引较为方便,而且维护代价较低,并且LOCAL索引是在分区的基础上去创建索引,类似于在一个子表内部去创建索引,这样开销主要是区分分区上,很规范的管理起来;而相对的GLOBAL索引是全局类型的索引,根据实际情况可以调整分区的类别,而并非按照分区结构一一定义,相对维护代价较高一些,在应用过程中依据实际情况而定,来提高整体的运行性能。

根据经验总结了本地索引与全局索引使用技巧

1、如果使用全局索引,当对某一个分区进行ddl操作时,该索引就无效了,必须重建,这一点比较麻烦。(DDl操作对全局索引和本地索引的影响详见下表的总结)可以在语句后增加UPDATEindexes在截断分区的同时维护全局索引避免失效

2、如果索引字段是分区键(主要是range),那么就用local的。
3、如果索引字段是id、电话号码等类型的,那么就用global的。
4、如果分区间的数据是相互独立的,即不会被同时访问,使用local index 更好些。相反如果数据跨越多个分区,可能local index会更差些。
5、当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。

组合索引与单列索引

整体来讲,索引是为了更快的查找到数据而产生的,一般建立在选择度较高的列上(位图索引不在这个讨论范畴)。组合索引一般是通过多列组合来达到提高选择度的,代价是索引比较占用空间,如下例:

我们复制t3表为t3_1,同时在id与name上建立组合索引(IDX_T31),对比两个索引大小

由于name列的加入,t3_1索引的大小是t3的5倍多,接下里查询相同的值来对比执行计划中的消耗

可以看到虽然结果相同,但是使用组合索引的字节与cpu消耗均高出不少。(某些特定情况,比如说要取出的列都是索引列,那么就会减少一步从表中读数)

综合以上实验可以知道,虽然执行计划都选择了走索引但是两个索引所占用的物理空间与被引用时带来的开销有很大不同,当单列选择度较高的时候尽量选择单列索引,只有当单列索引的选择度不够高时才考虑使用组合索引来提高选择度。

函数索引
函数索引与B*树索引的结构存在很大相似性。区别就在于形成树结构的叶子节点上,保存的不是索引列的取值,而是经过特定的函数处理过的索引列值。这样的结构,进行搜索的时候,就可以直接使用到函数索引的叶子节点,获取到对应的rowid集合。

B树索引通常用在精确匹配或者小范围的查询,但是当进行模糊匹配时就会常常引起B树索引的失效,这时就可以用到函数索引。

函数索引的使用要注意以下几点

首先,函数索引的综合消耗要大于普通的B*树索引。相对于传统索引,函数索引要保证创造的函数列数据一致性和多次进行函数计算。这样的消耗要远大于普通B*树索引;

其次,函数索引的适应范围较小。函数索引生效的最大要素就是函数的使用和定义是100%相同。

最后特别注意在10gR2版本中,发生过删除函数索引导致引用这个表的存储过程编译失效的情况,临时解决方案是在删除这个索引后把所有失效的脚本重新编译一次。所以更要慎重选择使用函数索引。

函数索引通常是一种事后补救措施。一个良好设计的应用,一个划分合理的数据库逻辑结构,应该是可以避免函数操作数据列的SQL大量出现的。只有在系统上线之后,问题暴露出来,但没精力进行修改时或修改代价太大,才开始使用函数索引,保证系统功能能够实现。

6.      其他优化方案
除以上总结的优化方法外在本轮优化中还尝试使用了其他方法,主要如下:

l  定位脚本中的sql 瓶颈:使用pl/sql developer测试窗口中的profiler来定位是哪一段sql耗时较久

l  去掉orderby,排序是一项开销很大的操作,如果非必须可以去掉(需要考虑B树索引中的簇族因子,如果列的排序过于无序也会导致索引的失效参看B树索引部分,这是一个需要平衡的选择)

l  将leftjoin 改写成子查询,根据子查询的特性对于返回到外层查询的记录来说,子查询会每次执行一次。因此,必须保证任何可能的时候子查询都要使用索引,如果父查询只返回较少的记录,那么再次执行子查询的开销不会非常大。因此在连接左侧的表如果在执行计划中使用了全表扫描并且没有参与where的条件可以考虑转换成关联子查询。

l  使用语句级并行,用资源换时间多数使用在全表扫描情况下。

l  增加过滤条件,可能会更改业务逻辑需要慎重选择。

l  一段sql中对一张大表进行了全表查询部分列,这种情况,需要使用全量抽取改增量抽取的方式来优化

 

转载地址:https://blog.csdn.net/wlg9527/article/details/107461292 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:IVR 交互式语音响应------Avaya
下一篇:hibernate经典问题

发表评论

最新留言

留言是一种美德,欢迎回访!
[***.207.175.100]2024年03月08日 12时11分02秒

关于作者

    喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!

推荐文章

java metrics 怎么样,Java metrics 2019-04-21
在vscode中php语言配置,Visual Studio Code C / C++ 语言环境配置 2019-04-21
php怎么翻译数据库中的中文,javascript – 如何将翻译后的文本插入数据库php 2019-04-21
普朗克公式matlab,用MATLAB实现普朗克函数积分的快捷计算.pdf 2019-04-21
swoolec+%3c?php,PHP+Swoole并发编程的魅力 2019-04-21
php 404配置,phpcms如何配置404 2019-04-21
matlab wash矩阵产生,洗衣机净衣效能与衣损程度的关系分析 2019-04-21
php中如何调用sql server,php调用SQL SERVER 2008及以上版本的方法 2019-04-21
python多线程实现kmeans,3种方式实现python多线程并发处理 2019-04-21
matlab 变量不存在,matlab程序运行时提示变量未定义 2019-04-21
php编码函数 base58,1. Base58可逆加密 2019-04-21
oracle 在需要下列之一,Oracle存储过程中PLS-00103:出现符号“/”在需要下列之一时:(... 2019-04-21
oracle10g dblink优化,Oracle10g通过dblink访问数据异常 2019-04-21
linux安装时的iso文件,直接用ISO文件在linux上安装新系统 2019-04-21
linux修改文件权限为所有人都可以访问,Linux 笔记分享八:文件权限的设定 2019-04-21
linux中卸载ambri-servle,Kerberos 命令使用 2019-04-21
linux二进制反编译,Xori:一款来自BlackHat 2018的二进制反汇编和静态分析工具 2019-04-21
linux两台主机添加信任,Linux两台机器间添加信任,实现不用密码问,互传文件... 2019-04-21
linux 自动获取ssl证书,linux生成自验证ssl证书的具体命令和步骤 2019-04-21
linux基础命令20个,20-linux中基础命令 2019-04-21