Home » RDBMS Server » Performance Tuning » taking to much time to update (oracle 9i/Linux)
taking to much time to update [message #349755] Tue, 23 September 2008 00:52 Go to next message
tannad
Messages: 43
Registered: January 2008
Location: mumbai
Member
Hi.
I am updating one table in which 26GB of data .
My query is
UPDATE invoice_data
SET supplierid = RTRIM (supplierid)
But its takes to much time ..
from more than one day its still running ..
I checked the locks its show mi folowing output

LOCK Type MODE
TRANSACTION Exclusive
DML Row-X (SX)


How to tune the query and increase the performance?

Please Help
Re: taking to much time to update [message #349764 is a reply to message #349755] Tue, 23 September 2008 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No way.
Or try to parallelize, if you have the hardware and table storage structure for.

Regards
Michel

[Updated on: Tue, 23 September 2008 01:15]

Report message to a moderator

Re: taking to much time to update [message #349771 is a reply to message #349764] Tue, 23 September 2008 01:31 Go to previous messageGo to next message
tannad
Messages: 43
Registered: January 2008
Location: mumbai
Member
how to pallalise it .. please give mi hint
Re: taking to much time to update [message #349778 is a reply to message #349771] Tue, 23 September 2008 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
PARALLEL hint

But you have much more to know and don't forget my warning: only useful if your storage structure and hardware... are appropriate.

Regards
Michel
Re: taking to much time to update [message #349788 is a reply to message #349755] Tue, 23 September 2008 02:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
YOu can't tune the query - it's got to read all the rows of the table, and that's it.

You could fire off a series of DBMS_JOB processes (20, say) each one of which would process a range of id values.
Assuming that you've got the datafiles for this table spread over several disks, that should give you some improvement in speed, but it will slow down everyone else using the system.

Have you had a look at v$session_longops to see how much longer the update has to run?
Re: taking to much time to update [message #350038 is a reply to message #349755] Tue, 23 September 2008 19:55 Go to previous messageGo to next message
reyazan
Messages: 53
Registered: February 2005
Member
pls filter out your update statement by date wise or id wise
Re: taking to much time to update [message #350045 is a reply to message #350038] Tue, 23 September 2008 22:08 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you are updating EVERY row, then it will probably be 10 to 100 times faster to just rebuild the table using CREATE TABLE new AS SELECT ... FROM old

Ross Leishman
Re: taking to much time to update [message #350716 is a reply to message #349755] Fri, 26 September 2008 00:50 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Ross is correct. The problem with large updates like this is that they take a long time to do period. All that disk movement is hard on any database.

If you are updating more than 10% of rows in the table then the usually the best way to get this done in a reasonable time frame is to create a new table. Forget about all the PARALLEL crap. If you feel you must have parallel work happening you can add it to this create statement wiht the PARALLEL clause, but you better have the CPU available, and the storage specifics set up right.

use something like this:

create table newtable
nologging
as
select ...
from oldtable
/

create bunch of indexes (use parallel)
setup proper grants
might need to set logging on for the newtable and its indexes, don't remember
rename old table
rename new table

[do a backup that night]

drop the old table

You can do 1 million row inserts per minute easy this way. You will see your job goes from days to hours.

You will eventually use this create table method. You just need to suffer enough first before you accept it.

Good luck, Kevin
Previous Topic: How to delete records without generating redo logs?
Next Topic: SQL execution time based on ash history
Goto Forum:
  


Current Time: Tue Nov 26 09:43:20 CST 2024