Oracle SQL 查询执行时间过长，例如 60 分钟
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25328801/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-19 02:28:05 来源:igfitidea点击:
Oracle SQL query taking too long like 60 minutes to execute
提问by jimagic
I have one query that taking too long, that is 60 minutes. I am programmer but i am not quite sure if all the table are indexes out.
我有一个查询耗时太长，即 60 分钟。我是程序员，但我不太确定是否所有表都已索引。
here is the query, any table that ends with _V is view:
这是查询，任何以 _V 结尾的表都是视图：
SELECT prod_eff.facility
, prod_eff.product
, (SELECT MIN (cbv1.bucket_header)
FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
WHERE cbv1.version_id = 1
AND cbv1.min_datetime >= prod_eff.EFF_START_DATETIME
) min_eff
, (SELECT MAX (cbv1.bucket_header)
FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
WHERE cbv1.version_id = 1
AND cbv1.min_datetime < prod_eff.EFF_END_DATETIME
) max_eff
, 1 valid
FROM (
SELECT pf.product
, pf.facility
, pf.eff_start_datetime
, pf.eff_end_datetime
FROM AMD_OARS.BOM_PRODUCT_FACILITY pf
, AMD_OARS.MASTER_FACILITY f
WHERE pf.version_id = 114847
AND pf.facility = f.facility
AND f.facility != 'NONE'
AND f.validated = 1
) prod_eff
, AMD_OARS.TEMP_SELECTED_PRODUCT tsp
WHERE tsp.product = prod_eff.product
AND (prod_eff.EFF_START_DATETIME BETWEEN
to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND
to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3') OR
prod_eff.EFF_END_DATETIME BETWEEN
to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND
to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3')
)
ORDER BY 1, 2, 3, 4 ;
This is the query I use to find out if some of the table are index or not.
这是我用来确定某些表是否为索引的查询。
select table_name
from dba_tables
where (owner, table_name) not in (select table_owner, table_name from dba_indexes)
and table_name = 'TEMP_SELECTED_PRODUCT';
I am sorry I am newbie in database thing. I only know basic thing like sql join and writing basic queries. Any help would be appreciate. I am using Oracle SQL Developer.
对不起，我是数据库方面的新手。我只知道基本的东西，比如 sql join 和编写基本查询。任何帮助将不胜感激。我正在使用 Oracle SQL Developer。
can anybody please guide me if I am doing anything wrong? I would appreciate it.
如果我做错了什么，有人可以指导我吗？我会很感激。
UPDATE: I saw this videoon how to execute 'explain plan for' and here is what i got result
更新：我看到了这个关于如何执行“解释计划”的视频，这是我得到的结果
Plan hash value: 1792060973
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 4 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | TEMP_CURRENT_BUCKETS | 1 | 66 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TCB_PK | 1 | | 1 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 66 | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | TEMP_CURRENT_BUCKETS | 1 | 66 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | TCB_PK | 1 | | 1 (0)| 00:00:01 |
| 7 | SORT ORDER BY | | 1 | 78 | 4 (25)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 78 | 3 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 69 | 2 (0)| 00:00:01 |
| 10 | INDEX FULL SCAN | TSP_PK | 1 | 18 | 1 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| BOM_PRODUCT_FACILITY | 1 | 51 | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | BPF_PK | 1 | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | MASTER_FACILITY | 1 | 9 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | MF_F_PK | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CB"."MIN_DATETIME">=:B1)
3 - access("CB"."VERSION_ID"=1)
5 - filter("CB"."MIN_DATETIME"<:B1)
6 - access("CB"."VERSION_ID"=1)
11 - filter(INTERNAL_FUNCTION("PF"."EFF_START_DATETIME")>=TIMESTAMP' 2014-07-27
00:00:01.000000000' AND INTERNAL_FUNCTION("PF"."EFF_START_DATETIME")<=TIMESTAMP' 2015-12-20
00:00:00.000000000' OR INTERNAL_FUNCTION("PF"."EFF_END_DATETIME")>=TIMESTAMP' 2014-07-27
00:00:01.000000000' AND INTERNAL_FUNCTION("PF"."EFF_END_DATETIME")<=TIMESTAMP' 2015-12-20
00:00:00.000000000')
12 - access("PF"."VERSION_ID"=114847 AND "TSP"."PRODUCT"="PF"."PRODUCT")
filter("TSP"."PRODUCT"="PF"."PRODUCT" AND "PF"."FACILITY"<>'NONE')
13 - filter("F"."VALIDATED"=1)
14 - access("PF"."FACILITY"="F"."FACILITY")
filter("F"."FACILITY"<>'NONE')
回答by Greg
SELECT prod_eff.facility
, prod_eff.product
, (SELECT MIN (cbv1.bucket_header)
FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
WHERE cbv1.version_id = 1
AND cbv1.min_datetime >= prod_eff.EFF_START_DATETIME
) min_eff
, (SELECT MAX (cbv1.bucket_header)
FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
WHERE cbv1.version_id = 1
AND cbv1.min_datetime < prod_eff.EFF_END_DATETIME
) max_eff
, 1 valid
FROM (
WITH prod_fac AS (
SELECT pf.product
, pf.facility
, pf.eff_start_datetime
, pf.eff_end_datetime
FROM AMD_OARS.BOM_PRODUCT_FACILITY pf
WHERE pf.version_id = 114847
AND (pf.EFF_START_DATETIME BETWEEN
to_timestamp('07/27/2014 00:00:01.000',
'mm/dd/yyyy hh24:mi:ss.ff3') AND
to_timestamp('12/20/2015 00:00:00.000',
'mm/dd/yyyy hh24:mi:ss.ff3') OR
pf.EFF_END_DATETIME BETWEEN
to_timestamp('07/27/2014 00:00:01.000',
'mm/dd/yyyy hh24:mi:ss.ff3') AND
to_timestamp('12/20/2015 00:00:00.000',
'mm/dd/yyyy hh24:mi:ss.ff3')
)
)
SELECT pf2.product
, pf2.facility
, pf2.eff_start_datetime
, pf2.eff_end_datetime
FROM prod_fac pf2, AMD_OARS.MASTER_FACILITY f
AND pf2.facility = f.facility
AND f.facility != 'NONE'
AND f.validated = 1
) prod_eff, AMD_OARS.TEMP_SELECTED_PRODUCT tsp
WHERE prod_eff.product = tsp.product
ORDER BY 1, 2, 3, 4 ;
- I'd try and get rid of as much data as I could before the final join to your tsp table. The date range checks move to the inline view.
- I don't like how you flipped the table order between the from class and the order in your where clause.
tsp.product = prod_eff.product= >prod_eff.product = tsp.product - I like how you are using the between clause with the
to_timestampfunction. Many people try totrunc()the time and that blows the index use. - If you can somehow remove the >= and < in your two scalar queries that would be great. The optimizer makes horrible guesses for less than or greater than. Can you somehow work in a between clause with 4000 years before or 4000 years after?
- 在最终加入您的 tsp 表之前，我会尝试尽可能多地删除数据。日期范围检查移至内嵌视图。
- 我不喜欢你如何在 from 类和 where 子句中的顺序之间翻转表顺序。
tsp.product = prod_eff.product= >prod_eff.product = tsp.product - 我喜欢你在
to_timestamp函数中使用 between 子句的方式。很多人尝试trunc()的时候和那吹的索引使用。 - 如果您能以某种方式删除两个标量查询中的 >= 和 <，那就太好了。优化器对小于或大于的做出可怕的猜测。你能以某种方式在 4000 年前或 4000 年后的 between 条款中工作吗？
回答by neshkeev
During the checking your query I ran into this
在检查您的查询时，我遇到了这个
AND (prod_eff.EFF_START_DATETIME BETWEEN
to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND
to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3') OR
prod_eff.EFF_END_DATETIME BETWEEN
to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND
to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3')
)
As I see they are the same, i guess this part damages your query performance the most, get rid one of them, rewrite your query like this:
正如我所见，它们是相同的，我想这部分对您的查询性能的损害最大，摆脱其中之一，像这样重写您的查询：
SELECT prod_eff.facility
, prod_eff.product
, (SELECT MIN (cbv1.bucket_header)
FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
WHERE cbv1.version_id = 1
AND cbv1.min_datetime >= prod_eff.EFF_START_DATETIME
) min_eff
, (SELECT MAX (cbv1.bucket_header)
FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
WHERE cbv1.version_id = 1
AND cbv1.min_datetime < prod_eff.EFF_END_DATETIME
) max_eff
, 1 valid
FROM (
SELECT pf.product
, pf.facility
, pf.eff_start_datetime
, pf.eff_end_datetime
FROM AMD_OARS.BOM_PRODUCT_FACILITY pf
, AMD_OARS.MASTER_FACILITY f
WHERE pf.version_id = 114847
AND pf.facility = f.facility
AND f.facility != 'NONE'
AND f.validated = 1
) prod_eff
, AMD_OARS.TEMP_SELECTED_PRODUCT tsp
WHERE tsp.product = prod_eff.product
AND prod_eff.EFF_START_DATETIME BETWEEN
to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND
to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3')
ORDER BY 1, 2, 3, 4 ;
