今天碰到一个性能问题，sql执行60多s，改后2s，把这个案例写成一个例子：
drop table t1 purge;
drop table t2 purge;drop table t3 purge;
create table t1 as select object_id,object_name,object_type from dba_objects;
create table t2 as select object_id,object_name from user_objects;
create table t3 as select object_id,object_name from user_objects where rownum <=1000;
create index ind_t1_object_id on t1(object_id);
create index ind_t2_object_id on t2(object_id);
create index ind_t3_object_id on t3(object_id);
exec dbms_stats.gather_table_stats(user,'t1');
exec dbms_stats.gather_table_stats(user,'t2');
exec dbms_stats.gather_table_stats(user,'t3');
alter session set statistics_level=all;
select count(1)
from t1
where t1.object_type = 'TABLE'
or exists (select 1 from t2 where t1.object_id = t2.object_id)
or exists (select 1 from t3 where t1.object_id = t3.object_id);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.13 | 184K| 60 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.13 | 184K| 60 |
|* 2 | FILTER | | 1 | | 31507 |00:00:01.13 | 184K| 60 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 108K| 108K|00:00:00.20 | 652 | 0 |
|* 4 | INDEX RANGE SCAN | IND_T2_OBJECT_ID | 99288 | 1 | 22590 |00:00:00.37 | 106K| 57 |
|* 5 | INDEX RANGE SCAN | IND_T3_OBJECT_ID | 76698 | 1 | 0 |00:00:00.23 | 77710 | 3 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("T1"."OBJECT_TYPE"='TABLE' OR IS NOT NULL OR IS NOT NULL))
4 - access("T2"."OBJECT_ID"=:B1)
5 - access("T3"."OBJECT_ID"=:B1)
--从这里可以看到这里的filter其实相当于三重循环，循环次数这么多，当然慢。如果优化，就是降低次数，如下：
select count(1) from (select t1.* from t1 where t1.object_type = 'TABLE' union all (select t1.* from t1,t2 where t1.object_id = t2.object_id and t1.object_type <> 'TABLE' union select t1.* from t1,t3 where t1.object_id = t3.object_id and t1.object_type <> 'TABLE' ) ); select * from table(dbms_xplan.display_cursor(null,'allstats last')); ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.33 | 2027 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.33 | 2027 | | | | | 2 | VIEW | | 1 | 29274 | 31507 |00:00:00.33 | 2027 | | | | | 3 | UNION-ALL | | 1 | | 31507 |00:00:00.32 | 2027 | | | | |* 4 | TABLE ACCESS FULL | T1 | 1 | 2303 | 8917 |00:00:00.01 | 652 | | | | | 5 | SORT UNIQUE | | 1 | 29274 | 22590 |00:00:00.29 | 1375 | 2250K| 704K| 1999K (0)| | 6 | UNION-ALL | | 1 | | 23456 |00:00:00.24 | 1375 | | | | |* 7 | HASH JOIN | | 1 | 25971 | 22590 |00:00:00.13 | 716 | 1815K| 1815K| 2511K (0)| | 8 | INDEX FAST FULL SCAN| IND_T2_OBJECT_ID | 1 | 26007 | 25980 |00:00:00.01 | 64 | | | | |* 9 | TABLE ACCESS FULL | T1 | 1 | 105K| 99322 |00:00:00.04 | 652 | | | | |* 10 | HASH JOIN | | 1 | 1000 | 866 |00:00:00.09 | 659 | 1452K| 1452K| 1488K (0)| | 11 | INDEX FAST FULL SCAN| IND_T3_OBJECT_ID | 1 | 1000 | 1000 |00:00:00.01 | 7 | | | | |* 12 | TABLE ACCESS FULL | T1 | 1 | 105K| 99322 |00:00:00.03 | 652 | | | | ------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("T1"."OBJECT_TYPE"='TABLE') 7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 9 - filter("T1"."OBJECT_TYPE"<>'TABLE') 10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID") 12 - filter("T1"."OBJECT_TYPE"<>'TABLE') set autotrace traceonly select count(1) from t1 where t1.object_type = 'TABLE' or exists (select 1 from t2 where t1.object_id = t2.object_id) or exists (select 1 from t3 where t1.object_id = t3.object_id); 已用时间: 00: 00: 01.27 执行计划 ---------------------------------------------------------- Plan hash value: 615550045 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 15 | 183 (1)| 00:00:03 | | 1 | SORT AGGREGATE | | 1 | 15 | | | |* 2 | FILTER | | | | | | | 3 | TABLE ACCESS FULL| T1 | 108K| 1585K| 183 (1)| 00:00:03 | |* 4 | INDEX RANGE SCAN | IND_T2_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IND_T3_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T1"."OBJECT_TYPE"='TABLE' OR EXISTS (SELECT 0 FROM "T2" "T2" WHERE "T2"."OBJECT_ID"=:B1) OR EXISTS (SELECT 0 FROM "T3" "T3" WHERE "T3"."OBJECT_ID"=:B2)) 4 - access("T2"."OBJECT_ID"=:B1) 5 - access("T3"."OBJECT_ID"=:B1) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 184940 consistent gets 0 physical reads 0 redo size 349 bytes sent via sql*Net to client 472 bytes received via sql*Net from client 2 sql*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed select count(1) from (select t1.* from t1 where t1.object_type = 'TABLE' union all (select t1.* from t1,t3 where t1.object_id = t3.object_id and t1.object_type <> 'TABLE' ) ); 已用时间: 00: 00: 00.42 执行计划 ---------------------------------------------------------- Plan hash value: 109299234 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 571 (1)| 00:00:07 | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | VIEW | | 29274 | | 571 (1)| 00:00:07 | | 3 | UNION-ALL | | | | | | |* 4 | TABLE ACCESS FULL | T1 | 2303 | 87514 | 183 (1)| 00:00:03 | | 5 | SORT UNIQUE | | 29274 | 1218K| 571 (69)| 00:00:07 | | 6 | UNION-ALL | | | | | | |* 7 | HASH JOIN | | 25971 | 1090K| 201 (1)| 00:00:03 | | 8 | INDEX FAST FULL SCAN| IND_T2_OBJECT_ID | 26007 | 126K| 17 (0)| 00:00:01 | |* 9 | TABLE ACCESS FULL | T1 | 105K| 3931K| 183 (1)| 00:00:03 | |* 10 | HASH JOIN | | 1000 | 43000 | 187 (2)| 00:00:03 | | 11 | INDEX FAST FULL SCAN| IND_T3_OBJECT_ID | 1000 | 5000 | 3 (0)| 00:00:01 | |* 12 | TABLE ACCESS FULL | T1 | 105K| 3931K| 183 (1)| 00:00:03 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("T1"."OBJECT_TYPE"='TABLE') 7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 9 - filter("T1"."OBJECT_TYPE"<>'TABLE') 10 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID") 12 - filter("T1"."OBJECT_TYPE"<>'TABLE') 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 2027 consistent gets 0 physical reads 0 redo size 349 bytes sent via sql*Net to client 472 bytes received via sql*Net from client 2 sql*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed