Tuesday, August 5, 2014

1z0-060 Upgrade to 12c: Managing Column Group Statistics

You can use DBMS_STATS.SEED_COL_USAGE and REPORT_COL_USAGE to determine which column groups are required for a table based on a specified workload. This technique is useful when you do not know which extended statistics to create. This technique does not work for expression statistics.

Setup the simulation environment

SQL> CREATE TABLE customers_test AS SELECT * FROM sh.customers;

Table created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');

PL/SQL procedure successfully completed.

Detecting Useful Column Groups for a Specific Workload

SQL> BEGIN
  2    DBMS_STATS.SEED_COL_USAGE(null,null,300);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> EXPLAIN PLAN FOR
  2    SELECT *
  3    FROM      customers_test
  4    WHERE  cust_city = 'Los Angeles'
  5    AND       cust_state_province = 'CA'
  6    AND       country_id = 52790;

Explained.

SQL>
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2112738156

----------------------------------------------------
| Id  | Operation         | Name           | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |     1 |
----------------------------------------------------

8 rows selected.

SQL>
SQL> EXPLAIN PLAN FOR
  2    SELECT   country_id, cust_state_province, count(cust_city)
  3    FROM     customers_test
  4    GROUP BY country_id, cust_state_province;

Explained.

SQL>
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1820398555

-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |  1949 |
|   1 |  HASH GROUP BY     |                |  1949 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------

9 rows selected.


SQL> SET LONG 100000
SQL> SET LINES 120
SQL> SET PAGES 0
SQL> SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test')
  2  FROM   DUAL;
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 DONGHUA.CUSTOMERS_TEST
..............................................

1. COUNTRY_ID                          : EQ
2. CUST_CITY                           : EQ
3. CUST_STATE_PROVINCE                 : EQ
4. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : FILTER
5. (CUST_STATE_PROVINCE, COUNTRY_ID)   : GROUP_BY

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

Creating Column Groups Detected During Workload Monitoring

SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;
###############################################################################

EXTENSIONS FOR DONGHUA.CUSTOMERS_TEST
.....................................

1. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
2. (CUST_STATE_PROVINCE, COUNTRY_ID)   : SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ created
###############################################################################

 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');

PL/SQL procedure successfully completed.


SQL> col COL_GROUP for a40
SQL> col EXTENSION_NAME for a40
SQL> col EXTENSION for a70
SQL> set pages 999
SQL> col COLUMN_NAME for a40
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
  2  FROM   USER_TAB_COL_STATISTICS
  3  WHERE  TABLE_NAME = 'CUSTOMERS_TEST'
  4  ORDER BY 1;

COLUMN_NAME                              NUM_DISTINCT HISTOGRAM
---------------------------------------- ------------ ---------------
COUNTRY_ID                                         19 FREQUENCY
CUST_CITY                                         620 HYBRID
CUST_CITY_ID                                      620 NONE
CUST_CREDIT_LIMIT                                   8 NONE
CUST_EFF_FROM                                       1 NONE
CUST_EFF_TO                                         0 NONE
CUST_EMAIL                                       1699 NONE
CUST_FIRST_NAME                                  1300 NONE
CUST_GENDER                                         2 NONE
CUST_ID                                         55500 NONE
CUST_INCOME_LEVEL                                  12 NONE
CUST_LAST_NAME                                    908 NONE
CUST_MAIN_PHONE_NUMBER                          51344 NONE
CUST_MARITAL_STATUS                                11 NONE
CUST_POSTAL_CODE                                  623 NONE
CUST_SRC_ID                                         0 NONE
CUST_STATE_PROVINCE                               145 FREQUENCY
CUST_STATE_PROVINCE_ID                            145 NONE
CUST_STREET_ADDRESS                             49900 NONE
CUST_TOTAL                                          1 NONE
CUST_TOTAL_ID                                       1 NONE
CUST_VALID                                          2 NONE
CUST_YEAR_OF_BIRTH                                 75 NONE
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_                    145 NONE
SYS_STUMZ$C3AIHLPBROI#SKA58H_N                    620 HYBRID

25 rows selected.

SQL>
SQL> EXPLAIN PLAN FOR
  2    SELECT *
  3    FROM      customers_test
  4    WHERE  cust_city = 'Los Angeles'
  5    AND       cust_state_province = 'CA'
  6    AND       country_id = 52790;

Explained.

SQL>
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2112738156

----------------------------------------------------
| Id  | Operation         | Name           | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT  |                |   871 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |   871 |
----------------------------------------------------

8 rows selected.

SQL>
SQL> EXPLAIN PLAN FOR
  2    SELECT   country_id, cust_state_province, count(cust_city)
  3    FROM     customers_test
  4    GROUP BY country_id, cust_state_province;

Explained.

SQL>
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1820398555

-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |   145 |
|   1 |  HASH GROUP BY     |                |   145 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------

9 rows selected.

SQL>
SQL> SELECT EXTENSION_NAME, EXTENSION
  2  FROM   USER_STAT_EXTENSIONS
  3  WHERE  TABLE_NAME='CUSTOMERS_TEST';

EXTENSION_NAME
----------------------------------------
EXTENSION
----------------------------------------------------------------------
SYS_STUMZ$C3AIHLPBROI#SKA58H_N
("CUST_CITY","CUST_STATE_PROVINCE","COUNTRY_ID")

SYS_STU#S#WF25Z#QAHIHE#MOFFMM_
("CUST_STATE_PROVINCE","COUNTRY_ID")


SQL>
SQL> SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
  2  FROM   USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
  3  WHERE  e.EXTENSION_NAME=t.COLUMN_NAME
  4  AND    e.TABLE_NAME=t.TABLE_NAME
  5  AND    t.TABLE_NAME='CUSTOMERS_TEST';

COL_GROUP                                NUM_DISTINCT HISTOGRAM
---------------------------------------- ------------ ---------------
("CUST_STATE_PROVINCE","COUNTRY_ID")              145 NONE
("CUST_CITY","CUST_STATE_PROVINCE","COUN          620 HYBRID
TRY_ID")

 

Manually creating and dropping a Column Group

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS( USER,'customers_test',
  3    METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' ||
  4                  'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' );
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_STATS.DROP_EXTENDED_STATS( 'donghua', 'customers_test',
  3                                    '(cust_state_province, country_id)' );
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> SELECT EXTENSION_NAME, EXTENSION
  2  FROM   USER_STAT_EXTENSIONS
  3  WHERE  TABLE_NAME='CUSTOMERS_TEST';

EXTENSION_NAME                           EXTENSION
---------------------------------------- ----------------------------------------------------------------------
SYS_STUMZ$C3AIHLPBROI#SKA58H_N           ("CUST_CITY","CUST_STATE_PROVINCE","COUNTRY_ID")