IPC versus TCP performance

Discussion:

Hameed, Amir

2018-10-30 13:01:07 UTC

Hi,
I have two databases running on the same physical server. The OS is RHEL7. Database A is 11.2.0.4 and is running as Active Data Guard. Database B is a reporting database and is version 12.1.0.2. In the past, database A was running on Solaris (four-node RAC) and we were using Oracle Streams to replicate over 400 tables from this particular database over to database B. We were also replicating tables from multiple other databases to database B but that is beside the point for this discussion. After we converted database A to LINUX, we decided to keep database B as the front-end and created synonyms to access tables from database A (ADG) using DB links. We have also setup IPC communication between A and B assuming that IPC would be faster than TCP. Unless I have misconfigured IPC, so far the performance of accessing database from B-to-A over a DB link is abysmal. Before I ask users to start accessing A directly instead of going through B, I wanted to understand the IPC communication a bit better.

In my scenario where both databases reside on the same physical server, is there a way to optimize access from B-to-A over a DB link?

Thank you,
Amir

Stefan Koehler

2018-10-30 20:27:38 UTC

Permalink

Hello Amir,
are you sure that this is a communication (latency) problem over the database link and not an optimizer problem (e.g. bad execution plans) due to the involved remote tables (distributed query)?

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de

Post by Hameed, Amir
Hi,
I have two databases running on the same physical server. The OS is RHEL7. Database A is 11.2.0.4 and is running as Active Data Guard. Database B is a reporting database and is version 12.1.0.2. In the past, database A was running on Solaris (four-node RAC) and we were using Oracle Streams to replicate over 400 tables from this particular database over to database B. We were also replicating tables from multiple other databases to database B but that is beside the point for this discussion. After we converted database A to LINUX, we decided to keep database B as the front-end and created synonyms to access tables from database A (ADG) using DB links. We have also setup IPC communication between A and B assuming that IPC would be faster than TCP. Unless I have misconfigured IPC, so far the performance of accessing database from B-to-A over a DB link is abysmal. Before I ask users to start accessing A directly instead of going through B, I wanted to understand the IPC communication a

bit better.

Post by Hameed, Amir
In my scenario where both databases reside on the same physical server, is there a way to optimize access from B-to-A over a DB link?
Thank you,
Amir

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

Mark W. Farnham

2018-10-31 16:54:29 UTC

Permalink

And, (not but), if you're using sqlplus for this this traffic please be sure you've set arraysize to something bigger than the default of 15.

Recommendations vary from 100ish to 1024ish. Last time I measured 1024 seemed to work about best for a variety of situations and your mileage will vary by too many infrastructure details for my measurements to be useful. I'm not aware of any noticeable resource consumption or extra overhead from 1024.

Then, to answer Stefan's question, consider a profiler to tell you where the problem lies.

mwf

-----Original Message-----
From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Stefan Koehler
Sent: Tuesday, October 30, 2018 4:28 PM
To: ***@xerox.com; ORACLE-L
Subject: Re: IPC versus TCP performance

Hello Amir,
are you sure that this is a communication (latency) problem over the database link and not an optimizer problem (e.g. bad execution plans) due to the involved remote tables (distributed query)?

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de

bit better.

Post by Hameed, Amir
In my scenario where both databases reside on the same physical server, is there a way to optimize access from B-to-A over a DB link?
Thank you,
Amir

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

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

Jeffrey Beckstrom

2018-10-31 17:25:12 UTC

Permalink

I agree with the optimizer question. At least a few versions ago, joining tables across a database link had poor performance. Anybody know if still true.
And, (not but), if you're using sqlplus for this this traffic please be sure you've set arraysize to something bigger than the default of 15.

Recommendations vary from 100ish to 1024ish. Last time I measured 1024 seemed to work about best for a variety of situations and your mileage will vary by too many infrastructure details for my measurements to be useful. I'm not aware of any noticeable resource consumption or extra overhead from 1024.

Then, to answer Stefan's question, consider a profiler to tell you where the problem lies.

mwf

-----Original Message-----
From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Stefan Koehler
Sent: Tuesday, October 30, 2018 4:28 PM
To: ***@xerox.com; ORACLE-L
Subject: Re: IPC versus TCP performance

Hello Amir,
are you sure that this is a communication (latency) problem over the database link and not an optimizer problem (e.g. bad execution plans) due to the involved remote tables (distributed query)?

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de

bit better.

Post by Hameed, Amir
In my scenario where both databases reside on the same physical server, is there a way to optimize access from B-to-A over a DB link?
Thank you,
Amir

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

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

Jonathan Lewis

2018-10-31 17:51:12 UTC

Permalink

Still (and inevitably) true.
You either do a nested loop that typically results in a large number of small dialogues taking place across the network or you do a hash join (or merge join) which typically requires a huge flood of redundant data to be dragged across the network (in relatively small chunks) and mostly discarded.

Often the best optimisation strategy for distributed queries is to make sure the data generation happens at the correct site. (driving_site hint).

Add to the mechanical problems the optimizer doesn't:
pull histograms across the network
take notice of virtual columns or extended stats
consider function-based indexes (which fits with the "virtual columns being ignored" bit)

and it only examines 20 of the indexes on any remote table - a few tests suggest its the 20 most recently created, but it may be more subtle than that.

Regards
Jonathan Lewis

________________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on behalf of Jeffrey Beckstrom <***@gcrta.org>
Sent: 31 October 2018 17:25
To: oracle-l-freelist; ***@rsiz.com; ***@soocs.de; ***@xerox.com
Subject: RE: IPC versus TCP performance

I agree with the optimizer question. At least a few versions ago, joining tables across a database link had poor performance. Anybody know if still true.
--
http://www.freelists.org/webpage/oracle-l