update takes much time to execute [message #310824] |
Wed, 02 April 2008 09:10 |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Hi,
In the TKprof output i can see that the following update query tooks much time to execute.
This took me 1 hr to execute and it actually updates 37 rows in the table.
Table is having 150 millian records.
No primary key.
Index on (DB_ID,Rep_ID), (DB_ID)
This happens for only one record.
Please tell me why this query takes so much time?
What are the factors i need to take care to imrove the performnace of the query.
UPDATE DEMO.REPOSITORY A SET A.VALUE = REPLACE(:B4 ,'"','"')
WHERE
A.DB_ID = :B3 AND A.Rep_ID=:B2 AND A.F_ID=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 135 0.12 4438.73 0 1348 297 135
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 136 0.12 4438.74 0 1348 297 135
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 1446 (recursive depth: 1)
Thanks in advance.
|
|
|
|
|
Re: update takes much time to execute [message #311067 is a reply to message #310834] |
Thu, 03 April 2008 05:44 |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
There are existing of the trigger but in that trigger there only 2 columns updated.
CREATE OR REPLACE TRIGGER upd_Repo
BEFORE UPDATE
ON REPOSITORY
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF USER<>'REPO' THEN
:NEW.syschangedby:=USER;
:NEW.syschanged:=SYSDATE;
END IF;
END;
And when i do execute the update query with 1 record it is updating the record but for 37 rows why it is taking so much time i dont know?
Thanks,
[Updated on: Thu, 03 April 2008 05:45] Report message to a moderator
|
|
|
|
|