Oracle Adviser cost estimates differs when using different VPN — oracle-tech

    Forum Stats

  • 3,707,910 Users
  • 2,240,909 Discussions
  • 7,839,920 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Oracle Adviser cost estimates differs when using different VPN

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 ;

  1. Windows JDBC connection using VPN1
  2. 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                     | Name              | Rows | Bytes | Cost (%CPU)| Time   |

------------------------------------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT                 |                |  44 | 12760 | 7079  (3)| 00:00:01 |

|  1 | SORT ORDER BY                  |                |  44 | 12760 | 7079  (3)| 00:00:01 |

|* 2 |  FILTER                     |                |    |    |      |     |

|  3 |  NESTED LOOPS                  |                |  44 | 12760 | 7078  (3)| 00:00:01 |

|  4 |   NESTED LOOPS                 |                |  44 | 12760 | 7078  (3)| 00:00:01 |

|  5 |   NESTED LOOPS                 |                |  36 | 9432 | 6973  (4)| 00:00:01 |

|* 6 |    HASH JOIN                  |                |  42 | 9954 | 6851  (4)| 00:00:01 |

|* 7 |    FILTER                   |                |    |    |      |     |

|* 8 |     HASH JOIN RIGHT OUTER           |                |  90 | 17820 | 6622  (4)| 00:00:01 |

|  9 |     TABLE ACCESS FULL            | SP2_JOB_FUNCTION_TYPE_FAMILIES |  23 |  138 |   2  (0)| 00:00:01 |

| 10 |     NESTED LOOPS OUTER            |                |  159 | 30528 | 6620  (4)| 00:00:01 |

| 11 |      NESTED LOOPS              |                |  159 | 29415 | 6302  (4)| 00:00:01 |

|* 12 |      FILTER                 |                |    |    |      |     |

|* 13 |       HASH JOIN OUTER            |                |  115 | 17020 | 6015  (4)| 00:00:01 |

|* 14 |       FILTER                |                |    |    |      |     |

|* 15 |        HASH JOIN OUTER           |                |  125 | 14625 | 5509  (4)| 00:00:01 |

|* 16 |        FILTER               |                |    |    |      |     |

|* 17 |         HASH JOIN RIGHT OUTER       |                |  130 | 12350 | 5064  (4)| 00:00:01 |

|* 18 |         TABLE ACCESS FULL        | SP2_CAR_ACTIVITY_FILES     |  73 | 1752 |   2  (0)| 00:00:01 |

|* 19 |         HASH JOIN            |                |  130 | 9230 | 5062  (4)| 00:00:01 |

|* 20 |          TABLE ACCESS FULL        | SP2_PER_PERSONS        | 1242 | 17388 | 2883  (3)| 00:00:01 |

|* 21 |          HASH JOIN            |                | 13248 |  737K| 2179  (6)| 00:00:01 |

|* 22 |          TABLE ACCESS FULL       | SP2_CAR_STATUTORY_LINKS    | 13248 |  388K| 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 |

------------------------------------------------------------------------------------------------------------------------------------


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

Answers

  • User_PWYUDUser_PWYUD Posts: 29 Newbie

    you can also send a message on [email protected]

  • Billy VerreynneBilly Verreynne Posts: 27,935 Red Diamond

    For starters, a traceroute from client to database server should shed light on the number of hops, and which specific hops are slow.

    Also keep in mind that VPNs can have vastly different configurations. CoS/QoS classes (DiffServe) can be different, classing Oracle traffic as business critical in one VPN, and bulk business in another.

    Likewise firewalls and app/port proxies can also have different configurations.

    Doubt that tcpdumps will show you the reason for performance difference - as it does not include details and metrics of client edge routers that routed the LAN traffic into the WAN cloud via provider edge routers.

    Suggest that client and provider edge router interfaces that routed the traffic be checked- stats and metrics.

  • User_PWYUDUser_PWYUD Posts: 29 Newbie

    question about:

    Suggest that client and provider edge router interfaces that routed the traffic be checked- stats and metrics.

    Which tools are used to define the traceroute, what are the edge router interfaces you mention?

  • Billy VerreynneBilly Verreynne Posts: 27,935 Red Diamond

    On Linux, the command is traceroute (part of the network utils package I think) and tracert on Windows.

    For a VPN you typically have a site (e.g. head office) that has a LAN. This LAN has one or more client edge (CE) routers. Multiple CE routers are used for failover/redundancy/load balancing/etc.

    A CE router routes LAN traffic into the VPN cloud. This traffic is send to a provider edge (PE) router, where the LAN traffic is MPLS'ed. Likewise, that PE router will receive WAN traffic for the site, unpack the MPLS packets, and transmit the traffic via the CE router to the LAN site.

    This may not exactly what you have, as there are a number of ways to configure VPNs, such as Metro Ethernet, SD-WAN, and so on.

    Point is that your LAN traffic is routed into the VPN WAN cloud, and the cloud routes WAN traffic back to your LAN.

    Keypoints to measure performance are at the CE and PE routers' ingress and egress interfaces that routes traffic between LAN and WAN.

    Your VPN provider should have tools that queries these PE interfaces for performance reporting. Your LAN team should have tools that queries the CE router interfaces for performance reporting.

    There are two basic performance reporting layers. Actual router interface metrics. E.g. bytes and packets volumes per QoS class transmitted for a specific time period by the router interface. Netflow/IPFIX data that reports on the actual IP traffic (e.g. source and destination IPs, ports and protocol, packets and bytes).

  • Billy VerreynneBilly Verreynne Posts: 27,935 Red Diamond

    Example of a CE router bandwidth utilisation for ingress (received from WAN) and egress (send to WAN) traffic with latency. In semi-realtime (up to 10 minutes ago):

    If your VPN provider does not provide this type of information to manage your VPN, you need to ask why.

    We do, and we are considered as a 3rd world country.

    User_PWYUD
  • User_PWYUDUser_PWYUD Posts: 29 Newbie

    Do you have experience with Weblogic datasource => URL => JDBC connection string

    We tried to add SDU peramter (chunck size)

    example :

    jdbc:oracle:thin:@ (description= (SDU=11280) (address_list= (address=(protocol=tcp)(port=1597)(host=olrdev21.cc.cec.eu.int)) ) (connect_data=(SERVICE_NAME=SYSPER2_HR_01_A_TAF.cc.cec.eu.int)))

    with max set to SDU => 32767

    also change

     <row-prefetch>true</row-prefetch>

        <row-prefetch-size>65536</row-prefetch-size>

        <stream-chunk-size>65536</stream-chunk-size>

    This didn't change anything on the performance / timeout.


    Any help is very appreciated!

    Kind regards,

    Johan

  • Billy VerreynneBilly Verreynne Posts: 27,935 Red Diamond

    Doubt that any changes on the JDBC side will solve the issue - as it does not seem to be at all JDBC related.

    Are the 2 VPN tests identical? If not, I suggest creating a single test for use. This eliminates a bunch of potential differences, and reduces the number of moving parts that could be causing the issue.

    A detailed error stack trace is important. Just where did the 1st error occurred and what was reported?

    On your side, you can enable SQL*Net tracing on the client side. The trace lists OCI (Oracle Call Interface) calls and will not shed much light on the network infrastructure used. But a failed OCI call will point to what failed and where from a client perspective.

    A tcpdump will be slightly more useful as you will be able to determine whether there were dropped packets, whether the timeout was due to a connection reset (RST TCP flag set), and so on.

    I would still want to see router interface latency and jitter reports, be provided the CoS/QoS classes implemented, and the class to which the TCP port used for Oracle client-server, is assigned to.

    If Oracle is assigned to a lower class, and there is a lot of higher class traffic volumes, Oracle traffic will very likely be dropped.

Sign In or Register to comment.