Oracle 23c new feature: default running background processes

 select distinct name,description,

  json_value(attributes,'$.instance_critical' returning varchar) critical,
  json_value(attributes,'$.restartable' returning varchar) restartable,
  json_value(attributes,'$.worker' returning varchar) worker
from v$bgprocess where paddr<>'00' order by name;

NAME	DESCRIPTION	CRITICAL	RESTARTABLE	WORKER
AQPC	AQ Process Coord	no	no	no
BG00	Testing SRV process	no	no	yes
BG01	Testing SRV process	no	no	yes
BG02	Testing SRV process3	no	no	yes
CJQ0	Job Queue Coordinator	no	yes	no
CKPT	checkpoint	yes	no	no
CL00	process cleanup slave	yes	no	yes
CLMN	process cleanup	yes	no	no
D000	Dispatchers	no	no	yes
DBRM	DataBase Resource Manager	yes	no	no
DBW0	db writer process 0	yes	no	no
DIA0	diagnosibility process 0	no	yes	no
DIA1	Deadlock Detection	no	yes	no
DIAG	diagnosibility process	no	yes	no
FENC	IOServer fence monitor	yes	no	no
GCR0	GCR Helpers (LMHB)	no	no	yes
GCW0	GCR Monitor processes (LMHB)	no	no	yes
GEN0	generic0	yes	no	no
GEN2	generic2	yes	no	no
LGWR	Redo etc.	yes	no	no
LMHB	lm heartbeat monitor	no	yes	no
LREG	Listener Registration	yes	no	no
M000	MMON slave class 1	no	no	yes
M001	MMON slave class 1	no	no	yes
M002	MMON slave class 1	no	no	yes
M003	MMON slave class 1	no	no	yes
M005	MMON slave class 1	no	no	yes
M006	MMON slave class 1	no	no	yes
M007	MMON slave class 1	no	no	yes
M008	MMON slave class 1	no	no	yes
M009	MMON slave class 1	no	no	yes
MMAN	Memory Manager	yes	no	no
MMNL	Manageability Monitor Process 2	no	yes	no
MMON	Manageability Monitor Process	no	yes	no
OFSD	Oracle File Server BG	no	yes	no
PMAN	process manager	yes	no	no
PMON	process cleanup	yes	no	no
PSP0	process spawner 0	yes	no	no
PXMN	PX Monitor	no	yes	no
Q003	QMON MS	no	no	yes
Q004	QMON MS	no	no	yes
QM02	QMON MS	no	no	yes
RCBG	Result Cache: Background	no	no	no
RECO	distributed recovery	no	yes	no
S000	Shared servers	no	no	yes
SCMN		no	no	yes
SMCO	Space Manager Process	no	yes	no
SMON	System Monitor Process	yes	no	no
SVCB	services background monitor	no	yes	no
TMON	Transport Monitor	no	no	no
TT00	Redo Transport	no	no	yes
TT01	Redo Transport	no	no	yes
VKRM	Virtual sKeduler for Resource Manager	no	yes	no
VKTM	Virtual Keeper of TiMe process	yes	no	no
VOSD	VOS Daemon	yes	no	no
W000	space management slave pool	no	no	yes
W001	space management slave pool	no	no	yes

Oracle 23c new feature: SQL Property Graphs

 

Demo Environment

$ sql hr/hr@192.168.0.200/FREEPDB1


SQLcl: Release 23.2 Production on Sat Oct 07 20:20:22 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL>
SQL> show user
USER is "HR"
SQL> 
SQL> set pages 999
SQL> set lines 120

Clean Up

SQL> drop property graph if exists employee_graph;

Property GRAPH dropped.

Create Property Graph

create property graph employee_graph
  vertex tables (
    HR.EMPLOYEES
    key (EMPLOYEE_ID)
    properties (EMPLOYEE_ID,FIRST_NAME, LAST_NAME,HIRE_DATE,JOB_ID,SALARY)
  )
  edge tables (
    HR.EMPLOYEES as WORK_FOR
    key (EMPLOYEE_ID)
    source key (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID)
    destination key (MANAGER_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID)
  )
  /

SQL> create property graph employee_graph
  2    vertex tables (
  3      HR.EMPLOYEES
  4      key (EMPLOYEE_ID)
  5      properties (EMPLOYEE_ID,FIRST_NAME, LAST_NAME,HIRE_DATE,JOB_ID,SALARY)
  6    )
  7    edge tables (
  8      HR.EMPLOYEES as WORK_FOR
  9      key (EMPLOYEE_ID)
 10      source key (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID)
 11      destination key (MANAGER_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID)
 12    )
 13*   /

Property GRAPH created.

Query Property Graph Example 1 (With Explain Plan)

-- top 20 rows order by mgr's employee_id for direct employees under him/her
select * from graph_table( employee_graph 
    match (src) -  [IS WORK_FOR] -> (dst)
    columns (src.employee_id as emp_id, src.first_name||src.last_name as EMP_NAME,
    dst.employee_id as mgr_id, dst.first_name||dst.last_name as MGR_NAME)
    ) 
order by mgr_id fetch first 20 rows only;

SQL> select * from graph_table( employee_graph
  2      match (src) -  [IS WORK_FOR] -> (dst)
  3      columns (src.employee_id as emp_id, src.first_name||src.last_name as EMP_NAME,
  4      dst.employee_id as mgr_id, dst.first_name||dst.last_name as MGR_NAME)
  5      )
  6* order by mgr_id fetch first 20 rows only;

   EMP_ID EMP_NAME               MGR_ID MGR_NAME
_________ ___________________ _________ _____________
      101 NeenaYang                 100 StevenKing
      102 LexGarcia                 100 StevenKing
      114 DenLi                     100 StevenKing
      120 MatthewWeiss              100 StevenKing
      121 AdamFripp                 100 StevenKing
      122 PayamKaufling             100 StevenKing
      123 ShantaVollman             100 StevenKing
      124 KevinMourgos              100 StevenKing
      145 JohnSingh                 100 StevenKing
      146 KarenPartners             100 StevenKing
      147 AlbertoErrazuriz          100 StevenKing
      148 GeraldCambrault           100 StevenKing
      149 EleniZlotkey              100 StevenKing
      201 MichaelMartinez           100 StevenKing
      108 NancyGruenberg            101 NeenaYang
      200 JenniferWhalen            101 NeenaYang
      203 SusanJacobs               101 NeenaYang
      204 HermannBrown              101 NeenaYang
      205 ShelleyHiggins            101 NeenaYang
      103 AlexanderJames            102 LexGarcia

20 rows selected.

PLAN_TABLE_OUTPUT
_____________________________________________________________________________________________________
Plan hash value: 1451186655

--------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |    20 |  2000 |     1   (0)| 00:00:01 |
|*  1 |  VIEW                           |                |    20 |  2000 |     1   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY         |                |    20 |  1820 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                |    20 |  1820 |     1   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                |   642 |  1820 |     1   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |   107 |  4173 |     1   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | EMP_EMP_ID_PK  |    21 |       |     1   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN           | EMP_MANAGER_IX |     6 |       |     0   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEES      |     1 |    52 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "DST"."EMPLOYEE_ID")<=20)
   7 - access("DST"."EMPLOYEE_ID"="MANAGER_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

26 rows selected.

Query Property Graph Example 2 (With Explain Plan)

-- top 20 rows order by emp's employee_id for direct employees under job title 'AD_PRES'
select * from graph_table( employee_graph 
    match (src) -  [IS WORK_FOR] -> (dst where dst.JOB_ID='AD_PRES')
    columns (src.employee_id as emp_id, src.first_name||src.last_name as EMP_NAME,
    dst.employee_id as mgr_id, dst.first_name||dst.last_name as MGR_NAME)
    ) 
order by emp_id fetch first 20 rows only; 

SQL> select * from graph_table( employee_graph
  2      match (src) -  [IS WORK_FOR] -> (dst where dst.JOB_ID='AD_PRES')
  3      columns (src.employee_id as emp_id, src.first_name||src.last_name as EMP_NAME,
  4      dst.employee_id as mgr_id, dst.first_name||dst.last_name as MGR_NAME)
  5      )
  6* order by emp_id fetch first 20 rows only;

   EMP_ID EMP_NAME               MGR_ID MGR_NAME
_________ ___________________ _________ _____________
      101 NeenaYang                 100 StevenKing
      102 LexGarcia                 100 StevenKing
      114 DenLi                     100 StevenKing
      120 MatthewWeiss              100 StevenKing
      121 AdamFripp                 100 StevenKing
      122 PayamKaufling             100 StevenKing
      123 ShantaVollman             100 StevenKing
      124 KevinMourgos              100 StevenKing
      145 JohnSingh                 100 StevenKing
      146 KarenPartners             100 StevenKing
      147 AlbertoErrazuriz          100 StevenKing
      148 GeraldCambrault           100 StevenKing
      149 EleniZlotkey              100 StevenKing
      201 MichaelMartinez           100 StevenKing

14 rows selected.

PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________
Plan hash value: 4176738749

-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |     6 |   600 |     1   (0)| 00:00:01 |
|*  1 |  VIEW                           |               |     6 |   600 |     1   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY         |               |     6 |   588 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |               |     6 |   588 |     1   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |               |   107 |   588 |     1   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |   107 |  5564 |     1   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | EMP_EMP_ID_PK |   107 |       |     1   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN           | EMP_JOB_IX    |     1 |       |     0   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEES     |     1 |    46 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "SRC"."EMPLOYEE_ID")<=20)
   7 - access("DST"."JOB_ID"='AD_PRES')
   8 - filter("DST"."EMPLOYEE_ID"="MANAGER_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Query Property Graph Example 3 (With Explain Plan)

select * from graph_table( employee_graph 
    match (emp) -  [IS WORK_FOR] -> (mgr1) -  [IS WORK_FOR] -> (mgr2) -  [IS WORK_FOR] -> (mgr3)  
    columns (emp.employee_id as emp_id, emp.first_name||emp.last_name as EMP_NAME,
    mgr1.first_name||mgr1.last_name as L1_MGR_NAME,
    mgr2.first_name||mgr2.last_name as L2_MGR_NAME,
    mgr3.first_name||mgr3.last_name as L3_MGR_NAME)
    ) 
order by L3_MGR_NAME,L2_MGR_NAME, L1_MGR_NAME fetch first 20 rows only; 

SQL> select * from graph_table( employee_graph
  2      match (emp) -  [IS WORK_FOR] -> (mgr1) -  [IS WORK_FOR] -> (mgr2) -  [IS WORK_FOR] -> (mgr3)
  3      columns (emp.employee_id as emp_id, emp.first_name||emp.last_name as EMP_NAME,
  4      mgr1.first_name||mgr1.last_name as L1_MGR_NAME,
  5      mgr2.first_name||mgr2.last_name as L2_MGR_NAME,
  6      mgr3.first_name||mgr3.last_name as L3_MGR_NAME)
  7      )
  8* order by L3_MGR_NAME,L2_MGR_NAME, L1_MGR_NAME fetch first 20 rows only;

   EMP_ID EMP_NAME            L1_MGR_NAME       L2_MGR_NAME    L3_MGR_NAME
_________ ___________________ _________________ ______________ ______________
      104 BruceMiller         AlexanderJames    LexGarcia      StevenKing
      107 DianaNguyen         AlexanderJames    LexGarcia      StevenKing
      106 ValliJackson        AlexanderJames    LexGarcia      StevenKing
      105 DavidWilliams       AlexanderJames    LexGarcia      StevenKing
      109 DanielFaviet        NancyGruenberg    NeenaYang      StevenKing
      110 JohnChen            NancyGruenberg    NeenaYang      StevenKing
      111 IsmaelSciarra       NancyGruenberg    NeenaYang      StevenKing
      112 Jose ManuelUrman    NancyGruenberg    NeenaYang      StevenKing
      113 LuisPopp            NancyGruenberg    NeenaYang      StevenKing
      206 WilliamGietz        ShelleyHiggins    NeenaYang      StevenKing

10 rows selected.

PLAN_TABLE_OUTPUT
__________________________________________________________________________________________________________________
Plan hash value: 1600920025

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                  |    20 |  3880 |     4  (50)| 00:00:01 |
|   1 |  SORT ORDER BY                             |                  |    20 |  3880 |     4  (50)| 00:00:01 |
|*  2 |   VIEW                                     |                  |    20 |  3880 |     3  (34)| 00:00:01 |
|*  3 |    WINDOW SORT PUSHED RANK                 |                  |   104 | 20280 |     3  (34)| 00:00:01 |
|   4 |     NESTED LOOPS                           |                  |   104 | 20280 |     2   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                          |                  |   630 | 20280 |     2   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                         |                  |   105 | 15015 |     2   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                        |                  |   106 |  9646 |     2   (0)| 00:00:01 |
|   8 |         VIEW                               | index$_join$_009 |   107 |  4173 |     2   (0)| 00:00:01 |
|*  9 |          HASH JOIN                         |                  |       |       |            |          |
|  10 |           INDEX FAST FULL SCAN             | EMP_EMP_ID_PK    |   107 |  4173 |     1   (0)| 00:00:01 |
|  11 |           INDEX FAST FULL SCAN             | EMP_NAME_IX      |   107 |  4173 |     1   (0)| 00:00:01 |
|  12 |         TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES        |     1 |    52 |     0   (0)| 00:00:01 |
|* 13 |          INDEX RANGE SCAN                  | EMP_MANAGER_IX   |     6 |       |     0   (0)| 00:00:01 |
|  14 |        TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES        |     1 |    52 |     0   (0)| 00:00:01 |
|* 15 |         INDEX RANGE SCAN                   | EMP_MANAGER_IX   |     6 |       |     0   (0)| 00:00:01 |
|* 16 |       INDEX RANGE SCAN                     | EMP_MANAGER_IX   |     6 |       |     0   (0)| 00:00:01 |
|  17 |      TABLE ACCESS BY INDEX ROWID           | EMPLOYEES        |     1 |    52 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   3 - filter(ROW_NUMBER() OVER ( ORDER BY "MGR3"."FIRST_NAME"||"MGR3"."LAST_NAME","MGR2"."FIRST_NAME"|
              |"MGR2"."LAST_NAME","MGR1"."FIRST_NAME"||"MGR1"."LAST_NAME")<=20)
   9 - access(ROWID=ROWID)
  13 - access("MGR3"."EMPLOYEE_ID"="MANAGER_ID")
  15 - access("MGR2"."EMPLOYEE_ID"="MANAGER_ID")
  16 - access("MGR1"."EMPLOYEE_ID"="MANAGER_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

39 rows selected.

Troubleshooting

ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

To fix:

SQL> grant create property graph to hr;

Grant succeeded.

References:

• https://github.com/oracle-samples/db-sample-schemas
• https://docs.oracle.com/en/database/oracle/property-graph/23.1/spgdg/sql-ddl-statements-property-graphs.html#GUID-6EEB2B99-C84E-449E-92DE-89A5BBB5C96E
• https://blogs.oracle.com/database/post/get-started-with-property-graphs-in-oracle-database-23c-free-developer-release