| update is slow with CLOB data [message #330293] |
Sat, 28 June 2008 23:11  |
shrinika
Messages: 306
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  |
 |
BlackSwan
Messages: 26766
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 |
shrinika
Messages: 306
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 #330935 is a reply to message #330933] |
Tue, 01 July 2008 18:08 |
shrinika
Messages: 306
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 #330943 is a reply to message #330938] |
Tue, 01 July 2008 21:08 |
shrinika
Messages: 306
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 #332463 is a reply to message #331097] |
Tue, 08 July 2008 10:47 |
shrinika
Messages: 306
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
