Re: Reasonable performance ?

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?

I would recommend working with your dba staff and oracle support. Received on Thu Sep 14 2006 - 16:39:19 CDT