Home » SQL & PL/SQL » SQL & PL/SQL » Increasing Initrans for Partitioned table without re-creating it (Oracle 11g (11.2.0.4))
Increasing Initrans for Partitioned table without re-creating it [message #637849] Wed, 27 May 2015 11:54 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #637853 is a reply to message #637852] Wed, 27 May 2015 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The web site to which you refer does not have a perfect reputation.


You can say it is b......t.

Re: Increasing Initrans for Partitioned table without re-creating it [message #637854 is a reply to message #637852] Wed, 27 May 2015 13:29 Go to previous messageGo to next message
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
  • Attachment: PA_CREATE.sql
    (Size: 12.06KB, Downloaded 1457 times)

[Updated on: Wed, 27 May 2015 14:09] by Moderator

Report message to a moderator

Re: Increasing Initrans for Partitioned table without re-creating it [message #637855 is a reply to message #637854] Wed, 27 May 2015 13:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
words have meanings

>We encountered a deadlock issue on this table

[oracle@localhost ~]$ oerr ora 60
00060, 00000, "deadlock detected while waiting for resource"
// *Cause: Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
// involved. Retry if necessary.

Are you claiming that ORA-00060 error; which is only true & honest DEADLOCK.
if so post the details that were written to alert_SID.log file.
ORA-00060 errors are only caused be poor coding within the application.

IMO, looking for performance problem by inspecting content of dba_hist_active_sess_history is folly.

If/when a performance problem exists, it should be reported querying active views & live tables.
Re: Increasing Initrans for Partitioned table without re-creating it [message #637856 is a reply to message #637854] Wed, 27 May 2015 14:12 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Please read How to use [code] tags and make your code easier to read
You do need to post the SQL that has the problem, you know. And those reports I requested. If you do not know how to generate them, your DBA will.

Re: Increasing Initrans for Partitioned table without re-creating it [message #637903 is a reply to message #637856] Thu, 28 May 2015 14:06 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
on heavily concurrently updated tables I typically set INITRANS to 99 and maxtrans to 254. You don't get a deadlock but it will wait on multiple updaters if the transactions are set to low.
Re: Increasing Initrans for Partitioned table without re-creating it [message #637906 is a reply to message #637903] Thu, 28 May 2015 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

MAXTRANS is obsolete and ignored since 10g; 255 is taken whatever you give.

Re: Increasing Initrans for Partitioned table without re-creating it [message #637908 is a reply to message #637906] Thu, 28 May 2015 14:48 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
That's true but I work on Oracle 8, 9, 10, 11, and 12 and they didn't say what version they were on (or i missed it)
Re: Increasing Initrans for Partitioned table without re-creating it [message #637909 is a reply to message #637908] Thu, 28 May 2015 14:50 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Bill B wrote on Thu, 28 May 2015 12:48
That's true but I work on Oracle 8, 9, 10, 11, and 12 and they didn't say what version they were on (or i missed it)


You missed it!

Increasing Initrans for Partitioned table without re-creating it (Oracle 11g (11.2.0.4))
Previous Topic: Interview questions
Next Topic: better query other than multiple for loop's?
Goto Forum:
  


Current Time: Tue Mar 19 03:56:59 CDT 2024