Home » RDBMS Server » Performance Tuning » Performance tunning
Performance tunning [message #256788] Mon, 06 August 2007 12:14 Go to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member
i Have this update statement, It contains 32000 row but taking alot of time to execute.
update LC_FEED.raw_hostfut_1 set EXECUTION_ID = TRANSACTION_ID_SEQ.NEXTVAL
WHERE EXECUTION_ID is null
and information_source_code = 84


Explain Plan:

Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=11261 Card=1 Bytes=8
          )

   1    0   UPDATE OF 'RAW_HOSTFUT_1'
   2    1     SEQUENCE OF 'TRANSACTION_ID_SEQ'
   3    2       COUNT (STOPKEY)
   4    3         TABLE ACCESS (FULL) OF 'RAW_HOSTFUT_1' (Cost=11261 C
          ard=1 Bytes=8)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     117177  consistent gets
     117075  physical reads
          0  redo size
        475  bytes sent via SQL*Net to client
        464  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed


Thanks in advance
Oracle 9.0.1.0

[Updated on: Mon, 06 August 2007 12:16]

Report message to a moderator

Re: Performance tunning [message #256856 is a reply to message #256788] Mon, 06 August 2007 22:59 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Does it update 32000 rows? Or does the table contain 32000 rows in total?

If the table contains more rows, how many more? If it contains 1,000,000,000 then you cannot expect a Full Table Scan to be really quick, even if it only updates 32000.

Ross Leishman
Re: Performance tunning [message #256927 is a reply to message #256788] Tue, 07 August 2007 03:12 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Additional questions:
1. How many distinct values exist for information_source_code column?
2. How many rows have information_source_code = 84?

Michael
Re: Performance tunning [message #257049 is a reply to message #256927] Tue, 07 August 2007 08:15 Go to previous message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member
Thanks for everybody's interest. I figure out the problem. Actually, Problem was high mark value. This table was in the test environment and every time, they were using the delete statement to remove the old data. But, I truncate the table and then load the data again, that's how i overcome the problem.

Thanks again..
Previous Topic: Performance problem when using Analytic Functions. Need Help !!!
Next Topic: Performance Slow - Jobs
Goto Forum:
  


Current Time: Tue Nov 26 22:58:49 CST 2024