首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >95-最近几个oracle数据库优化项目的经验总结

95-最近几个oracle数据库优化项目的经验总结

作者头像
老虎刘
发布于 2022-12-09 13:43:04
发布于 2022-12-09 13:43:04
5890
举报

最近完成了几个比较大型的oracle数据库的优化项目, 发现一些共性问题, 写出来供大家参考.

这些项目都是远程完成的, 远程收集信息(用ora工具), 远程分析, 最后腾讯会议汇报(同时也相当于一次实战培训).

其中的一个项目, 我没敢接. 这也是到目前为止唯一一个拒绝的项目. 为什么?

现场工程师把采集到的一些性能信息发给了我, 虽然系统运行在oracle的一体机exadata上(较早版本), 但SQL的执行效率还是非常不理想. 几百G的大表, 也做了分区, 但大部分没有使用分区字段上的条件, 仍然需要全表扫描;

客户的期望是能够通过一通操作, 很快能看到明显的优化效果, 这实在是做不到. 这个系统, 我能提出一些优化建议, 但是因为设计上的原因, 需要做很多大的改动, 短期想看到效果是不可能的. 涉及到需要开发商的配合和较大的改动, 这个时间就没法控制了.

给DBA的一些建议:

很多时候, 只有系统hang才会引起dba的关注, 而系统hang大部分都是性能问题导致, DBA可能会重启数据库来临时解决, 但是如果不能从根本上解决性能问题, 这种情况还会频繁出现, 而且频率会越来越高.

  1. 参数不要乱改 很多DBA百度到了一些"专家"建议, 希望通过修改参数达到优化的目的, 实际可能起到相反的作用, 下面列举的是某些"专家"建议修改的一些参数:

_optimizer_cost_based_transformation

_optimizer_null_aware_antijoin

_optimizer_mjc_enabled

_optimizer_unnest_corr_set_subq

_optimizer_squ_bottomup

_optim_peek_user_binds

optimizer_index_caching

optimizer_index_cost_adj

......

我的参数设置原则是:

保持大部分参数为默认值, 对于存在bug的情况, 影响的是极少数特殊的SQL, 可以在sql级别单独处理 (hint或sql_patch); 只有对那些影响面比较大的参数, 才在system级别做全局调整.

(很多开发人员也期望有神奇的参数可以达到优化的效果, 可惜真没有)

2. 关于统计信息收集

不知道是出于什么原因, 有一部分数据库的自动收集统计信息被关闭.

oracle为收集统计信息做了自动任务, 这个自动任务建议是要开启的, 否则就有可能不定时的出现SQL执行计划变差导致的性能问题.

统计信息是优化器为sql生成正确执行计划的最重要依据, 统计信息不准确, 生成的执行计划就非常可能不是最优.

有些资深dba用自己的脚本收集, 不太建议; dba可以结合业务特点, 在默认收集任务的基础上做微调, 比如调整时间窗口, 与业务错开; 分区表增量收集;大表并行收集; 并发收集; 调整默认stale比例(默认10%); 调整采样比例; lock 归档历史表统计信息; 大分区表copy/set统计信息等方法.

关于手动收集统计信息:

遇到执行计划变差, 手动收集统计信息的几个重要的知识点(包括开发人员在程序代码里面调用收集统计信息命令):

使用dbms_stats.gather_table_stats命令,不要使用analyze table 收集统计信息, 因为:

analyze命令是oracle很早之前没有dbms_stats命令的时候用的,也能收集统计信息(有了dbms_stats后, analyze主要只用来做validate structure 和list chained rows), 命令写起来比较简单,但是有比较大的缺陷;

可能因为mysql数据库用的就是这个命令收集表的统计信息,也会误导一部分人在oracle数据库上也用这个命令.

dbms_stats.gather_table_stats和analyze table 具体有什么区别, 你可以自己动手做个比较, 这样会印象深刻.

还有人发现用dbms_stats.gather_table_stats收集完统计信息后, 执行计划没有马上改变. 这是因为没有使用 no_invalidate=>false参数. 这个参数, 只有那些经验丰富的dba才知道.

最后给DBA的一点补充建议:

给开发人员合理的采集性能信息权限, 比如生成awr , 查看数据字典等.

给oracle数据库开发设计人员的几个建议:

数据库性能问题, 90%以上(我个人的经验值)是开发设计上的问题, 下面列举几个常见的:

  1. 基本上绝大部分业务端没有做负载控制(并发量控制) 遇到sql执行时间长, 很多active session长时间不释放, 新的业务请求仍不断增加, 系统不堪重负, 就hang了.

需要中间件起到一个闸门的作用, 不要以为数据库有求必应, 数据库的负载是有限度的, 超出极限负荷就会出问题. 在active session达到一定的阀值后, 就不要再增加新的请求. (有时虽然应用端没有新的请求,但是数据库本身的定时任务也是没办法避免的)

2. 没有合理使用绑定变量

导致大量的SQL硬解析, 95%以上的开发人员不知道什么是绑定变量的写法; 这个百度一下, 很简单, 只是知道和不知道的关系.

如果一开始没有这个概念,等系统上线前测试或者已经上线了, 再发现问题,那改动起来就比较难了.

对于mysql/pg等其他数据库, 不使用绑定变量, 问题可能还没有那么严重, 但是对于oracle, 会带来很多问题,比如并发量上不去, ora-4031, 系统越来越慢(buffer cache被shared pool侵占), top sql不容易被发现等问题.

3. 使用模糊查询作为大表的主要过滤条件

如 instr(col_name,'xxx')>0 或者 col_name like '%xxxxx%', 这种一般需要全表扫描, 随着记录数的增加还会越来越慢; 虽然可以通过改写, 使用index fast full scan,也是提升有限; 最好还是调整业务需求, 能改成 like 'xxxxx%' 为最佳(去掉一个百分号,去掉前面那个最好, 去掉后面那个也行, 就是需要做一些特殊处理).

4. sql写法与业务逻辑的实现方法:

大结果集的分页查询,还有用分页查询的逻辑做数据导出 , 都是不建议的. 分页就尽量把结果集缩小; 导出就一次性导出;

大结果集使用标量子查询 ,执行时间会很长, 而且并行也帮不上忙. 有的需要改成外关联, 有的可能要借助物化视图的快速刷新(比如一些报表);

......

5.plsql结合上下文的写法优化:

如果不看plsql代码的上下文, 单个sql是没有问题的, 下面举几个例子:

5.1 两步可以合并为一步, 这个比较简单

原写法: delete + insert两步:

delete from xxxxxx rp

where rp.resource_id = :b1 and rp.entitytype_id = :b2;

insert into xxxxxx (georegion_id, entitytype_id, resource_id)

values (:b3, :b2, :b1);

改进写法, 直接一步update(表中其他字段置为null):

update xxxxxx set georegion_id=:b3

, other_col1=null,other_col2=null,....

where entitytype_id=:b2 and resource_id= :b1;

5.2 两步可以合并为一步, 也比较简单

原写法, 先insert , 再update:

insert into temp_xxx (aaa, bbb, ... memo)

select * From ext_xxx;

update temp_xxx na set na.aaa = 29010 where na.bbb = '111';

update temp_xxx na set na.aaa = 29011 where na.bbb = '222';

update temp_xxx na set na.aaa = 29012 where na.bbb = '333';

改进写法, 一次insert即可, 不需要update:

insert into temp_xxx (aaa, bbb, ... memo)

select decode(bbb,'111',29010,'222',29011,'333',29012,aaa) ,bbb, .....,memo From ext_xxx;

5.3 一个存储过程调用一个函数实现的逻辑, 其实可以用一个简单SQL就能实现:

--函数(sql存在没有使用绑定变量的情况, 可以增加一个rownum=1的条件):

create function func_getcnt(p_fids clob) return number is

v_count number;

v_sql varchar2(3000);

begin

v_sql := 'select count(distinct id)

from tf

where id in (' || p_fids || ')';

execute immediate v_sql

into v_count;

if v_count > 0 then

return 1;

else

return 0;

end if;

end func_getcnt;

/

--存储过程, 调用上面的函数, 计算出两个值:

create procedure proc_getjituan( p_out1 out number, p_out2 out number)

is

v_count1 number := 0;

begin

select count(distinct pid) into p_out1 from tp;

for x in (select pid,wm_concat(fid) fids from tp group by pid) loop

if func_getcnt(x.fids) = 1 then

v_count1 := v_count1 + 1;

end if;

end loop;

p_out2 := v_count1;

end;

/

SQL怎么写, 你可以动手试试, 也不难.

注: 上面几个SQL都是从客户的业务代码中脱敏简化而来.

以上观点仅为个人看法, 如有不当欢迎批评指正.

(全文完)

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2022-07-26,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 老虎刘谈oracle性能优化 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
Oracle数据库该如何着手优化一个SQL
这是个终极问题,因为优化本身的复杂性实在是难以总结的,很多时候优化的方法并不是用到了什么高深莫测的技术,而只是一个思想意识层面的差异,而这些都很可能连带导致性能表现上的巨大差异。 所以有时候我们应该先搞清楚需求到底是什么,SQL本身是否合理,这些思考很可能会使优化工作事半功倍。而本文是假设SQL本身合理,从Oracle提供给我们的一些技术手段来简单介绍下Oracle数据库,该如何使用一些现有的技术来优化一个SQL执行的性能。 确定需要优化的SQL文本及当前SQL执行计划 确定SQL涉及的所有表及其索引的相
Alfred Zhao
2018/05/11
1K0
90-最近优化过的几套Oracle数据库回顾
最近给几个比较重要的客户优化了几套Oracle数据库, 套用一句名言: 性能好的数据库都是相似的, 性能差的数据库各有各的"不幸". 实际上性能好的数据库基本看不到,除非是一些负载非常小的库. 绝大部分数据库都是处于亚健康状态, 很多拿来做优化的库, 要么是濒临瘫痪,要么是业务用户实在是忍无可忍.
老虎刘
2022/06/22
3800
90-最近优化过的几套Oracle数据库回顾
六千字带你了解 Oracle 统计信息和执行计划
前几天,微信上收到《Oracle DBA工作笔记》、《MySQL DBA工作笔记》作者,DBAplus社群联合发起人杨建荣老师的邀请,说在他的 QQ 群里分享一下技术类、职场类、感悟类的文章,我顿时感到诚惶诚恐,荣幸之至,分享也是一个学习的过程呀,便欣然答应了杨老师的邀请。想着最近也在学习优化相关的东西,那就一边学习一边总结分享,文中如有其它不到之处,还请多多指教。
JiekeXu之路
2020/04/20
3.6K0
Oracle 12c数据库优化器统计信息收集的最佳实践(二)
原文链接 http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf 译者 杨禹航 何时收集统计信息 为了选择最佳执行计划,优化器必须可以获得有代表性的统计信息。有代表性的统计数据不必是最新的,而是一组能够帮助优化器确定执行计划中每个操作所能返回的行数。 自动统计信息收集任务 Oracle会在预定义维护窗口期间 (工作日10pm 到2am 和周末6am 到2am
沃趣科技
2018/03/26
1.9K0
Oracle 12c数据库优化器统计信息收集的最佳实践(二)
Oracle 12c数据库优化器统计信息收集的最佳实践(一)
原文链接 http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf 译者 刘金龙 导 语 Oracle优化器会为SQL语句产生所有可能的访问路径(执行计划),然后从中选择一条COST值最低的执行路径,这个cost值是指oracle估算执行SQL所消耗的资源。为了让优化器能够精确计算的每一条执行计划的COST值,这就需要被执行SQL语句所需访问的所有对象(表和
沃趣科技
2018/03/26
1.8K0
Oracle 12c数据库优化器统计信息收集的最佳实践(一)
一波三折:一次CPU使用率过高故障分析SQL优化解决过程
作者 | 罗贵林: 云和恩墨技术工程师,具有8年以上的 Oracle 数据库工作经验,曾任职于大型的国家电信、省级财政、省级公安的维护,性能调优等。精通 Oracle 数据库管理,调优,问题诊断。擅长 SQL 调优,Oracle Rac 等维护,管理。
数据和云
2018/10/25
3.4K0
一波三折:一次CPU使用率过高故障分析SQL优化解决过程
【DB笔试面试586】在Oracle中,什么是自适应游标共享(1)?
绑定变量窥探的副作用就在于,使用了绑定变量的目标SQL只会沿用之前硬解析时所产生的解析树和执行计划,即使这种沿用完全不适合于当前的情形。在Oracle 10g及其后续的版本中,Oracle会自动收集直方图统计信息,这意味着与之前的版本相比,在Oracle 10g及其后续的版本中Oracle有更大的概率会知道目标列实际数据的分布情况,也就是说绑定变量窥探的副作用将会更加明显。当Oracle执行绑定变量窥探操作时绑定变量所对应的输入值是否具有代表性就至关重要了(这里“代表性”是指该输入值所对应的执行计划和该SQL在大多数情况下的执行计划相同),因为这会直接决定此目标SQL在硬解析时所选择的执行计划,进而决定后续以软解析/软软解析重复执行时所沿用的执行计划。
AiDBA宝典
2019/09/29
8110
Oracle 12c数据库优化器统计信息收集的最佳实践(三)|何时不需要收集统计信息
原文链接 http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf 译者 胡红伟 虽然优化器需要准确的统计信息来选择最优的执行计划,但是有些场景下,收集统计信息比较困难,或消耗资源较高,或收集统计信息不能及时完成,那么就需要另一种备选策略。 不稳定的表 不稳定的表即随着时间的变化,数据会发生巨大变化的表。例如,一个订单队列表,一天的开始它是空的,随着时间推移,订
沃趣科技
2018/03/26
1.4K0
Oracle 12c数据库优化器统计信息收集的最佳实践(三)|何时不需要收集统计信息
使用SQL tuning advisor(STA)自动优化SQL
      Oracle 10g之后的优化器支持两种模式,一个是normal模式,一个是tuning模式。在大多数情况下,优化器处于normal模式。基于CBO的normal模式只考虑很小部分的执行计划集合用于选择哪个执行计划,因为它需要在尽可能短的时间,通常是几秒或毫秒级来对当前的SQL语句进行解析并生成执行计划。因此并不能保证SQL语句每次都是使用最佳的执行计划。而tuning模式则将高负载的SQL语句直接扔给优化器,优化器来自动对其进行详细的分析,调试并给出建议,这就是Oracle 提供的Automatic Tuning Optimizer,即自动调整优化器。Oracle 自动调整优化器通过SQL调优建议器(SQL tuning advisor)来体现。
Leshami
2018/08/14
1.8K0
使用SQL tuning advisor(STA)自动优化SQL
Oracle优化07-分析及动态采样-直方图
获取准确的段对象(表、表分区、索引等)的分析数据,是CBO存在的基石。所以数据段的分析对于CBO来讲非常的重要。
小小工匠
2021/08/16
3640
【DB笔试面试628】Oracle的统计信息包括哪几种类型?
Oracle数据库里的统计信息是一组存储在数据字典里,且从多个维度描述了数据库里对象的详细信息的一组数据。当Oracle数据库工作在CBO(Cost Based Optimization,基于代价的优化器)模式下时,优化器会根据数据字典中记录的对象的统计信息来评估SQL语句的不同执行计划的成本,从而找到最优或者是相对最优的执行计划。所以,可以说,SQL语句的执行计划由统计信息来决定,若没有统计信息则会采取动态采样的方式来生成执行计划。统计信息决定着SQL的执行计划的正确性,属于SQL执行的指导思想。若统计信息不准确,则会导致表的访问方式(例如应该使用索引,但是选择了全表扫描)、表与表的连接方式出现问题(例如应该使用HJ,但是使用了NL连接),从而导致CBO选择错误的执行计划。
AiDBA宝典
2019/09/29
8000
【DB笔试面试628】Oracle的统计信息包括哪几种类型?
【DB笔试面试598】在Oracle中,如何得到真实的执行计划?
在Oracle数据库中判断得到的执行计划是否准确,就是看目标SQL是否被真正执行过,真正执行过的SQL所对应的执行计划就是准确的,反之则有可能不准,因此,通过10046事件及如下的几种方式得到的执行计划是最准确的,而从其它方式获取到的执行计划都有可能不准确。
AiDBA宝典
2019/09/29
8440
经验分享(2) 一次表空间不足引起的连锁反应
都还有很多啊, 但是data表空间使用率很高(超过了95%), 但是还剩几百GB呢. 登录服务器查看日志(tail -100f $ORACLE_BASE/diag/rdbms/ddcw/ddcw/trace/alert*.log)报错大概如下:
大大刺猬
2021/04/23
1.4K0
经验分享(2)  一次表空间不足引起的连锁反应
Oracle数据库12cR2版本的SQL计划管理
文章翻译自ORACLE WHITE PAPER SQL Plan Management with Oracle Database 12c Release 2 概 述 任何数据库应用程序的性能严重依赖于一致性查询语句的执行。而oracle优化器非常适合在没有用户干扰的情况下产生最佳执行计划。但SQL语句的执行计划会有不可预测的变化,原因包括重新收集优化器统计信息,更改优化器参数或对象元数据定义。由于缺少一个执行计划的改变总是好的保证,一些客户会选择固定他们的SQL执行计划(stored outlines)或
沃趣科技
2018/03/26
1.5K0
Oracle数据库12cR2版本的SQL计划管理
按图索骥:SQL中数据倾斜问题的处理思路与方法
数据倾斜即表中某个字段的值分布不均匀,比如有100万条记录,其中字段A中有90万都是相同的值。这种情况下,字段A作为过滤条件时,可能会引起一些性能问题。 本文通过示例分享部分场景的处理方法 未使用绑定变量 使用绑定变量 几种特殊场景 1 测试环境说明 数据库版本:ORACLE 11.2.0.4 新建测试表tb_test: create tablescott.tb_test as select * from dba_objects; 创建索引: create indexscott.idx_tb_test_01
企鹅号小编
2018/01/23
1.1K0
按图索骥:SQL中数据倾斜问题的处理思路与方法
67-oracle数据库,有索引,但是没有被使用的N种情况,以及应对方法(上篇)
如果说性能优化是数据库技术中的明珠,那么索引无疑是其中最耀眼的一颗,特别是OLTP业务数据库。掌握了索引技术,基本上性能就不会有太大的问题。
老虎刘
2022/06/22
1.2K0
Oracle优化09-绑定变量
Oracle-Soft Parse/Hard Parse/Soft Soft Parse解读
小小工匠
2021/08/16
8080
执行计划 - Oracle谓词越界与绑定变量窥探
编辑手记:在SQL执行的过程中,选择不同的执行计划所产生的性能差异非常大,因此能够符合业务地选择正确的执行计划非常重要。但在真实环境中,总会受到一些因素的影响,今天我们来分析谓词越界和绑定变量窥探对SQL执行计划的影响。 案例场景 最近有一客户晚上新导入了一批数据到数据库中,第二天发现业务变慢,主要是其中有一条核心业务SQL执行计划走错导致。 结果排查发现客户在导入数据后并未重新收集统计信息,SQL使用绑定变量,窥探的变量刚好是越界,导致SQL第一次硬解析生成的执行计划走错。再加上10G的库导致接下
数据和云
2018/03/07
1.9K0
执行计划 - Oracle谓词越界与绑定变量窥探
如何保持Oracle数据库SQL性能的稳定性
使用Oracle数据库的应用系统,有时出现SQL性能突然变差,特别是对于OLTP类型系统执行频繁的核心SQL,如果出现性能问题,通常会影响整个数据库的性能,进而影响整个系统的正常运行。这是常常遇到的问题,也是一些DBA的挑战。 SQL性能变差原因分析 SQL的性能变差,通常是在SQL语句重新进行了解析,解析时使用了错误的执行计划出现的。 下列情况是SQL会重新解析的原因: SQL语句没有使用绑定变量,这样SQL每次执行都要解析。 SQL长时间没有执行,被刷出SHARED POOL,再次执行时需要重新解析。
数据和云
2018/03/07
1.6K0
如何保持Oracle数据库SQL性能的稳定性
Oracle统计信息的那点事儿
在Oracle的11g版本中,统计信息为自动收集功能,在部署安装11g Oracle软件过程中,其中有一个步骤便是提示是否启动这个功能(默认是启用这个功能);且有时候在生产环境中,还会对一些对象做手动统计信息的搜集,如果新搜集的统计信息产生的执行计划在实际生产中不符和生产要求,则需继续使用原有的执行计划。
bisal
2020/03/25
1.9K0
推荐阅读
相关推荐
Oracle数据库该如何着手优化一个SQL
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档