今天碰到一个性能问题,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,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, 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'
)
);
已用时间: 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