Home » RDBMS Server » Performance Tuning » update is slow with CLOB data (oracle9i)
update is slow with CLOB data [message #330293] Sat, 28 June 2008 23:11 Go to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
All, I have a problem with update statement.

I have a table with CLOB data type. Here is the table structure.

The tablespace is ASSM set to auto and it is LMT tablespace.

CREATE TABLE collection
( 
  STORY_ID             NUMBER(10), 
  HEADLINE             VARCHAR2(80 BYTE), 
  WEB_BODY             CLOB, 
  SOURCE_PUBLISHED_DT  DATE, 
  STATE                VARCHAR2(10 BYTE), 
  RECORD_CREATED       DATE, 
  RECORD_MODIFIED      DATE, 
  AUTHOR               VARCHAR2(20 BYTE), 
  LOCATION_ID          VARCHAR2(5 BYTE) 
) 
TABLESPACE DATA_APP 
PCTUSED    0 
PCTFREE    10 
INITRANS   1 
MAXTRANS   255 
STORAGE    ( 
            INITIAL          1M 
            NEXT             1M 
            MINEXTENTS       1 
            MAXEXTENTS       2147483645 
            PCTINCREASE      0 
            BUFFER_POOL      DEFAULT 
           ) 
LOGGING 
NOCOMPRESS 
LOB (WEB_BODY) STORE AS 
      ( TABLESPACE  DATA_APP 
        ENABLE      STORAGE IN ROW 
        CHUNK       4096 
        PCTVERSION  10 
        NOCACHE 
        STORAGE    ( 
                    INITIAL          1M 
                    NEXT             1M 
                    MINEXTENTS       1 
                    MAXEXTENTS       2147483645 
                    PCTINCREASE      0 
                    BUFFER_POOL      DEFAULT 
                   ) 
      ) 
NOCACHE 
NOPARALLEL 
NOMONITORING; 


CREATE INDEX idx_COLLECTION_IDX ON  COLLECTION 
(RECORD_CREATED, RECORD_MODIFIED) 
LOGGING 
TABLESPACE APP_INDX_02 
PCTFREE    10 
INITRANS   2 
MAXTRANS   255 
STORAGE    ( 
            INITIAL          1M 
            NEXT             1M 
            MINEXTENTS       1 
            MAXEXTENTS       2147483645 
            PCTINCREASE      0 
            BUFFER_POOL      DEFAULT 
           ) 
NOPARALLEL; 


CREATE UNIQUE INDEX PK_MWS_COLLECTION ON MWS_COLLECTION 
(STORY_ID) 
LOGGING 
TABLESPACE APP_INDX_02 
PCTFREE    10 
INITRANS   2 
MAXTRANS   255 
STORAGE    ( 
            INITIAL          1M 
            NEXT             1M 
            MINEXTENTS       1 
            MAXEXTENTS       2147483645 
            PCTINCREASE      0 
            BUFFER_POOL      DEFAULT 
           ) 
NOPARALLEL; 


ALTER TABLE COLLECTION ADD ( 
  CONSTRAINT PK_MWS_COLLECTION 
 PRIMARY KEY 
 (STORY_ID) 
    USING INDEX 
    TABLESPACE APP_INDX_02 
    PCTFREE    10 
    INITRANS   2 
    MAXTRANS   255 
    STORAGE    ( 
                INITIAL          1M 
                NEXT             1M 
                MINEXTENTS       1 
                MAXEXTENTS       2147483645 
                PCTINCREASE      0 
               )); 


The update statement is extremely slow. Any lights on this??

The update statement is running through java code. UPdate statment where clause is store_id which is primary key. The table data records are around two million. Any general suggestion???

Re: update is slow with CLOB data [message #330294 is a reply to message #330293] Sat, 28 June 2008 23:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>The update statement is extremely slow
WHAT update statement?

Why in the world do you think we can tune SQL we have never seen?

Unless & until you tell us where time is being spent, nobody will have any clue as to how it might be made faster!

When are you going to learn how to ask meaningful questions with sufficient details that answers are feasible.

Are you incapable or unwilling to RTFM found at http://tahiti.oracle.com?

PLEASE place this in context to the business problem that needs to be solve.

You're On Your Own (YOYO)!

[Updated on: Sun, 29 June 2008 00:41] by Moderator

Report message to a moderator

Re: update is slow with CLOB data [message #330931 is a reply to message #330294] Tue, 01 July 2008 17:30 Go to previous messageGo to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
Hello,

I am sorry for not giving the detail info.

Here is the update statement.

update mws_collection
set                
 HEADLINE = HEADLINE  ,       
 WEB_BODY = WEB_BODY   ,           
 SOURCE_PUBLISHED_DT    = SOURCE_PUBLISHED_DT,
 STATE    = STATE              ,
 RECORD_CREATED  = RECORD_CREATED,       
 RECORD_MODIFIED        = RECORD_MODIFIED,
 AUTHOR =AUTHOR                ,
 LOCATION_ID   = location_id
where story_id= 79634
/


The update statement completes in 145 seconds. But it was used to complete in 2 second. This update statement is running in front end applcation(The application is developed in JAVA code).

Here is tkprof output.


update MWS_COLLECTION set WEB_BODY=:1, headline=:2, STATE=:3, 
  SOURCE_PUBLISHED_DT=:4, author=:5 
where
 STORY_ID=:6


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     55.99     145.55       1057    9495719       8238           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     55.99     145.56       1057    9495719       8238           1

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 67  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  UPDATE  
      1   TABLE ACCESS BY INDEX ROWID MWS_COLLECTION 
      1    INDEX UNIQUE SCAN PK_MWS_COLLECTION (object id 139890)

********************************************************************************


I will be really thankful if any one could help me on this. Thanks in advance.
Re: update is slow with CLOB data [message #330932 is a reply to message #330293] Tue, 01 July 2008 17:39 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
1) When was the last time statistics gathered on table & index?
2) How much DML has occurred since statistics collected?
3) How many total rows in table?
4) How many rows updated?
Re: update is slow with CLOB data [message #330933 is a reply to message #330932] Tue, 01 July 2008 17:42 Go to previous messageGo to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
The last_analyzed column is showing 6/28/2008 6:50:58 PM

But i am going to analyze now and run the tkprof again and reply back soon.

Thanks
Re: update is slow with CLOB data [message #330935 is a reply to message #330933] Tue, 01 July 2008 18:08 Go to previous messageGo to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
I just analyzed the table.

SQL> 

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'MWSADMIN',TABNAME => 'MWS_COLLECTION',ESTIMAT
E_PERCENT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', CASCADE => TRUE);

PL/SQL procedure successfully completed.

SQL> 
SQL> 


Again i user ran the application. Here is the tkprof result.

update MWS_COLLECTION set WEB_BODY=:1, headline=:2, STATE=:3, 
  SOURCE_PUBLISHED_DT=:4, author=:5 
where
 STORY_ID=:6


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      9.42     114.49         39    1351029       1341           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      9.42     114.49         39    1351029       1341           1

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 67  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  UPDATE  
      1   TABLE ACCESS BY INDEX ROWID MWS_COLLECTION 
      1    INDEX UNIQUE SCAN PK_MWS_COLLECTION (object id 139890)

********************************************************************************



Here is answer for your question.

1) When was the last time statistics gathered on table & index?
Just now analyzed.
2) How much DML has occurred since statistics collected?
I collected tkprof result as soon as the statistics generated.
3) How many total rows in table?
834682 rows

4) How many rows updated?
I updated only one row through the application. The update statement where clause is primary key.

The table structure is provided in the begining of the thread.

I hope, i answered all your questions.

Thanks

Re: update is slow with CLOB data [message #330938 is a reply to message #330293] Tue, 01 July 2008 18:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
What is the elapsed time doing the following UPDATE?
update MWS_COLLECTION set headline=:2, STATE=:3, 
  SOURCE_PUBLISHED_DT=:4, author=:5 
where
 STORY_ID=:6

How much data is contained within CLOB?
From where is the CLOB data originating for this UPDATE?
Re: update is slow with CLOB data [message #330943 is a reply to message #330938] Tue, 01 July 2008 21:08 Go to previous messageGo to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
What is the elapsed time doing the following UPDATE?

Around 120 second. But i was running for only 2 second.

update MWS_COLLECTION set headline=:2, STATE=:3,
SOURCE_PUBLISHED_DT=:4, author=:5
where
STORY_ID=:6

How much data is contained within CLOB?

around 5MB

From where is the CLOB data originating for this UPDATE?

Analyst create the document and java appliation update the document into the clob column.
Re: update is slow with CLOB data [message #331097 is a reply to message #330943] Wed, 02 July 2008 05:31 Go to previous messageGo to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member

The tablespace is localled managed, SSM is AUTO. FYI.

I am not sure why tkprof result EXECUTE part is showing too high.. Any lights??
Re: update is slow with CLOB data [message #332463 is a reply to message #331097] Tue, 08 July 2008 10:47 Go to previous message
shrinika
Messages: 267
Registered: April 2008
Senior Member
Business agreed to purge 80% of the data in table. So my problem resolved with below steps.

1. cloned the 20% of the data into another table
2. Truncate the original table
3. Insert the 20% of the data into original table
4. analyze the table

Now the problem resolved. FYI.
Previous Topic: Optimizing the Query
Next Topic: Query plan change
Goto Forum:
  


Current Time: Sat Dec 10 20:27:37 CST 2016

Total time taken to generate the page: 0.09312 seconds