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

   今天处理开发同学提交的一个数据查询需求,看起来是一个很常规的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来限定,如果查询的数据缺失够多,走全表也不失为一种合理的方法。

时间: 2024-09-21 15:13:47

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

百倍性能的PL/SQL优化案例(r11笔记第13天)

我相信你是被百倍性能的字样吸引了,不过我所想侧重的是优化的思路,这个比优化技巧更重要,而结果嘛,其实我不希望说成是百倍提升,""自黑""一下.     有一个真实想法和大家讨论一下,就是一个SQL语句如果原本运行20秒,优化到了1秒,性能提升该说是20倍还是提高了95%.当然还见过一种说法,一条SQL语句每次运行20秒,每天运行100次,优化后每次运行1秒,运行还是100次,那么性能提升是说成优化累计时间为100*20-100=1990秒? 好了,我们来看看PL/S

闪回区报警引发的性能问题分析(r11笔记第11天)

自从有了Zabbix+Orabbix,很多监控都有了一种可控的方式,当然对于报警处理来说,报警是表象,很可能通过表象暴露出来的是一些更深层次的问题.这不又来一个,不看不知道,一看让自己着实吓了一跳. 首先是一个报警信息,可以看到是闪回区超过了报警的阈值,为了尽可能提前发现问题,我把阈值设置为了70%,和Oracle默认的80%有一些差别. ZABBIX-监控系统: ------------------------------------ 报警内容: archive_area_usage ----

Mysql中两个有用的sql性能分析语句

1> explain SQL,类似于Oracle中explain语句 例如:explain select * from nad_aditem; 2> select benchmark(count,sql);计算sql语句执行count次所花费的时间 例如: mysql> select benchmark(1000;Select * FROM AD;); +--------------+ | benchmark(1000;Select * FROM AD;) | +------------

Oracle中的PGA监控报警分析(r11笔记第97天)

最近接到一个数据库报警,让我颇有些意外,这是一个PGA相关的报警.听起来感觉是应用端的资源调用出了问题. 报警内容大体如下: 报警内容: PGA Alarm on alltest ------------------------------------报警级别: PROBLEM ------------------------------------监控项目: PGA:6118.6 这是一个12cR1的环境,是一套测试环境,确切的说是多套环境整合后的一套大的测试环境,里面含有近8个PDB,也就是

MySQL中insert语句没有响应的问题分析(r11笔记第21天)

 今天开发的一个同学问我一个MySQL的问题,说在测试数据库中执行一条Insert语句之后很久没有响应.我一看语句是一个很常规的insert into xxx values形式的语句.看起来有些不太合乎常理啊,我对这类问题立马来了兴趣,准备好好看看到底是什么原因.  向开发同学了解了环境之后,我登录到服务端,首先查看是否可能是磁盘空间不足导致的问题.结果df -h的结果显示,空间还绰绰有余. 使用show proceslist查看线程情况. 可以看到大量的线程是Waiting for table

Oracle数据库的SQL性能问题分析

在Oracle 10.2.0.4数据库中,有一个SQL执行缓慢,超过数分钟无结果,等待事件又是空闲的 SQL*Net message事件,最后只好强行中断. 这个SQL是一个普通的UPDATE语句,where子句中多张表 关联,关联的表都是临时表. update t_fund_product_info set is_valid = 'N' where prdt_id not in (select a.prdt_id from tmp_crm_DX_PRDT_FOR_INFO a, tmp_crm

SQL2005性能分析一些细节功能你是否有用到?(二)

原文:SQL2005性能分析一些细节功能你是否有用到?(二)        上一篇:SQL2005性能分析一些细节功能你是否有用到? 我简单的提到了些关于SQL性能分析最基本的一些方法,下面的文章我会陆续补充.前面提到了根据SQL的执行IO和执行计划来分析,还有一个特别重要的参数,就是SET STATISTICS TIME.        第一: SET STATISTICS TIME                定义:SET STATISTICS TIME (Transact-SQL)  显

SQL Server-聚焦INNER JOIN AND IN性能分析(十四)

原文:SQL Server-聚焦INNER JOIN AND IN性能分析(十四) 前言 本节我们来讲讲联接综合知识,我们在大多教程或理论书上都在讲用哪好,哪个性能不如哪个的性能,但是真正讲到问题的实质却不是太多,所以才有了本系列每一篇的篇幅不是太多,但是肯定是我用心去查找许多资料而写出,简短的内容,深入的理解,Always to review the basics. 初次探讨INNER JOIN和IN性能分析 接下来我们看第一篇联接综合知识讲解INNER JOIN和IN的比较分析,我们通过创建

SQL Server-聚焦NOT EXISTS AND NOT IN性能分析(十五)

前言 上一节我们分析了INNER JOIN和IN,对于不同场景其性能是不一样的,本节我们接着分析NOT EXISTS和NOT IN,简短的内容,深入的理解,Always to review the basics. 初步探讨NOT EXISTS和NOT IN NOT EXISTS和NOT IN有很大的不同,尤其是对NULL的处理,为何这样说,当子查询中有NULL时,此时NOT IN不会返回任何行,下面我们来看下简单的示例. USE TSQL2012 GO WITH table1 AS ( SELE