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