SQL Performance (MOSC)

MOSC Banner

Update CLOB column is very slow in 11g R2 ( 11.2.0.4 )

edited Nov 28, 2014 10:02AM in SQL Performance (MOSC) 5 commentsAnswered
we have recenly migrated our databases from 11.1.0.7 to 11.2.0.4. we are facing performance issues after the upgrade in update queries (which include CLOBS).

We have 2 tables, please find description below -


SQL> desc ade_data.TEMP_ADV_VALUES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_ID                                            CLOB
 ADV_ID                                             VARCHAR2(100)
 TYPE                                               CHAR(14)


 SQL> desc ade_Data.gad_search_stage
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(200)
 ADV_ID                                             NUMBER
 TYPE                                               VARCHAR2(32)
 TIME_STAMP                                         TIMESTAMP(6)
 FULLDATA                                           CLOB
 USER_ID                                            CLOB
 ACTIVE                                             NUMBER(1)

the update statement we run is below -

UPDATE GAD_SEARCH_STAGE GSO SET USER_ID = (SELECT USER_ID FROM TEMP_ADV_VALUES WHERE ADV_ID = GSO.ADV_ID );

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center