Extended Stats in Oracle

Extended stats is introduced in Oracle 11g. Extended stats help optimizer to choose more better plan or more accurate cardinality estimate when the columns are used together in a where clause of a SQL statement. you can create extended stats on groups of columns or expressions used in where clause.

Note: Maximum value for a table to create extended stats is 20.

set wrap on
set line 150 pages 150
COLUMN extension FORMAT A100
SELECT extension_name, extension FROM dba_stat_extensions WHERE table_name = 'EMP';


In 11g release 1, you can create manually extended stats
In 11g Release 2, you have procedure to monitor and create extended stats for query with help of DBMS_STATS.SEED_COL_USAGE
In 12c Release, New feature is Automatic Column Group Detection

SQL plan directives can be used by Oracle to determine if extended statistics specifically column groups, are missing and would resolve the cardinality estimates. After a SQL directive is used the optimizer decides if the cardinality not be estimate could be resolved with a column group. If so, the database can automatically create that column group the next time statistics are gathered on the appropriate table.

This step is “always on” in Oracle Database 12c Release 1, but from Oracle Database 12c Release 2, it is controlled by the DBMS_STATS preference AUTO_STAT_EXTENSIONS. Note that the default is OFF, so to enable automatic column group creation the following.
step is required:

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_STAT_EXTENSIONS', 'ON');


Create Extended Stats with DBMS_STATS Package
Note: For manually create as on 11 release 1 and above

Step 1: For creation of extended stats on column like SCOTT user , EMP table , EMP_NO or DEPT_NO column

SET SERVEROUTPUT ON
DECLARE
l_name VARCHAR2(30);
BEGIN
l_name := DBMS_STATS.create_extended_stats(ownname => 'SCOTT',
tabname => 'EMP',extension => '(EMP_NO,DEPT_NO)');
DBMS_OUTPUT.put_line('l_name=' || l_name);
END;
/
l_name=SYS_STU3VG629OEYG6FN0EKTGVPQWD


Step 2: Verify its created or already created by following procedure.

SELECT DBMS_STATS.show_extended_stats_name(ownname => 'SCOTT',
tabname => 'EMP',extension => '(EMP_NO,DEPT_NO)') AS Extended_name
FROM dual;


Step 3: After extended stats created, need to gather stats for table or both column

--Gather stats auto
EXEC DBMS_STATS.gather_table_stats('SCOTT','EMP',method_opt => 'for all columns size auto');

OR
-- You can directly specify the column name
EXEC DBMS_STATS.gather_table_stats('SCOTT','EMP',method_opt => 'for columns (EMP_NO,DEPT_NO)');


Step 4: Verify that extended stats is created

SELECT extension_name, extension FROM dba_stat_extensions WHERE table_name = 'EMP';
OR
COLUMN extended_column FORMAT A30
SELECT a.extension extended_column,b.num_distinct, b.histogram
FROM dba_stat_extensions a, dba_tab_col_statistics b
where a.extension_name=b.column_name
AND b.table_name = 'EMP';


Step 5. Drop the extended stats

EXEC dbms_stats.drop_extended_stats(ownname => 'SCOTT',tabname => 'EMP',
extension => '(EMP_NO,DEPT_NO)');


Create Extended Stats with SEED_COL_USAGE
In 11g Release 2,DBMS_STATS.SEED_COL_USAGE procedure used to determine which column group statistics would be useful.

Step 1: Database will monitor the column group usage for 60 seconds, after executing this statement execute SQL query within 60 seconds.

EXEC DBMS_STATS.seed_col_usage(NULL, NULL, 60);


Step 2: Use the DBMS_STATS.REPORT_COL_USAGE report to check the column group is detected

SET LONG 100000
SET LINES 120
SET PAGES 50
SELECT DBMS_STATS.report_col_usage('SCOTT', 'EMP') FROM dual;

COLUMN USAGE REPORT FOR TEST.TAB1
...................................

1. EMP_NO                 : EQ
2. DEPT_NO                : EQ
4. (EMP_NO,DEPT_NO)       : FILTER

Step 3: Create extended stats

SELECT DBMS_STATS.create_extended_stats('SCOTT', 'EMP') FROM dual;


Step 4: Gather the stats

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP', method_opt => 'for all columns size auto');


Step 5: Verify

SELECT extension_name, extension FROM dba_stat_extensions WHERE table_name = 'EMP';