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

We have freelists set as null for both table and indexes. And another question i had mainly wrt indexes as index blocks always fill up to 100% full before they split, so that means its okay to rebuild those with pctfree -0? And also if there is some different significance of initrans too for indexes as compared to table , which means we should set the pctfree and initrans different for table and its related indexes?

We have the non default storage parameter set atleast before we moved to exadata i believe. So that also mean , considering all the faster IO like write back flash cache mode for DMLs etc, we would may no longer need those non default initrans and so we should set those back to defaults.

On Thu, 9 Jun 2022, 11:18 am Pap, <oracle.developer35_at_gmail.com> wrote:

> Your current initrans set as 40 and whatever variation in initrans test
> you did the response time were all close and even the size of the
> table/index were not varying significantly if you atleast drop the initras
> from 40 to 20 or 10. It's mainly dropping the pctfree size , which is
> getting a big chunk of space back. And as per your calculation you need no
> more than 5% pctfree based on the existing column updates. So I would say
> keep the pctfree at default 10 and initrans at 10/20 both for table and
> indexes to have a safe guard against your concurrent DML and also not
> lose much storage space. Again I am not very much sure to which extent
> really , pctfree plays a role in case of index blocks or even initrans.
> Others may confirm this.
>
> Additionally , Do you also have non default freelists for these objects
> which were part of this test? As because that will help minimize the waits
> in case of concurrent DMLs. You mentioned you had these setups long back,
> so were those setup done even before your ASSM was in place. Then you can
> really think of evaluating the defaults setup with current ASSM rather than
> these non-defaults.
>
> On Thu, Jun 9, 2022 at 1:11 AM Lok P <loknath.73_at_gmail.com> wrote:
>
>> 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