Re: Modify IniTrans on composite interval-hash partitioned table, due to ora-00060
- From: Stefan Koehler <contact@xxxxxxxx>
- To: sandro.gallo.81@xxxxxxxxx, Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Mar 2015 20:45:42 +0100 (CET)
Hi Sandro,
> 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: @OracleSK
> Sandro Gallo <sandro.gallo.81@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
