Re: Oracle8i Partitioning

Review the queries which seem to be operating at reasonable speeds - it is possible that this is the case because you currently have only 14 partitions - get the execution plan for each query, and then run it with event 10128 set to level 7. This event will show you what steps Oracle is taking on partition elimination, and could give some advance warning of problems to come.

It is possible to make your partitions too small. If every physical data segment is small, Oracle may decide that the best strategy is to scan the segment - if this strategy is then escalated to scan every segment in the table, the overhead could be dramatic.

8.1.7 is dramatically different from earlier versions, and 8.1.7.2 is clearly different from 8.1.7.0 (I am still trying to get 8.1.7.0 to execute a really odd, but very clever, path that dropped out of an 8.1.7.2 database recently).

Amongst other things, I believe that in 8.1.7, Oracle uses table global stats if it thinks it is going to access multiple partitions, and individual partition stats only if it thinks it is going to take a single-partition path. This means trouble if your table-level stats go blank; and in earlier versions of Oracle, this could happen if you added, dropped, truncated or exchanged a partition. I haven't tested all these options with 8.1.7 yet - but I suggest you do.

Given the presence of the bitmap indexes, I assume you are doing NO data maintenance during the working day. (Unless is it by partition exchange)

With 1,000 partitions and 8 indexes, remember that a single 'drop oldest partition' will result in 9,000 rows in the data dictionary being updated one at a time - be careful about when you drop your old data.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Donna wrote in message <79808a91.0201301405.6d0ffc7_at_posting.google.com>...


>Jonathan,

>Thanks for the information.  To answer some of your questions....

>

>We are utilizing range partitioning and local indexes on version

>8.1.7.2.

>It is a datamart enviroment with a dimensional modeling design.  The

>fact tables

>are partitioned and store 13 weeks of data in one day partitions which

>comes out

>to be 91 (table) partitions with approx. 7 million rows of data in

>each partition.  There are 5-6 local indexes on each fact table with

>most of them being bitmap indexes and 2 or so b-trees (based on their

>cardinality).  Based on your earlier calculations, that should put us

>at roughly 637 partitons for one fact table including local

>indexes(for example). (Thanks that reminder, I had not considered the

>impact the local indexes make on the partitioning grand total.

>

>The queries access large amounts of data and span across several

>partitions.

>We have been getting pretty good performance so far except for a few

>complex user queries that we are currently trying to address.  But I

>have to add that this is a newly deployed datamart and currently holds

>only 14 days of the 91

>day requirement.

>

>Because of data extract challenges and the nature of the "extract and

>load" strategy being used to load the datamart, I have been asked to

>research "Whether it makes sense to make the partitions smaller?" and

>"What are the implications of having possibly over 1000 partitions?"

>

>This is why I ask.  I welcome any further advice you and anyone else

>have to offer.

>

>Thanks

>Donna