Reasonable performance ?

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

Any comments ?

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.