我有一个 Oracle SQL 查询，它涉及与 4 个大表和几个小表的连接。
大表是 TBL_1、TBL_2、TBL_3、TBL4，每个表都有大约 800 万条记录。其余的是少于 10K 记录的小表。
问题:即使没有数据返回，查询也需要3分钟以上。
表和索引统计信息是最新的。这些表上没有陈旧的统计数据。
我试过使用提示，但没有用。
请看下面我的观察:
询问:
SELECT a.*, ROWNUM AS rnm
FROM ( SELECT c.idntfr,
pr.program_name AS "Program",
e.case_number,
(SELECT DECODE (s.status_name,
'EA', 'A',
'ED', 'D',
'EU', 'U',
s.status_name)
FROM TBL_5 ms, status s
WHERE ms.status_type_cid = 7
AND mbr_sid = c.mbr_sid
AND ms.status_type_cid = s.status_type_cid
AND s.status_cid = ms.status_cid
AND ms.oprtnl_flag = 'A'
AND SYSDATE BETWEEN ms.from_date AND ms.TO_DATE),
DECODE (
LENGTH (TRIM (e.social_security_nmbr)),
NULL, 'Not Available',
( SUBSTR (e.social_security_nmbr, 1, 3)
|| '-'
|| SUBSTR (e.social_security_nmbr, 4, 2)
|| '-'
|| SUBSTR (e.social_security_nmbr, 6, 4)))
AS "SSN",
e.last_name || ',' || e.first_name || ' ' || e.middle_name,
TO_CHAR (e.injury_date, 'MM/dd/yyyy'),
DECODE (e.gender_lkpcd,
'M', 'Male',
'F', 'Female',
'U', 'Unknown'),
e.mbr_sid,
pr.program_cid,
e.last_name,
e.social_security_nmbr,
e.first_name AS
FROM TBL_1 c,
program pr,
TBL_2 e,
TBL_3 mai,
TBL_4 uaxou
WHERE c.mbr_sid = e.mbr_sid
AND c.mbr_sid = mai.mbr_sid
AND c.oprtnl_flag = 'A'
AND c.idntfr_type_cid = 423
AND TRUNC (SYSDATE) BETWEEN c.from_date AND c.TO_DATE
AND TRUNC (SYSDATE) BETWEEN e.from_date AND e.TO_DATE
AND e.oprtnl_flag = 'A'
AND e.status_cid = 2
AND mai.oprtnl_flag = 'A'
AND mai.status_cid = 2
AND TRUNC (SYSDATE) BETWEEN mai.from_date AND mai.TO_DATE
AND e.program_code = pr.program_code
AND pr.oprtnl_flag = 'A'
AND uaxou.user_acct_sid = 1
AND uaxou.oprtnl_flag = 'A'
AND SYSDATE BETWEEN uaxou.from_date AND uaxou.TO_DATE
AND uaxou.org_unit_sid = mai.org_unit_sid
ORDER BY "Program" ASC) a
WHERE ROWNUM < 102;
没有以下条件的数据
AND uaxou.user_acct_sid = 1
预期结果:如果没有返回数据，响应时间应低于 4 秒。
解释计划:
Plan hash value: 2272581586
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 22220 | 1361 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 58 | 7 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 58 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5 | 1 | 31 | 6 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | XIF1TBL_5 | 8 | | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | XPKSTATUS | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | STATUS | 1 | 27 | 1 (0)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | VIEW | | 169 | 37180 | 1361 (1)| 00:00:01 |
| 9 | NESTED LOOPS | | 169 | 36166 | 767 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 11904 | 36166 | 767 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 62 | 11284 | 333 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 45 | 6660 | 108 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 33 | 3564 | 9 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | PROGRAM | 5 | 70 | 2 (0)| 00:00:01 |
| 15 | INDEX FULL SCAN | XAK1OWCP_PROGRAM | 2 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | TBL_2 | 20 | 1880 | 4 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | TBL_1 | 1 | 40 | 3 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | TUNE_WS_19NOV10_X2 | 1 | | 2 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | TBL_3 | 1 | 34 | 5 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | XIE2_TBL_3 | 3 | | 2 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | XIF3TBL_4 | 192 | | 1 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | TBL_4 | 3 | 96 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND
"MS"."FROM_DATE"<=SYSDATE@!)
4 - access("MBR_SID"=:B1)
5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
7 - filter(ROWNUM<102)
14 - filter("PR"."OPRTNL_FLAG"='A')
16 - filter("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND
"E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND TRUNC(INTERNAL_FUNCTION("FROM_DATE"))<=TRUNC(TRUNC(SYSDATE@!)) AND
"E"."TO_DATE">=TRUNC(SYSDATE@!) AND TRUNC(INTERNAL_FUNCTION("TO_DATE"))>=TRUNC(TRUNC(SYSDATE@!)))
17 - filter("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!))
18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
19 - filter("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND
"MAI"."TO_DATE">=TRUNC(SYSDATE@!))
20 - access("C"."MBR_SID"="MAI"."MBR_SID")
21 - access("UAXOU"."USER_ACCT_SID"=1)
22 - filter("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND
"UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!)
这是来自 v$parameter 的查询输出
NAME | VALUE
compatible | 12.2.0
optimizer_adaptive_plans | TRUE
optimizer_adaptive_reporting_only | FALSE
optimizer_features_enable | 12.2.0.1
这是添加 GATHER_PLAN_STATISTICS 后显示实际基数值的解释计划:
Plan hash value: 2272581586
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 0 |
| 1 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 2 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5 | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 4 | INDEX RANGE SCAN | XIF1TBL_5 | 0 | 8 | 0 |00:00:00.01 | 0 |
|* 5 | INDEX UNIQUE SCAN | XPKSTATUS | 0 | 1 | 0 |00:00:00.01 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID | STATUS | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 7 | COUNT STOPKEY | | 1 | | 0 |00:00:00.01 | 0 |
| 8 | VIEW | | 1 | 169 | 0 |00:00:00.01 | 0 |
| 9 | NESTED LOOPS | | 1 | 169 | 0 |00:00:00.01 | 0 |
| 10 | NESTED LOOPS | | 1 | 11904 | 0 |00:00:00.01 | 0 |
| 11 | NESTED LOOPS | | 1 | 62 | 0 |00:00:00.01 | 0 |
| 12 | NESTED LOOPS | | 1 | 45 | 0 |00:00:00.01 | 0 |
| 13 | NESTED LOOPS | | 1 | 33 | 0 |00:00:00.01 | 0 |
|* 14 | TABLE ACCESS BY INDEX ROWID | PROGRAM | 1 | 5 | 1 |00:00:00.01 | 2 |
| 15 | INDEX FULL SCAN | XAK1OWCP_PROGRAM | 1 | 2 | 2 |00:00:00.01 | 1 |
|* 16 | TABLE ACCESS FULL | TBL_2 | 1 | 20 | 0 |00:00:00.01 | 0 |
|* 17 | TABLE ACCESS BY INDEX ROWID | TBL_1 | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 18 | INDEX RANGE SCAN | TUNE_WS_19NOV10_X2 | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 19 | TABLE ACCESS BY INDEX ROWID | TBL_3 | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 20 | INDEX RANGE SCAN | XIE2_TBL_3 | 0 | 3 | 0 |00:00:00.01 | 0 |
|* 21 | INDEX RANGE SCAN | XIF3TBL_4 | 0 | 192 | 0 |00:00:00.01 | 0 |
|* 22 | TABLE ACCESS BY INDEX ROWID | TBL_4 | 0 | 3 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND
"MS"."FROM_DATE"<=SYSDATE@!))
4 - access("MBR_SID"=:B1)
5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
7 - filter(ROWNUM<102)
14 - filter("PR"."OPRTNL_FLAG"='A')
16 - filter(("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND
"E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "E"."TO_DATE">=TRUNC(SYSDATE@!)))
17 - filter(("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!)))
18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
19 - filter(("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND
"MAI"."TO_DATE">=TRUNC(SYSDATE@!)))
20 - access("C"."MBR_SID"="MAI"."MBR_SID")
21 - access("UAXOU"."USER_ACCT_SID"=1)
22 - filter(("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND
"UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!))
我尝试了各种提示 USE_HASH(c e) 和各种其他组合，都没有奏效。
一个有趣的观察，如果我评论条件:
--AND uaxou.user_acct_sid = 1
结果在 7 秒内出现。 (显然，在这种情况下返回数据)。
那么，当没有数据返回时，是什么导致查询花费这么长时间？ (即此条件未注释
AND uaxou.user_acct_sid = 1 )
我让慢查询完成，花了 10 分 46 秒。没有数据返回
这是解释计划。我不知道为什么 A-Time 与实际执行时间不匹配。
Plan hash value: 2272581586
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 0 |
| 1 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 2 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL_5 | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 4 | INDEX RANGE SCAN | XIF1TBL_5 | 0 | 8 | 0 |00:00:00.01 | 0 |
|* 5 | INDEX UNIQUE SCAN | XPKSTATUS | 0 | 1 | 0 |00:00:00.01 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID | STATUS | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 7 | COUNT STOPKEY | | 1 | | 0 |00:00:00.01 | 0 |
| 8 | VIEW | | 1 | 169 | 0 |00:00:00.01 | 0 |
| 9 | NESTED LOOPS | | 1 | 169 | 0 |00:00:00.01 | 0 |
| 10 | NESTED LOOPS | | 1 | 11904 | 0 |00:00:00.01 | 0 |
| 11 | NESTED LOOPS | | 1 | 62 | 0 |00:00:00.01 | 0 |
| 12 | NESTED LOOPS | | 1 | 45 | 0 |00:00:00.01 | 0 |
| 13 | NESTED LOOPS | | 1 | 33 | 0 |00:00:00.01 | 0 |
|* 14 | TABLE ACCESS BY INDEX ROWID | PROGRAM | 1 | 5 | 1 |00:00:00.01 | 2 |
| 15 | INDEX FULL SCAN | XAK1OWCP_PROGRAM | 1 | 2 | 2 |00:00:00.01 | 1 |
|* 16 | TABLE ACCESS FULL | TBL_2 | 1 | 20 | 0 |00:00:00.01 | 0 |
|* 17 | TABLE ACCESS BY INDEX ROWID | TBL_1 | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 18 | INDEX RANGE SCAN | TUNE_WS_19NOV10_X2 | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 19 | TABLE ACCESS BY INDEX ROWID | TBL_3 | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 20 | INDEX RANGE SCAN | XIE2_TBL_3 | 0 | 3 | 0 |00:00:00.01 | 0 |
|* 21 | INDEX RANGE SCAN | XIF3TBL_4 | 0 | 192 | 0 |00:00:00.01 | 0 |
|* 22 | TABLE ACCESS BY INDEX ROWID | TBL_4 | 0 | 3 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("MS"."STATUS_TYPE_CID"=7 AND "MS"."OPRTNL_FLAG"='A' AND "MS"."TO_DATE">=SYSDATE@! AND
"MS"."FROM_DATE"<=SYSDATE@!))
4 - access("MBR_SID"=:B1)
5 - access("S"."STATUS_TYPE_CID"=7 AND "S"."STATUS_CID"="MS"."STATUS_CID")
7 - filter(ROWNUM<102)
14 - filter("PR"."OPRTNL_FLAG"='A')
16 - filter(("E"."PROGRAM_CODE"="PR"."PROGRAM_CODE" AND "E"."OPRTNL_FLAG"='A' AND "E"."STATUS_CID"=2 AND
"E"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "E"."TO_DATE">=TRUNC(SYSDATE@!)))
17 - filter(("C"."FROM_DATE"<=TRUNC(SYSDATE@!) AND "C"."TO_DATE">=TRUNC(SYSDATE@!)))
18 - access("C"."MBR_SID"="E"."MBR_SID" AND "C"."IDNTFR_TYPE_CID"=423 AND "C"."OPRTNL_FLAG"='A')
19 - filter(("MAI"."OPRTNL_FLAG"='A' AND "MAI"."STATUS_CID"=2 AND "MAI"."FROM_DATE"<=TRUNC(SYSDATE@!) AND
"MAI"."TO_DATE">=TRUNC(SYSDATE@!)))
20 - access("C"."MBR_SID"="MAI"."MBR_SID")
21 - access("UAXOU"."USER_ACCT_SID"=1)
22 - filter(("UAXOU"."ORG_UNIT_SID"="MAI"."ORG_UNIT_SID" AND "UAXOU"."OPRTNL_FLAG"='A' AND
"UAXOU"."FROM_DATE"<=SYSDATE@! AND "UAXOU"."TO_DATE">=SYSDATE@!))
请您参考如下方法:
自适应计划可以改进执行计划。
您将问题标记为 12c，但由于某种原因，执行计划似乎没有使用自适应计划。自适应计划让 Oracle 在运行时更改操作，例如在 NESTED LOOPS 和 HASH JOINS 之间切换。
NESTED LOOPS 适用于一小部分行，而 HASH JOINS 适用于大部分行。由于 ROWS 估计值都很小，但查询运行了三分钟，我猜优化器明显低估了表达式和连接的基数，并且使用了太多 NESTED LOOP。
如果启用了自适应计划，执行计划应该在底部有这个:
Note
-----
- this is an adaptive plan
由于缺少该注释，我猜想您的数据库中存在一个参数问题，阻止了自适应计划。运行以下查询并查看其中一项功能是否已关闭，或者这些功能是否设置为低于 12 的版本:
select name, value
from v$parameter
where name in (
'optimizer_adaptive_features', --12.1 only
'optimizer_adaptive_plans', --12.2+
'optimizer_adaptive_reporting_only',
'optimizer_features_enable',
'compatible'
)
order by 1;
编辑 1
我不确定为什么适应性计划对您不起作用。如果没有人能弄清楚，那么我们需要调查执行计划的实际值，以准确找出哪些操作很慢。
至少有两种方法可以获得实际数字。如果您可以更改并重新运行有问题的查询，则可以使用提示 GATHER_PLAN_STATISTICS .
--Run slow query and wait for it to finish:
select /*+ gather_plan_statistics */ * from dual;
--Find the SQL_ID of the query using some distinctive text:
select *
from v$sql
where lower(sql_fulltext) like '%gather_plan_statistics%';
--Generate execution plan with actual values.
select *
from table(dbms_xplan.display_cursor(sql_id => 'SQL_ID from above', format=>'allstats last'));
如果您无法更改查询，则可以使用 SQL Monitor 报告来查找实际值。 (此功能需要企业版和调整包许可证。)
--Generate SQL Monitoring Report:
select dbms_sqltune.report_sql_monitor(sql_id => 'SQL_ID from above') from dual;
编辑 2
您是否 100% 确定您找到了正确的 SQL_ID？您可能需要仔细检查
GV$SQL .如果 SQL 是从应用程序或 PL/SQL 块提交的，有时情况会发生变化。很少有真正的 SQL 语句会过时
GV$SQL , 如果有人运行
alter system flush shared_pool; , 或者收集了统计信息，或者如果您等待的时间过长。
如果这确实是正确的执行计划，则不会在查询上花费时间。通常，这意味着网络发送结果或应用程序处理结果必须花费时间。但由于没有返回行，网络或应用程序问题听起来不太可能。
如果时间花在数据库上，而不是花在该查询上，那么我猜它要么是解析问题，要么是递归查询问题。解析问题可以通过跟踪找到，但这些问题是由极其不寻常的问题或查询引起的，在这里可能不是这种情况。
也许 Oracle 用于收集元数据的查询之一花费的时间太长。对于许多查询，Oracle 需要运行其他查询来检查权限、动态采样等。您可能需要调整其他查询之一，以下语句可以帮助完成这个痛苦的过程:
--Clear existing run times (be careful running this on production).
--(This won't flush queries that are actively running.)
alter system flush shared_pool;
--Run your slow SQL statement here.
--...
--Now look for anything "weird" that has taken up most of the time.
select elapsed_time/1000000 seconds, gv$sql.*
from gv$sql
order by seconds desc;
