首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >相差数十倍的SQL性能分析(r11笔记第98天)

相差数十倍的SQL性能分析(r11笔记第98天)

作者头像
jeanron100
发布于 2018-03-21 06:58:29
发布于 2018-03-21 06:58:29
6080
举报

今天处理开发同学提交的一个数据查询需求,看起来是一个很常规的SQL,但是有一点不同的是,他们提供了两份文件,一份是一个id列表,大概有3000多个id值,另外一个份是个SQL文件。

之前也处理过几十万,上百万id值的情况,使得我原来开发中对于变动的敏感性依旧存在,所以我采用了另外一种灵活的方式,即外部表,外部表是数据库外的数据存在,在数据库依旧可以读取访问。

CREATE TABLE test_cn (cn varchar2(50) ) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE ) LOCATION ('data.txt') );

而在这个基础上运行的SQL语句也很简短。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ;

这样一来就达到了一种一劳永逸的效果,那就是后期如果开发同学继续提供另外一个查询,只要提供了id值,不管是多大,我都能轻松处理,不管是哪个业务的SQL我都能灵活套用。

但是问题来了,上面的SQL语句执行的时候,速度让我很不满意,因为持续了近2分钟。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ; Elapsed: 00:01:59.39

为什么很不满意,是因为这个“表”中的主键是基于字段uin的,竟然查询速度这么慢,实在不给面子。

INDEX_NAME COLUMN_NAME INDEX_TYPE UNIQUENES ------------------------------ ------------ --------------------- PK_USER_CERTIFICATION_INFO1 UIN NORMAL UNIQUE

对于这类问题我还是有不小的兴趣,毕竟能够顺手优化优化也是不错的体验。我尝试加了rownum,尽管这样不够严谨,但是输出结果和时间还是和开始的差不多。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) and rownum<=4000 ; Elapsed: 00:01:59.15

可见Oracle优化器早就看穿了我的心思,我怎么能够耍点小聪明呢。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn where rownum<=3200) ; Elapsed: 00:00:00.29

这样一个查询就能够达到非一般的速度。

这是为什么呢。要想得到一些更为细致的问题,那我们就开启trace来诊断一下,怎么诊断呢,一种比较自然的思路那就是10053事件。

10053事件诊断SQL

开启10053事件的步骤如下:

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'; explain plan for select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ; ALTER SESSION SET EVENTS '10053 trace name context off';

其中能够看到不少细节的信息,我摘取出一小段来。

FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0) FPD: Current where clause predicates "TEST_CERTIFICATION_INFO"."UIN"=ANY (SELECT "TEST_CN"."CN" FROM "TEST"."TEST_CN" "TEST_CN") try to generate transitive predicate from check constraints for query block SEL$1 (#0) finally: "TEST_CERTIFICATION_INFO"."UIN"=ANY (SELECT "TEST_CN"."CN" FROM "TEST"."TEST_CN" "TEST_CN") 最后经过查询转换,得到的最终语句如下:

Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "TEST_CERTIFICATION_INFO"."UIN" "UIN",..... FROM "TEST"."TEST_CN" "TEST_CN", ( (SELECT "ACC00_TEST_CERTIFICATION_INFO"."UIN" ... "ACC35_TEST_CERTIFICATION_INFO")) "TEST_CERTIFICATION_INFO" WHERE "TEST_CERTIFICATION_INFO"."UIN"=TO_NUMBER("TEST_CN"."CN") kkoqbc: optimizing query block SEL$2 (#14)

可能看到这里就有些懵了,这个test_certification_info其实是个视图,里面包含有12个物化视图。其实这个简单的查询就好比是12个物化视图和一个外部表的关联查询。

那么为什么子查询使用了rownum之后,效率大大提升呢。这个可以从日志中看出端倪,我们可以清楚的看到优化器预估的时候这个外部表的数据条数是179950,和现在的3000多条想去甚远。

Table Stats:: Table: TEST_CN Alias: TEST_CN #Rows: 179950 #Blks: 462 AvgRowLen: 21.00 ChainCnt: 0.00 Access path analysis for TEST_CN

那么为什么优化器认为是179950条数据呢,这个和统计信息还是密切相关,尽管外部表不占用数据文件的存储,但是依然还是有一个基本的统计信息。

SQL> select num_rows from dba_tables where table_name='TEST_CN'; NUM_ROWS ---------- 179950

可能有很多同学说,那就收集统计信息,应该能够解决这个问题。SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',TABNAME=>'TEST_CN'); PL/SQL procedure successfully completed.

然后再次尝试,竟然还是很慢,查看执行计划发现里面始终是走了全表扫描。

这个问题的一种快速解决方式就是使用子查询中的rownum来限定,如果查询的数据缺失够多,走全表也不失为一种合理的方法。

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

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

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

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

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
物化视图相关的性能改进 (r7笔记第58天)
今天早上开发的一个同事找到我说他早上做了一个统计查询,但是感觉速度很慢,已经过了一个小时了还没有反应。想让我看看是什么情况。 我通过v$session查到有一个会话确实已经持续了近一个小时,查看sql语句是一个create table select * from xxx这样格式的语句。也就是通过关联查询创建出一个所谓的临时表来。 语句如下: create table APP_BI_ENCRYPT_QUERY.t_result_1312 as select t1.SECURITY_PHONE as MOB
jeanron100
2018/03/16
6530
【DB笔试面试677】在Oracle中,对于一个NUMBER(1)的列,若WHERE条件是大于3和大于等于4,这二者是否等价?
在Oracle中,对于一个NUMBER(1)的列,如果查询中的WHERE条件分别是大于3和大于等于4,那么这二者是否等价?
AiDBA宝典
2019/11/05
2.6K0
【DB笔试面试677】在Oracle中,对于一个NUMBER(1)的列,若WHERE条件是大于3和大于等于4,这二者是否等价?
通过IP定位区域的SQL优化思路(r10笔记第10天)
昨天中午吃饭的时候,突然手机收到一条报警信息,提示数据库的负载突然提高了。对于一个高配,稳定,核心的系统来说,出现这么一个报警会立刻引起关注。 连接到环境之后,发现在问题发生时间段快照中资源消
jeanron100
2018/03/19
6860
通过IP定位区域的SQL优化思路(r10笔记第10天)
Oracle和MySQL竟然可以这么写这样的SQL?(r12笔记第99天)
今天看到Franck Pachot‏ 发了一个Twitter,意思是Oracle里的SQL还能这么写。猛一看确实让人有些意外。 禁不住诱惑,自己也尝试了一番。我现在12cR2的环境中测试了一下。 Or
jeanron100
2018/03/21
8390
Oracle和MySQL竟然可以这么写这样的SQL?(r12笔记第99天)
一条SQL语句的执行计划变化探究(r10笔记第3天)
最近有个同事碰到一个问题,想让我给点思路。我大体了解了一下,是一个系统目前在做压力测试,但是经业务反馈发现某个环节的处理时间有些长,排查了一圈,最后这件事情就落在了DB这边,希望DB能够给点意见,是否
jeanron100
2018/03/19
6880
一条SQL语句的执行计划变化探究(r10笔记第3天)
通过外部表改进一个繁琐的大查询 (r8笔记第32天)
今天处理了一个比较有意思的案例,说是有意思,因为涉及多个部门,但是哪个部门似乎都不愿意接。最后还是用了一些巧力,化干戈为玉帛。 问题的背景是这样的,业务部门需要做一个大查询,他们目前只拿到了部分账号的一个id字段的值,需要匹配得到一个类似手机号的字段值,开发部门提供了对应 的sql语句,会关联两张表来匹配得到对的数据,然后反馈到DBA这里的时候就是最终的sql语句了,DBA查询得到数据,然后反馈给业务部门。大体的流 程是这样的。 但是现在的问题是,业务部门需要提供的id有60多万个,开发部门看到这个
jeanron100
2018/03/19
6570
物化视图中的统计信息导致的查询问题分析和修复 (r7笔记第47天)
今天开发的同事下午反馈给我一个问题,说有操作直接卡住了,听这个描述,感觉很可能是查询慢了。 于是连接到环境中,查看了一下正在执行的sql语句情况,发现下面的语句已经执行了一段时间。 语句类似下面的形式: select t1.SECURITY_PHONE as MOBILE_PHONE, t1.SECURITY_EMAIL as OTHER_EMAIL, t2.* from accstat.ACCOUNT_DELTA t1, bidata.TMP_CN06 t2 where t1.CN_MASTER =
jeanron100
2018/03/16
1.2K0
物化视图中的统计信息导致的查询问题分析和修复 (r7笔记第47天)
Oracle SQL性能分析之10053事件
优化器生成正确执行计划的前提条件是要有正确的统计信息,不准确的统计信息往往会导致错误的执行计划。当通过SQL和基数推断出的执行计划和实际执行计划不同时,就可以借助10053事件。10053事件是用来诊断优化器如何估算成本和选择执行计划的,用它产生的trace文件提供了Oracle如何选择执行计划,为什么会得到这样的执行计划信息。和10046事件类似,它主要用于特殊情况下的分析和诊断。
星哥玩云
2022/08/17
7450
关于consistent gets(r5笔记第12天)
在sql调优的时候,一个关键指标就是consistent gets,如果这个指标很低,一般认为sql语句执行还是很高效的,反之效率会很低。但是这个指标我们知之甚少,对于这个指标的计算方式我们也是懵懵懂懂。对于逻辑读来说,一般都是基于Logical Reads= Consistent Gets + DB Block Gets 如果我们知道logical reads是1000,我们可能错误地认为查询读取了1000*8k(约为8M) 看了博客https://viveklsharma.wordpress.com/2
jeanron100
2018/03/15
8220
通过Snapshot Standby来精确评估SQL性能 (r9笔记第73天)
最近处理了一个需求,比较紧急,映射到数据库层面是需要更新17万id的值,听起来是不少,根据数据架构进行了分析,发现目前是做了分库分表的方式,所以这17万的id在这些分库中都可能存在,而跨部门的数据交付中,也没有做分库的区分,所以拿到的id是一个笼统的概念,即哪个id对应哪个分库没有事先过滤甄别,这个工作就自然而然的下落到了DBA头上。 分库分表的方式,目前有12个分库,是以十二个用户的形式体现出来的,所以如果要整体更新,那么每个分库都需要更新一遍,有匹配的数据就更新,否则忽略。如此一来,更新的数据规
jeanron100
2018/03/19
6220
一条简单的sql在11g和12c中的不同(r5笔记第2天)
今天在查看awr报告的时候,有一句很简单的sql语句引起了我的注意,因为它排在SQL Order by Reads的第2位。 Physical ReadsExecutionsReads per Exec%TotalElapsed Time (s)%CPU%IOSQL IdSQL Module13,092,7000 9.47294.852.741.066src3hcd9mpt3T.O.A.D. SQL Textselect * from mo1_memo where entity_id in (1188930
jeanron100
2018/03/15
1.1K0
一个SQL性能问题的优化探索(一)(r11笔记第33天)
今天同事问我一个问题,看起来比较常规,但是仔细分析了一圈,发现实在是有些晕,我隐隐感觉这是一个bug,但是有感觉问题还有很多需要确认和理解的细节。 同事说有个10gR2的数据库SQL语句有性能
jeanron100
2018/03/21
7720
一个SQL性能问题的优化探索(一)(r11笔记第33天)
关于索引和空值的讨论(r3笔记第80天)
在日常的工作中,空值总是有特殊的身份,对于它的处理有时候也是比较纠结。 有时候创建索引的时候会因为空值出现一些奇怪的结果。 有时候一个简单的查询因为空值却走不了索引。 有时候却因为空值而能走索引。 我们来简单的模拟一下这些问题。 首先创建一个空表,注意对于id列我们是加了not null的约束的。 SQL> create table index_test(id number not null,name varchar2(30) ) ; Table created. 我们创建一个唯一性索引,包含了id
jeanron100
2018/03/15
8000
一个SQL性能问题的优化探索(二)(r11笔记第38天)
继续前几天的一个案例一个SQL性能问题的优化探索(一)(r11笔记第33天) 如下的SQL语句存在索引字段CARD_NO,但是执行的时候却走了全表扫描,因为这是一个核心表,数据量很大,导致数据库负载很高。 SQL_FULLTEXT ---------------------------------------------------------------------------------------------------- SELECT ID,CN,CARD_NO,TO_CHAR(CHARGE_DAT
jeanron100
2018/03/21
8041
一个SQL性能问题的优化探索(二)(r11笔记第38天)
海量数据切分抽取的实践场景(r11笔记第43天)
如果一个大表要抽取数据导出成csv文件,我们有什么策略,如何改进。 一、问题背景 今天开发的同学找到我,他们需要做一个数据统计分析,需要我提供一些支持,把一个统计库中的大表数据导出成文本提供给他们
jeanron100
2018/03/21
8040
海量数据切分抽取的实践场景(r11笔记第43天)
记一次数据同步需求的改进(二) (r7笔记第5天)
在之前写过记一次数据同步需求的改进(一) (r7笔记第2天)之后,就开始着手对这个需求进行实践。 所谓实践出真知,在实际做的时候才发现可能计划的再好,做的时候还真不是那么回事。 在之前的邮件中已经确认目标库是一个统计分析库,首先拿到这个环境,先调查一番,发现了一个奇怪的现象。 查看这个库的归档情况的时候发现这个库每天的凌晨开始要切换20多次日志。 Redo Switch times per hour STATDB1
jeanron100
2018/03/16
9340
记一次数据同步需求的改进(二) (r7笔记第5天)
基于DB time的调优分析 (r6笔记第79天)
继昨天使用DB time能够快速灵活的定位sql语句之后,发现分析问题更快捷,高效了。今天就牛刀小试,把一个数据库从500%的负载调到不到100%的负载。前提是确实有可提升可改进的空间。 首先查看了在快照57611的时间段里DB time很快,也收到了zabbix的邮件通知, ZABBIX-监控系统: ------------------------------------ 报警内容: DB time is too high ------------------------------------ 报警
jeanron100
2018/03/16
7750
基于DB time的调优分析 (r6笔记第79天)
聚簇因子和执行计划的联系(r3笔记第90天)
在平时的工作中,可能会碰到一种很奇怪的问题,本来在生产环境中有些sql语句执行没有问题,一个很普通的查询预期走了索引扫面,但是拷贝数据到其它环境之后,就发现却走了全表扫描。 或者情况相反,本来出现问题的查询走了全表扫描,我们尝试在测试环境中浮现,但是测试环境中在相同的数据量的情况下,查询却又走了索引扫描,问题无法复现了。 出现这种情况的原因比较复杂,涉及很多的原因,其中一个很重要的原因就是聚簇因子的导致的。 聚簇因子是一个与索引相关的统计信息,它通过查看表中的数据块来进行计算得到。 对于这个问题,可能直接说
jeanron100
2018/03/15
6200
备库报警邮件的分析案例(三)(r7笔记第16天)
继前两篇分析了一个看似非常普通的报警邮件,结果在分析问题的时候八面玲珑,相关因素都给分析了一下,没想到还真是有不小的收获。 前两篇地址: 备库报警邮件的分析案例(二) (r7笔记第15天)、备库报警邮件的分析案例(一) (r7笔记第14天) 最后通过手工定位监控的方式终于把罪魁祸首揪了出来,为什么在备库使用ash无果,因为还是10g的库,还没有这个特性,在11g中才可以。这个也算是在10g中的一个监控盲点吧。 最后得到的语句是下面这样的形式每天凌晨都会在备库查询一次,资源消耗极大,目前临时表空间为98G都不
jeanron100
2018/03/16
6240
巧用rowid简化sql查询(r2笔记47天)
生产系统中有一条sql语句,目前执行的时间有点长了,而且看起来有些臃肿,客户问能不能改进一下。得到的sql语句如下: SELECT COUNT(1) FROM ( SELECT /*+ leading (payment_temp_table payment PAYMENT_DETAILS account memo) use_nl (payment_temp_table ar1_payment PAYMENT_DETAILS account memo) index (payment p
jeanron100
2018/03/14
6960
推荐阅读
相关推荐
物化视图相关的性能改进 (r7笔记第58天)
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档