Re: What will be the rollback plan for a table move + index rebuild operation?

I was a bit nervous on the performance perspective as we may not justify the storage gain in exchange for significant performance overhead. And as i mentioned, in the real scenario we have bulk Update and bulk inserts running in 10+ threads though, but i tried testing by running four sample Update queries in four sessions simultaneously for updating the column C1 and C2 from null to not null. All those sessions were updating ~12million rows each in a bulk update fashion , with a bulk limit of ~1000.

I tried multiple random combinations of PCTFREE and INITRANS for table and indexes. And below are the results. What I see from here is, there is not much difference in the response time between these combinations even though I am dropping the pctfree all the way from current value of ~30 to default ~10 and initrans from current value of 40 to default 1/2 for the table and indexes respectively.

So I was thinking if we should set the pctfree for table and index both to default 5 or 10 max for the active partitions. And initrans to 1 and 2 for table and index partitions respectively. Please correct me if i am doing it wrong.
  Session - 1 - (12million Update) Session-2 - (11million Update) Session-3 - (11million Update) Session-4 -(14million update) Table size , Index size Table - pct free - 10 , initrans -20 , Index - pctfree 10 initrans 20. 16.4mins 15.4mins 21.3mins 13.4mins 300GB, 29GB Table - pct free - 10 , initrans -20 , Index - pctfree 10 initrans 30 15.5mins 14.5mins 21.0minutes 12.5minutes 300GB, 28GB Table - pct free - 10 , initrans -20 , Index - pctfree 20 initrans 20 15.3mins 14.3mins 20.4mins 12.4mins 300GB, 32GB Table - pct free - 10 , initrans -20 Index - pctfree 30 initrans 40 15.08mins 13.59mins 19.59mins 12.12mins 300GB, 42GB Table - pct free - 10 , initrans -20 Index - pctfree 10 initrans 2 15.15mins 14.05mins 19.50mins 12.16mins 300GB, 27GB Table - pct free - 10 , initrans -20 Index - pctfree 10 initrans 10 16.12 mins 15.02mins 21.25mins 13.04mins 300GB, 28GB Table - pct free - 10 , initrans -1 ,
Index - pctfree 10 initrans 2 15.48mins 14.56mins 20.31mins 12.48mins 285GB, 27GB
Table - pct free - 30 , initrans -40 Index - pctfree 30 initrans 40 16.38minutes 15.35mins 21.29mins 13.29mins 414GB, 42GB Table - pct free - 30 , initrans -40 Index - pctfree 10 initrans 2 15.59mins 15.04mins 20.53mins 12.59mins 414GB, 27GB

On Tue, Jun 7, 2022 at 11:23 PM Lok P <loknath.73_at_gmail.com> wrote:

> Thank You so much Mark Powel, Mark W. Farnham.
>
> These Non default PCTFREE and INITRANS are there in this system since long
> back and I just re-verified the update patterns for column C1 and C2 in the
> table. Basically if i sum-up the Avg column length of both of the columns,
> they are MAX going till ~10Bytes. Considering block size of ~8K and even a
> 5% pctfree comes to 400bytes+. So i believe keeping the pctfree as ~5(or
> say default 10) in table/partition level is good for this scenario. It
> should be lot better(in space usage perspective) as compared to currently
> set non default value which is ~30. Correct me if i am wrong.
>
> In regards to the "initrans" , as it also occupies space in the data/index
> blocks and the default value of initrans for index and table is 2 and 1
> respectively. And i mentioned before, we have set it as non default 40 for
> table and again 40 for few indexes and 20 for other indexes in same table.
> I read in few places in case ASSM(which we have our tablespace in), oracle
> can automatically bump up till max_trans(whose default is 255) based on the
> load. So do we really need to have this set higher or set it back to
> default is okay? or we really have to set it based on the count of the MAX
> concurrent DML happen in our application? for e.g. if we have 20
> simultaneous insert + 15 updates+2 deletes can happen at same time during
> peak in a certain partition/table, should we keep initrans as 37 for that
> segment?
>
> And specifically for indexes I am bit confused, we have combination of
> local and global indexes in this table, but say one index IDX1 on a column
> - C1 which is initially populated as null and later updated to a not null
> values(with Avg/Max column length 8 bytes) by 15 concurrent sessions. In
> this case i understand the new not null column value has to be fit into the
> same table block so pctfree should be adequate otherwise row chaining can
> occur. However , i am unable to understand , to what extent the
> pctfree/initrans for index is really going to play a role and be impacted
> by this update. As no such row chaining effect exists for index blocks i
> believe. Is it okay to set pctfree and initrans for all the indexes/index
> partitions to <5 and keep it like that post rebuild too for future index
> partitions to save space and also get some performance gain in terms of
> compact/smaller index access?
>
>
>
>
> On Fri, 3 Jun 2022, 4:32 am Mark W. Farnham, <mwf_at_rsiz.com> wrote:
>
>> Inline.
>>
>>
>>
>> *From:* Lok P [mailto:loknath.73_at_gmail.com]
>> *Sent:* Wednesday, June 01, 2022 3:57 PM
>> *To:* Mark W. Farnham
>> *Cc:* Powell, Mark; Oracle L
>> *Subject:* Re: What will be the rollback plan for a table move + index
>> rebuild operation?
>>
>>
>>
>> Thank You Mark.
>>
>> So keeping the importance of performance as more than storage space save
>> in mind, it looks like doing table move+rebuild with a default pctfree of
>> -10 seems a safer bet rather than going with pctfree-0 , mainly for tables
>> which are prone to more updates in its lifecycle. And then set it back to
>> the original/non default pctfree post rebuild.
>>
>> I checked about the data life cycle of the table which we are considering
>> here. Below are three types mainly
>>
>> First type)
>>
>> The transaction table here, is going through Avg ~100million INSERTS/day
>> in batch mode (~100 is the insert batch size) and the inserts happen
>> multithreaded which is why INITRANS is kept as non default- 40. And then
>> a couple of the columns( say C1, C2) get updated for all those rows which
>> were populated/inserted initially as null. And then the data gets moved
>> to a default partition(by updating the partition key itself) from where it
>> gets finally deleted/purged based on that date column which is incremental
>> in nature.
>>
>> RUN (don’t walk) and find the developer. Scan your existing data for the
>> maximum and average lengths of “C1” and “C2” post update. IF the average is
>> not much different than the average, use the maximum. Otherwise, do
>> statistics on the column length to find a length that is big enough 80% of
>> the time. Explain to the developer that they must plug in an “impossible
>> value” of either that maximum length or the 80% of the time long enough
>> length instead of NULL. This will eliminate a potentially catastrophic
>> amount of row movement it seems likely pctfree 10 is too small for and if
>> maximum is used, perhaps you can go pctfree 0 or close.
>>
>>
>>
>> OR, put all the initial rows in a completely different table (perhaps
>> what I call an interim table, that you throw away after every batch is
>> whizzed through). Grab the rows from the interim table doing the update on
>> the fly INTO the real destination.
>>
>>
>>
>> Also for the index on column C1 the pctfree kept as - 30 and initrans as
>> - 40. Mostly because it's going through concurrent parallel updates for
>> that column. Maximum during the month end process , it can look back for a
>> past month's worth of data and can Update the column-C1 for specific
>> records.
>>
>> On a test environment for the same table Avg_row_len is noted as 158 and
>> the size of table is ~1.5TB and holds ~1.3billion rows now. It's a
>> list-range composite partitioned table. After Table moved with PCTFREE-10,
>> its size dropped from ~1.5TB to ~320GB. With pctfree-30 the size becomes
>> ~414 GB. So considering this many DML patterns, I am thinking of just ALTER
>> the table and index to pctfree- 10 and then do the subpartition move+ index
>> rebuild. And finally Alter those back to pctfree-30 i.e. as they were
>> currently.
>>
>> Second Type)
>>
>> Here the table is range partitioned and all the historical partitions are
>> just readonly and are already compressed too. This table is never updated
>> unless some ad hoc data fix issue comes up. Data is purged using a drop
>> partition. So in this case we are thinking of rebuilding the historical
>> index partitions with pctfree-0 (which is currently set as pctfree-10 for
>> all).
>>
>>
>>
>> Sounds good. When you do those ad hoc data fixes, you probably want to do
>> them into a different table per partition and then use partition exchange.
>> Read some version of Tim Gorman’s “Scaling to Infinity” which is the
>> easiest way to understand the concepts and mechanisms.
>>
>> Third type)
>>
>> Here the range partitioned table is mainly loaded using batch insert. No
>> update is happening here. And the data deleted/purged using date criteria
>> which is not really a date column. So here we are thinking of performing
>> table move+ index rebuild with default pctfree-0. And then reset the
>> pctfree back to 10.
>>
>> I would delete by copying the keepers and doing partition exchange.
>> Unless the deletions are a tiny percentage, this will be faster. (Also
>> explained in Tim Gorman’s paper extremely well.)
>>
>>
>>
>> On Wed, Jun 1, 2022 at 6:04 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
>>
>> When folks write “OLTP” meaning heavy insert/update/delete, there is a
>> need to examine what sort of information is being considered.
>>
>>
>>
>> For the transaction log type information, where the initial row may go
>> through a few changes until it is “complete,” then usually on a rebuild
>> (especially if date or rising number is the first partition), most of the
>> older data can be tightly compressed in a rebuild and any periodic “delete”
>> can likely best be done by removing the oldest partition. 0 percent free is
>> often fine or best for this type of information. Leaving free space in
>> blocks for rows that by application system definition are no longer allowed
>> to change is simply wasted space in tables.
>>
>>
>>
>> For inventory balance levels you probably need a little bit of room since
>> numbers can grow in length, and if you do real deletes for discontinued
>> items, that can leave holes that might make a block a candidate free block.
>>
>>
>>
>> Those are two examples. For an actual system it is well worth considering
>> the life history length of a row in your big tables that are candidates for
>> rebuild to apply Mr. Powell’s good advice most usefully.
>>
>>
>>
>> “Smallest” is often best, but as Mr. Powell wrote, there is dynamic
>> tension between range scan size and insert block splits for indexes, so you
>> have to think about the life cycle of each index to do the optimal thing.
>> The method to employ is to focus your energy on things most likely to have
>> the biggest impact on your goals (which I take it are less space and
>> acceptable performance) and let lukewarm defaults take care of the things
>> that are neither obvious nor likely to change the result most.
>>
>>
>>
>> Good luck.
>>
>>
>>
>>
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Lok P
>> *Sent:* Tuesday, May 31, 2022 3:33 PM
>> *To:* Powell, Mark
>> *Cc:* Oracle L
>> *Subject:* Re: What will be the rollback plan for a table move + index
>> rebuild operation?
>>
>>
>>
>>
>>
>> Thank You so much Mark. I was thinking of simply running commands like
>> 'alter table move subpartition XXXX'; and 'alter index XXXX rebuild
>> subpartition XXXX'; to reclaim the space back. But as you pointed out, I
>> saw table subpartition level the pct_free and ini_trans have non default
>> values set both in table and index level. These tables are OLTP kind of
>> tables which will go through heavy insert/update/deletes. So do you mean to
>> say , in such scenarios we should rebuild the table with say default
>> PCTFREE- 10 and then set the pctfree back to non default(as these are
>> currently set) post rebuild? And I hope other non default params like
>> ini_trans etc. will not play any role in this table move and rebuild
>> operation.
>>
>>
>>
>> Below is two sample partition and subpartition table/index storage
>> parameters:-
>>
>> *Table subpartition:- *
>>
>>
>> PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT
>>
>> 30 40 255 8388608
>> 1048576 1 2147483645
>>
>> *Local subpartition Index:- *
>>
>>
>> PCT_FREE INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT
>>
>> 30 40 255 65536 1048576
>> 1 2147483645
>>
>> *global index:-*
>>
>> PCT_FREE INI_TRANS MAX_TRANS
>>
>> 10 20 255
>>
>> *********
>>
>> *Table partition :- *
>>
>>
>> PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT
>>
>> 10 20 255 8388608
>> 1048576 1 2147483645
>>
>> *global index:- *
>>
>> PCT_FREE INI_TRANS MAX_TRANS
>>
>> 10 20 255
>>
>>
>>
>> On Tue, May 31, 2022 at 11:51 PM Powell, Mark <mark.powell2_at_dxc.com>
>> wrote:
>>
>> LOK, while it is technically possible that reorganizing a table/index can
>> have an adverse performance impact I would not be that concerned. Just
>> check to see if any of the tables in your plan have non-default percent
>> free reservations. For example, if you have a table with a pctfree of 50
>> you probably want to lower the pctfree, perform the move, and set it back.
>> Otherwise you would create a lot of basically half full blocks that will
>> never use the free space resulting in the need to read more blocks during a
>> full table scan operation. Likewise, an index range scan on the same
>> values used before the table move could also have to visit more block after
>> the rebuild.
>>
>>
>>
>> An index rebuild can adversely impact an insert task if after the index
>> rebuild significantly more block splits are required to support the
>> inserts. This issue tends to fix itself with additional running of the
>> insert task.
>>
>>
>>
>> Again, I would not be overly concerned, but you should always check the
>> object space usage and parameter values in use before performing
>> maintenance. This should help you in spotting objects that may need
>> special handling.
>>
>>
>>
>>
>>
>>
>>
>> Mark Powell
>>
>> Database Administration
>>
>> (313) 592-5148
>>
>>
>>
>>
>> ------------------------------
>>
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Pap <oracle.developer35_at_gmail.com>
>> *Sent:* Tuesday, May 31, 2022 12:19 PM
>> *To:* Lok P <loknath.73_at_gmail.com>
>> *Cc:* Oracle L <oracle-l_at_freelists.org>
>> *Subject:* Re: What will be the rollback plan for a table move + index
>> rebuild operation?
>>
>>
>>
>> You are rebuilding means just dropping and creating the same table and
>> index without any change in structure and data pattern. So how can it
>> affect the performance negatively?
>>
>>
>>
>> On Tue, May 31, 2022 at 7:52 PM Lok P <loknath.73_at_gmail.com> wrote:
>>
>> As we are trying to move forward with table move+index rebuild operation
>> to get some storage space back on a 12.1 version database. Management asks
>> about the rollback plan just in case we see negative performance reading
>> that table/index or while performing DML on the same table , post the table
>> move+index rebuild operation. We don't have an exact lower environment with
>> similar data volume and that too fragmented one to test application. So
>> trying to understand, is there any way out here to get the exact fragmented
>> table and index back in place as it was before?
>>
>>
>>
>> Regards
>>
>> Lok
>>
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l