Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Fetching CLOB column Faster

User_OCZ1TJul 30 2019 — edited Aug 5 2019

Hi, We are using version 11.2.0.4 of oracle exadata. We have below query which fetch the data and is getting executed from informatica. And its just a "SELECT * from TAB1" query. It was taking ~1hr for fetching ~135k rows and then from the sql monitor we found all the time was just spent on client for fetching data. And then we got to know it has one CLOB column which is causing the issue, if we comment the CLOB column(C10) , the data fetch is finishing in few seconds. So as an alternative we were using below SUBSTR option to fetch the column C10 and it was helping us to finish the query in few seconds. But suddenly we got to see failure for this query with error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" and then its found its failing because of few of the values came into column C10 which were holding values >4000 bytes.  So want to understand if there is any alternate way we can fetch the clob column here without fail and for large value(>4000bytes)?

Query:-

SELECT c1,c2,c3...c39 FROM TAB1;

Alternate option to fetch column C10:-

DBMS_LOB.SUBSTR (C10,(SELECT MAX (LENGTH (C10)) FROM TAB1)) C10

Error:-

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 1

Below is the sql monitor for one of the slow execution which we used to see with CLOB column fetched in full:-

Query:-

SELECT c1,c2,c3...c39 FROM TAB1;

Global Information

------------------------------

Status              :  EXECUTING                

Instance ID         :  4                        

SQL Execution ID    :  67108864                 

Execution Started   :  04/09/2018 06:02:49      

First Refresh Time  :  04/09/2018 06:02:49      

Last Refresh Time   :  04/09/2018 06:40:45      

Duration            :  2277s                    

Module/Action       :  SQL*Plus/-               

Program             :  sqlplus.exe              

Fetch Calls         :  26415                    

Global Stats

=================================================

| Elapsed |   Cpu   | Cluster  | Fetch | Buffer |

| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |

=================================================

|    0.69 |    0.69 |     0.01 | 26415 |  27031 |

=================================================

SQL Plan Monitoring Details (Plan Hash Value=2531190874)

============================================================================================================================================================

| Id   |          Operation          |        Name        |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail | Progress |

|      |                             |                    | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |          |

============================================================================================================================================================

| -> 0 | SELECT STATEMENT            |                    |         |      |      2278 |     +0 |     1 |    26417 |          |                 |          |

| -> 1 |   TABLE ACCESS STORAGE FULL | TAB1               |    135K | 7212 |      2278 |     +0 |     1 |    26417 |          |                 |       6% |

============================================================================================================================================================

This post has been answered by AndrewSayer on Jul 31 2019
Jump to Answer

Comments

Frank Kulash

Hi, @user-r4vjd
How can we write this code efficiently in oracle 11g
Don't use cursors unless you really need to. Depending on what you're trying to do, you can do it in a single query. If you really do need to use a cursor, use only one; combine the two cursors into one. Exactly how depends on your data and your requirements. If you'd care to post some sample data (CREATE TABLE and INSERT statements) for all tables involved, the results you want from that sample data, and explanation (in general terms) of how you get those results from that data, and the code behind the other cursor, then I could try to show you exactly how to do it.

User_R4VJD

Please avoid that cursor details.
Just help in writing code efficiently

GregV

Hi,
As Frank said, don't use cursors if you don't need to, especially here as you seem to be interested only in getting a max date.
Combine both queries and retrieve the max dates directly.
By the way, are you sure about this kind of condition:
AND T1_cln =NVL (p_vent, '%')
Did you mean a LIKE operator since you're using '%', or is '%' really stored as is?

Jonathan Lewis

There is no purely logical reason why that code should be inefficient, given the correct constraints and indexing there would be an extremely efficient execution path to produce the required result.
It's possible, however, that in your specific case the data model means the relevant constraints are not valid.
Note that the "via = (select from T2)" means the subquery MUST logically be able to return exactly one row. (If it returns more than one then Oracle will raise an error, if it returns no rows you have effectively got a predicate "via = null" which does not evaluate to true (this is not an error, but it might not be what you were expecting).
As the previous two responses have implied - is this query inefficient, or is it simply that the surrounding pl/sql cursor approach means it executes a very large number of times, accumulating a much greater volume of work that seems reasonable.
Regards
Jonathan Lewis

EdStevens

Please avoid that cursor details.
Just help in writing code efficiently
But it is most likely that the cursor IS the source of any inefficiency.
I'm always amazed when people ask for help solving a problem, but try to take the most likely solutions off the table.

BluShadow

We don't have your data, nor have you provided details of the cur1 and cur2 cursors, nor shown the full PL code nor described the logic you are trying to achieve, so that kinda makes it hard for us to help fully.
From what I can gather, you've got cur1 as a main cursor, then cur2 is likely a cursor that opens up inside that cur1 cursor based on some related data from cur1, and then you're querying the MAX value from T1 using some related data in T2 based on both of the cursors.
As others have already said, having cursor looping is more likely to slow things down as you'll be issuing your inner query for each loop of the inner cursor. This is kind of "row by row" or otherwise known as "slow by slow" processing.
If you can, you should consider combining your cursors into a single SQL query, something along the lines of...

SELECT T1.c_cd, T1.d_cd, T1.l_cd, T1.l_sub_1, max(T1.generation_date)
FROM   (<query of cur1>) cur1
       JOIN (<query of cur2) cur2 on (<whatever join conditions related cursor 2 to cursor 1)
       JOIN T1 on (    T1.c_cd = cur1.c_cd
                   AND T1.d_cd = cur1.d_cd
                   AND T1.l_cd = cur1.l_cd
                   AND T1.T1_sub_l = cur2.sub_lat
                  )
       JOIN T2 on (    T2.via   = T1.via               -- equivalent via comparison
                   and T2.l_cd  = T1.l_cd              -- T1.l_cd = cur1.l_cd
                   and T2.sub_l = T1.T1_sub_l          -- T1.T1_sub_l = cur2.sub_lat
                   and T2.cln   = T1.T1_cln            -- T1.T1_cln = NVL(p_vent, '%')
                  )
WHERE  T1.T1_cln = NVL(p_vent, '%')
AND    T2.p_cd   = NVL(p_pri_code, '%')
GROUP BY T1.c_cd, T1.d_cd, T1.l_cd, T1.l_sub_1

Your "via = (select via from T2 where ...) was essentially using many of the same conditions from your cursors as you were using with T1, so it makes more logical sense to just treat it as a join to T2 instead using the values you've already compared with T1.
Then bring in your cursors as the main drivers of the query, starting with cur1, joining that to cur2 on whatever conditions are needed to relate them, and then join T1 to the results of those.
You'll then get results of your MAX for all the T1 records in one go, so you'll need to include the columns from T1 in the select and group by them, so you've got the MAX for each one.
Whether you stick all that in one cursor and process that one by one is up to you... depends what you're doing with those results.

1 - 6

Post Details

Added on Jul 30 2019
20 comments
28,236 views