Presentation Title: Index Statistics & Column Groups
Speaker: Mr Jonathan Lewis
Company: JL Computer Consultancy
Presentation abstract: This session will discuss the significance of the set_table_prefs('table_cached_blocks') procedure and how it affects the clustering_factor of indexes in a way that helps the optimizer pick better execution paths. Then it examines the column group (extended) stats that handle the problem of bad cardinality estimates for predicates on correlated columns within a single table. Finally it shows how the latter can also reduce some of the work needed to collect accurate stats on multi-column indexes.

The optimizer has a history of picking bad execution plans because of two critical defects. The first is due to over-estimating the cost of using an index, the second is due to overestimating the number of distinct combinations of values that might appear for multi-column predicates.

Presentation begins: 04/12/2017 09:00
Presentation duration: 50
Presentation content level: 4 (1 = indepth, 5 = strategic overview)
Audience experience: Beginner/Exploratory
Audience function: Product User
Speaker biography:  Jonathan Lewis is well-known in the Oracle world with over 29 years experience using the software. He has published three books about Oracle, contributed to three others, posted more than 1200 articles on his blog, and has appeared in 50 different countries. Jonathan has been self-employed for most of his time in the IT industry. For the last 25 years he has specialised in short-term assignments often spending no more than two or three days on a client site to address problems, and even spending no more than a couple of hours solving problems using VPN over the Internet.
Related topics: Database: 12c Database, Database: Performance & Internals
Hall: Hall 5