Client Case Study.....Tune the Pools

Moving
- Five Randomly Accessed Tables with Small WKSETs into BP1
- Two large Indexes always Scanned into BP0...Pending Application Analysis
  - Indicates Poor SQL Access Path/Index Usage
- Reduced Avg. I/O Wait to .088

Previous slide Next slide Back to the first slide View Graphic Version

Notes:

Now we got serious, analyzed the object usage, and moved objects into different pools. Since this was a V2.3 system, we were certainly limited by having only 3-4K pools.

Again, in retrospect, it would have been more efficient to dedicate BP0 for randomly accessed Indexes since the catalog and directory are in BP0 and are almost always accessed randomly. On V3 and later systems, BP0 should usually be dedicated to the catalog and directory... unless the installation is memory constrained. Then placing small indexes with high hit ratios in there will usually perform well.

The type of environment must also be factored into the considerations... is it transaction oriented, with little or no dynamic SQL, or is there a heavy dynamic SQL workload?