Using Barcode reader for Software Control to read, scan read, scan image in Software applications.

www.OnBarcode.com

This is from the 9i trace file for hash_multi.sql. As you can see, the optimizer chose to split the hash table into eight partitions. This section of the trace file then shows how many rows were distributed to each partition and, as you can see, there is a little imbalance in the distribution one partition has only 208 rows, another has 282 rows. This is actually quite reasonable, and nothing to be alarmed about. However, if you can see massive imbalance between the partitions, this may be because your data has an odd distribution with a few highly repetitive values in the join columns. This may lead to excessive CPU consumption as the join takes place. For a multitable join, this may give you a clue that you should try to rewrite the query in some way to rearrange the join order. An imbalance is unlikely to be the result of multiple hash collisions with Oracle s hashing function. However, I believe there may be a second hashing function that the code can use to rehash the data in memory if there are indications that the primary hashing function has produced excessive collisions between nonmatching rows. The other columns in this part of the trace are as follows: Clusters: The number of clusters (slots) needed to hold all the rows in that partition. As I pointed out earlier, each cluster in this example is made up of exactly one block. Slots: The number of slots (clusters) from this partition that are currently in-memory. This part of the trace file is reported as the build completes, but before Oracle has tried to tidy up the hash table. As you can see, under pressure Oracle has been dumping partitions zero to six ferociously, although it has to keep at least one slot per partition in memory, and has only managed to keep seven slots of the 33 needed for partition seven. Kept: A flag set to zero or one to show whether or not the entire partition is still in memory. If all partitions are marked as kept, then this is an optimal hash join. This flag isn t included in the 8i trace you have to infer it by checking whether the clusters value matches the slots value (which is labelled as in-memory slots in 8i). This bit of the trace file gives another clue about how much you have to increase your hash_area_size to get from a multipass hash join to a onepass hash join. We can see that the best retention Oracle has managed is 7 slots compared to a worst case of 37 (the best partition is partition 7, which needs only 33 slots and got 7 slots, but the worst case partition is partition 6 which needs 37 slots). We probably need to multiply the hash_area_size by roughly 37/7 = 5.3 to get to a onepass hash.