I have the table and it had 5 columns
CREATE TABLE LIS.LD_EXHIBIT_DESC
AGMT number (10) NOT NULL,
EX VARCHAR2(4000) NOT NULL,
SYSGEN VARCHAR2(1) NULL,
UPDT_DATE DATE NULL,
UP VARCHAR2(12) NULL,
LINE_NO NUMBER(3) NULL
I try to update the column LINE_NO=5 and it takes forever,
the table has about 40K now.
What do I need to to do to speed up this process ???
02-28-2002, 03:56 PM
Please help , it's urgent
02-28-2002, 03:57 PM
run a trace with explain plan and check what is it waiting for and doing.
02-28-2002, 03:59 PM
use bot of these and check how many records are used and
How much more time it will take ..
check whether you have space for rollback space ...
select l.sid sid,p.spid PID,NVL ( s.username, 'NO TRANSACTION' ) USERNAME,
NVL ( s.osuser, '----' ) osuser,
s.terminal TERMINAL ,
FROM v$lock l, v$process p, v$session s, v$rollname r ,V$TRANSACTION T
WHERE l.sid = s.sid(+) AND TRUNC (l.id1(+)/65536) = r.usn
AND l.type(+) = 'TX' AND l.lmode(+) = 6 AND s.paddr = p.addr
and s.taddr=t.addr ORDER BY r.name
to_char(START_TIME,'DD-MON-YYYY HH:MI:SS PM')"START_TIME",
to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS PM')"SYSDATE",message
from v$session_longops where TIME_REMAINING>0
02-28-2002, 04:10 PM
Thanks for your reply
I did run the explain plan and here is the result:
table access full, obviously it will scan the full table since we update everyhting on there
I did run your query and here is the result
I didn't see anything on the longop, any other advise ????
02-28-2002, 04:16 PM
i meant to say run dbms_system.set_ev(sid,serial#,10046,8,'')
and check waits and then run tkprof to get the
i/o and cpu usage.
02-28-2002, 04:19 PM
First if You update a big table you should assign a big rollback segment with a high initial and next extent
Ensure there is proper log switching ...
If checkpoint problem is there your update will take longer(check in the alert log)
If there is a problem add one or two more log membets ...
if possible while doing mass update do that as nightly job