Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.
So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.
Ready? Here it goes:
Extended Statistics
One of the most expected features of Oracle 11g is improvements to the dbms_stats package, specifically the ability to aid complex queries by providing extended statistics to the cost-based optimizer (CBO).
The 11g extended statistics are intended to improve the optimizers guesses for the cardinality of combined columns and columns that are modified by a built-in or user-defined function. In Oracle 10g dynamic sampling can be used to provide inter-table cardinality estimates, but dynamic sampling has important limitations. However, the 11g create_extended_stats in dbms_stats relieves much of the problem of sub-optimal table join orders allowing for extended statistics on correlated columns.
One of the expectation is to avoid using the ORDERED hint, one of the most popular SQL tuning hints, used to to specify that the tables be joined together in the same order that they appear in the FROM clause.
This feature can be controlled by hidden parameter:
_optimizer_enable_extended_stats in case of SQL Performance regression.
You can also see more detailed material here:
– About improvements on 18c (yeah, lots of things on 12c and 18c).
– Oracle 12c: Automatic Column Group Detection in Oracle Database 12c Release 1 (12.1)
– Oracle Base: Extended Statistics on 11gR2.
Cheers!
