Home » RDBMS Server » Performance Tuning » update takes much time to execute
update takes much time to execute [message #310824] Wed, 02 April 2008 09:10 Go to next message
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 #310828 is a reply to message #310824] Wed, 02 April 2008 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
make sure statistics are current
>A.DB_ID = :B3 AND A.Rep_ID=:B2 AND A.F_ID=:B1
Make sure that the data type of the bind variables matches the datatypes of the columns.

Post EXPLAIN_PLAN, please.
Re: update takes much time to execute [message #310834 is a reply to message #310824] Wed, 02 April 2008 09:55 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Do you have some triggers attached to the table?
Re: update takes much time to execute [message #311067 is a reply to message #310834] Thu, 03 April 2008 05:44 Go to previous messageGo to next message
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

Re: update takes much time to execute [message #311086 is a reply to message #311067] Thu, 03 April 2008 06:57 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
if possible , do the same thing in trigger for
"for each statement"
Re: update takes much time to execute [message #311228 is a reply to message #310824] Thu, 03 April 2008 15:51 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Post complete TKPROF of event 10046 (with waits):

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';


Michael
Previous Topic: Which Performance hint we should use when?
Next Topic: How to increase the database size of oracle 9i
Goto Forum:
  


Current Time: Tue Nov 26 18:02:47 CST 2024