Re: Modify IniTrans on composite interval-hash partitioned table, due to ora-00060

> Do you think that is correct to solve this problem by increasing the value of INITRANS?

The deadlock graph (trace file) answers this question. An ORA-00060 error can have several reasons and the amount of ITL slots is just one possibility. For example bitmap indexes can be another common reason (as you mentioned that this is a DWH).

> If yes, how to calculate the optimal value?

The amount of individual and parallel (DML) sessions per block, if it is really caused by insufficient available ITL slots. We need to know how the DataStage processes perform the update and how the data itself looks like (per block) to provide you a well-founded answer.

> And, what are the necessary steps so that all partitions and subpartitions have the new value?

(Online) reorganization of the corresponding objects, if this is caused by insufficient available ITL slots.

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Sandro Gallo <sandro.gallo.81_at_gmail.com> hat am 25. März 2015 um 20:07 geschrieben:
>
>
> Hello everyone,
> I follow the mailing list for a long time, but I never wrote.
> I work on a DWH environment configured as follows: Datastage 8.7 + Exadata 11G.
>
> In our Datastage project we have a job that update single field of a table, using PK.
> This table is composite partitioned with local index: interval+hash.
>
> Working with a low degree of DataStage parallelism, the job has always worked, but increasing the parallelism job aborted. Increase the parallelism
> leads to having more sessions and more transactions to the DB.
> The error is the Oracle ORA-00060.
>
> Considering all that, searching the internet, a suggestion that is given is to increase the value of the parameter INITRANS, which for the table in
> question is set to the default value.
>
> Two questions.
> Do you think that is correct to solve this problem by increasing the value of INITRANS?
> If yes, how to calculate the optimal value?
> And, what are the necessary steps so that all partitions and subpartitions have the new value?
>
> Thank you very much,
> greetings
> Sandro

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