ORACLE-执行计划查询
--实际执行计划查询
1.alter session set statistics_level = all;
（这一步对当前会话窗口有效，可以不做，下面解释）
2.执行语句;
（如果上一步不做，则需要在语句中添加 /*+ gather_plan_statistics */。 例如：select /+ gather_plan_statistics */ * from dual; 但这样做比较麻烦 ，每条语句都要加上，不推荐）
3.找出执行语句的SQL ID，例如：
select SQL_ID,v.last_active_time, v.* from v$sql v where v.last_active_time > to_date('2024/01/10 17:00:00', 'yyyy/mm/dd hh24:mi:ss') and v.SQL_FULLTEXT not like'%SQL_ID%'
and v.SQL_FULLTEXT like '%select %'
order by v.last_active_time desc4.根据SQL ID查出执行计划 select * from table(dbms_xplan.display_cursor('SQL_ID',null,'allstats last'));
select * from table(dbms_xplan.display_cursor('79qcx7ttjnqrv',null,'allstats last'));
5.A-Rows 和 A-Time 为真实时间
5.A-Rows 和 A-Time 为真实时间
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 |00:00:24.93 | 163K| 25395 | 25395 | | | | |
|* 1 | HASH JOIN | | 1 | 1 | 50 |00:00:24.93 | 163K| 25395 | 25395 | 362M| 15M| 394M (0)| |
|* 2 | HASH JOIN | | 1 | 1 | 1140K|00:00:14.61 | 163K| 12915 | 12915 | 366M| 15M| 397M (0)| 108M|
|* 3 | HASH JOIN | | 1 | 1 | 1140K|00:00:04.51 | 163K| 0 | 0 | 332M| 15M| 358M (0)| |
|* 4 | INDEX RANGE SCAN | PA_CHARGES_DETAILS_VS_PERFORMANCEUNIT_INDEX2 | 1 | 1 | 1208K|00:00:01.66 | 103K| 0 | 0 | | | | |
|* 5 | INDEX FAST FULL SCAN | PA_CHARGE_POINT_INDEX2 | 1 | 1 | 1451K|00:00:01.09 | 59607 | 0 | 0 | | | | |
| 6 | TABLE ACCESS FULL | BD_WA_SALARY_PERFORMANCEUNIT | 1 | 1 | 277 |00:00:00.01 | 37 | 0 | 0 | | | | |
| 7 | INLIST ITERATOR | | 1 | | 1 |00:00:00.01 | 2 | 0 | 0 | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| BD_PA_DEPT_TYPE | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | | |
|* 9 | INDEX RANGE SCAN | IDX_DTYPE_CODE | 1 | 1 | 1 |00:00:00.01 | 1 | 0 | 0 | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------原文地址：https://blog.csdn.net/etinemark/article/details/145061188
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！