Re: Multi column update

From: Charles Hooper <hooperc2000_at_yahoo.com>
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

Original text of this message