Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 390 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
Fetching CLOB column Faster
Answers
-
so your clob data might be too big and instead of fetching some GBs of data, fetching some MB of data must be faster of course. check the total length of your lob data:
select sum(size_in_mb)/1024/1024 total_clob_size_in_mb,
sum(case when size_in_mb > 4000 then 4000
when size_in_mb <= 4000 then size_in_mb
end
) size_for_first_4000_mb
from (select dbms_lob.getlength(col1)/1024/1024 size_in_mb from table1);
first column is total size and the second is the size when you get first 4000 char. probably there is a huge difference.
-
The sum size of CLOB column coming as 5.76MB. And also the maximum size of the CLOB is coming as ~.04MB.
select sum(size_in_mb) total_clob_size_in_mb,
sum(case when size_in_mb > 4000 then 4000
when size_in_mb <= 4000 then size_in_mb
end
) size_for_first_4000_mb
from (select dbms_lob.getlength(C10)/1024/1024 size_in_mb from TAB1);
5.76065254211426 5.76065254211426
select max(dbms_lob.getlength(C10))/1024/1024 size_in_mb from TAB1
0.0448904037475586
-
that's odd. did you delete a huge data from this table? there might be a too much empty space in your table or lob segment. use dbms_space_admin and check empty spaces in your table/lob segment. I assume this could be the problem.
sample code:
DECLARE fs1_blocks number; fs1_bytes number; fs2_blocks number; fs2_bytes number; fs3_blocks number; fs3_bytes number; fs4_blocks number; fs4_bytes number; full_blocks number; full_bytes number; unformatted_bl number; unformatted_by number;BEGIN DBMS_SPACE.space_usage('TABLE_OWNER HEREEEE' , upper('TABLE_NAME OR SEGMENT NAME HEREEEE') , 'TABLE' , unformatted_bl, unformatted_by, fs1_blocks , fs1_bytes , fs2_blocks , fs2_bytes , fs3_blocks , fs3_bytes , fs4_blocks , fs4_bytes , full_blocks, full_bytes ); DBMS_OUTPUT.PUT_LINE('fs1_blocks : ' || fs1_blocks ); DBMS_OUTPUT.PUT_LINE('fs1_bytes : ' || fs1_bytes ); DBMS_OUTPUT.PUT_LINE('fs2_blocks : ' || fs2_blocks ); DBMS_OUTPUT.PUT_LINE('fs2_bytes : ' || fs2_bytes ); DBMS_OUTPUT.PUT_LINE('fs3_blocks : ' || fs3_blocks ); DBMS_OUTPUT.PUT_LINE('fs3_bytes : ' || fs3_bytes ); DBMS_OUTPUT.PUT_LINE('fs4_blocks : ' || fs4_blocks ); DBMS_OUTPUT.PUT_LINE('fs4_bytes : ' || fs4_bytes ); DBMS_OUTPUT.PUT_LINE('full_blocks: ' || full_blocks); DBMS_OUTPUT.PUT_LINE('full_bytes : ' || full_bytes ); DBMS_OUTPUT.PUT_LINE('unformatted_bl: ' || unformatted_bl); DBMS_OUTPUT.PUT_LINE('unformatted_by : ' || unformatted_by );END;/
to check lob segment:
select segment_name from all_lobs where table_name = 'YOUR_TABLE_HERE';
change the code above as this:
.....
DBMS_SPACE.space_usage('TABLE OWNER HEREEEE' ,
upper('SEGMENT NAME FROM ALL_LOBS') ,
'LOB' ,
.....
-
Not able to fully understand the output. But i just ran it for the exact LOB segment and below is the output for the specific LOB segment.
fs1_blocks : 0
fs1_bytes : 0
fs2_blocks : 0
fs2_bytes : 0
fs3_blocks : 0
fs3_bytes : 0
fs4_blocks : 0
fs4_bytes : 0
full_blocks: 171991
full_bytes : 1408950272
unformatted_bl: 3868
unformatted_by : 31686656
-
933257 wrote:Tried modifying the query as you mentioned. But not seeing any improvement. Below is the sql monitor for fetching ~1000 rows of LOB having length<4000bytes. Both using CASE and SUBSTR clause.Using CASE statement taking same time as it was for fetching whole LOB:-
- SQLMonitoringReport
- SQLText
- ------------------------------
- SELECTc1,c2...,CASEWHENLENGTH(C10)<=4000thenCAST(C10ASVARCHAR2(4000))ELSENULLENDASMY_CLOB_COLUMN_AS_VARCHAR2,CASEWHENLENGTH(C10)>4000thenNULLELSEC10ENDASMY_CLOB_COLUMN_AS_CLOBFROMTAB1whereLENGTH(C10)<4000andrownum<1000
- GlobalInformation
- ------------------------------
- Status:DONE(ALLROWS)
- InstanceID:4
- SQLID:3m0j31tb3rd8x
- SQLExecutionID:67108864
- ExecutionStarted:07/31/201905:41:21
- FirstRefreshTime:07/31/201905:41:21
- LastRefreshTime:07/31/201905:42:46
- Duration:85s
- Module/Action:SQL*Plus/-
- Program:sqlplus.exe
- FetchCalls:1000
- GlobalStats
- =================================================
- |Elapsed|Cpu|Other|Fetch|Buffer|
- |Time(s)|Time(s)|Waits(s)|Calls|Gets|
- =================================================
- |0.02|0.00|0.02|1000|1065|
- =================================================
- SQLPlanMonitoringDetails(PlanHashValue=4129443724)
- ===========================================================================================================================================================
- |Id|Operation|Name|Rows|Cost|Time|Start|Execs|Rows|Activity|ActivityDetail|
- ||||(Estim)||Active(s)|Active||(Actual)|(%)|(#samples)|
- ===========================================================================================================================================================
- |0|SELECTSTATEMENT||||86|+0|1|999|||
- |1|COUNTSTOPKEY||||86|+0|1|999|||
- |2|TABLEACCESSSTORAGEFULLFIRSTROWS|TAB1|999|2335|86|+0|1|999|||
- ===========================================================================================================================================================
- PredicateInformation(identifiedbyoperationid):
- ---------------------------------------------------
- 1-filter(ROWNUM<1000)
- 2-filter(LENGTH("C10")<4000)
- Statistics
- ----------------------------------------------------------
- 1recursivecalls
- 0dbblockgets
- 1068consistentgets
- 3physicalreads
- 0redosize
- 608700bytessentviaSQL*Nettoclient
- 306929bytesreceivedviaSQL*Netfromclient
- 2000SQL*Netroundtripsto/fromclient
- 0sorts(memory)
- 0sorts(disk)
- 999rowsprocessed
SQL Monitoring Report SQL Text ------------------------------ SELECT c1,c2...,CASE WHEN LENGTH(C10) <= 4000 then CAST(C10 AS VARCHAR2(4000)) ELSE NULL END AS MY_CLOB_COLUMN_AS_VARCHAR2, CASE WHEN LENGTH(C10) > 4000 then NULL ELSE C10 END AS MY_CLOB_COLUMN_AS_CLOB FROM TAB1 where LENGTH (C10)<4000 and rownum<1000 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 4 SQL ID : 3m0j31tb3rd8x SQL Execution ID : 67108864 Execution Started : 07/31/2019 05:41:21 First Refresh Time : 07/31/2019 05:41:21 Last Refresh Time : 07/31/2019 05:42:46 Duration : 85s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 1000 Global Stats ================================================= | Elapsed | Cpu | Other | Fetch | Buffer | | Time(s) | Time(s) | Waits(s) | Calls | Gets | ================================================= | 0.02 | 0.00 | 0.02 | 1000 | 1065 | ================================================= SQL Plan Monitoring Details (Plan Hash Value=4129443724) =========================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | =========================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 86 | +0 | 1 | 999 | | | | 1 | COUNT STOPKEY | | | | 86 | +0 | 1 | 999 | | | | 2 | TABLE ACCESS STORAGE FULL FIRST ROWS | TAB1 | 999 | 2335 | 86 | +0 | 1 | 999 | | | =========================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<1000) 2 - filter(LENGTH("C10")<4000) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1068 consistent gets 3 physical reads 0 redo size 608700 bytes sent via SQL*Net to client 306929 bytes received via SQL*Net from client 2000 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999 rows processed
Using SUBSTR function Finishing in 3 seconds:-
- SELECTc1,c2....,DBMS_LOB.SUBSTR(C10,(SELECTMAX(LENGTH(C10))FROMTAB1))C10FROMTAB1whereLENGTH(C10)<4000andrownum<1000
- 999rowsselected.
- Elapsed:00:00:03.59
- ExecutionPlan
- ----------------------------------------------------------
- Planhashvalue:2117026829
- ------------------------------------------------------------------------------------------------------------
- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
- ------------------------------------------------------------------------------------------------------------
- |0|SELECTSTATEMENT||999|195K|2335(1)|00:00:29|
- |1|SORTAGGREGATE||1|164|||
- |2|TABLEACCESSSTORAGEFULL|TAB1|151K|23M|7212(1)|00:01:27|
- |*3|COUNTSTOPKEY||||||
- |*4|TABLEACCESSSTORAGEFULLFIRSTROWS|TAB1|999|195K|2335(1)|00:00:29|
- ------------------------------------------------------------------------------------------------------------
- PredicateInformation(identifiedbyoperationid):
- ---------------------------------------------------
- 3-filter(ROWNUM<1000)
- 4-filter(LENGTH("C10")<4000)
- Statistics
- ----------------------------------------------------------
- 47recursivecalls
- 0dbblockgets
- 41131consistentgets
- 14physicalreads
- 572redosize
- 25154bytessentviaSQL*Nettoclient
- 1197bytesreceivedviaSQL*Netfromclient
- 68SQL*Netroundtripsto/fromclient
- 0sorts(memory)
- 0sorts(disk)
- 999rowsprocessed
SELECT c1,c2....,DBMS_LOB.SUBSTR (C10,(SELECT MAX (LENGTH (C10)) FROM TAB1)) C10 FROM TAB1 where LENGTH (C10)<4000 and rownum<1000 999 rows selected. Elapsed: 00:00:03.59 Execution Plan ---------------------------------------------------------- Plan hash value: 2117026829 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 999 | 195K| 2335 (1)| 00:00:29 | | 1 | SORT AGGREGATE | | 1 | 164 | | | | 2 | TABLE ACCESS STORAGE FULL | TAB1 | 151K| 23M| 7212 (1)| 00:01:27 | |* 3 | COUNT STOPKEY | | | | | | |* 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| TAB1 | 999 | 195K| 2335 (1)| 00:00:29 | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(ROWNUM<1000) 4 - filter(LENGTH("C10")<4000) Statistics ---------------------------------------------------------- 47 recursive calls 0 db block gets 41131 consistent gets 14 physical reads 572 redo size 25154 bytes sent via SQL*Net to client 1197 bytes received via SQL*Net from client 68 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999 rows processed
You could use two queries, one to fetch the smaller lobs as a varchar2, this will use standard array fetches.
One query to fetch the remaining rows with lobs that cant be substr’ed, this will hopefully only be a few rows.
-
Initially i thought you are suggesting to have a UNION but then i realized as the SUBSTR will be VARCHAR data type vs the other part of the query will be a CLOB, so it has to be fetched twice by informatica. Correct me if wrong.
And also i tested the query by setting high value of longchunksize and long but that didn't affect the query performance. So other one i was thinking, if i should test this by setting the CACHE option for the CLOB storage and if that will benefit us?
-
933257 wrote:Initially i thought you are suggesting to have a UNION but then i realized as the SUBSTR will be VARCHAR data type vs the other part of the query will be a CLOB, so it has to be fetched twice by informatica. Correct me if wrong.And also i tested the query by setting high value of longchunksize and long but that didn't affect the query performance. So other one i was thinking, if i should test this by setting the CACHE option for the CLOB storage and if that will benefit us?
Exactly, unioning will need to use the same data type so won't work too well. Although if you can make it project the clob as null for the short lines it might be okay - would probably confuse informatica though!
I meant run:
select ..substr(clobcol,1,4000) my_clob from my_table where dbms_lob.getLength(clobcol)<=4000
and
select ..clobcol my_clob from my_table where dbms_lob.getLength(clobcol)>4000
Assuming 1 byte characters.
I don't think cache will help, the problem is in the network round trips from what I can tell
-
Your database spent 0.02 seconds working.
Your client or your network has spent the other 85.98 seconds.
If you want to check this, take a starting and ending snapshot of v$sesstat and v$session_event for your session, don't rely only on the autotrace stats. This may give you some idea of why your are losing time.
Regards
Jonathan Lewis
-
Yes Jonathan, i see ~1minute 25seconds total time as the overall run duration and the DB time is fraction of seconds. Below is the stats i fetched from gv$session_event with event like *SQL*NET.
SELECT event, total_waits, time_waited_micro, max_wait
FROM gv$session_event
WHERE SID = 1350 and upper(event) like upper('%SQL*%') and inst_id=4
order by total_waits desc;
EVENT TOTAL_WAITS TIME_WAITED_MICRO MAX_WAIT SQL*Net message to client 2024 1959 0 SQL*Net message from client 2023 122900382 2173 -
Might not be relevant to Informatica but good article on the JDBC options for LOB prefetching here: