RE: RAC - Global Cache Transfer Times

Yes. So notably my "ANDs" below are not in any way "BUTs."

AND, have you checked whether you have forced parallel local, or is the ETL load itself putting load on the interconnect?

AND, if you are using connections to multiple nodes from various clients executing the ETL jobs, are they operating on disjoint tables?

It is possible to minimize the "RAC" tax, the most likely contributors to which JL has just documented. The notion of "Application Affinity" extends to ETL: to the extent possible, do all the updates to the tables belonging to application X on one node, Y on another node, and so forth, balancing the load on nodes. Or if a single node will handles all the update load, just do all the insert/update/deletes on a single node, so at least the gcc traffic driven by queries from other nodes is at least unidirectional.

A little data model analysis and listener service configuration later, and you can minimize your ractax.

Good luck!

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Tuesday, August 06, 2019 5:45 AM
To: oracle-l_at_freelists.org
Subject: Re: RAC - Global Cache Transfer Times

Definitely on the slow side.

The simplest rule of thumb is "if it's slower than a single block read we probably shouldn't be using RAC".

You can see this type of repsonse (slow busy CR transfers) if you've got a reporting node and an update node and the update node gets very busy. The reporting node starts a "long" query and the update node has to do a lot of work on pinning busy blocks, copying them, then making the copy read-consistent.

In a single instance I think you'd see a lot of buffer busy waits, and a lot of "consistent gets - undo records applied" (both of which you may see on node 2 while node 1 is reporting these slow gc cr gets). Are you also seeing a lot of "gc cr disk read" waits ?

Regards
Jonathan Lewis

                CR Avg Time (us)        Current Avg Time (us)
Inst No Block Class     All     Immed   Busy    Congst  All     Immed   Busy
Congst
2       data block      26907   592     151759          942     392     9890
6391
2       undo header     2612    193     241481          1361            1361
2       others  11129   268     489014          54862   247     81818
2       undo block      201     201

[https://docs.google.com/uc?id=0BwovDucFT1fXaEREVHNWRWZyNjg&export=download]

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l