Re: Table / Index Rebuild,, less data but slower

On Fri, 14 Nov 1997 14:12:11 -0600, al.davis_at_ns.sympatico.ca wrote:

>Table / Index Rebuild Problem, less data but slower response.
>
>This is a real challenge question. Two 20 million row tables, X and Y,
>were created from two 40 million row tables, A and B. The 40 million row
>tables were dropped and the 20 miliion row tables were renamed to A and B
>respectively. The one PK index on A was recreated and the 4 indexes on B
>were recreated. The tables and indexes reside on the same file systems
>as before the rebuild, but the application runs 10 times slower. It now
>takes 12 seconds to process a claim as opposed to approx. 1 second.

A few possibilities

1. The pre-existing tables used differenct physical layout e.g. cluster, hash cluster.
2. The key names were different previously, and the application has been optimised by the use of hints specifying index names. Such optimisation is often required for range searches involving bind variables.
3. The range of values present in the tables has changed such that the optimiser chooses not to use an index that was previously used e.g. a status flag may have had a wide range of values previously, but only one or two values in the latest records.
4. Some other physical storage parameter is different in the new tables e.g. if a transaction contains many updates which increase the size of existing records, you could get row-chaining.
5. Tables and indexes are stored in the same tablespace and extents were previously interleaved in such a way as to have index entries near to the corresponding row in the table.
6. The change in the size of the tables in itself has caused the optimiser to change its acess path e.g. because it has bad information about the relative cost of an index search and a full table scan, which I've heard can happen if for instance if db_file_multi_block_read_count is set too high. Other database initialisation parameters that may have the same effect, mentioned in the tuning guide: HASH_AREA_SIZE - larger value causes hash join costs to be cheaper, giving more hash joins SORT_AREA_SIZE - large value causes sort costs to be cheaper, giving more sort merge joins ALWAYS_ANTI_JOIN sets the type of antijoin that Oracle uses: NESTED_LOOPS/MERGE/HASH HASH_JOIN_ENABLED enables or disables the hash join feature; should always be set to TRUE for data warehousing applications SORT_DIRECT_WRITES gives lower sort costs and more sort merge joins PARTITION_VIEW_ENABLED enables partition views HASH_MULTIBLOCK_IO_COUNT larger value causes hash join costs to be cheaper, giving more hash joins SORT_WRITE_BUFFER_SIZE large value causes sort costs to be cheaper, giving more sort merge joins OPTIMIZER_SEARCH_LIMIT the maximum number of tables in the FROM clause for which all possible join permutations will be considered BITMAP_MERGE_AREA_SIZE is the size of the area used to merge the different bitmaps that match a range predicate. Larger size will favor use of bitmap indexes for range predicates.
7. Some other change to database or machine operation took place at about the same time.

Of these, the most likely to produce such a large change must be those that result in a change in the access plan i.e. 2, 3 or 6. Received on Fri Nov 14 1997 - 00:00:00 CST