Home » , » Manageability – Extended Statistics

Manageability – Extended Statistics

Written By Srikrishna Murthy Annam on Sunday, August 30, 2009 | 4:42 AM

Extended Statistics :


In releases prior to 11g , if we execute a query which includes multiple predicates in where conditions,it is impossible for the optimizer to calculate the correct selectivity of these predicates as it had no way of knowing if the columns were related or not.This makes working out the selectivity of the column group very difficult.

In Oracle Database 11g, there is a new kind of statistic, called multi-column statistics, which is a type of extended stat. Using this feature, you can actually create associations between different columns to help the optimizer make better decisions

Extended statistics
1) Multicolumn stats
2) Function-Based Stats


=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================


SQL> create table scott.multicol_stats as select * from dba_objects;
Table created.

SQL> conn scott/tiger
Connected.
SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS');

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';
COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER                                    28 NONE
OBJECT_NAME                           41192 NONE
SUBOBJECT_NAME                          120 NONE
OBJECT_ID                             68851 NONE
DATA_OBJECT_ID                         6632 NONE
OBJECT_TYPE                              42 NONE
CREATED                                1493 NONE
LAST_DDL_TIME                          1552 NONE
TIMESTAMP                              1647 NONE
STATUS                                    1 NONE
TEMPORARY                                 2 NONE
GENERATED                                 2 NONE
SECONDARY                                 2 NONE
NAMESPACE                                20 NONE
EDITION_NAME                              1 NONE

15 rows selected.

SQL> select count(1) from MULTICOL_STATS;

COUNT(1)
----------
68851

SQL> create index TEST1_IDX1 on multicol_stats (owner, object_type);

Index created.

SQL> select owner ,  count(1) from multicol_stats group by owner order by owner;

OWNER                            COUNT(1)
------------------------------ ----------
BI                                      8
CTXSYS                                342
DBSNMP                                 55
EXFSYS                                303
FLOWS_030000                         1528
FLOWS_FILES                            12
HR                                     34
IX                                     53
MDSYS                                1281
OE                                    125
OLAPSYS                               720
ORACLE_OCM                              8
ORDPLUGINS                             10
ORDSYS                               2349
OUTLN                                   9
PM                                     26
PUBLIC                              26604
SCOTT                                  12
SH                                    306
SI_INFORMTN_SCHEMA                      8
SYS                                 29711
SYSMAN                               3285
SYSTEM                                516
TSMSYS                                  3
WKSYS                                 371
WK_TEST                                47
WMSYS                                 315
XDB                                   810

28 rows selected.

SQL> select object_type , count(1) from multicol_stats group by object_type order by object_type;

OBJECT_TYPE           COUNT(1)
------------------- ----------
CLUSTER                     10
CONSUMER GROUP              14
CONTEXT                      7
DIMENSION                    5
DIRECTORY                    9
EDITION                      1
EVALUATION CONTEXT          13
FUNCTION                   298
INDEX                     3278
INDEX PARTITION            304
INDEXTYPE                   11
JAVA CLASS               22103
JAVA DATA                  305
JAVA RESOURCE              833
JOB                         11
JOB CLASS                   13
LIBRARY                    179
LOB                        785
LOB PARTITION                7
MATERIALIZED VIEW            3
OPERATOR                    57
PACKAGE                   1267
PACKAGE BODY              1206
PROCEDURE                  133
PROGRAM                     18
QUEUE                       37
RESOURCE PLAN                7
RULE                         1
RULE SET                    21
SCHEDULE                     2
SEQUENCE                   233
SYNONYM                  26690
TABLE                     2589
TABLE PARTITION            145
TRIGGER                    488
TYPE                      2657
TYPE BODY                  227
UNDEFINED                    6
VIEW                      4773
WINDOW                       9
WINDOW GROUP                 4
XML SCHEMA                  92

42 rows selected.

SQL> select owner , object_type , count(1) from multicol_stats group by owner,object_type order by owner,object_type;

OWNER                          OBJECT_TYPE           COUNT(1)
------------------------------ ------------------- ----------
..
..

PM                             LOB                         17
PM                             TABLE                        3
PM                             TYPE                         3
PUBLIC                         SYNONYM                  26604
SCOTT                          INDEX                        4
SCOTT                          TABLE                        8
SH                             DIMENSION                    5
SH                             INDEX                       27
SH                             INDEX PARTITION            196
..

..
..
277 rows selected.

SQL> select count(1) from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';
COUNT(1)
----------
26604

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683

--------------------------------------------------------------------------------
--------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

--------------------------------------------------------------------------------
--------------

|   0 | SELECT STATEMENT            |                |   249 | 25149 |    13   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |   249 | 25149 |    13   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |   249 |       |     1   (
0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')

14 rows selected.

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SEQUENCE';
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683

--------------------------------------------------------------------------------
--------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

--------------------------------------------------------------------------------
--------------

|   0 | SELECT STATEMENT            |                |   249 | 25149 |    13   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |   249 | 25149 |    13   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |   249 |       |     1   (
0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SEQUENCE')

14 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS',method_opt=>'FOR ALL COLUMNS')

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER                                    28 FREQUENCY
OBJECT_NAME                           41192 HEIGHT BALANCED
SUBOBJECT_NAME                          120 HEIGHT BALANCED
OBJECT_ID                             68851 HEIGHT BALANCED
DATA_OBJECT_ID                         6632 HEIGHT BALANCED
OBJECT_TYPE                              42 FREQUENCY
CREATED                                1493 HEIGHT BALANCED
LAST_DDL_TIME                          1552 HEIGHT BALANCED
TIMESTAMP                              1647 HEIGHT BALANCED
STATUS                                    1 FREQUENCY
TEMPORARY                                 2 FREQUENCY
GENERATED                                 2 FREQUENCY
SECONDARY                                 2 FREQUENCY
NAMESPACE                                20 FREQUENCY
EDITION_NAME                              1 FREQUENCY

15 rows selected.

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935

--------------------------------------------------------------------------------
----

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT  |                |  9794 |   966K|   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS |  9794 |   966K|   285   (1)| 00:00:
04 |

--------------------------------------------------------------------------------
----

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')

13 rows selected.

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SEQUENCE';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683

--------------------------------------------------------------------------------
--------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

--------------------------------------------------------------------------------
--------------

|   0 | SELECT STATEMENT            |                |    74 |  7474 |     5   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |    74 |  7474 |     5   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |    74 |       |     1   (
0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SEQUENCE')

14 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS',method_opt=>'FOR COLUMNS (owner,object_type)')

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';
COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER                                    28 FREQUENCY
OBJECT_NAME                           41192 HEIGHT BALANCED
SUBOBJECT_NAME                          120 HEIGHT BALANCED
OBJECT_ID                             68851 HEIGHT BALANCED
DATA_OBJECT_ID                         6632 HEIGHT BALANCED
OBJECT_TYPE                              42 FREQUENCY
CREATED                                1493 HEIGHT BALANCED
LAST_DDL_TIME                          1552 HEIGHT BALANCED
TIMESTAMP                              1647 HEIGHT BALANCED
STATUS                                    1 FREQUENCY
TEMPORARY                                 2 FREQUENCY
GENERATED                                 2 FREQUENCY
SECONDARY                                 2 FREQUENCY
NAMESPACE                                20 FREQUENCY
EDITION_NAME                              1 FREQUENCY
SYS_STUXJ8K0YTS_5QD1O0PEA514IY          277 HEIGHT BALANCED

16 rows selected.

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935

--------------------------------------------------------------------------------
----

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT  |                | 25704 |  2635K|   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS | 25704 |  2635K|   285   (1)| 00:00:
04 |

--------------------------------------------------------------------------------
----

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')

13 rows selected.

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SEQUENCE';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683

--------------------------------------------------------------------------------
--------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

--------------------------------------------------------------------------------
--------------

|   0 | SELECT STATEMENT            |                |    64 |  6720 |     4   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |    64 |  6720 |     4   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |    64 |       |     1   (
0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SEQUENCE')

14 rows selected.

SQL> select count(*) from multicol_stats where lower(object_type) = 'index';

COUNT(*)
----------
3278

SQL> explain plan for select * from multicol_stats where lower(object_type) = 'index';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935

--------------------------------------------------------------------------------
----

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT  |                |   689 | 72345 |   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS |   689 | 72345 |   285   (1)| 00:00:
04 |

--------------------------------------------------------------------------------
----

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LOWER("OBJECT_TYPE")='index')

13 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS',method_opt => 'for all columns size skewonly for columns (lower(object_type))');

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER                                    28 FREQUENCY
OBJECT_NAME                           41192 HEIGHT BALANCED
SUBOBJECT_NAME                          120 FREQUENCY
OBJECT_ID                             68851 NONE
DATA_OBJECT_ID                         6632 HEIGHT BALANCED
OBJECT_TYPE                              42 FREQUENCY
CREATED                                1493 HEIGHT BALANCED
LAST_DDL_TIME                          1552 HEIGHT BALANCED
TIMESTAMP                              1647 HEIGHT BALANCED
STATUS                                    1 FREQUENCY
TEMPORARY                                 2 FREQUENCY
GENERATED                                 2 FREQUENCY
SECONDARY                                 2 FREQUENCY
NAMESPACE                                20 FREQUENCY
EDITION_NAME                              1 FREQUENCY
SYS_STUXJ8K0YTS_5QD1O0PEA514IY          277 HEIGHT BALANCED
SYS_STUVJ5LMUFWDLVURW#_ROXEK1U           42 FREQUENCY

17 rows selected.

SQL> explain plan for select * from multicol_stats where lower(object_type) = 'index';

Explained.

SQL> select dbms_metadata.get_ddl('TABLE','MULTICOL_STATS') from dual;

DBMS_METADATA.GET_DDL('TABLE','MULTICOL_STATS')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."MULTICOL_STATS"
(    "SYS_STUXJ8K0YTS_5QD1O0PEA514IY"

SQL> explain plan for select * from multicol_stats where lower(object_type) = 'index';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935

--------------------------------------------------------------------------------
----

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT  |                |  3344 |   372K|   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS |  3344 |   372K|   285   (1)| 00:00:
04 |

--------------------------------------------------------------------------------
----

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("MULTICOL_STATS"."SYS_STUVJ5LMUFWDLVURW#_ROXEK1U"='index')

13 rows selected.

SQL>
Multi column Statistics



Share this article :

Related Articles By Category



Post a Comment

Thank you for visiting our site and leaving your valuable comment.

 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger