7.4 Interpreting EXPLAIN PLAN and Parallelism for Graph Algorithm Functions

A graph algorithm function (GAF) execution appears in EXPLAIN PLAN as a dedicated operation node named GRAPH ALGORITHM.

For example, the following statement generates an EXPLAIN PLAN for a SQL query that runs the PageRank graph algorithm on bank_graph and returns the top five accounts by rank.

SQL> EXPLAIN PLAN FOR
SELECT id, rank
FROM GRAPH_TABLE(
  DBMS_OGA.pagerank(
    bank_graph,
    PROPERTY(VERTEX OUTPUT rank),
    10, 1.0, 0.85d, FALSE
  )
  MATCH (a IS accounts)
  COLUMNS (a.id, a.rank)
) ORDER BY rank DESC
  FETCH FIRST 5 ROWS ONLY;

The following shows selected details from the EXPLAIN PLAN output for the preceding query:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 2408226440
-----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                           |     5 |    95 |     3  (34)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |                           |       |       |            |          |
|   2 |   VIEW                  |                           |     5 |    95 |     3  (34)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|                           |     5 |    95 |     3  (34)| 00:00:01 |
|   4 |     GRAPH ALGORITHM     | PAGERANK                  |       |       |            |          |
|   5 |      VIEW               | BANK_GRAPH                |     5 |    95 |     2   (0)| 00:00:01 |
|   6 |       NESTED LOOPS      |                           |     5 |   160 |     2   (0)| 00:00:01 |
|   7 |        TABLE ACCESS FULL| SYS_TEMP_0FD9D698F_157610 |   409 |  7771 |     2   (0)| 00:00:01 |
|*  8 |        INDEX UNIQUE SCAN| SYS_C008972               |     1 |    13 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2
   2 - SEL$1        / "from$_subquery$_002"@"SEL$2"
   3 - SEL$1
   5 - SEL$8A26CF64 / "from$_subquery$_001"@"SEL$1"
   6 - SEL$8A26CF64
   7 - SEL$8A26CF64 / "SYS_SYS_TEMP_0FD9D698F_157610_0_A"@"SEL$8A26CF64"
   8 - SEL$8A26CF64 / "A"@"SEL$8A26CF64"

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=5)
   3 - filter(ROWNUM<=5)
   8 - access("A"."ID"="SYS_SYS_TEMP_0FD9D698F_157610_0_A"."ID")

As seen in the preceding output, the specific algorithm (for example, PAGERANK) being executed is displayed in the Name column for the GRAPH ALGORITHM operation. The child operation under GRAPH ALGORITHM represents the SQL/PGQ translation produced for the GRAPH_TABLE operator.

Graph algorithms store computed results in cursor-duration temporary tables (CDTTs). These temporary tables typically appear in the EXPLAIN PLAN as internal temporary objects (for example, objects with SYS_TEMP_ prefix). Note that the EXPLAIN PLAN may not list every CDTT created during algorithm execution. It generally shows only those temporary objects that are used in the final SQL/PGQ translation.

Parallelization

A graph algorithm function (GAF) executes in two phases, and parallelism behaves differently in each phase:

1. Computing the algorithm and writing results to CDTTs:

   During computation, the algorithm’s control-flow logic runs sequentially. Most of the runtime is spent executing recursive SQL statements, which can run in parallel. However, the degree of parallelism (DOP) is determined by the optimizer based on system, session, or table specific parameters. You cannot explicitly set the DOP for the algorithm’s internal SQL.

2. Reading results in the enclosing SQL query:

   After the graph algorithm finishes, its results are read by the enclosing SQL query (the GRAPH_TABLE translation as well as the outer SQL). The query plan shown by EXPLAIN PLAN reflects this overall query, which can run in parallel. The optimizer ultimately chooses the DOP, but you can influence it by passing DOP hints on both the enclosing SQL query and/or the GRAPH_TABLE operator.