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

From: Sandro Gallo <sandro.gallo.81_at_gmail.com>
Date: Thu, 26 Mar 2015 23:35:33 +0100
Message-ID: <551489B5.40208_at_gmail.com>



Hi,
Our DBA did not find any deadlock graph in trace file, i don't know why.

I have found the cause of the error: incorrect implementation of the DataStage job. I had to review the way in which the data is distributed among the nodes DataStage. This change seems to have solved the deadlock. I'm testing the job.

Best Regards

Il 25/03/15 20:45, Stefan Koehler ha scritto:
> 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: _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
Received on Thu Mar 26 2015 - 23:35:33 CET

Original text of this message