Slow LOB fetch Informatica or SQLPLUS client
Issue : Fetching CLOB column is very expensive from network point of view. We have over 6 Million rows and Fetching was talking over 15 hrs. Database is Oracle Cloud OCI and Client was Informatica or even testing with SQLPLUS on prem.
Here is example just shows selecting just 99 rows and see 167 Network round trips. These network round trips are quite expensive and that was causing over 15 hrs to fetch.
Quite Interestingly , in my case Informatica was only using < 4000 bytes in their application side so when I tried to use dbms_lob.substr fetch was quicker.
You can see RoundTrip reduced from 167 to 8 roundtrips.
sqlplus arraysize set to 5000 and for our 5 Millions rows our query finished under 3 mins while without these changes it was taking over 15 hrs.
Wait events from SQL*Net from client. DB time for query was negligible.
sqlplus>
select Clob_Column from MyClobTable where rownum <100 ;
99 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3787731144
--------------------------------------------------------------------------------
------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
--------------------------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT | | 99 | 12474 |
3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | |
| |
| 2 | TABLE ACCESS STORAGE FULL FIRST ROWS| MyClobTable | 99 | 12474 |
3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
497 consistent gets
0 physical reads
0 redo size
46076 bytes sent via SQL*Net to client
96120 bytes received via SQL*Net from client
167 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
sqlplus >
select dbms_lob.substr(Clob_Column,amount=>4000, offset=>1) from
MyClobTable where rownum <100
SQL> /
99 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3787731144
--------------------------------------------------------------------------------
------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
--------------------------------------------------------------------------------
------------------------
| 0 | SELECT STATEMENT | | 99 | 12474 |
3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | |
| |
| 2 | TABLE ACCESS STORAGE FULL FIRST ROWS| MyClobTable | 99 | 12474 |
3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<100)
Statistics
----------------------------------------------------------
23 recursive calls
6 db block gets
19 consistent gets
0 physical reads
1300 redo size
7512 bytes sent via SQL*Net to client
494 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
Bottom Line : Fetching CLOB ( Large Objects) from Cloud to on prem will suffer from performance and there is at least 1 roundtrip for each row but check whether you can use dbms_lob.substr to improve fetch performance, if your application is truncating clob data to fewer bytes anyway.
.
No comments:
Post a Comment