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

Install Oracle 23c free using docker in Amazon Linux 2023

 

Install docker package and enable it

[ec2-user@ip-10-1-1-152 ~]$ cat /etc/amazon-linux-release
Amazon Linux release 2023 (Amazon Linux)

sudo yum install docker -y
sudo systemctl enable docker
sudo systemctl start docker

Pull Oracle 23c free version docker image

sudo docker pull container-registry.oracle.com/database/free:latest

[ec2-user@ip-10-1-1-152 ~]$ sudo docker pull container-registry.oracle.com/database/free:latest
latest: Pulling from database/free
089fdfcd47b7: Pull complete
43c899d88edc: Pull complete
47aa6f1886a1: Pull complete
f8d07bb55995: Pull complete
c31c8c658c1e: Pull complete
b7d28faa08b4: Pull complete
1d0d5c628f6f: Pull complete
db82a695dad3: Pull complete
25a185515793: Pull complete
Digest: sha256:5ac0efa9896962f6e0e91c54e23c03ae8f140cf6ed43ca09ef4354268a942882
Status: Downloaded newer image for container-registry.oracle.com/database/free:latest
container-registry.oracle.com/database/free:latest

Check the downloaded docker images

sudo docker images

[ec2-user@ip-10-1-1-152 ~]$ sudo docker images
REPOSITORY                                    TAG       IMAGE ID       CREATED       SIZE
container-registry.oracle.com/database/free   latest    39cabc8e6db0   4 weeks ago   9.16GB

Run the docker images

sudo docker run -d -it --name 23cfree -p 1521:1521 -p 5500:5500 -p 8080:8080 -p 8443:8443 -e ORACLE_PWD=Welcome123 container-registry.oracle.com/database/free:latest

[ec2-user@ip-10-1-1-152 ~]$ sudo docker run -d -it --name 23cfree -p 1521:1521 -p 5500:5500 -p 8080:8080 -p 8443:8443 -e ORACLE_PWD=Welcome123 container-registry.oracle.com/database/free:latest
ff618602d4402b6dbf92fd2c69612027c885937a176b9b160a8a9e93018bd519

[ec2-user@ip-10-1-1-152 ~]$ sudo docker logs 23cfree
Starting Oracle Net Listener.
Oracle Net Listener started.
Starting Oracle Database instance FREE.
Oracle Database instance FREE started.

The Oracle base remains unchanged with value /opt/oracle

SQL*Plus: Release 23.0.0.0.0 - Production on Fri Oct 6 12:31:14 2023
Version 23.3.0.23.09

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>
User altered.

SQL>
User altered.

SQL>
Session altered.

SQL>
User altered.

SQL> Disconnected from Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
The Oracle base remains unchanged with value /opt/oracle
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
2023-10-06T12:31:12.630086+00:00
FREEPDB1(3):Opening pdb with Resource Manager plan: DEFAULT_PLAN
Completed: Pluggable database FREEPDB1 opened read write
Completed: ALTER DATABASE OPEN
2023-10-06T12:31:15.404495+00:00
FREEPDB1(3):TABLE AUDSYS.AUD$UNIFIED: ADDED INTERVAL PARTITION SYS_P342 (3385) VALUES LESS THAN (TIMESTAMP' 2023-10-07 00:00:00')
[ec2-user@ip-10-1-1-152 ~]$

Login to running docker

sudo docker  exec -it 23cfree /bin/bash

[ec2-user@ip-10-1-1-152 ~]$ sudo docker  exec -it 23cfree /bin/bash
bash-4.4$ adrci

ADRCI: Release 23.0.0.0.0 - Production on Fri Oct 6 12:45:05 2023

Copyright (c) 1982, 2023, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/opt/oracle"
adrci> set home FREE
adrci> show alert -tail -f

Login to the database remotely

• Password is the one specified during during "docker run" command.
• Server name:
  • FREE: CDB Name (Container)
  • FREEPDB1: PDB Name
• Users created:
  • sys (login as sysdba)
  • system
  • pdbadmin

sql system/Welcome123@3.216.132.117:1521/FREEPDB1

[oracle@ol ~]$ sql system/Welcome123@3.216.132.117:1521/FREEPDB1


SQLcl: Release 22.4 Production on Fri Oct 06 20:41:31 2023

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


	New version: 23.2.0 available to download

Last Successful login time: Fri Oct 06 2023 20:43:09 +08:00

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> select host_name from v$instance;

HOST_NAME
_______________
ff618602d440

SQL> select banner from v$version;

BANNER
_________________________________________________________________________________
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Subsequent stop/start Docker

sudo docker stop 23cfree
sudo docker start 23cfree

Currently docker requires sudo to run it, to run as default ec2-user, you can add it to docker group.

sudo usermod -a -G docker ec2-user

ec2-user@ip-10-1-1-152 ~]$ sudo usermod -a -G docker ec2-user

[ec2-user@ip-10-1-1-152 ~]$ id -a ec2-user
uid=1000(ec2-user) gid=1000(ec2-user) groups=1000(ec2-user),4(adm),10(wheel),190(systemd-journal),992(docker)

[ec2-user@ip-10-1-1-152 ~]$ exit
logout

[root@ip-10-1-1-152 ~]# su - ec2-user
Last login: Fri Oct  6 12:24:26 UTC 2023 on pts/0

[ec2-user@ip-10-1-1-152 ~]$ docker ps
CONTAINER ID   IMAGE                                                COMMAND                  CREATED          STATUS                PORTS                                                                      NAMES
ff618602d440   container-registry.oracle.com/database/free:latest   "/bin/bash -c $ORACL…"   32 minutes ago   Up 2 minutes (healthy)   0.0.0.0:1521->1521/tcp, :::1521->1521/tcp, 0.0.0.0:5500->5500/tcp, :::5500->5500/tcp, 0.0.0.0:8080->8080/tcp, :::8080->8080/tcp, 0.0.0.0:8443->8443/tcp, :::8443->8443/tcp   23cfree

Impacts on MS-Replication or MS-CDC to enable CDC solution

 

Configuration	DML Duration	Datafile (Allocated MB)	Datafile (Free MB)	Tlog (Allocated MB)	Tlog Size (Free MB)
No Replication	00:07:19	8	4.4	392	146.4
MS-Replication	00:07:22	8	3.2	392	214
MS-CDC	00:07:11	136	17.8	456	24.3
MS-Replication + MS-CDC	00:09:27	136	22.7	456	190

Instance Configuration

/*
To avoid error: Update mask evaluation will be disabled in net_changes_function because the CLR configuration option is disabled.
*/
EXEC sp_configure 'clr enabled';  
EXEC sp_configure 'clr enabled' , '1';  
RECONFIGURE;    

Database Creation

CREATE DATABASE [db_msrepll]
 ON  PRIMARY ( NAME = N'db_msrepl', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\db_msrepll.mdf' , 
SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON ( NAME = N'db_msrepll_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\db_msrepll_log.ldf' , 
SIZE = 8192KB , FILEGROWTH = 65536KB );
-- repeat this for database db_mscdc, db_msrepl_cdc and db_norep

Table Creation

create table [db_msrepll].dbo.T (i int identity primary key, c text);
create table [db_mscdc].dbo.T (i int identity primary key, c text);
create table [db_msrepl_cdc].dbo.T (i int identity primary key, c text);
create table [db_norep].dbo.T (i int identity primary key, c text);

Enable CDC requirement with MS-Replication

For replication tool, it only requires additional information in the TLog, thus there is a filter 1=2, so no extra data writing to DistributionDB.

use [db_msrepl]
exec sp_replicationdboption @dbname = N'db_msrepl', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [db_msrepl]
exec sp_addpublication @publication = N'msrep', @description = N'Transactional publication of database ''db_msrepl'' from Publisher ''EC2AMAZ-PPQ3EA9''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @publication = N'msrep', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [db_msrepl]
exec sp_addarticle @publication = N'msrep', @article = N'T', @source_owner = N'dbo', @source_object = N'T', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'T', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboT', @del_cmd = N'CALL sp_MSdel_dboT', @upd_cmd = N'SCALL sp_MSupd_dboT', @filter_clause = N'1=2'

-- Adding the article filter
exec sp_articlefilter @publication = N'msrep', @article = N'T', @filter_name = N'FLTR_T_1__51', @filter_clause = N'1=2', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

-- Adding the article synchronization object
exec sp_articleview @publication = N'msrep', @article = N'T', @view_name = N'SYNC_T_1__51', @filter_clause = N'1=2', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO

-- repeat this for [db_msrep_cdc]

Enable CDC requirement with MS-CDC

In this case, MS-CDC automatically populates CDC related table (such as dbo_T_CT) along the DML changes, that explains why data file size increases.

USE [db_mscdc]  
GO  
EXEC sys.sp_cdc_enable_db  
GO  

EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'T',  
@role_name     = NULL,  
@supports_net_changes = 1  
GO  
-- repeat this for [db_msrep_cdc]

Simulate DML workloads

use [db_msrepl];

DECLARE
	@Counter int= 1
WHILE @Counter< = 100000
BEGIN
	insert into T (c) values (replicate('x',100));
	update T set c=replicate('y',100);
	delete from T;
	SET @Counter= @Counter + 1
END;

-- repeat this for database db_mscdc, db_msrepl_cdc and db_norep

Check the data file and Tlog size

SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128 AS CurrentSizeMB,  
    size/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);