我正在运行一个相对简单的查询
SELECT * FROM confirm_v c
JOIN person p ON c.created_by=p.id
INNER JOIN invoice_confirm ic ON ic.confirm_id=c.id
WHERE c.id = (SELECT id FROM
(SELECT c2.id FROM confirm c2
JOIN invoice_confirm ic2 ON ic2.confirm_id=c2.id
WHERE ic2.invoice_id=11954081
AND c2.previous=0
AND c2.canceled=0
AND c2.confirm_type='INVOICE'
ORDER BY c2.id)
WHERE rownum=1);这会导致rdb的cpu使用率达到100%。confirm_type是一个varchar2(50 char),如果它有任何意义的话,其余的都是number(10)。
索引覆盖了invoice_confirm和confirm表,在此查询的执行计划中看不到全表扫描。
此查询执行的次数不多,但几乎占总cpu使用率的100%。任何想法都是值得感谢的。
编辑:查询的文本形式的解释计划。
EXPLAIN PLAN FOR ...
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
Plan hash value: 1705859247
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 69 | 10 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 69 | 10 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 57 | 7 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 30 | 5 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | CONFIRM | 1 | 24 | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_CONFIRM | 1 | | 2 (0)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | VIEW | | 4 | 52 | 27 (4)| 00:00:01 |
|* 9 | SORT ORDER BY STOPKEY | | 4 | 132 | 27 (4)| 00:00:01 |
| 10 | NESTED LOOPS | | 4 | 132 | 26 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 11 | 132 | 26 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED| INVOICE_CONFIRM | 3 | 36 | 4 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | FKI_INVOICE_CONFIRM_INVOICE | 2 | | 3 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_CONFIRM | 1 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | CONFIRM | 1 | 21 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | FKI_INVOICE_CONFIRM_CONFIRM | 1 | 6 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | PERSON | 1 | 27 | 2 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_KASUTAJA | 1 | | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | FKI_INVOICE_CONFIRM_CONFIRM | 1 | | 2 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | INVOICE_CONFIRM | 1 | 12 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("CONFIRM"."ID"= (SELECT "ID" FROM (SELECT "C2"."ID" "ID" FROM "INVOICE_CONFIRM" "IC2","CONFIRM" "C2"
WHERE "IC2"."CONFIRM_ID"="C2"."ID" AND "C2"."CANCELED"=0 AND "C2"."PREVIOUS"=0 AND "C2"."CONFIRM_TYPE"='INVOICE' AND
"IC2"."INVOICE_ID"=11954081 ORDER BY "C2"."ID") "from$_subquery$_006" WHERE ROWNUM=1))
7 - filter(ROWNUM=1)
9 - filter(ROWNUM=1)
13 - access("IC2"."INVOICE_ID"=11954081)
14 - access("IC2"."CONFIRM_ID"="C2"."ID")
15 - filter("C2"."CANCELED"=0 AND "C2"."PREVIOUS"=0 AND "C2"."CONFIRM_TYPE"='INVOICE')
16 - access("IC"."CONFIRM_ID"="CONFIRM"."ID")
18 - access("CONFIRM"."CREATED_BY"="P"."ID")
19 - access("IC"."CONFIRM_ID"="CONFIRM"."ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 2 Sql Plan Directives used for this statement发布于 2018-05-01 13:19:38
收集相关表上的优化器统计信息,并调查统计信息缺失的原因。
begin
dbms_stats.gather_table_stats(user, 'CONFIRM');
dbms_stats.gather_table_stats(user, 'INVOICE_CONFIRM');
dbms_stats.gather_table_stats(user, 'PERSON');
end;
/优化器统计信息对于Oracle实现良好性能至关重要。注释dynamic statistics used: dynamic sampling (level=2)表示存在缺少优化器统计信息的表。除非表是在最后一天内创建的,否则这种情况永远不会发生。
Oracle自动收集过时和丢失的统计信息。检查作业是否正在使用此查询运行。如果没有最近的行,请要求您的DBA重新启用该任务。
select *
from dba_optstat_operations
where operation like '%auto%'
order by start_time desc;自动任务对于大多数表来说已经足够好了。但是,如果有一个更新很多行的大型批处理过程,那么应该在作业完成后立即手动收集统计信息。
https://stackoverflow.com/questions/50102129
复制相似问题