Re: Multi column update
From: Sashi <smalladi_at_gmail.com>
Date: Tue, 17 Nov 2009 20:10:21 -0800 (PST)
Message-ID: <0d49ca5d-d7c9-4e95-a105-f316407ddde1_at_l13g2000yqb.googlegroups.com>
On Nov 13, 10:57 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.
Date: Tue, 17 Nov 2009 20:10:21 -0800 (PST)
Message-ID: <0d49ca5d-d7c9-4e95-a105-f316407ddde1_at_l13g2000yqb.googlegroups.com>
On Nov 13, 10:57 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.
Hi Charles, I had a real duh moment when I saw your post.
Of course! Just use a temp table to store the intermediate values and
avoid the (9x10^6 * 132 ) comparisons!!
Thanks a lot for the pointer. I'm using a temp table and the update
runs in less than a second.
Talk about efficiency!!
Regards,
Sashi
Received on Tue Nov 17 2009 - 22:10:21 CST