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