OCM 11g Preparation - Use multi column statistics

Multi column is another item any DBA must know about. It's very important on tables where you have 2 columns that are correlated. So you must tell the optimizer that! The classical example for that is:

  1. How many people have a birth month of May?
  2. How many people have a Zodiac sign of Taurus?
  3. How many people have a birth month of May and a Zodiac sign of Taurus?

If you have 12.000 very well distributed lines, optimizer will answer 1k for the first and 1k second question, but ~ 100 to the last question. In fact, the last question would also be 1k (assuming everybody from May is Taurus).

So, study it!

Practice:

  • Create extended stats using Enterprise Manager (very easy and fast).
  • The same above using CLI (easy, but not so fast).

Don't forget to think before starting any exam question in "What's the the fast approach for that?".

Path to Documentation:

Performance Tuning Guide -> 13 Managing Optimizer Statistics


Click here to go back to the Main OCM 11g Preparation page.

Have you enjoyed? Please leave a comment or give a 👍!

2 comments

    • Radek on October 12, 2016 at 17:22
    • Reply

    *****************************************************

    Worth mentioning auto extended columns creation

    -------------------------------------------------------------------
    1. Collect info on columns usage (time in 'seconds')
    ------------------------------------------------------------------

    RADEK@emrep > exec DBMS_STATS.SEED_COL_USAGE(NULL,NULL,60);

    PL/SQL procedure successfully completed.

    ------------------------------
    2. Perform few selects
    ------------------------------

    RADEK@emrep > SELECT * FROM TEST4 WHERE NAME LIKE 'A' OR ID=2;

    ID NAME
    -------------------- --------------------
    1 A
    2
    1 A
    1 A

    4 rows selected.

    RADEK@emrep > SELECT * FROM TEST4 WHERE NAME='B' AND ID=1;

    no rows selected

    RADEK@emrep > SELECT * FROM TEST4 WHERE NAME='B' AND ID >0 ORDER BY 1;

    no rows selected

    RADEK@emrep > SELECT * FROM TEST4 WHERE NAME='A' AND ID BETWEEN 0 AND 3 GROUP BY NAME,ID ORDER BY 2 DESC;

    ID NAME
    -------------------- --------------------
    1 A

    1 row selected.

    RADEK@emrep > SELECT * FROM TEST4 WHERE NAME IS NULL;

    ID NAME
    -------------------- --------------------
    2
    3
    4
    6
    4
    4
    5
    5

    8 rows selected.

    RADEK@emrep > SELECT * FROM TEST4 WHERE NAME LIKE 'A' OR ID=2;

    ID NAME
    -------------------- --------------------
    1 A
    2
    1 A
    1 A

    4 rows selected.

    RADEK@emrep > SELECT * FROM TEST4 WHERE NAME='A' AND ID BETWEEN 0 AND 3 GROUP BY NAME,ID ORDER BY 2 DESC;

    ID NAME
    -------------------- --------------------
    1 A

    RADEK@emrep > SELECT * FROM TEST4 WHERE NAME='B' AND ID >0 ORDER BY 1;

    no rows selected

    RADEK@emrep > SELECT * FROM TEST4 WHERE NAME='B' AND ID=1;

    no rows selected

    --------------------------------------------------------------------
    3. After 60 seconds generate column usage report:
    ----------------------------------------------------------------------

    RADEK@emrep > SELECT DBMS_STATS.REPORT_COL_USAGE(USER,'TEST4') FROM DUAL;

    DBMS_STATS.REPORT_COL_USAGE(USER,'TEST4')
    --------------------------------------------------------------------------------
    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 RADEK.TEST4
    ...................................

    1. ID : EQ RANGE EQ_JOIN
    2. NAME : EQ NULL
    3. (ID, NAME) : GROUP_BY
    ###############################################################################

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    4. Create extended statistics without provideing the columns definitions. Oracle will pick it up for you based on the report results:
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    RADEK@emrep > SET LONG 99999999
    RADEK@emrep > SELECT SYS.DBMS_STATS.CREATE_EXTENDED_STATS(USER,'test4') A from dual;

    A
    ----------------------------------------------------------------------------------------------------
    ###############################################################################

    EXTENSIONS FOR RADEK.TEST4
    ..........................

    1. (ID, NAME) : SYS_STUIA0V924QODN5R5SCAKM60G# created
    ###############################################################################

    1. Thanks for contributing!

Leave a Reply

Your email address will not be published.