Re: Multi column update
Date: Fri, 13 Nov 2009 07:57:45 -0800 (PST)
On Nov 11, 6:32 pm, Sashi <small..._at_gmail.com> wrote:
> On Nov 9, 5:19 pm, Sashi <small..._at_gmail.com> wrote:
> > Charles,
> > I should've been more careful with my post, I guess.
> > It works like a charm.
> > Thanks for your time.
> > Sashi
> The sub-query runs in about 90 seconds and comes up with a result set
> with 132 records. In my example, this would be the B table. The A
> table also has the same number of records.
> However, the update runs for about 22 minutes.
> Since the subquery runs rather fast (and it has to go thru about 9
> million rows to come up with this summary), I'm surprised that the
> update should take so long.
> The example I've given is a sort of simplified version of my query,
> but it's a very good approximation.
> i.e., select sum(b1), sum(b2), sum(b3) from B group by b4 runs in less
> than 90 seconds.
> So it's safe to assume that the join condition is causing the
> Any ideas on how to make it run quicker?
I must have missed your follow up post. Robert offers a good suggestion to determine why the update is slow.
If I had to make a guess, I would guess one of three things:
* The update is taking 3 columns of table A with 9 million rows from a
NULL value to a non-null value. This will increase the space
requirements for each row, and could cause performance problems as
Oracle tries to find room for the expanded rows. Based on what has
been report in various forums (and my own tests) this appears to be
more of a problem in ASSM tablespaces, especially when a block size
larger than 8KB is used. With Robert's suggestion you would see a
large number of current mode block gets. Jonathan Lewis has written a
bit about finding a related bug in ASSM tablespaces which use a block
size larger than 8KB when column values are updated, which causes the
row to expand, and Oracle to visit many blocks in an attempt to find
one with sufficient free space.
* The UPDATE statement is generating a lot of redo, with the speed limited by how fast the server is able to write the redo information. This may be aggravated by the need to relocate the rows due to the first problem mentioned.
* The query is performing the GROUP BY operation on table B for each row in table A. You might consider defining a temp table that clears its contents ON COMMIT, insert the rows from the subquery into the temp table, and then reference that temp table with your update query: INSERT INTO
SUM(B1) B1, SUM(B2) B2, SUM(B3) B3
B.B4 = A.A4);
A 10046 trace at level 8 might provide enough information to determine what is happening - make certain that you execute a simple SQL statement, such as SELECT SYSDATE FROM DUAL; after the update statement to increase the chances that the row source execution plan will be written to the trace file. Take a look at the TKPROF interpreted version of the 10046 trace file to help determine what it takes so long for the update.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Nov 13 2009 - 09:57:45 CST