8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

Extended Statistics Enhancements in Oracle Database 11g Release 2

Extended statistics were introduced in Oracle 11g to allow statistics to be gathered on groups of columns, to highlight the relationship between them, or on expressions. Oracle 11gR2 makes the process of gathering extended statistics for column groups easier.

Related articles.

Setup

Make sure the shared pool is flushed, so we know we are starting with a clean system.

CONN / AS SYSDBA

ALTER SYSTEM FLUSH SHARED_POOL;

The following test table contains columns to indicate gender and the presence of a Y chromosome and the presence of a beard. These columns have check constraints to tell the optimizer about the allowable values, as well as indexes on the columns. The assumption is that all men have a Y chromosome, whereas women do no, and approximately half the men have a beard, but no women do.

CONN test/test

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id               NUMBER,
  name             VARCHAR2(20),
  gender           VARCHAR2(1),
  y_chromosome VARCHAR2(1),
  beard        VARCHAR2(1),
  CONSTRAINT tab1_pk PRIMARY KEY (id),
  CONSTRAINT tab1_gender_chk CHECK (gender IN ('M', 'F')),
  CONSTRAINT tab1_has_y_chromosome_chk CHECK (y_chromosome IN ('Y', 'N')),
  CONSTRAINT tab1_has_beard_chk CHECK (beard IN ('Y', 'N'))
);


INSERT INTO tab1
SELECT level, 
       DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,16))) AS name,
       CASE
         WHEN MOD(rownum, 2) = 0 THEN 'M'
         ELSE 'F'
       END AS gender,
       CASE
         WHEN MOD(rownum, 2) = 0 THEN 'Y'
         ELSE 'N'
       END AS y_chromosome,
       CASE
         WHEN MOD(rownum, 2) = 0 THEN DECODE(TRUNC(DBMS_RANDOM.value(1,3)), 1, 'Y', 'N')
         ELSE 'N'
       END AS beard
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

CREATE INDEX tab1_gender_idx ON tab1(gender);
CREATE INDEX tab1_has_y_chromosome_idx ON tab1(y_chromosome);
CREATE INDEX tab1_has_beard_idx ON tab1(beard);

EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

We can see the breakdown of the data below.

SELECT gender, y_chromosome, beard, COUNT(*) AS total
FROM   tab1
GROUP BY gender, y_chromosome, beard
ORDER BY 1,2,3;

G Y B      TOTAL
- - - ----------
F N N       5000
M Y N       2583
M Y Y       2417

SQL>

Column Group Detection

In Oracle 11gR1, extended statistics had to be created manually for column groups. In Oracle 11gR2, the DBMS_STATS.SEED_COL_USAGE procedure was added, allowing Oracle to determine which column group statistics would be useful, based on a specified SQL Tuning Set or a specific monitoring period.

Tell the database to monitor column group usage for the next 30 seconds, then perform an action to be monitored.

CONN / AS SYSDBA

-- Monitor column group usage for 30 second.
EXEC DBMS_STATS.seed_col_usage(NULL, NULL, 30);


-- Do something here that is monitored.
CONN test/test

SELECT *
FROM   tab1
WHERE  gender = 'F'
AND    y_chromosome = 'N'
AND    beard = 'N';

Use the DBMS_STATS.REPORT_COL_USAGE function to see what, if any, useful column groups have been detected.

CONN / AS SYSDBA

SET LONG 100000
SET LINES 120
SET PAGES 50

SELECT DBMS_STATS.report_col_usage('TEST', 'TAB1')
FROM   dual;

DBMS_STATS.REPORT_COL_USAGE('TEST','TAB1')
--------------------------------------------------------------------------------
LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................

###############################################################################

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

1. BEARD                               : EQ
2. GENDER                              : EQ
3. Y_CHROMOSOME                        : EQ
4. (GENDER, Y_CHROMOSOME, BEARD)       : FILTER
###############################################################################

SQL>

If necessary, create the extended stats using the DBMS_STATS.CREATE_EXTENDED_STATS function.

SELECT DBMS_STATS.create_extended_stats('TEST', 'TAB1')
FROM   dual;

DBMS_STATS.CREATE_EXTENDED_STATS('TEST','TAB1')
--------------------------------------------------------------------------------
###############################################################################

EXTENSIONS FOR TEST.TAB1
........................

1. (GENDER, Y_CHROMOSOME, BEARD)       : SYS_STUJ_NI$1E_EDU917#V63HO2OA created
###############################################################################

SQL>

Gather the stats for the table again.

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

The type of extended statistics required is displayed using the DBA_STAT_EXTENSIONS view.

-- Display the extended stats.
COLUMN extension FORMAT A40

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

EXTENSION_NAME                 EXTENSION
------------------------------ ----------------------------------------
SYS_STUJ_NI$1E_EDU917#V63HO2OA ("GENDER","Y_CHROMOSOME","BEARD")

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.