Hi, we were migrated from 12.1 version of oracle HP unix to 19c Oracle Exadata. And we found suddenly the INSERT queries running longer. And the the main wait event was "SQL*Net more data from client". The insert query was simple "insert into table.. value(..)" types. And these inserts were hitting database with batch/array size of ~100 and ~1000 in few cases. These inserts were getting triggered from ETL/informatica.
We had not set any explicit SDU_SIZE in the sqlnet.ora or in client tns files in old HP superdome box. And this this new Exadata box also kept that value default(which i believe is 8k) as it was not explicitly mentioned. But as we are seeing this wait event in the new Exadata box and searching for a solution, after review by Oracle support we increased the SDU_SIZE to 65K in both client and server(sqlnet ora file). Then we saw immediately , the wait event "SQL*Net more data from client" disappeared. But we had not performed any client application recycle or DB server reboot at that time. But listeners restarted. Application team mentioned things working fine. After sometime application team again complained of same issue, though we were not seeing any "SQL*Net more data from client" wait event for the queries this time.
So my question is ,
1)As because we have not encountered such need in any database before so not set explict value in any of them and working fine. Even this same application was working fine on the old HP box in which we dont have any SDU_SIZE set explicitly. Which means the default sdu_size of ~8k was working fine on old HP box , so why its causing issue here in new exadata box? And if there is a way to validate the suitable SDU_SIZE for any such migration?
2)Why the INSERT were mostly impacted due to this , but not SELECTs?
3)If its required to reboot the database/client machines to take this SDU_SIZE in effect?
Below is the sql monitor and the TKProf of the query:
I am not able to see the fomatting option like we used to see in old community version.
INSERT INTO TAB1(c1,c2,c3...) VALUES ( :1, :2, :3, :4, ...) ;
-----------------------------------------------------------
| Id | Operation | Name | Cost |
-----------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | TAB1 | |
-----------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - INS$1
Note
-----
- cpu costing is off (consider enabling it)
- dynamic statistics used: statistics for conventional DML enabled
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[TAB1]
]></t><s><![CDATA[INS$1]]></s></h></f></q>