Increasing Initrans for Partitioned table without re-creating it [message #637849] |
Wed, 27 May 2015 11:54 |
|
venkatesh_b
Messages: 3 Registered: May 2015
|
Junior Member |
|
|
Hi All,
We have a partitioned table with 25Million recs in it ( roughly 100+ columns). We are encountering ITL waits on these partitions and we want to increase the Initrans as suggested in many places.
But what i want to know is how do we increase this without re-creating the table?
I read elsewhere..
If you use an ALTER TABLE MODIFY DEFAULT you will only define the INITRANS value for new (not yet existing) partitions.
If you use an ALTER TABLE MODIFY PARTITION you will only define the INITRANS value for new blocks but not for the existing ones
If you use an ALTER TABLE MOVE PARTITION you will reset the INITRANS parameter for the entire partition/segment.
So i prefer the 3rd option in the 3, but I want to know
1) If option 3 will make the INITRANS effective for New and existing blocks
2) If I have to retain the table in the same tablesace i.e. if the present tablespace is TSP1 i will move partition using the command
alter table <Tablename> move partition <PArtition name> TSP1 INITRANS 4;
Will this work for existing blocks as well if i do so? How does 'moving' into same tablespace work?
Or should i be Moving it to a different tablespace and back to this?
All suggestions/advises Highly appreciated. Thanks..
Regards,
Venky
|
|
|
Re: Increasing Initrans for Partitioned table without re-creating it [message #637850 is a reply to message #637849] |
Wed, 27 May 2015 12:07 |
John Watson
Messages: 8919 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Moving partitions as you suggest, within the tablespace, will certainly work. It also break all your indexes unless you specify an UPDATE INDEXES clause. Which has it';s own problems.
I have to say that I dpo not know why you are doing this. You say you have researched it, and think that raising INITRANS to 4 will help. I cannot imagine why it would. And if the table is only 25 million rows, you might be better off with no partitioning at all.
|
|
|
Re: Increasing Initrans for Partitioned table without re-creating it [message #637851 is a reply to message #637850] |
Wed, 27 May 2015 12:22 |
|
venkatesh_b
Messages: 3 Registered: May 2015
|
Junior Member |
|
|
Thanks for the insight. I only have the primary key index on this Table.
We have been encountering Deadlocks and ITL Waits on this table. I am a developer supporting the prod system. And after some research done, on internet of course, I found that one of most suggested option was to increase INITRANS.
Our problem is almost exactly what is in this link:
http://www.dba-oracle.com/t_enq_tx_alocate_itl_entry.htm
i.e. update job waiting excessively for the enq: TX - allocate ITL entry wait event. This is a parallel batch stream with multiple concurrent update processes being spawned.
So after cross checking multiple suggestions across other sites with respect to ITL Waits, i thought that was the option to go forward. And yes my table has 25 million recs that are for active calcs and querying other jobs in system and another probably 25-30 million of historic data, purge is not to be done as these data are required by one process. But we are pursuing it.
The idea behind partition is that whole system is analysed or calcs done based on the same partiotining. We have partition on year and type i.e. S2015, W2015 etc.. So we need that inherently in all tables big or small, where we store calcs.
Having seen the problem, would you suggest any other change that would help resolve this issue in a better manner? Please let me know.
Also, to understand the issue better is there any other info you think i should be putting here?
P.S.: I am not sure how much difference would this make but our system runs on EXADATA.
[Updated on: Wed, 27 May 2015 12:23] Report message to a moderator
|
|
|
Re: Increasing Initrans for Partitioned table without re-creating it [message #637852 is a reply to message #637851] |
Wed, 27 May 2015 12:41 |
John Watson
Messages: 8919 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The web site to which you refer does not have a perfect reputation.
Apart from that, you would need to proved more information:
The DDL of the table, of course. So far you have not said even whether it is hash or range partiiotned. Also DDL for the index.
Then, how did you diagnose that this wait event was a problem? Is it on the table or the index? Do you have AWR reports that cover the entire batch job, and AWR SQL reports for any particular SQLs? Can you post them here?
The fact that it is Exadata is interesting, but I wouldn't think relevant to this.
|
|
|
|
Re: Increasing Initrans for Partitioned table without re-creating it [message #637854 is a reply to message #637852] |
Wed, 27 May 2015 13:29 |
|
venkatesh_b
Messages: 3 Registered: May 2015
|
Junior Member |
|
|
Ah that's interesting about that site..
Here are the details:
1) Partition: partition by range i.e. partition by range(SEASON_YEAR, SEASON_TYPE)
partition PKG_ATR_W15 values less than ('2016', 'S')
tablespace CY_YIELD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
)
2) PK/Index: alter table PACKAGE_ATTRIBUTE
add constraint PK_PKG_ATR1 primary key (INVENTORY_ITEM_REFERENCE)
using index
3) Deadlock Issue: We encountered a deadlock issue on this table, package attribute, we call it PA. We have a JBoss code that fires update statements on this table for every "package"/iiref (the PK) we process concurrently. And no other process updates this table.
So we requested for DB Trace files.. In which we found the sessions that were "deadlock dumps" and then took the active session history of that. And its blocking sessions had ITL Wait events. enq: TX - allocate ITL entry to be specific. 2. The active session history shows the wait event enq: TX - allocate ITL entry and this event blocks many sessions running at same time.
I have, again read in some forums, understood that the reasons for this wait could be, concurrent updates to same data blocks. And then i stumbled across many sites' suggestions and finally on to the one i pasted above.
select
sql_id,
event,
count(*),
lpad(round(ratio_to_report(count(*)) over () * 100)||'%',10,' ') percent
from
dba_hist_active_sess_history
where
sample_time between timestamp '2015-05-11 03:00:00'
and
timestamp '2015-05-11 06:00:00'
and
event = 'enq: TX - allocate ITL entry'
group by
sql_id, event
order by
percent desc;
Waits were on partitions.
SELECT Owner,
Object_Name,
Subobject_Name, Object_Type, Value
FROM V$Segment_Statistics c
WHERE Tablespace_Name = UPPER ('&parTABLESPACE')
AND Value > 0
AND Statistic_Name = 'ITL waits'
ORDER BY Value DESC;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE VALUE
1 YIELD PACKAGE_ATTRIBUTE PKG_ATR_W15 TABLE PARTITION 2599
DDL of the table is big so attached SQL. please let me know if you want me to attach the trace file. I will do so in next reply (there seems to be only one attachment allowed per message).
So that is in a nutshell what was done. Not sure if this is the right process, but since this is my first time i just went with the knowledge i have. Do let me know if that seems erroneous. Thanks again for the help..
--mod update: added [code] tags, plese do so yourself in future
[Updated on: Wed, 27 May 2015 14:09] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|