将计划详细信息解释为纯文本

Question

我正在尝试学习如何强制Oracle 18C使用基于功能的索引 - 使用提示：

--I've omitted the custom function and function-based index because it cluttered the question too much.
--https://gis.stackexchange.com/a/431019/62572

with cte as (
    select  /*+ INDEX (active_transportation atn_endpoint_list_idx) */
         infrastr.endpoint_list(shape) as list
    from
        infrastr.active_transportation
    where
        infrastr.endpoint_list(shape) is not null
    )    
select
    to_char(rownum) || '-STARTPOINT' as unique_id,
    cast(regexp_substr(list, '[^,]+', 1, 1) as number) as x,
    cast(regexp_substr(list, '[^,]+', 1, 2) as number) as y
from
    cte  
union all
select
    to_char(rownum) || '-ENDPOINT' as unique_id,
    cast(regexp_substr(list, '[^,]+', 1, 3) as number) as x,
    cast(regexp_substr(list, '[^,]+', 1, 4) as number) as y
from
    cte  

---

当我点击SQL Developer中的“解释计划”按钮时，我会得到：

我可以看到有关提示的信息。好的。

---

同样，我想将解释计划作为纯文本输出：

explain plan for
...(the query)

select * from table(dbms_xplan.display());  

但是，当我这样做时，我看不到任何有关提示的细节：

| Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             | 29068 |   110M|    77   (2)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION               |                             |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D669A_67C8FF97 |       |       |            |          |
|*  3 |    INDEX FULL SCAN                       | ATN_ENDPOINT_LIST_IDX       | 14534 |   965K|   161   (0)| 00:00:01 |
|   4 |   UNION-ALL                              |                             |       |       |            |          |
|   5 |    COUNT                                 |                             |       |       |            |          |
|   6 |     VIEW                                 |                             | 14534 |    27M|    38   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D669A_67C8FF97 | 14534 |   965K|    38   (0)| 00:00:01 |
|   8 |    COUNT                                 |                             |       |       |            |          |
|   9 |     VIEW                                 |                             | 14534 |    27M|    38   (0)| 00:00:01 |
|  10 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D669A_67C8FF97 | 14534 |   965K|    38   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("INFRASTR"."ENDPOINT_LIST"("SHAPE") IS NOT NULL)

---

问题：

如何将解释计划作为纯文本输出 - 并包括有关提示的详细信息用法？

我尝试使用此页面中使用一行： oracle scratchpad -fussy fbis ：

select * from table(dbms_xplan.display_cursor(format=>'cost allstats last hint_report remote outline'));

但是我有一个错误：

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
-----------------
Error: format 'cost allstats last hint_report remote outline' not valid for DBMS_XPLAN.DISPLAY_CURSOR()

Answer 1

您可能会在大纲之后，即

SQL> explain plan for select * from emp;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'+outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    11 |   407 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    11 |   407 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "EMP"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

40 rows selected.