Oracle Database Discussions

Hello all,

My table update time is taking too long time. It is taking 120 second to update. But it is supposed to take only 2 second. The table has CLOB data.

The database is running in oracle9i.

Here is the detail info.

1. The table is analyzed very recently. No DML activities after analyze.

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>

2. Total number records in the table is 834682 rows


3. Only one record is updating every update.

4. Here is the update statement.Where clause column is primary key.

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
/

5. 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 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)

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

6. The CLOB column, the input data size is around 5MB

7. The tablespace is LMT and segment space managment is AUTO

8. Here is the table structure.

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
));

I would appreciate if any one could help me on this..