We were migrated from 12.1 version of oracle HP unix to 19c Oracle Exadata. we have used export/import method to move data from HP to Exadata. We found the INSERT queries running longer on exadata box, 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. I am seeing, in both databases, the table size is same ~15.4 GB. holding ~85.5million rows. And the avg_row_len is 183 bytes. Which means one batch insert with a batch size of ~100, will occupy ~(100*183)= ~17KB. Again this is same in both the old and new databases and there is no changes done in client side.
We had not set any explicit SDU_SIZE in the sqlnet.ora or in client tns files in old HP superdome box. 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 , 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. But something odd we see , after sometime few application queries were still running slow, though we were not seeing any "SQL*Net more data from client" wait event for the queries this time. They were mostly spending time client side or say experiencing sql * net message from client.
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 suitable method 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?
4)was it(sdu_size) has some different treatment/behavior which depend on the Version of Oracle. As because we had our old HP database on 12.1.0.2 whereas new exadata is on 19C. And in that case if we have to be careful about the default SDU_SIZE setup while migrating from such version?
Below is the sql monitor and the TKProf of the query:
{ 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
}
{
*******10046 Trace*********
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 21217 72.92 745.30 2 187 626242 2036832
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21217 72.92 745.30 2 187 626242 2036832
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 232
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net more data from client 63649 6.25 663.21
SQL*Net message to client 21217 0.00 0.01
SQL*Net message from client 21216 3.24 249.76
enq: FB - contention 2540 0.00 0.60
cell single block physical read 2 0.00 0.00
gc current grant busy 1 0.00 0.00
enq: TT - contention 2 0.00 0.00
ges enter server mode 27 0.00 0.00
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 21217 72.92 745.30 2 187 626242 2036832
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21217 72.92 745.30 2 187 626242 2036832
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net more data from client 63649 6.25 663.21
SQL*Net message to client 21217 0.00 0.01
SQL*Net message from client 21216 3.24 249.76
enq: FB - contention 2540 0.00 0.60
cell single block physical read 2 0.00 0.00
gc current grant busy 1 0.00 0.00
enq: TT - contention 2 0.00 0.00
ges enter server mode 27 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
1 user SQL statements in session.
0 internal SQL statements in session.
1 SQL statements in session.
}