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")
No comments:
Post a Comment