AND (not but, JL’s note is an example of knowing a lot and thinking clearly;
that’s the norm for JL but segregating the four key problems so succinctly is
excellent even for JL),
it appears you have four disjoint P.CODE values drawn from P. If those are a
small fraction of P, you might do well pulling the required columns for P into
P_XX, P_YY, …, etc. as GTTs or materialized inline views as well as starting
with GTTs GTT_TAB_XX, _YY, …, etc. to begin with instead of mixing them up and
then filtering through them.
Don’t ignore the if regarding the part before the “as well as.” Since they are
disjoint sets, breaking up the initial GTT makes sense regardless of the
fraction of each with respect to P. I doesn’t make sense to mix them together
and then filter through them. This, of course, needs to be done in place of how
GTT_TAB is currently created. Don’t screw up where you use ‘AA’ twice, and you
may want to pull ‘XX’ into two different GTTs, one where CIND is ‘Y’ and the
other where CIND is something else.
If you’re using the Tim Gorman “Scaling to Infinity” (the fastest update is an
insert) approach, you may want to destroy/purge each GTT as soon as you are
done with it. Materialized inline views will take care of themselves but you
can index GTTs at a sufficient release.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Monday, May 03, 2021 4:55 AM
To: Lok P
Cc: Oracle L
Subject: Re: Tuning Update query
If you're updating many columns of every row in a large (global temporary)
table using lots of separate statements that use the "set .. subquery"
strategy you are introducing 4 problems.
a) Updating lots of columns means you will probably start doing lots of row
migration - and you can't set pctfree on GTTs to avoid this, it WILL be 10%
b) You will be generating a huge amount of undo
c) If you aren't including a "where exists" predicate on the source table that
matches the predicate on the set subquery you will be updating rows to null
that don't need to be updated - the monitor report echoed by Mladen showed 17M
rows updated, but only 8M executions of the subquery - which probably means 9M
rows in the GTT updated to NULL.
In your specific case there's (probably) a logic flaw in the update statement.
You have a "where tmp.cind='Y'" in the subquery. This means (effectively) set
the values to NULL when tmp.cind ='Y', NOT 'don't update the rows where
tmp.cind='Y'. If you want the update to be selective then you need the
predicate as part of the where clause on the update.
Finally -
If this is a table of roughly 17M rows (rather than the 8billion of the
estimate) then the fastest strategy would probably be to
a) See if you can minimise the number of update ste[ps by updating from a JOIN
of several source tables
b) Rewrite the code to step through a series of GTT doing
insert /*+ append */ into next_gtt select from previous_gtt join
{source tables}
Using insert /*+ append */ with gtts will:
a) use bulk processing rather that the row by row processing for filter
subqueries
b) miimise BOTH undo and redo.
Regards
Jonathan Lewis
On Sun, 2 May 2021 at 19:53, Lok P <loknath.73@xxxxxxxxx> wrote:
Hello, We have a plsql procedure in which it updates multiple columns of the
global temporary table after loading it in different steps. It's updated for
different filter criteria and each of these UPDATES are running for hours even
if they are simple ones. I am trying to see if it's possible to do it in an
efficient fashion by combining all different updates into one UPDATE statement
or any other possible method? One initial thought was to do it as part of the
INSERT itself rather than updating it through multiple statements at the later
stage , but then I see the data load/INSERT is happening for multiple if/else
conditions which may not be straight enough.
Attached is the sql monitor for few of the long running UPDATES. And i am
seeing this is not really spending time for updating rows but rather in finding
the eligible rows for update i.e. the SELECT part of it is actually taking
time. So wanted to understand how these UPDATES can be done faster?
Its version 11.2.0.4 of Oracle Exadata-X5.
Regards
Lok
