To improve optimizer estimates in case of skewed data distribution , histograms can be created. Prior to 12c frequency and height balanced histograms could be created. If no. of buckets = NDV, frequency histogram is created and the optimizer makes correct estimates.<br />If no. of buckets an endpoint or not. 12c resolves this problem of optimizer misestimates in case of height balanced histograms to a large extent by introducing top-frequency and hybrid histograms which are created if no. of buckets ogram buckets is automatically chosen so that occurences of popular values are accurately captured at the expense of not capturing the data for least occuring values. Hybrid histograms : If no. of buckets ght-based histogram sometimes produces inaccurate estimates if a value that occurs as an endpoint value of only one bucket but almost occupies two buckets and is not considered as popular. To solve this problem, a hybrid histogram stores the endpoint repeat count value and the cumulative frequency of values that are smaller than the corresponding endpoint value for each bucket in the histogram. By using these two pieces of information, the optimizer can obtain accurate estimates for almost popular values.
Anju Garg hasover 12 years of experience in IT Industry in various roles. Her initial tenure was with TCS where she was involved in design, development and testing of various Applications using C , SQL and oracle .
For the past 3.5 years, she has been a part of Koenig Solutions Private Limited as Senior Corporate Trainer (Oracle DBA). She has trained more than 100 DBA's from across the world in various technologies like RAC, Dataguard, Performance Tuning, SQL statement tuning etc. She is certified for
- Oracle 9i OCP (DBA)
- Oracle 11g OCP (DBA)
- Oracle 11g Performance Tuning OCE
- Oracle 11g R2 RAC OCE
- Oracle 11g SQL statement Tuning OCE
She is passionate about learning and has her technical blog at http://oracleinaction.com/