Hotsos Symposium Speaker – Amit Poddar

Mr. Poddar is a new Hotsos Symposium presenter.

Biography

Amit PoddarAmit has been an Oracle Applications DBA since 1998. During this time, he has worked on many large Oracle Applications databases. Query tuning and Oracle Cost-Based Optimizer have always been his deepest interests. Tuning queries in Oracle Applications with often no access to the SQL itself has forced him to explore the mechanics of Oracle's cost-based optimizer and statistics gathering. Mr. Poddar has spoken about Oracle performance and other topics at OAUG and local Oracle User Groups.

Presentation Title

One Pass Distinct Sampling

Abstract

Accurate object statistics are critical for the cost-based optimizer to calculate the accurate cardinalities. Accurate cardinalities are critical for the optimizer to choose the right access paths, join orders and join methods. In the absence of histograms, which is the case most of the time, NDV (num distinct) of columns is a very critical object statistic.

Calculating accurate NDVs on large tables or calculating accurate global NDV over large partitioned tables has always been a problem since they involve aggregation over large amounts of data. Until Oracle 10g, the only solution was to sample a small percentage of the data instead of the full table which often resulted in inaccurate NDV estimation. Moreover, choosing the right sample size for each table was also a big problem. Oracle 9i introduced automatic sample size to solve the later problem, but it often resulted in many scans of the table to determine the right sample size.

Oracle 11g introduced aggregate NDV and Synopsis Aggregation techniques based on one pass distinct sampling algorithms. This solves all the problems above very cleverly. These new features sample the full table/partition (with automatic sample size) very efficiently and still produce very accurate NDV statistics. Synopsis Aggregation allows Oracle to calculate global NDV for partitioned tables by just aggregating the partition statistics and still result in very accurate statistic.

This presentation will look at these algorithms and Oracle's implementation in detail. It will also compare the performance of gathering statistics and accuracy of statistics using the new and old methods. It will include all the test scripts used to do the above comparisons.

Presentation Materials

Presentation materials are available online to attendees only. Amit's materials may be found at the Hotsos portal. Attendees may use their portal ids to log in.

Schedule

The speaker schedules can be found at the following links: