Re: How to accelerate UPDATE SQL?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Mon, 04 Feb 2002 10:17:06 GMT
Message-ID: <3c5e5ab4.2175520211@news.saix.net>

Dino Hsu <dino1.nospam_at_ms1.hinet.net> wrote:

Inserts and updates can become very slow if there are a large number of overheads on the tables - e.g. indexes, constraints, triggers and so on. This gets very noticable when dealing with very large tables. One of many things to remember to look at when transaction performance is slow. :-)

>insert into INVENTORY
> (select
> a.DEF_DATE,
> a.SRCODE,
> a.FSC,
> b.SALESQTY + b.EXCHANGEQTY - b.COUNTQTY OHQty,
> b.SALESQTY CTDQty,
> '0' TYPE
> from OHCTD a, FSRDRP b, INVENTORY c
> where
> a.SRCODE = b.SRCODE and
> a.FSC = b.FSC and
> a.SRCODE = c.SRCODE (+) and
> a.FSC = c.FSC (+) and
> c.SRCODE is null);

Hmmm.. this seems to be pretty straight forward. I suggest that you take the select on its own first, put it through explain plan even tkprof it, if necessary. Make sure that the joins are done correctly - merge and nested loop joins can become messy at times when CBO makes the wrong decision. For example, a nested loop join should be done from the smaller table using a nested loop to find the corresponding row in the larger table. IOW, you plough through the smaller data set, using an index scan in a nested loop on the larger data set. Similarly, merge joins work well in certain situations and exceedingly bad in others.

Can not really blame the CBO when it works without sufficient or with incorrect stats.

>update INVENTORY c
>set (OHQty, CTDQty)=
> (select
> b.SALESQTY + b.EXCHANGEQTY - b.COUNTQTY OHQty,
> b.SALESQTY CTDQty
> from OHCTD a, FSRDRP b
> where
> a.SRCODE = b.SRCODE and
> a.FSC = b.FSC and
> a.SRCODE = c.SRCODE and
> a.FSC = c.FSC);
>

Again, this seems to be pretty straight forward stuff. Same suggestion as above. Isolate the SELECT first, analysis that, and then analyse the complete statement.

What you can do while the above is running, is to get the SID of the session doing the update, and look at the events (v$session_waits and v$session_events) and stats (v$sesstat) for that session.

If the v$session_waits point to db file reads or writes, you can use the following SQL to determine exactly what database object it is accessing:
SELECT

  SUBSTR(w.event,1,30)  EVENT,
  SUBSTR(f.tablespace_name,1,20) TABLESPACE,
  SUBSTR( owner||'.'||segment_name, 1,60) SEGMENT,
  RTRIM( SUBSTR(w.p1text,1,20)||'='||SUBSTR(w.p1,1,10)) PARAM1,

  RTRIM( SUBSTR(w.p2text,1,20)||'='||SUBSTR(w.p2,1,10)) PARAM2,   RTRIM( SUBSTR(w.p3text,1,20)||'='||SUBSTR(w.p3,1,10)) PARAM3 FROM v$session_wait w, dba_extents f

WHERE w.p1 = f.file_id
AND   w.p2 BETWEEN ( f.block_id ) AND ( f.block_id + f.blocks )
AND   w.sid = <insert SID here>

It is not much use for realtime monitoring, but if a session is stuck doing tons of i/o, you can get a fair idea of what is doing.

For example - some years had this guy who had a huge problem with running updates on what could have been called a VLT at the time. I expected to see a lot of write events in v$session_events. Instead, most of the time was spend on waiting for read events. Running the above SQL we saw that it was scanning the indexes. The VLT had several of these massive indexes - which required index range scans before the row can be inserted or created. Every single write required (update/insert row) required a huge number of reads on the indexes.

Dropping the indexes changed the update process from a runtime of 20 hours (incompleted) to a run that completed in less than a hour. Putting the indexes back afterwards was a breeze and also addressed the problem of fragmentation. Of course, this was possible as we only dealt with unique data and there were no unique and foreign key constraints to consider.

Use the v$ tables and explain plan. IMO there is a lot you can do with these when it comes to addressing performance issues.

--
Billy

Received on Mon Feb 04 2002 - 04:17:06 CST