Re: Reasonable performance ?

In message <1158269959.276115.260380_at_h48g2000cwc.googlegroups.com>, hpuxrac <johnbhurley_at_sbcglobal.net> writes
>
>Adrian Simpson wrote:
>> We're not entirely sure that we have got a system that is performing
>> well.
>>
>> We had a partitioned table with 2254 partitions, of which 260 had data
>> in, varying between 1 and ~41,000 rows. On these partitions, using
>> dbms_stats.gather_tables_stats it was taking ~6 minutes to analyse one
>> partition, and about the same time to analyse each of the indexes (1
>> global - Primary key, 1 local B tree and 28 local bitmap), this
>> irrespective of the number of items in each partition. Select count (*)
>> from table_name partition (partition_name) took 30 seconds, again
>> irrespective of the number of rows. The empty partitions gave an almost
>> instantaneous response.
>>
>> The DB is set up to parallelise using 16 processors, but at any one
>> time, only 8 processes (ora_pxxx_<dbname>) seem to be active.
>>
>> We are far from convinced that this is a reasonable performance.
>>
>> In what may be a related issue, trying to truncate the table, I got an
>> ORA-04031: Unable to allocate 4096 bytes of shared memory.
>>
>> Trying to truncate one partition (with 1 row in) gave the same error,
>> our DBAs suggested that Oracle bug 4231921 may relevant, but I'm not
>> sure. To get around this, all local indexes were dropped, the table
>> truncated (no errors), then the indexes replaced. Again, this was
>> slower than expected, taking between 2 and 7 minutes per index
>> (generally getting quicker as the process went on).
>>
>>
>> Itanium (32 processors)
>> HP-UX 11.23
>> 9.2.0.5
>>
>
>What were the processes waiting on while the index rebuilds were
>occurring?
>

We don't know. Tried running a trace on it (10046 level 12 as memory serves), but it filled a 100M file before it even started on the indexes.

>I would recommend working with your dba staff and oracle support.
>

We are working with our DBAs (who no doubt will contact Oracle when they need to), but this is something beyond our usual experience.

Adrian

-- 
To Reply :
replace "bulleid" with "adrian" - all mail to bulleid is rejected
Sorry for the rigmarole, If I want spam, I'll go to the shops
Every time someone says "I don't believe in trolls", another one dies.