Skip to Main Content

Oracle Database Discussions

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SDU for distributed mview refresh

OU_230Jan 24 2025 — edited Jan 24 2025

I am in Oracle EE 19.21 in AWS RDS. I am testing approaches to improve materialized view refresh using DB link. I found out parallelism does not work with distributed MVIEW per Doc ID 577870.1 so wanted to see if changing SDU size would help.

I found no changing SDU size from default 8192 to 65535 or making it 2MB.

I change both server sqlnet setting as well as database link definition.

Moreover, it seem SDU size is already 3.5MB when default_sdu_size is 8192.

I calculated this based on “bytes received via SQL*Net from dblink”which was around 580 MB and “SQL*Net roundtrips to/from dblink” is 162.

Even if I change SDU , I get same “SQL*Net roundtrips” and same elapsed time. Am I missing anything?

We are not using “Oracle Advanced Network Compression Service”

create materialized view test_sdu as select * from mils.license@test_without_sdu ; 
Materialized view created. 
Elapsed: 00:00:32.33 

SELECT name, VALUE 
FROM v$statname sn, v$mystat ms 
WHERE ms.VALUE != 0 AND sn.statistic# = ms.statistic# 
and name like '%SQL*Net %'order by value desc;

bytes received via SQL*Net from dblink 608765247 #580.5 MB 
bytes sent via SQL*Net to dblink 25802 
bytes received via SQL*Net from client 5151 
bytes sent via SQL*Net to client 4767 
SQL*Net roundtrips to/from dblink 162 # this would be 3.5MB / sdu change did not imapct 
SQL*Net roundtrips to/from client 14

Thanks in advance.

This post has been answered by Jonathan Lewis on Jan 24 2025
Jump to Answer
Comments
Post Details
Added on Jan 24 2025
2 comments
57 views