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

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.

Setup
Column Group Detection
Comments

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;

Create a test user.

conn / as sysdba

--drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource, create view to testuser1;
grant select_catalog_role to testuser1;
grant execute on dbms_lock to testuser1

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 not, and approximately half the men have a beard, but no women do.

conn testuser1/testuser1

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 /*+ append */ into tab1
select rownum, 
       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   (select 1 from dual connect by level <= 2000) a,
       (select 1 from dual connect by level <= 1000) b;

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 10 second.
exec dbms_stats.seed_col_usage(null, null, 10);


-- Do something here that is monitored.
conn testuser1/testuser1

select count(*)
from   tab1
where  gender = 'F'
and    y_chromosome = 'N'
and    beard = 'N';

Wait approximately 10 seconds for the monitoring window to complete, then use the DBMS_STATS.REPORT_COL_USAGE function to see what, if any, useful column groups have been detected.

set long 100000
set lines 120
set pages 50

exec dbms_lock.sleep(10);

select dbms_stats.report_col_usage(user, 'tab1')
from   dual;

DBMS_STATS.REPORT_COL_USAGE(USER,'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(user, 'tab1')
from   dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'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(user, 'tab1', method_opt => 'for all columns size auto');

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

conn / as sysdba

-- 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>

Comments

This article has been updated to work for later releases. Thanks to Nigel Bayliss for helping to clarify these differences.

In the original article there were only 10,000 rows in the test table. In later releases this is not enough to trigger the column group detection, so it has been increased to 2,000,000 rows.
In Oracle 11.2 we didn't have to wait for the column group detection monitoring window to close, but in later released we do. A sleep has been added to make sure we don't proceed to quickly.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.