怎么性能相差这么大?

显示全部楼层 · 2005-10-30 17:05:33

Oracle 9.2.0.6+Windows2003
一.下面的语句要运行很长时间,(没有等它结束就中断)看下面的执行计划:
SQL> explain plan for
2select count(MY_STUDY_ID)
3from study
4where facility_id in (select my_facility_id from delete_facility)
5 or context_id =1
6 or source_id in (select my_source_id from delete_source)
7 or patient_id in (select a.patient_id
8 from (select patient_id, count(1) from patient_public_id group by patient_id having count(1)=1) a,
9
patient_public_id b
10where a.patient_id=b.patient_id and b.context_id=1);
Explained.
Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
| Id| Operation
|Name
| Rows| Bytes | Cost|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 |20 | 557 |
| 1 |SORT AGGREGATE |
| 1 |20 | |
| 2 | FILTER
|
| | | |
| 3 |TABLE ACCESS FULL| STUDY
| 323K|6317K| 557 |
| 4 |INDEX RANGE SCAN | TEMP_DEL_FACILITY | 1 |13 | 1 |
| 5 |INDEX RANGE SCAN | TEMP_DEL_SOURCE | 1 |13 | 1 |
| 6 |FILTER
|
| | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
| 7 | SORT GROUP BY |
| 1 |20 |1277 |
| 8 |HASH JOIN
|
| 368K|7205K| 528 |
| 9 | INDEX FAST FULL SCAN| PK_PATIENT_PUBLIC_ID| 215K|3159K|33 |
|10 | INDEX FAST FULL SCAN| PK_PATIENT_PUBLIC_ID| 515K|2519K|33 |
-----------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version
18 rows selected.
Elapsed: 00:00:00.00
SQL> drop table tmp_patient_id;
Table dropped.
二.把最后一个条件改成生成一个表.
Elapsed: 00:00:00.00
SQL> create table tmp_patient_id
2as select a.patient_id
3 from (select patient_id, count(1) from patient_public_id group by patient_id having count(1)=1) a,
4
patient_public_id b
5where a.patient_id=b.patient_id and b.context_id=1;
Table created.
Elapsed: 00:00:01.05
三.再执行同样的逻辑,执行计划也一样,为什么快很多.(2分钟)
SQL> explain plan for
2select count(MY_STUDY_ID)
3from study
4where facility_id in (select my_facility_id from delete_facility)
5 or context_id=1
6 or source_id in (select my_source_id from delete_source)
7 or patient_id in (select patient_id from tmp_patient_id);
Explained.
Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id| Operation
|Name
| Rows| Bytes | Cost|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 |20 | 557 |
| 1 |SORT AGGREGATE|
| 1 |20 | |
| 2 | FILTER
|
| | | |
| 3 |TABLE ACCESS FULL | STUDY
| 323K|6317K| 557 |
| 4 |INDEX RANGE SCAN| TEMP_DEL_FACILITY| 1 |13 | 1 |
| 5 |INDEX RANGE SCAN| TEMP_DEL_SOURCE| 1 |13 | 1 |
| 6 |TABLE ACCESS FULL | TMP_PATIENT_ID | 5 |65 | 2 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version
14 rows selected.
Elapsed: 00:00:00.01
SQL> select count(MY_STUDY_ID)
2from study
3where facility_id in (select my_facility_id from delete_facility)
4 or context_id=1
5 or source_id in (select my_source_id from delete_source)
6 or patient_id in (select patient_id from tmp_patient_id);
COUNT(MY_STUDY_ID)
------------------

255783
Elapsed: 00:01:49.07
问题:
同样的执行计划,只是把最后一个条件换成产生一个表格,从表里读.为什么速度差很多?
谢谢!

千问 · 2005-10-30 17:05:33

tmp_patient_id有多少笔记录?

千问 · 2005-10-30 17:05:33

你把or 用union all 替换了会更快

千问 · 2005-10-30 17:05:33

二.把最后一个条件改成生成一个表.
Elapsed: 00:00:00.00
SQL> create table tmp_patient_id
2as select a.patient_id
3 from (select patient_id, count(1) from patient_public_id group by patient_id having count(1)=1) a,
4
patient_public_id b
5where a.patient_id=b.patient_id and b.context_id=1;

我觉得你所有的时间都是浪费在这个语句上了， lz 可以看看单独执行
select a.patient_id
from (select patient_id, count(1) from patient_public_id group by patient_id having count(1)=1) a,

patient_public_id b
where a.patient_id=b.patient_id and b.context_id=1;
这个语句的执行计划，看它花费了多长时间吧！！优化这个语句就应该ok了

千问 · 2005-10-30 17:05:33

谢谢大家.
1.tmp_patient_id有3388条记录.
2.其实,我把最后一个条件挑出来单独执行,也很快的.当我把最后的条件转换成表也就几十秒(不会多于2分钟).
3.最后条件中的patient_public_id 也就70万条记录.
[ 本帖最后由 supersea 于 2008-4-10 11:34 编辑 ]

千问 · 2005-10-30 17:05:33

| 6 |FILTER
|
| | | |
| 7 | SORT GROUP BY |
| 1 |20 |1277 |
| 8 |HASH JOIN
|
| 368K|7205K| 528 |
| 9 | INDEX FAST FULL SCAN| PK_PATIENT_PUBLIC_ID| 215K|3159K|33 |
|10 | INDEX FAST FULL SCAN| PK_PATIENT_PUBLIC_ID| 515K|2519K|33 |
-----------------------------------------------------------------------------------
这一步操作其实是nest loop, 用前面的查询中间结果做为驱动集, 执行n次
select a.patient_id
from (select patient_id, count(1) from patient_public_id group by patient_id having count(1)=1) a,

patient_public_id b
where a.patient_id=b.patient_id and b.context_id=1;
而每次执行该语句的逻辑读都很大,我为什么这么说呢, 因为
"2.其实,我把最后一个条件挑出来单独执行,也很快的.当我把最后的条件转换成表也就几十秒(不会多于2分钟)."
一条执行需要几十秒的SQL, 逻辑读应该在几百万左右.
所以整个SQL执行就非常慢.

而修改为临时表之后,
---------------------------------------------------------------------------
| Id| Operation
|Name
| Rows| Bytes | Cost|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 |20 | 557 |
| 1 |SORT AGGREGATE|
| 1 |20 | |
| 2 | FILTER
|
| | | |
| 3 |TABLE ACCESS FULL | STUDY
| 323K|6317K| 557 |
| 4 |INDEX RANGE SCAN| TEMP_DEL_FACILITY| 1 |13 | 1 |
| 5 |INDEX RANGE SCAN| TEMP_DEL_SOURCE| 1 |13 | 1 |
| 6 |TABLE ACCESS FULL | TMP_PATIENT_ID | 5 |65 | 2 |
---------------------------------------------------------------------------
虽然还是执行nest loop, 但是每次对临时表TMP_PATIENT_ID访问的逻辑读很小(因为只有几千笔记录, 估计单次访问的逻辑读就几十),所以速度提高很快.
我想, 如果能将原来的执行计划中的filter都改为hash, 应该更快, 不过由于你的各个谓词条件都是or连接的, 我也拿不准能不能改成hash.
[ 本帖最后由 mihawk 于 2008-4-10 14:00 编辑 ]