Re: Multi column update
Date: Fri, 13 Nov 2009 07:57:45 -0800 (PST)
Message-ID: <f768c978-db85-458d-a3d0-b72a8f3c3ed6_at_d5g2000yqm.googlegroups.com>
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
> inefficiency.
> Any ideas on how to make it run quicker?
> Thanks,
> Sashi
Sashi,
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
TEMP_B
SELECT
B4,
SUM(B1) B1, SUM(B2) B2, SUM(B3) B3
FROM
T4 B
GROUP BY
B4;
UPDATE
T3 A
SET
(A1,A2,A3) =
(SELECT
B1,
B2,
B3
FROM
TEMP_B B
WHERE
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.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Nov 13 2009 - 09:57:45 CST