1 2 Previous Next 20 Replies Latest reply on Aug 5, 2019 3:44 PM by Dom Brooks Go to original post
      • 15. Re: Fetching CLOB column Faster
        AndrewSayer

        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:-

         

        1. SQLMonitoringReport
        2. SQLText
        3. ------------------------------
        4. SELECTc1,c2...,CASEWHENLENGTH(C10)<=4000thenCAST(C10ASVARCHAR2(4000))ELSENULLENDASMY_CLOB_COLUMN_AS_VARCHAR2,CASEWHENLENGTH(C10)>4000thenNULLELSEC10ENDASMY_CLOB_COLUMN_AS_CLOBFROMTAB1whereLENGTH(C10)<4000andrownum<1000
        5. GlobalInformation
        6. ------------------------------
        7. Status:DONE(ALLROWS)
        8. InstanceID:4
        9. SQLID:3m0j31tb3rd8x
        10. SQLExecutionID:67108864
        11. ExecutionStarted:07/31/201905:41:21
        12. FirstRefreshTime:07/31/201905:41:21
        13. LastRefreshTime:07/31/201905:42:46
        14. Duration:85s
        15. Module/Action:SQL*Plus/-
        16. Program:sqlplus.exe
        17. FetchCalls:1000
        18. GlobalStats
        19. =================================================
        20. |Elapsed|Cpu|Other|Fetch|Buffer|
        21. |Time(s)|Time(s)|Waits(s)|Calls|Gets|
        22. =================================================
        23. |0.02|0.00|0.02|1000|1065|
        24. =================================================
        25. SQLPlanMonitoringDetails(PlanHashValue=4129443724)
        26. ===========================================================================================================================================================
        27. |Id|Operation|Name|Rows|Cost|Time|Start|Execs|Rows|Activity|ActivityDetail|
        28. ||||(Estim)||Active(s)|Active||(Actual)|(%)|(#samples)|
        29. ===========================================================================================================================================================
        30. |0|SELECTSTATEMENT||||86|+0|1|999|||
        31. |1|COUNTSTOPKEY||||86|+0|1|999|||
        32. |2|TABLEACCESSSTORAGEFULLFIRSTROWS|TAB1|999|2335|86|+0|1|999|||
        33. ===========================================================================================================================================================
        34. PredicateInformation(identifiedbyoperationid):
        35. ---------------------------------------------------
        36. 1-filter(ROWNUM<1000)
        37. 2-filter(LENGTH("C10")<4000)
        38. Statistics
        39. ----------------------------------------------------------
        40. 1recursivecalls
        41. 0dbblockgets
        42. 1068consistentgets
        43. 3physicalreads
        44. 0redosize
        45. 608700bytessentviaSQL*Nettoclient
        46. 306929bytesreceivedviaSQL*Netfromclient
        47. 2000SQL*Netroundtripsto/fromclient
        48. 0sorts(memory)
        49. 0sorts(disk)
        50. 999rowsprocessed

         

         

        Using SUBSTR function Finishing in 3 seconds:-

         

        1. SELECTc1,c2....,DBMS_LOB.SUBSTR(C10,(SELECTMAX(LENGTH(C10))FROMTAB1))C10FROMTAB1whereLENGTH(C10)<4000andrownum<1000
        2. 999rowsselected.
        3. Elapsed:00:00:03.59
        4. ExecutionPlan
        5. ----------------------------------------------------------
        6. Planhashvalue:2117026829
        7. ------------------------------------------------------------------------------------------------------------
        8. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
        9. ------------------------------------------------------------------------------------------------------------
        10. |0|SELECTSTATEMENT||999|195K|2335(1)|00:00:29|
        11. |1|SORTAGGREGATE||1|164|||
        12. |2|TABLEACCESSSTORAGEFULL|TAB1|151K|23M|7212(1)|00:01:27|
        13. |*3|COUNTSTOPKEY||||||
        14. |*4|TABLEACCESSSTORAGEFULLFIRSTROWS|TAB1|999|195K|2335(1)|00:00:29|
        15. ------------------------------------------------------------------------------------------------------------
        16. PredicateInformation(identifiedbyoperationid):
        17. ---------------------------------------------------
        18. 3-filter(ROWNUM<1000)
        19. 4-filter(LENGTH("C10")<4000)
        20. Statistics
        21. ----------------------------------------------------------
        22. 47recursivecalls
        23. 0dbblockgets
        24. 41131consistentgets
        25. 14physicalreads
        26. 572redosize
        27. 25154bytessentviaSQL*Nettoclient
        28. 1197bytesreceivedviaSQL*Netfromclient
        29. 68SQL*Netroundtripsto/fromclient
        30. 0sorts(memory)
        31. 0sorts(disk)
        32. 999rowsprocessed

        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.

        1 person found this helpful
        • 16. Re: Fetching CLOB column Faster
          933257

          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?

          • 17. Re: Fetching CLOB column Faster
            AndrewSayer

            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

            • 18. Re: Fetching CLOB column Faster
              Jonathan Lewis

              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

              • 19. Re: Fetching CLOB column Faster
                933257

                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;

                 

                    

                EVENTTOTAL_WAITSTIME_WAITED_MICROMAX_WAIT
                SQL*Net message to client202419590
                SQL*Net message from client20231229003822173
                • 20. Re: Fetching CLOB column Faster
                  Dom Brooks

                  Might not be relevant to Informatica but good article on the JDBC options for LOB prefetching here:

                  https://blog.aregger.io/jdbc-lob-prefetching/

                  1 2 Previous Next