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:
- How many people have a birth month of May?
- How many people have a Zodiac sign of Taurus?
- 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
*****************************************************
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
###############################################################################
Author
Thanks for contributing!