Part II: Neo4j vs Oracle Performance Test
Introduction
Let's random generate 2 million unique Customers [:VISITED] 40000 unique Merchants. Every [:VISIT] has properties: amount (double) and dt (date). Every Customer has property “pty_id” (Integer). And every Merchant has mcht_id (String) property.
One Customer may visit one Merchant for more than one time. And of course, one Customer may visit many Merchants. So there are 43 978 539 relationships in my graph between Customers and Merchants.
The task is:
Get top 10 Customers ordered by total_amount who spent their money at NOT specified Merchant(M) but visit that Merchants which have been visited by Customers who visit this specified Merchant(M)
Hardware
Intel i7 2.3 GHz, SSD; VM (macOS Mojave + Parallels 12): 4 cores, 8 GB RAM
ORACLE Part
Oracle 19C Ent. Edition
SHOW SGA:
Total System Global Area 6777994992 bytes
Fixed Size 9150192 bytes
Variable Size 4630511616 bytes
Database Buffers 2130706432 bytes
Redo Buffers 7626752 bytes
select value/1024/1024/1024 from V$PGASTAT where name = 'aggregate PGA target parameter';
3.96875
ORA TEMPORARY SEGMENT = 40 GB
as scott:
CREATE TABLE OP_DET(
MCHT_ID VARCHAR2(100)
,PTY_ID NUMBER
,AMOUNT NUMBER
,DT DATE
);
--Generate 2M Customers, 40K Merchants and ~40+M relationships.
DECLARE
K NUMBER;
L NUMBER;
MCHT VARCHAR2(100);
TYPE T_TAB IS TABLE OF OP_DET%ROWTYPE;
TAB T_TAB := T_TAB();
LIMIT CONSTANT NUMBER := 10000;
T NUMBER;
BEGIN
/* IF 600 (THIS IS LIMIT VALUE FOR XE WITH RESTRICTION ON DB SIZE = 12 GB) THEN
SELECT COUNT(*) FROM OP_DET; ==132 000 671; LS -LAH /ORADATA/ ==5.9G */
FOR Q IN 1..200 LOOP
TAB.DELETE();
FOR I IN 1..LIMIT LOOP
K := ROUND(DBMS_RANDOM.VALUE(1,7));
FOR J IN 1..K LOOP
L := ROUND(DBMS_RANDOM.VALUE(1,40000));
MCHT := LPAD(TRIM(UPPER(TO_CHAR(L,'XXXXXXXXXXX'))),10,'0');
T := ROUND(DBMS_RANDOM.VALUE(1,10));
FOR Z IN 1..T LOOP
TAB.EXTEND();
TAB(TAB.COUNT).MCHT_ID := MCHT;
TAB(TAB.COUNT).PTY_ID := I+(Q-1)*LIMIT;
TAB(TAB.COUNT).AMOUNT := ROUND(DBMS_RANDOM.VALUE(1,10000),2);
TAB(TAB.COUNT).DT := SYSDATE + ROUND(DBMS_RANDOM.VALUE(1,5)) -
ROUND(DBMS_RANDOM.VALUE(1,5));
END LOOP;
END LOOP;
END LOOP;
FORALL I IN TAB.FIRST .. TAB.LAST
INSERT INTO OP_DET VALUES TAB(I);
COMMIT;
END LOOP;
END;
PL/SQL procedure successfully completed.
Elapsed: 00:03:34.178
-rw-r-----. 1 oracle oinstall 1.9G Jul 3 11:00 wrkdata.dat
Create indexes
CREATE INDEX OP_DET$PTY_ID ON OP_DET(PTY_ID);
Index OP_DET$PTY_ID created.
Elapsed: 00:00:38.268
-rw-r-----. 1 oracle oinstall 2.7G Jul 3 11:10 wrkdata.dat
CREATE INDEX OP_DET$MCHT_ID ON OP_DET(MCHT_ID);
([linux]$ top: 5429 oracle 20 0 2174560 181652 64392 R 48.8 (%CPU) (%MEM)10.2
2:48.30 oracle_5429_xe )
Index OP_DET$MCHT_ID created.
Elapsed: 00:01:01.954
ls -lah /oradata/
-rw-r-----. 1 oracle oinstall 3.6G (3829407744 bytes) Jul 3 12:04 wrkdata.dat
Compute statistics
analyze table OP_DET compute statistics;
Export customers, merchants and its relationships into CSV-files
as sys:
CREATE DIRECTORY CSV_DIR AS '/gdbms';
GRANT READ ON DIRECTORY CSV_DIR TO SCOTT;
GRANT WRITE ON DIRECTORY CSV_DIR TO SCOTT;
as scott:
--Export distinct PTY_IDs:
DECLARE
TYPE T_TAB IS TABLE OF PLS_INTEGER;
RWS T_TAB;
OUTPUT UTL_FILE.FILE_TYPE;
FILENAME VARCHAR2(100);
BEGIN
SELECT DISTINCT (PTY_ID)
BULK COLLECT INTO RWS
FROM OP_DET
--WHERE ROWNUM <= 100
;
FILENAME := 'CUSTOMER.CSV';
OUTPUT := UTL_FILE.FOPEN ( 'CSV_DIR', FILENAME, 'W' );
UTL_FILE.PUT_LINE(OUTPUT, 'PTY_ID');
FOR I IN 1 .. RWS.COUNT LOOP
UTL_FILE.PUT_LINE ( OUTPUT, RWS(I));
END LOOP;
UTL_FILE.FCLOSE(OUTPUT);
END;
PL/SQL procedure successfully completed.
Elapsed: 00:00:13.940
[root@luora oradata]# wc -l CUSTOMER.CSV
2000001 CUSTOMER.CSV
-- Export distinct MCHT_ID
DECLARE
TYPE T_TAB IS TABLE OF VARCHAR2(100);
RWS T_TAB;
OUTPUT UTL_FILE.FILE_TYPE;
FILENAME VARCHAR2(100);
BEGIN
SELECT DISTINCT (MCHT_ID)
BULK COLLECT INTO RWS
FROM OP_DET
--WHERE ROWNUM <= 1000
;
FILENAME := 'MERCHANT.CSV';
OUTPUT := UTL_FILE.FOPEN ( 'CSV_DIR', FILENAME, 'W' );
UTL_FILE.PUT_LINE(OUTPUT, 'MCHT_ID');
FOR I IN 1 .. RWS.COUNT LOOP
UTL_FILE.PUT_LINE ( OUTPUT, RWS(I));
END LOOP;
UTL_FILE.FCLOSE(OUTPUT);
END;
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.761
[root@luora oradata]# wc -l MERCHANT.CSV
40001 MERCHANT.CSV
---export relations of customers to merchants i.e. operations (pl/sql tables are not appropriate for this because we may exceed PGA_AGGREGATE_LIMIT)
DECLARE
OUTPUT UTL_FILE.FILE_TYPE;
FILENAME VARCHAR2(100);
BEGIN
FILENAME := 'OP_DET.CSV';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD''';
OUTPUT := UTL_FILE.FOPEN ( 'CSV_DIR', FILENAME, 'W' );
UTL_FILE.PUT_LINE(OUTPUT, 'PTY_ID,MCHT_ID,AMOUNT,DT');
FOR I IN (SELECT * FROM OP_DET /* WHERE ROWNUM <=1000000 */) LOOP
UTL_FILE.PUT_LINE ( OUTPUT, I.PTY_ID || ',' || I.MCHT_ID ||',' || I.AMOUNT ||',' || I.DT );
END LOOP;
UTL_FILE.FCLOSE(OUTPUT);
END;
PL/SQL procedure successfully completed.
Elapsed: 00:05:08.229
[root@luora oradata]# wc -l OP_DET.CSV
43978540 OP_DET.CSV
[root@luora oradata]# ls -lah
drwxr-xr-x. 2 oracle oinstall 120 Jul 3 12:25 .
dr-xr-xr-x. 19 root root 4.0K Jul 1 17:28 ..
-rw-r--r--. 1 oracle oinstall 15M Jul 3 12:15 CUSTOMER.CSV
-rw-r--r--. 1 oracle oinstall 430K Jul 3 12:19 MERCHANT.CSV
-rw-r--r--. 1 oracle oinstall 1.6G Jul 3 12:30 OP_DET.CSV
Neo4j Part
[root@graph ~]# nano /etc/neo4j/neo4j.conf
# The name of the database to mount
#dbms.active_database=graph.db
dbms.active_database=perf.db
# Paths of directories in the installation.
dbms.directories.data=/gdbms/neo4j/data
dbms.directories.logs=/gdbms/neo4j
#dbms.memory.heap.max_size=512m
dbms.memory.heap.max_size=3584m
#dbms.memory.pagecache.size=10g
dbms.memory.pagecache.size=4g
Import CSV-files into Neo4j
[root@graph ~]# neo4j-admin import --database=perf.db --nodes:Customer=./CUSTOMER.CSV
--nodes:Merchant=./MERCHANT.CSV --relationships:VISITED=./OP_DET.CSV
[root@graph ~]# chown -R neo4j:neo4j /gdbms/neo4j/data/databases/perf.db
[root@graph ~]# cypher-shell
Transform data types to more appropriate:
cypher-shell> call apoc.periodic.iterate("MATCH (c:Customer) return c",
"SET c.pty_id = toInteger(c.pty_id)",{batchSize:100000,parallel:true})
yield batches, total return batches, total
cypher-shell> call apoc.periodic.iterate("match (c:Customer)-[r:VISITED]->(m:Merchant) return r",
"set r.amount=toDouble(r.amount)", {batchSize:100000,parallel:true})
yield batches, total return batches, total
Create indexes
cypher-shell> CREATE INDEX on :Customer(pty_id)
cypher-shell> CREATE INDEX on :Merchant(mcht_id)
Run Tests
All queries return the same data:
pty_id |
amount |
1433798 |
348925.94 |
739510 |
339169.83 |
374933 |
327962.95 |
1925125 |
327545.95 |
768789 |
327463.18 |
1851163 |
326690.50 |
1290014 |
323256.00 |
258667 |
322899.84 |
293300 |
321374.56 |
1111539 |
320392.02 |
But time of execution is different:
Oracle |
Neo4j |
1-st run |
00:00:08.673 |
not optimized |
413020 ms (1st run) / 210428 ms (2nd run) |
2-nd run |
00:00:06.654 |
opt_1 |
154885 ms (1st run) / 15371 ms (2nd run) |
add /*+ PARALLEL(4) */ |
00:00:02.197 |
opt_2 |
108747 ms (1st run) / 5326 ms (2nd run) |
Queries are:
SQL:
SELECT
id, to_char(amnt, '99999999.99') from
(
SELECT
cust.id, sum(op.amount) amnt
from
(
SELECT
distinct op3.pty_id as id
FROM
OP_DET op1
,OP_DET op2
,OP_DET op3
WHERE
OP1.MCHT_ID = '0000000DA5'
AND OP2.PTY_ID = OP1.PTY_ID
and op2.mcht_id <> '0000000DA5'
and op2.mcht_id = op3.mcht_id
and op3.pty_id not in (SELECT distinct PTY_ID FROM OP_DET WHERE MCHT_ID = '0000000DA5')
) cust, op_det op
where op.pty_id = cust.id
group by cust.id
order by amnt desc
)
where rownum <=10
;
-- +parallel hint
SELECT
id, to_char(amnt, '99999999.99') from
(
SELECT
cust.id, sum(op.amount) amnt
from
(
SELECT /*+ PARALLEL(4) */
distinct op3.pty_id as id
FROM
OP_DET op1
,OP_DET op2
,OP_DET op3
WHERE
OP1.MCHT_ID = '0000000DA5'
AND OP2.PTY_ID = OP1.PTY_ID
and op2.mcht_id <> '0000000DA5'
and op2.mcht_id = op3.mcht_id
and op3.pty_id not in (SELECT distinct PTY_ID FROM OP_DET WHERE MCHT_ID = '0000000DA5')
) cust, op_det op
where op.pty_id = cust.id
group by cust.id
order by amnt desc
)
where rownum <=10
;
CYPHER:
not opt:
MATCH
(c:Customer)-[r:VISITED]->(mm:Merchant)<-[:VISITED]-(cc:Customer)-[:VISITED]->
(m:Merchant {mcht_id: "0000000DA5"})
WHERE
NOT (c)-[:VISITED]->(m)
WITH
DISTINCT c as uc
MATCH
(uc:Customer)-[rr:VISITED]->()
RETURN
uc.pty_id
,round(100*sum(rr.amount))/100 as v_amt
ORDER BY v_amt DESC
LIMIT 10;
opt[imixed]_1:
MATCH
(cc:Customer)-[:VISITED]->(m:Merchant {mcht_id: "0000000DA5"})
WITH m, collect(DISTINCT cc) as visitors
UNWIND visitors as cc
MATCH (uc:Customer)-[:VISITED]->(mm:Merchant)<-[:VISITED]-(cc)
WHERE
mm <> m
WITH
DISTINCT visitors, uc
WHERE NOT uc IN visitors
MATCH
(uc:Customer)-[rr:VISITED]->()
WITH
uc, round(100*sum(rr.amount))/100 as v_amt
ORDER BY v_amt DESC
LIMIT 10
RETURN uc.pty_id, v_amt;
opt_2:
MATCH (m:Merchant {mcht_id: "0000000DA5"})
CALL apoc.path.expandConfig(m, {uniqueness:'NODE_GLOBAL', relationshipFilter:'VISITED'
,minLevel:3, maxLevel:3}) YIELD path
WITH last(nodes(path)) as uc
MATCH
(uc:Customer)-[rr:VISITED]->()
WITH
uc
,round(100*sum(rr.amount))/100 as v_amt
ORDER BY v_amt DESC
LIMIT 10
RETURN uc.pty_id, v_amt;
Great thanks for help with Neo4j optimizing to InverseFalcon in this topic.
But despite all efforts at the moment Oracle wins this performance test!
See also: Part I: Neo4j vs Oracle Performance Test
#neo4j #oracle #performance
Conclusions
Good results for OpenSource project Neo4j but such monster as Oracle once again demonstrated its superiority.
Appendix
Neo4j EXPLAINS and PROFILE:
not optimized
opt_1
opt_2
opt_2 profile
Oracle Queries Plans:
1-st run:
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 10 | 260 | | 332K (1)| 00:00:13 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 2000K| 49M| | 332K (1)| 00:00:13 |
|* 3 | SORT ORDER BY STOPKEY | | 2000K| 43M| 61M| 332K (1)| 00:00:13 |
|* 4 | HASH JOIN | | 2000K| 43M| 32M| 318K (1)| 00:00:13 |
| 5 | VIEW | | 2000K| 9765K| | 191K (1)| 00:00:08 |
| 6 | HASH UNIQUE | | 2000K| 95M| 1525M| 191K (1)| 00:00:08 |
|* 7 | HASH JOIN RIGHT ANTI NA | | 26M| 1266M| | 60679 (1)| 00:00:03 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED | OP_DET | 1099 | 16485 | | 216 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | OP_DET$MCHT_ID | 1099 | | | 7 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 26M| 886M| | 60392 (1)| 00:00:03 |
| 11 | NESTED LOOPS | | 24166 | 471K| | 2472 (1)| 00:00:01 |
| 12 | NESTED LOOPS | | 24178 | 471K| | 2472 (1)| 00:00:01 |
| 13 | VIEW | VW_DTP_8085E41A | 1099 | 5495 | | 217 (1)| 00:00:01 |
| 14 | HASH UNIQUE | | 1099 | 16485 | | 217 (1)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID BATCHED| OP_DET | 1099 | 16485 | | 216 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | OP_DET$MCHT_ID | 1099 | | | 7 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | OP_DET$PTY_ID | 22 | | | 2 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | OP_DET | 22 | 330 | | 3 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | OP_DET | 43M| 629M| | 57802 (1)| 00:00:03 |
| 20 | VIEW | VW_GBC_11 | 2000K| 34M| | 122K (2)| 00:00:05 |
| 21 | HASH GROUP BY | | 2000K| 17M| 842M| 122K (2)| 00:00:05 |
| 22 | TABLE ACCESS FULL | OP_DET | 43M| 377M| | 57767 (1)| 00:00:03 |
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
4 - access("ITEM_1"="CUST"."ID")
7 - access("OP3"."PTY_ID"="PTY_ID")
9 - access("MCHT_ID"='0000000DA5')
10 - access("OP2"."MCHT_ID"="OP3"."MCHT_ID")
16 - access("OP1"."MCHT_ID"='0000000DA5')
17 - access("OP2"."PTY_ID"="ITEM_1")
18 - filter("OP2"."MCHT_ID"<>'0000000DA5')
PLAN_TABLE_OUTPUT
_______________________________________________
19 - filter("OP3"."MCHT_ID"<>'0000000DA5')
Statistics
-----------------------------------------------------------
676 CPU used by this session
678 CPU used when call started
697 DB time
44 Requests to/from client
4 enqueue conversions
7 enqueue releases
7 enqueue requests
1 enqueue waits
3771 non-idle wait count
76 non-idle wait time
49 opened cursors cumulative
2 opened cursors current
3965 physical read total IO requests
3316 physical read total multi block requests
8 process last non-idle time
75 recursive calls
3 recursive cpu usage
424626 session logical reads
75 user I/O wait time
45 user calls
Elapsed: 00:00:08.673
2-nd run:
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 10 | 260 | | 332K (1)| 00:00:13 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 2000K| 49M| | 332K (1)| 00:00:13 |
|* 3 | SORT ORDER BY STOPKEY | | 2000K| 43M| 61M| 332K (1)| 00:00:13 |
|* 4 | HASH JOIN | | 2000K| 43M| 32M| 318K (1)| 00:00:13 |
| 5 | VIEW | | 2000K| 9765K| | 191K (1)| 00:00:08 |
| 6 | HASH UNIQUE | | 2000K| 95M| 1525M| 191K (1)| 00:00:08 |
|* 7 | HASH JOIN RIGHT ANTI NA | | 26M| 1266M| | 60679 (1)| 00:00:03 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED | OP_DET | 1099 | 16485 | | 216 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | OP_DET$MCHT_ID | 1099 | | | 7 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 26M| 886M| | 60392 (1)| 00:00:03 |
| 11 | NESTED LOOPS | | 24166 | 471K| | 2472 (1)| 00:00:01 |
| 12 | NESTED LOOPS | | 24178 | 471K| | 2472 (1)| 00:00:01 |
| 13 | VIEW | VW_DTP_8085E41A | 1099 | 5495 | | 217 (1)| 00:00:01 |
| 14 | HASH UNIQUE | | 1099 | 16485 | | 217 (1)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID BATCHED| OP_DET | 1099 | 16485 | | 216 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | OP_DET$MCHT_ID | 1099 | | | 7 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | OP_DET$PTY_ID | 22 | | | 2 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | OP_DET | 22 | 330 | | 3 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | OP_DET | 43M| 629M| | 57802 (1)| 00:00:03 |
| 20 | VIEW | VW_GBC_11 | 2000K| 34M| | 122K (2)| 00:00:05 |
| 21 | HASH GROUP BY | | 2000K| 17M| 842M| 122K (2)| 00:00:05 |
| 22 | TABLE ACCESS FULL | OP_DET | 43M| 377M| | 57767 (1)| 00:00:03 |
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
4 - access("ITEM_1"="CUST"."ID")
7 - access("OP3"."PTY_ID"="PTY_ID")
9 - access("MCHT_ID"='0000000DA5')
10 - access("OP2"."MCHT_ID"="OP3"."MCHT_ID")
16 - access("OP1"."MCHT_ID"='0000000DA5')
17 - access("OP2"."PTY_ID"="ITEM_1")
18 - filter("OP2"."MCHT_ID"<>'0000000DA5')
PLAN_TABLE_OUTPUT
_______________________________________________
19 - filter("OP3"."MCHT_ID"<>'0000000DA5')
Statistics
-----------------------------------------------------------
659 CPU used by this session
659 CPU used when call started
659 DB time
6 Requests to/from client
1 enqueue releases
1 enqueue requests
3493 non-idle wait count
62 non-idle wait time
3 opened cursors cumulative
1 opened cursors current
3332 physical read total IO requests
3316 physical read total multi block requests
1 pinned cursors current
45 process last non-idle time
4 recursive calls
424400 session logical reads
60 user I/O wait time
6 user calls
Elapsed: 00:00:06.654
with parallel:
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | | 10 | 260 | | 53241 (2)| 00:00:03 | | | |
|* 1 | COUNT STOPKEY | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (ORDER) | :TQ10010 | 2000K| 49M| | 53241 (2)| 00:00:03 | Q1,10 | P->S | QC (ORDER) |
| 4 | VIEW | | 2000K| 49M| | 53241 (2)| 00:00:03 | Q1,10 | PCWP | |
|* 5 | SORT ORDER BY STOPKEY | | 2000K| 43M| 61M| 53241 (2)| 00:00:03 | Q1,10 | PCWP | |
| 6 | PX RECEIVE | | 10 | 260 | | | | Q1,10 | PCWP | |
| 7 | PX SEND RANGE | :TQ10009 | 10 | 260 | | | | Q1,09 | P->P | RANGE |
|* 8 | SORT ORDER BY STOPKEY | | 10 | 260 | | | | Q1,09 | PCWP | |
|* 9 | HASH JOIN | | 2000K| 43M| | 53227 (2)| 00:00:03 | Q1,09 | PCWP | |
| 10 | PX RECEIVE | | 2000K| 9765K| | 17355 (2)| 00:00:01 | Q1,09 | PCWP | |
| 11 | PX SEND HASH | :TQ10007 | 2000K| 9765K| | 17355 (2)| 00:00:01 | Q1,07 | P->P | HASH |
| 12 | VIEW | | 2000K| 9765K| | 17355 (2)| 00:00:01 | Q1,07 | PCWP | |
| 13 | HASH UNIQUE | | 2000K| 95M| 1525M| 17355 (2)| 00:00:01 | Q1,07 | PCWP | |
| 14 | PX RECEIVE | | 2000K| 95M| | 17355 (2)| 00:00:01 | Q1,07 | PCWP | |
| 15 | PX SEND HASH | :TQ10005 | 2000K| 95M| | 17355 (2)| 00:00:01 | Q1,05 | P->P | HASH |
| 16 | HASH UNIQUE | | 2000K| 95M| 1525M| 17355 (2)| 00:00:01 | Q1,05 | PCWP | |
|* 17 | HASH JOIN RIGHT ANTI NA | | 26M| 1266M| | 17152 (1)| 00:00:01 | Q1,05 | PCWP | |
| 18 | PX RECEIVE | | 1099 | 16485 | | 216 (0)| 00:00:01 | Q1,05 | PCWP | |
| 19 | PX SEND BROADCAST | :TQ10003 | 1099 | 16485 | | 216 (0)| 00:00:01 | Q1,03 | P->P | BROADCAST |
| 20 | TABLE ACCESS BY INDEX ROWID BATCHED | OP_DET | 1099 | 16485 | | 216 (0)| 00:00:01 | Q1,03 | PCWP | |
| 21 | BUFFER SORT | | | | | | | Q1,03 | PCWC | |
| 22 | PX RECEIVE | | 1099 | | | 7 (0)| 00:00:01 | Q1,03 | PCWP | |
| 23 | PX SEND HASH (BLOCK ADDRESS) | :TQ10001 | 1099 | | | 7 (0)| 00:00:01 | Q1,01 | S->P | HASH (BLOCK|
| 24 | PX SELECTOR | | | | | | | Q1,01 | SCWC | |
|* 25 | INDEX RANGE SCAN | OP_DET$MCHT_ID | 1099 | | | 7 (0)| 00:00:01 | Q1,01 | SCWP | |
|* 26 | HASH JOIN | | 26M| 886M| | 16918 (1)| 00:00:01 | Q1,05 | PCWP | |
| 27 | PX RECEIVE | | 24166 | 471K| | 843 (1)| 00:00:01 | Q1,05 | PCWP | |
| 28 | PX SEND BROADCAST | :TQ10004 | 24166 | 471K| | 843 (1)| 00:00:01 | Q1,04 | P->P | BROADCAST |
| 29 | NESTED LOOPS | | 24166 | 471K| | 843 (1)| 00:00:01 | Q1,04 | PCWP | |
| 30 | NESTED LOOPS | | 24178 | 471K| | 843 (1)| 00:00:01 | Q1,04 | PCWP | |
| 31 | VIEW | VW_DTP_8085E41A | 1099 | 5495 | | 217 (1)| 00:00:01 | Q1,04 | PCWP | |
| 32 | HASH UNIQUE | | 1099 | 16485 | | 217 (1)| 00:00:01 | Q1,04 | PCWP | |
| 33 | PX RECEIVE | | 1099 | 16485 | | 217 (1)| 00:00:01 | Q1,04 | PCWP | |
| 34 | PX SEND HASH | :TQ10002 | 1099 | 16485 | | 217 (1)| 00:00:01 | Q1,02 | P->P | HASH |
| 35 | HASH UNIQUE | | 1099 | 16485 | | 217 (1)| 00:00:01 | Q1,02 | PCWP | |
| 36 | HED TABLE ACCESS BY INDEX ROWID BATC | OP_DET | 1099 | 16485 | | 216 (0)| 00:00:01 | Q1,02 | PCWP | |
| 37 | PX RECEIVE | | 1099 | | | 7 (0)| 00:00:01 | Q1,02 | PCWP | |
| 38 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1099 | | | 7 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 39 | PX SELECTOR | | | | | | | Q1,00 | SCWC | |
|* 40 | INDEX RANGE SCAN | OP_DET$MCHT_ID | 1099 | | | 7 (0)| 00:00:01 | Q1,00 | SCWP | |
|* 41 | INDEX RANGE SCAN | OP_DET$PTY_ID | 22 | | | 1 (0)| 00:00:01 | Q1,04 | PCWP | |
|* 42 | TABLE ACCESS BY INDEX ROWID | OP_DET | 22 | 330 | | 1 (0)| 00:00:01 | Q1,04 | PCWP | |
| 43 | PX BLOCK ITERATOR | | 43M| 629M| | 16045 (1)| 00:00:01 | Q1,05 | PCWC | |
|* 44 | TABLE ACCESS FULL | OP_DET | 43M| 629M| | 16045 (1)| 00:00:01 | Q1,05 | PCWP | |
| 45 | PX RECEIVE | | 2000K| 34M| | 35869 (2)| 00:00:02 | Q1,09 | PCWP | |
| 46 | PX SEND HASH | :TQ10008 | 2000K| 34M| | 35869 (2)| 00:00:02 | Q1,08 | P->P | HASH |
| 47 | VIEW | VW_GBC_11 | 2000K| 34M| | 35869 (2)| 00:00:02 | Q1,08 | PCWP | |
| 48 | HASH GROUP BY | | 2000K| 17M| 842M| 35869 (2)| 00:00:02 | Q1,08 | PCWP | |
| 49 | PX RECEIVE | | 2000K| 17M| | 35869 (2)| 00:00:02 | Q1,08 | PCWP | |
| 50 | PX SEND HASH | :TQ10006 | 2000K| 17M| | 35869 (2)| 00:00:02 | Q1,06 | P->P | HASH |
| 51 | HASH GROUP BY | | 2000K| 17M| 842M| 35869 (2)| 00:00:02 | Q1,06 | PCWP | |
| 52 | PX BLOCK ITERATOR | | 43M| 377M| | 16035 (1)| 00:00:01 | Q1,06 | PCWC | |
| 53 | TABLE ACCESS FULL | OP_DET | 43M| 377M| | 16035 (1)| 00:00:01 | Q1,06 | PCWP | |
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
5 - filter(ROWNUM<=10)
8 - filter(ROWNUM<=10)
9 - access("ITEM_1"="CUST"."ID")
17 - access("OP3"."PTY_ID"="PTY_ID")
25 - access("MCHT_ID"='0000000DA5')
PLAN_TABLE_OUTPUT
__________________________________________________
26 - access("OP2"."MCHT_ID"="OP3"."MCHT_ID")
40 - access("OP1"."MCHT_ID"='0000000DA5')
41 - access("OP2"."PTY_ID"="ITEM_1")
42 - filter("OP2"."MCHT_ID"<>'0000000DA5')
44 - filter("OP3"."MCHT_ID"<>'0000000DA5')
Note
-----
- Degree of Parallelism is 4 because of hint
Statistics
-----------------------------------------------------------
785 CPU used by this session
1 CPU used when call started
1847 DB time
6 Requests to/from client
1 application wait time
20 enqueue conversions
24 enqueue releases
32 enqueue requests
2 enqueue waits
1064 in call idle wait time
2 messages sent
3553 non-idle wait count
70 non-idle wait time
14 opened cursors cumulative
1 opened cursors current
3436 physical read total IO requests
3388 physical read total multi block requests
1 pinned cursors current
9 process last non-idle time
40 recursive calls
784 recursive cpu usage
424955 session logical reads
69 user I/O wait time
37 user calls
Elapsed: 00:00:02.197
sdmrnv, 2019-07-01 [5.422ms, s]