Re: How Long to Update a table with 33 Million records

chandra_2000_us_at_yahoo.com (chan01) wrote in message

> This is my config. SunFire 3800 with 745x8 CPUs and 8 GB RAM DB
> 8.1.6.0 Veritas QI file system on Sun T3's.

If you have your rollbacks on the same disk as the tablespace being updated, you will get slower performance. If the tablespace being updated (written to) is not striped, you will get slower performance. One fibre channel to the disk array as opposse to two of three or more, will give you slower performance... and so it goes... There are a myriad of technical configuration detail that determines performance. From the hardware, to the Unix kernel config, to the Oracle config.

A "simple" thing like having 10 indexes for that table will make the UPDATE slow to crawl. What about constraints? What about triggers?

What's more - depending on _what_ you are trying to do, an UPDATE may just be totally the wrong approach. There are other ways to skin VLTs.

> i have a 17 million rows

> in a table which i am updating with 18 byets per record. For 180k
> records to update it took us 2 minutes.

Have you looked at that process to determine what is happening from Oracle's side? The wait states for the process? The events for the process? The stats for the process? Have you looked at parallel DML?

On the operating system - same thing. Have you looked at potential bottlenecks like disk i/o? Page swapping? Context switching? Etc.

Come back with those results to this newsgroup and I'm sure there are people that will be able to provide you with good advice.

But stating a hardware config and expecting us to spew performance numbers and stats at you... I have yet to see a working crystal ball around here.

Q. How long is a piece of string?

1. You take the string, you take measuring tape and you measure it. You do not hold it up to the monitor and ask others how long it is.

--
Billy