【SQL TUNING】
文章平均质量分 71
24号信仰
Focus on SQL Tuning
展开
-
用MERGE改写UPDATE的优化
某系统负责人反应一个新上线的存储过程跑了 10个小时没出结果,中间卡在了一个update语句上面: ETL日志看到这个流程2017-04-11 23:59:43开始跑 ,到第二天10点还没有跑完开发告诉我他们存储过程里面新上线了一条update语句,在测试环境测试可以跑出结果:sql如下 UPDATE UUU_UIU_ITTTTTTTTT S SET S.ASSET_LOS...原创 2017-05-02 15:17:13 · 3399 阅读 · 0 评论 -
parallel优化案例
背景:某数据仓库系统 一个ETL流程执行了10多个小时才完成!平时都是1小时问题定位:查看ETL时间段内所有SQL的运行时间。select count(1), sql_id from dba_hist_active_sess_history where to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') >= '2018...原创 2018-08-14 11:10:12 · 959 阅读 · 0 评论 -
半连接、反连接的优化案例
先来理解几个概念:半连接:两表关联,只返回匹配上的数据并且只会返回一张的表的数据,半连接一般就是指的在子查询中出现 IN 和 EXISTS反连接:两表关联,只返回主表的数据,并且只返回主表与子表没关联上的数据,这种连接就叫反连接。反连接一般就是指的 NOT IN 和 NOT EXISTS子查询展开:优化器将嵌套的子查询展开成一个等价的JOIN,然后去优化这个JOIN。如果不展开的情形是...原创 2018-08-14 16:52:51 · 1539 阅读 · 2 评论 -
关系型数据库通用的坑-自定义函数的优化
自定义函数,和标量子查询一样,在主查询返回结果集(行数N)巨大时,自定义函数里面的查询会被访问N多次……自定义函数唯一的适用场景就是 分页查询,其他场景基本上都具有潜在风险,需要改写。SELECT rrs.o_id, (SELECT min(IFNULL(get_product_minOrderPkgQty(rrs.o_id, rct.typeId) * ...原创 2018-08-07 10:34:40 · 743 阅读 · 0 评论 -
简单的视图合并
简单的视图合并,案例SQL和PLAN如下:SELECT * FROM QQ_VIEW_RRRRRR_FFFFFF WHERE howtime >= 0 And inorout = 1 And Callid like '%13511232777%' AND starttime >= to_date('2017-08-06 00:49:01', 'yyyy-m...原创 2018-08-07 15:34:22 · 785 阅读 · 0 评论 -
谓词推入的优化案例
案例很简单,主要就是长……Sql文本:select count(*) from VIEW_SB_UUUUUUUUUUUUUUUUU where 1 = 1 and (KKKKID in (SELECT orgno FROM PP_OOO WHERE PARENTORGNO = '110000078...原创 2018-08-07 17:12:18 · 19370 阅读 · 0 评论 -
一次从业务出发的优化
背景:朋友发过来一个存储过程,每天跑一次,单次执行时间略长……执行的结果插入到t_error表中,代码及结果数据如下:下面是优化过程:优化后代码如下:把四条反连接的SQL语句合并成一条FULL JOIN语句,性能提升顶多也就4倍!和我们基础优化动辄上1000倍的性能提升相比简直"相形见绌"!但是这种从业务角度发起的优化是凌驾于基础优化(建索引,加HINT等)之上,让...原创 2018-08-08 15:14:16 · 423 阅读 · 4 评论 -
SQL优化经典案例合集
案例即笔记,难免有疏漏。如对案例有任何问题 请直接留言或者联系本人(微信/手机号:15652625652)我很乐意和大家相互学习,共同进步!!34.关注业务-把优化做到极致了解业务,优化新高度33.一波三折:UPDATE语句改写优化 UPDATE/MERGE/分批提交 哪个高效用哪个!!!32.一次'诡异'的执行SQL报错ORA-03113的问题处理这个ORA报错很肤浅...原创 2018-09-01 08:11:35 · 2805 阅读 · 0 评论 -
大量慢SQL导致节点宕机的故障分析
故障背景:项目负责人找到我说2018-08-28 11:40:00-2018-08-28 11:45:00左右收到频繁告警短信 数据库节点1宕机,节点2 SESSION数一直在增加。这是一个数据仓库系统,正常情况下SESSION一般是90左右,现在将近500,而且还在增长select * from dba_hist_active_sess_history where to_char(s...原创 2018-09-01 11:39:37 · 1620 阅读 · 0 评论 -
选择最合适的连接方式
声明:本例十分简单,只适合新手DBA走马,老司机请自觉绕道~背景:跑批中一条SQL“卡住”了三个小时.....SQL以及执行计划如下:SELECT A.* FROM SSS_DDD_ACCT_DDD_DDD aWHERE exists (select nameFrom from (select Acct_Num as nameFrom ...原创 2018-09-26 16:18:37 · 707 阅读 · 0 评论 -
树形查询的优化案例
某数仓系统一条SQL执行了很长时间,要求分析原因使用如下查询获取时间段内SQL语句的执行情况select count(1),sql_id from dba_hist_active_sess_history where to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') > '2018-07-13 12:03:57' ...原创 2018-09-26 16:19:27 · 1303 阅读 · 2 评论 -
又是标量子查询引起的性能问题
某系统巡检,发现TOPSQL里面第一条SQL语句的执行时间相当不合理~select (select EC_CUST_NO from GYL.FAAAAASM cus where cus.refcode = a.SELLER_REFCODE) EC_CUST_NO, BUSSTYPE, sum(LOANAMT) SUMLOANA...原创 2018-09-26 16:20:36 · 1203 阅读 · 2 评论 -
从业务上消除SORT MERGE JOIN
背景:某系统跑批的一个存储过程一直报错ORA-1652: unable to extend temp segment by 128 in tablespace xxx查看alertlog 获取报错的时间点:收集 报错期间的AWR 直接在SQL Statistics 里面可以定位到SQLMERGE INTO T_AA_BBB_CCCC_DDDD_E_G4H3 G4H3US...原创 2018-09-26 16:26:26 · 483 阅读 · 0 评论 -
使用HASH代替NEST LOOP
某系统有一条SQL 执行了十几个小时没出结果~【SQL复杂 案例简单 已经精通oracle连接方式的可自行绕道别的案例~】SQL语句和执行计划如下:select tt.org_type, tt.prod_code, tt.prod_name, tt.BRAND_CODE, tt.iscapital, tt.cu...原创 2018-10-22 16:27:36 · 682 阅读 · 1 评论 -
分页语句优化案例
某数仓系统,一条SQL语句执行了很长时间,SQL和执行计划如下select c.data_dt, c.cust_id, c.cust_mgr_id, c.org_id, c.outlets_id, c.ent_master_cret_typ, c.ent_master_cret_num, c.o...原创 2018-10-22 16:28:24 · 579 阅读 · 0 评论 -
一次'诡异'的执行SQL报错ORA-03113的问题处理
银行某系统存储过程报错,重新调起仍报错,而前几天这个一直正常。通过应用日志定位到报错信息,ORA-03113: 通信通道的文件结尾进程 ID: 36503726会话 ID: 586 序列号: 65345遇到这种错误,习惯性在网上找答案。然而网上那些关于这个经典错误的经典案例无一能和我的错误匹配这时候我会习惯性的"看看SQL"从存储过程里面定位到这个导致报错的SQL,单独拿出来跑...原创 2018-10-22 16:29:02 · 4079 阅读 · 0 评论 -
一波三折:UPDATE语句改写优化
最近赶上第四季度上版。很多套系统迁移测试,太忙,抽空带徒弟去优化了一条UPDATE改写的SQL具体的故障分析报告是她写的,如下: 2018年10月份的一天,历史报表系统的开发人员让我帮忙优化一个每天执行报错ORA-01555的存储过程,由于最近给他们系统处理过其他的几个存储过程,优化后的效率都得到了大幅度提升,只是本人比较懒,没有保存下来优化前后的脚本以及优化思路,听领导说本月...原创 2018-10-31 10:27:19 · 952 阅读 · 0 评论 -
查询SQL执行计划中的对象信息(脚本分享)
对于优化来说,表和索引信息(bytes;num_rows)方便开发DBA作出准确判断,快速定位SQL语句性能瓶颈下面分享两个我自用着比较方便的脚本,如发现脚本有疏漏或可改进之处,请留言告知。谢谢!!1.针对某个SQL_IDWITH X AS (SELECT /*+ MATERIALIZE */ OBJECT_OWNER, OBJECT_NAME, LISTAGG(O...原创 2018-08-01 14:35:10 · 461 阅读 · 0 评论 -
再一次用merge优化update
代码上线前审核,xx系统一条update执行了一个多小时……改写前的SQL以及执行计划update fnc.T1_CCCC_SSSS_OOOO s set s.is_wealth = (select case when ttt.total_amt >= (select sp.parava...原创 2018-08-01 11:24:26 · 571 阅读 · 0 评论 -
使用sql_profile脚本处理执行计划突变的案例
现象:2017年12:21 日 XX系统某个模块跑批超时。通过查看会话发现是某个会话在执行sql b2j4crf66rpzn时间很长。等待事件为 SQL*Net message to client 查看该sqlb2j4crf66rpzn详情如下:SQL语句的历史执行计划如下:以上是我开发的优化工具抓取到的图片:可以看到p...原创 2018-08-01 09:52:29 · 560 阅读 · 0 评论 -
特殊分页语句的改写优化
甲方某一个客户反应一个报表页面查询点击之后需要等很久(半分钟)才能显示数据,点击页面生成的SQL如下:SELECT ROWNUM R, EE.* FROM (SELECT E.* FROM (SELECT C.*, PI.CN_NAME FROM CCCCCC_FFFF_INFO C, DDDDD_INFO PI ...原创 2015-06-28 11:08:34 · 876 阅读 · 1 评论 -
脑残设计-视图里包含order by和union
今天开发找到我说一条SQL执行的特别慢。SQL里面有一张视图,视图单拿出来跑,需要十几分钟才能出结果(从这里基本上可以定位是视图的问题了)。SQL和 视图的定义 如下WITH "VVVVSUM_TABLE0" AS ( SELECT ...... FROM "ANNE_APPS".VVVVSUM_TABLE "VVVVSU...原创 2017-05-03 16:04:09 · 948 阅读 · 2 评论 -
rows算错导致错误的笛卡尔积造成temp不足
今天一哥们发来一条sql说一直不出结果,最后报错:ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段 select tran_info.*, rp.mcc_typ_id, urm2.org_no, trim(urm2.rate_type) as rate_type, (case ...原创 2014-12-15 16:41:35 · 1508 阅读 · 0 评论 -
分页语句优化注意点(一):关注业务数据
前些天优化过一个分页函数,回头整理的时候发现了一个问题。让我做了一周多的实验才找到原因:相关表的信息:CCCCCC_FUUU_INFO表90w+条数据 DDDDD_INFO表11条数据 SYS_SEQ_ID是主键。TRAN_TIME的选择性也非常高。DDDDC_ID是表DDDDD_INFO的主键实验一:DROP INDEX IDX_CCCCCC_FUUU_INFO;CREA...原创 2015-06-28 11:39:26 · 699 阅读 · 0 评论 -
序列设计之enq SQ - contention处理一则
某系统负责人反应系统很慢,收集持续报警短信。查看数据库当前活动回话等待事件为enq: SQ – contention,当前活动session 200+enq: SQ – contention是sequence相关的锁,在内存上缓存(cache)范围内,调用sequence.nextval期间拥有此锁。如果创建sequence时,cache值太小(默认是20(单位是“个”)),在调用sequ...原创 2017-06-05 16:21:15 · 2186 阅读 · 0 评论 -
一个跑不出结果的视图的优化
作为开发DBA,职责之一就是每个月都给各个系统出一份性能优化报告连续给某个系统优化了好几条坑爹的大SQL之后,接口人今天找到我说 有一个视图从创建开始和这个视图相关的SQL从来没有跑出来过。最长的一次跑了24小时没出结果。视图单跑也是2个小时跑不出来用户很是抱怨,甚至现在都没有人愿意去点这个页面了,基本要放弃了,问我能不能拯救一下视图源码SQL如下:CREATE OR ...原创 2017-05-03 20:59:34 · 1454 阅读 · 1 评论 -
一条hang住数据库的SQL的分析
2017年某一天某个系统接口人找到我说 有个存储过程跑不过去,尝试了几次均报错:ORA-12801:并行查询服务器p001 ,instance xx中发出错误信号ORA-04030:在尝试分配16328字节(xxxx)时进程内存不足并有监控收到空间急增的短信告警(oracle根目录下产生几十GB的core文件),alertlog内容如下:Errors in file ...原创 2017-12-04 15:14:07 · 1454 阅读 · 0 评论 -
数据仓库设计的隐患-标量子查询
首先,来理解一下标量子查询:处于select之后from之前的子查询称为标量子查询 .比如:select num1,cal,(select name from t2 where t2.id = t1.id)from t1;举这个例子只是为了方便理解标量的含义。当然定义为返回单列的选择语句,或者返回一行的表达式的子查询称为标量子查询。标量子查询的缺点十分明显:驱动表固定是外表t1, t1返回的...原创 2017-05-04 17:15:04 · 1328 阅读 · 0 评论 -
union和or互换
今天,某数据仓库系统的开发发来一条SQL,说跑了3个小时,严重拖慢了整个的跑批流程。INSERT INTO ETL.RRR_SSSS_GGGG_INTERNAL_PTB (RPT_ORG_ID, ITEM_CD, ASSET_SPOT, LIABILITY_SPOT, FORWARD_LONG, FORWARD_SHORT, ADJ_OPTION...原创 2017-05-04 14:34:21 · 3164 阅读 · 0 评论 -
dblink远端数据库统计信息过期
某CRM数据库系统跑批一条SQL执行了24小时 还没执行完问题SQL已经定位到,SQL中表信息以及执行计划如下:SELECT t.CRM_DT, ...... ...... SUM(nvl(t.OUTSTD_AMT, 0) * T9.HL) AS amt, T7.CB_HOST_NO as CB_CUST_ID from A_...原创 2018-07-26 14:58:25 · 454 阅读 · 0 评论 -
Exadata迁移到双节点RAC性能下降
背景:某个数据抽取系统(OLAP)一个跑批存储过程在老环境(40分钟)迁到 新环境(140分钟)老环境是一体机,新环境是双节点RAC.其实不算是迁移,可以看作俩系统同时存在 一条SQL分别在两个系统上跑,性能差异很大SQL语句如下:INSERT INTO TTTT_AAA SELECT POST_DATE,…………BY3 FROM TTTT_AAA_T ILEFT JOI...原创 2018-07-26 15:28:18 · 570 阅读 · 0 评论 -
直方图缺失的优化案例
背景:某监控系统一条SQL在老环境执行3s,迁移到新环境执行了15分钟不出结果,报错ORA-01555SQL语句如下:select ..... from SMMMMVIEW.POS_TTTTT cc, SMMMMVIEW.OOOOOIZATION org, SMMMMVIEW.TRRRTYPE ty, SMMMMVIEW.POS_...原创 2018-07-27 11:10:25 · 611 阅读 · 0 评论 -
一次执行计划突变的故障分析
一,现象描述2018年5月23日 20:30分XX系统出现超时现象,原本10-20分钟执行完成的过程持续了很长时间(3小时)。1)定位超时的会话及语句:通过自己开发的管控工具-快速问题定位模块,l定位到当前会话为975,l语句是一条UPDATE语句:update T5_CCCC_VVV c set c.cust_manager = (sele...原创 2018-07-27 16:24:59 · 1029 阅读 · 0 评论 -
bitmap index的优化案例
某系统存储过程执行不过去,通过查询长时间ACTIVE的SESSION定位到如下语句:UPDATE AAA_BBBBBBBBB_ALM T SET KEY_BS='1111111111' WHERE T.PARTITION_KEY = 'S00000014097' AND T.KEY_BS_BK = 'ASSET_NS-INB-DMD' and SUBSTR(ATTRIBUTE_...原创 2018-08-09 11:09:05 · 1189 阅读 · 0 评论 -
一条存在多处性能问题的SQL分析
背景:定制了一个脚本 排查数据库中具有潜在风险的SQL,显示下面这条SQL触发了多个风险 执行时间是33分钟SELECT T1.DATA_DT, T1.BRANCH_NO, T5.FINANCE_ORG_NO, DECODE(T2.CUST_TYP, '01', '1', '02', '0', NULL), SUBSTR(T1.KEY_...原创 2018-07-31 21:21:13 · 633 阅读 · 0 评论 -
使用sql_profile固定执行计划(脚本分享)
生产上经常遇到执行计划“突变”的问题,针对这种问题 常规的解决就是收集统计信息。如果相关表的segment很大,收集统计信息需要很长时间。在应急的情况下快速解决问题,只需要绑定这个SQL突变前的执行计划,即可!下面分享一个脚本 用于绑定执行计划:直接调用存储过程,传入的参数有三个 ORIGINAL_SQL_ID 需要固定执行计划的SQLMODIFIED_SQ...原创 2018-08-01 09:12:53 · 914 阅读 · 0 评论 -
关注业务-把优化做到极致
SELECT .. .. .. FROM AWP.TTT_TTT_TRANSACTION_2018 INCT LEFT JOIN AWP.TTT_TTT_TELM TELM ON INCT.TELLER_NO = TELM.TELLER_NO LEFT JOIN SJB_CD_01.TMP_TT_BAN_EDP ED1P ON INCT.TRAN_CODE = ED1...原创 2019-04-03 17:52:54 · 1234 阅读 · 0 评论