5.10 SQLプロパティ・グラフ問合せのチューニング

SQLグラフ問合せは、EXPLAIN PLAN文を使用してチューニングできます。

プロパティ・グラフを含むGRAPH_TABLE演算子は、内部的に同等のSQLに変換されます。したがって、次のようにプロパティ・グラフ問合せに対してEXPLAIN PLANを生成できます:

SQL> EXPLAIN PLAN FOR SELECT * FROM GRAPH_TABLE (students_graph
MATCH (a is person)-[e is friends]-> (b is person)
COLUMNS (a.name AS a , b.name AS b)
);
Explained.

EXPLAIN PLANは、次のように表示できます:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

Plan hash value: 1420380663
-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     4 |   264 |    10  (10)| 00:00:01 |
|*  1 |  HASH JOIN          |             |     4 |   264 |    10  (10)| 00:00:01 |
|*  2 |   HASH JOIN         |             |     4 |   184 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| PERSONS     |     4 |    80 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| FRIENDSHIPS |     4 |   104 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | PERSONS     |     4 |    80 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$B92C7F25
   3 - SEL$B92C7F25 / "A"@"SEL$213F43E5"
   4 - SEL$B92C7F25 / "E"@"SEL$213F43E5"
   5 - SEL$B92C7F25 / "B"@"SEL$213F43E5"

オプティマイザ・ヒントを使用して、前述の問合せをチューニングできます。たとえば、次の例ではPARALLELヒントを使用し、次の実行計画でヒントの使用方法を確認できます:

SQL> EXPLAIN PLAN FOR SELECT /*+ PARALLEL(4) */ * FROM GRAPH_TABLE (students_graph
MATCH (a is person)-[e is friends]-> (b is person)
COLUMNS (a.name AS a , b.name AS b)
);
Explained.

SQL>  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'ALL'));

Plan hash value: 1486901074
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |     4 |   264 |     4   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                  |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)            | :TQ10000    |     4 |   264 |     4   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS                  |             |     4 |   264 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     NESTED LOOPS                 |             |     4 |   264 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      NESTED LOOPS                |             |     4 |   184 |     3   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |       PX BLOCK ITERATOR          |             |       |       |            |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL         | FRIENDSHIPS |     4 |   104 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |       TABLE ACCESS BY INDEX ROWID| PERSONS     |     1 |    20 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  9 |        INDEX UNIQUE SCAN         | PERSON_PK   |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 10 |      INDEX UNIQUE SCAN           | PERSON_PK   |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  11 |     TABLE ACCESS BY INDEX ROWID  | PERSONS     |     1 |    20 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$B92C7F25
   7 - SEL$B92C7F25 / "E"@"SEL$213F43E5"
   8 - SEL$B92C7F25 / "A"@"SEL$213F43E5"
   9 - SEL$B92C7F25 / "A"@"SEL$213F43E5"
  10 - SEL$B92C7F25 / "B"@"SEL$213F43E5"
  11 - SEL$B92C7F25 / "B"@"SEL$213F43E5"

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
  0 -  STATEMENT

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
           -  PARALLEL(4)
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of hint