Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Adviser cost estimates differs when using different VPN

User_PWYUDDec 17 2020

Hi Community,
I need to find the root cause of a performance issue we encounter when using JDBC connection over VPN.
I have 2 situations ;
Windows JDBC connection using VPN1
Linux JDBC connection using VPN2
When running a query, the first setup performance is ok, the second setup gives a timeout after 5 minutes.
There seems to be a difference in the data transfer chunc size, lots of lost packages and latency on the network.
When using the Oracle Tuning Advisor, I get a significant difference in Cost (CPU) and temp space usage.
WINDOWS:

1- Original
-----------
Plan hash value: 507262279

------------------------------ | Id | Operation ------------------------------ | 0 | SELECT STATEMENT | 1 | SORT ORDER BY |* 2 | FILTER | 3 | NESTED LOOPS | 4 | NESTED LOOPS | 5 | NESTED LOOPS |* 6 | HASH JOIN |* 7 | FILTER |* 8 | HASH JOIN RIGHT OUTER | 9 | TABLE ACCESS FULL | 10 | NESTED LOOPS OUTER | 11 | NESTED LOOPS |* 12 | FILTER |* 13 | HASH JOIN OUTER |* 14 | FILTER |* 15 | HASH JOIN OUTER |* 16 | FILTER |* 17 | HASH JOIN RIGHT OUTER |* 18 | TABLE ACCESS FULL |* 19 | HASH JOIN |* 20 | TABLE ACCESS FULL |* 21 | HASH JOIN |* 22 | TABLE ACCESS FULL |* 23 | TABLE ACCESS FULL |* 24 | TABLE |* 25 | INDEX RANGE SCAN | 26 | INLIST ITERATOR |* 27 | TABLE |* 28 | INDEX RANGE SCAN |* 29 | TABLE ACCESS |* 30 | INDEX RANGE SCAN | 31 | TABLE ACCESS BY INDEX ROWID |* 32 | INDEX UNIQUE SCAN |* 33 | TABLE ACCESS FULL |* 34 | TABLE ACCESS |* 35 | INDEX RANGE SCAN |* 36 | INDEX RANGE SCAN |* 37 | TABLE ACCESS BY INDEX ROWID ------------------------------ ------------------------------------------------------------------------------------------------------
| Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| | 44 | 12760 | 7079 (3)| 00:00:01 |
| | 44 | 12760 | 7079 (3)| 00:00:01 |
| | | | | |
| | 44 | 12760 | 7078 (3)| 00:00:01 |
| | 44 | 12760 | 7078 (3)| 00:00:01 |
| | 36 | 9432 | 6973 (4)| 00:00:01 |
| | 42 | 9954 | 6851 (4)| 00:00:01 |
| | | | | |
| | 90 | 17820 | 6622 (4)| 00:00:01 |
| SP2_JOB_FUNCTION_TYPE_FAMILIES | 23 | 138 | 2 (0)| 00:00:01 |
| | 159 | 30528 | 6620 (4)| 00:00:01 |
| | 159 | 29415 | 6302 (4)| 00:00:01 |
| | | | | |
| | 115 | 17020 | 6015 (4)| 00:00:01 |
| | | | | |
| | 125 | 14625 | 5509 (4)| 00:00:01 |
| | | | | |
| | 130 | 12350 | 5064 (4)| 00:00:01 |
| SP2_CAR_ACTIVITY_FILES | 73 | 1752 | 2 (0)| 00:00:01 |
| | 130 | 9230 | 5062 (4)| 00:00:01 |
| SP2_PER_PERSONS | 1242 | 17388 | 2883 (3)| 00:00:01 |
| | 13248 | 737K| 2179 (6)| 00:00:01 |
| SP2_CAR_STATUTORY_LINKS | 13248 | 388K| 1798 (5)| 00:00:01 |
| SP2_CAREERS | 142K| 3753K| 380 (11)| 00:00:01 |
ACCESS BY INDEX ROWID BATCHED| SP2_PER_FILES | 2759 | 60698 | 444 (0)| 00:00:01 |
| PEF_ROT_FK_I | 2924 | | 9 (0)| 00:00:01 |
| | | | | |
ACCESS BY INDEX ROWID BATCHED | SP2_CAR_ADMIN_POSITIONS | 2857 | 88567 | 506 (1)| 00:00:01 |
| CAM_ADP_FK_I | 5894 | | 14 (0)| 00:00:01 |
BY INDEX ROWID BATCHED | SP2_CAR_ASSIGNMENTS | 1 | 37 | 3 (0)| 00:00:01 |
| CAS_CET_TO_FK_I | 1 | | 2 (0)| 00:00:01 |
| SP2_JOBS | 1 | 7 | 2 (0)| 00:00:01 |
| JOB_PK | 1 | | 1 (0)| 00:00:01 |
| SP2_ORG_UNIT_VERSIONS | 9102 | 346K| 228 (4)| 00:00:01 |
BY INDEX ROWID BATCHED | SP2_CAR_CONTRACTS | 1 | 25 | 3 (0)| 00:00:01 |
| CCN_CET_TO_FK_I | 1 | | 2 (0)| 00:00:01 |
| CAM_CET_TO_FK_I | 1 | | 2 (0)| 00:00:01 |
| SP2_CAR_ADMIN_POSITIONS | 1 | 28 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

LINUX:

1- Original With Adjusted Cost
------------------------------
Plan hash value: 507262279

--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 324 | 93960 | | 15456 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 324 | 93960 | | 15456 (2)| 00:00:01 |
|* 2 | FILTER | | | | | | |
| 3 | NESTED LOOPS | | 324 | 93960 | | 15455 (2)| 00:00:01 |
| 4 | NESTED LOOPS | | 373 | 93960 | | 15455 (2)| 00:00:01 |
| 5 | NESTED LOOPS | | 373 | 97726 | | 14370 (2)| 00:00:01 |
|* 6 | HASH JOIN | | 428 | 99K| | 13124 (2)| 00:00:01 |
|* 7 | FILTER | | | | | | |
|* 8 | HASH JOIN RIGHT OUTER | | 915 | 176K| | 12895 (2)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SP2_JOB_FUNCTION_TYPE_FAMILIES | 23 | 138 | | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1619 | 303K| | 12893 (2)| 00:00:01 |
| 11 | NESTED LOOPS | | 1619 | 292K| | 9654 (3)| 00:00:01 |
|* 12 | FILTER | | | | | | |
|* 13 | HASH JOIN OUTER | | 1405 | 203K| | 6143 (4)| 00:00:01 |
|* 14 | FILTER | | | | | | |
|* 15 | HASH JOIN OUTER | | 1530 | 174K| | 5637 (4)| 00:00:01 |
|* 16 | FILTER | | | | | | |
|* 17 | HASH JOIN RIGHT OUTER | | 1594 | 147K| | 5192 (4)| 00:00:01 |
|* 18 | TABLE ACCESS FULL | SP2_CAR_ACTIVITY_FILES | 73 | 1752 | | 2 (0)| 00:00:01 |
|* 19 | HASH JOIN | | 1595 | 110K| | 5190 (4)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | SP2_PER_PERSONS | 4022 | 56308 | | 2883 (3)| 00:00:01 |
|* 21 | HASH JOIN | | 50090 | 2788K| 2056K| 2306 (6)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | SP2_CAR_STATUTORY_LINKS | 50090 | 1467K| | 1798 (5)| 00:00:01 |
|* 23 | TABLE ACCESS FULL | SP2_CAREERS | 142K| 3753K| | 380 (11)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID BATCHED| SP2_PER_FILES | 2759 | 60698 | | 444 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | PEF_ROT_FK_I | 2924 | | | 9 (0)| 00:00:01 |
| 26 | INLIST ITERATOR | | | | | | |
|* 27 | TABLE ACCESS BY INDEX ROWID BATCHED | SP2_CAR_ADMIN_POSITIONS | 2857 | 88567 | | 506 (1)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | CAM_ADP_FK_I | 5894 | | | 14 (0)| 00:00:01 |
|* 29 | TABLE ACCESS BY INDEX ROWID BATCHED | SP2_CAR_ASSIGNMENTS | 1 | 37 | | 3 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | CAS_CET_TO_FK_I | 1 | | | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | SP2_JOBS | 1 | 7 | | 2 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | JOB_PK | 1 | | | 1 (0)| 00:00:01 |
|* 33 | TABLE ACCESS FULL | SP2_ORG_UNIT_VERSIONS | 9102 | 346K| | 228 (4)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID BATCHED | SP2_CAR_CONTRACTS | 1 | 25 | | 3 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | CCN_CET_TO_FK_I | 1 | | | 2 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | CAM_CET_TO_FK_I | 1 | | | 2 (0)| 00:00:01 |
|* 37 | TABLE ACCESS BY INDEX ROWID | SP2_CAR_ADMIN_POSITIONS | 1 | 28 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------------------

I wonder how I can trace why the Oracle tuning advisor calculates these differences.
Our network team is working on the VPN side, to explore tcpdump dump and trace files, but I want to know if I can trace something on the Oracle connectivity. For example by tracing the fetch size, data transfer between client and server, etc.
Any suggestions are very appreciated.
Thanks

Kind regards,
Johan

Added on Dec 17 2020

#database-connectivity-discussions

7 comments

297 views