Oracle ->> Oracle下查看实际执行计划的方法

最新推荐文章于 2025-06-28 14:00:00 发布

转载最新推荐文章于 2025-06-28 14:00:00 发布 · 90 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/jenrrychen/p/4634256.html

文章标签：

#数据库

本文探讨了SQL查询中使用crossapply操作符的优化方法，并通过实例展示了如何使用Oracle数据库的dbms_xplan.display_cursor函数进行性能分析。重点介绍了查询执行计划、关键操作及其成本，帮助读者理解并优化复杂SQL查询的性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

也许有很多种方法，这里只是书上学到的一种方法

with a as (
select grp_factor from (select distinct grp_factor from numbers where id < 10 order by grp_factor) t 
where rownum <= 5)

select b.id, a.grp_factor
from a cross apply(select id from (select id from numbers b where a.grp_factor = b.grp_factor order by id) t where rownum<=3)b

--use v$sqlarea to find out your query by searching with key words. this might --take a little bit long.
select sql_id, sql_text from v$sqlarea where sql_text like '%cross apply%';

--copy the sql_id and place in the first place of parameters in the 
--function "dbms_xplan.display_cursor" . in our case, it is 1gbpuv6zfq64s
select * from table(dbms_xplan.display_cursor('1gbpuv6zfq64s',null,'typical'));



SQL_ID  1gbpuv6zfq64s, child number 0
-------------------------------------
with a as ( select grp_factor from (select distinct grp_factor from 
numbers where id < 10 order by grp_factor) t  where rownum <= 5)  
select b.id, a.grp_factor from a cross apply(select id from (select id 
from numbers b where a.grp_factor = b.grp_factor order by id) t where 
rownum<=3)b
 
Plan hash value: 3737636938
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |       |       |     2 (100)|          |
|   1 |  NESTED LOOPS                            |                 |     1 |    26 |     2   (0)| 00:00:01 |
|   2 |   VIEW                                   |                 |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY                         |                 |       |       |            |          |
|   4 |     VIEW                                 |                 |     1 |    13 |     1   (0)| 00:00:01 |
|*  5 |      SORT UNIQUE STOPKEY                 |                 |     1 |    26 |     1   (0)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| NUMBERS         |     1 |    26 |     1   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN                  | SYS_C009920     |     1 |       |     1   (0)| 00:00:01 |
|   8 |   VIEW                                   | VW_LAT_A83890C2 |     1 |    13 |     1   (0)| 00:00:01 |
|*  9 |    COUNT STOPKEY                         |                 |       |       |            |          |
|  10 |     VIEW                                 |                 |     1 |    13 |     1   (0)| 00:00:01 |
|* 11 |      TABLE ACCESS BY INDEX ROWID         | NUMBERS         |     1 |    26 |     1   (0)| 00:00:01 |
|  12 |       INDEX FULL SCAN                    | SYS_C009920     |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(ROWNUM<=5)
   5 - filter(ROWNUM<=5)
   7 - access("ID"<10)
   9 - filter(ROWNUM<=3)
  11 - filter("A"."GRP_FACTOR"="B"."GRP_FACTOR")
 
SQL_ID  1gbpuv6zfq64s, child number 1
-------------------------------------
with a as ( select grp_factor from (select distinct grp_factor from 
numbers where id < 10 order by grp_factor) t  where rownum <= 5)  
select b.id, a.grp_factor from a cross apply(select id from (select id 
from numbers b where a.grp_factor = b.grp_factor order by id) t where 
rownum<=3)b
 
Plan hash value: 3737636938
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------