Jon
Thanks for the explanation. The environment is currently refreshed with
expdp/impdp and table was in Advance compression due to size as without
compression it is 4-5 TB and with Advance compression, it is 790G. Table later
on is not getting any Update but Insert and this is one time Update due to App
upgrade requirements to multiple tables. Even PCT setting are in place but
additional space due to Row migrations is fine as main target is to reduce the
timing.
Simple Parallel UPDATE is taking 20+ hour with 100 Parallel process and using
50 CPU_count per node and Buffer Cache of 100G CTAS was also used by selecting
column twice but was showing no improvement and taking almost same time
Yes partition can be another good point that can help to reduce the update time
but need to go thru all testing and currently the env need to be moved to 19c
due to 12.2 support ending in Nov and not sufficient time available to redo
performance test.
What are the best way to improve CTAS performance ? or other Bulk operation for
Update/INSERT ?
TxSanjay
On Wednesday, August 26, 2020, 11:00:52 AM EDT, Jonathan Lewis
<jlewisoracle@xxxxxxxxx> wrote:
Is that 3-4 billion rows each, or total ?
I would be a little suspicious of an update which populates a new column with a
value derived from existing columns. What options might you have for declaring
a virtual column instead - which you could index if needed.
Be extremely cautious about calculating space requirements - if you're updating
every row on old data might you find that you're causing a significant fraction
of the rows in each block to migrate, and there's a peculiarity of bulk row
migration that can effectively "waste" 25% of the space in every block that
becomes the target of a migrated row.
This effects can be MUCH work when the table is compress (even for OLTP) since
the update has to decompress the row before updating and then only
"re-compresses" intermittently as the block becomes full. The CPU cost can be
horrendous and you still have the problem of migration if the addition means
the original rows can no longer fit in the block.
If it is necessary to add the column you may want to review "alter table move
online" can do in the latest versions (in case you can make it add the column
as you move) or review the options for dbms_redefinition - maybe running
several redefinitions concurrently rather than trying to do any parallel update
to any single table.
RegardsJonathan Lewis
