转载

相差数十倍的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来限定，如果查询的数据缺失够多，走全表也不失为一种合理的方法。

正文到此结束