oracle Extended Statistics 维护

-----自动创建列组
会话一  sys用户登入：
2. Enable workload monitoring.
In a different SQL*Plus session, connect as SYS and run the following PL/SQL program to enable monitoring for 300 seconds:


BEGIN
  DBMS_STATS.SEED_COL_USAGE(null,null,300);
END;
/




-----测试要验证的sql：
--使用业务用户执行：


EXPLAIN PLAN FOR
  SELECT *
  FROM   customers_test
  WHERE  cust_city = 'Los Angeles'
  AND    cust_state_province = 'CA'
  AND    country_id = 52790;



---查看自动生成的扩展列报告：
SET LONG 100000
SET LINES 120
SET PAGES 0
SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test') FROM   DUAL;



---创建扩展列统计信息：
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;


----收集表上扩展列统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');


---查看扩展列信息
SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM   USER_TAB_COL_STATISTICS WHERE  TABLE_NAME = 'CUSTOMERS_TEST' ORDER BY 1;


####手动创建扩展列统计信息：
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS( 'sh-user','customers-table',
  METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' ||
                'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' );
END;
/



SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
FROM   USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
WHERE  e.EXTENSION_NAME=t.COLUMN_NAME
AND    e.TABLE_NAME=t.TABLE_NAME
AND    t.TABLE_NAME='CUSTOMERS';



-----Dropping a Column Group
BEGIN
  DBMS_STATS.DROP_EXTENDED_STATS( 'sh', 'customers', 
                                  '(cust_state_province, country_id)' );
END;
/



#######Expression Statistics
---创建函数表达式扩展统计信息：
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS( 
    'sh',   
    'customers'
    ,method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY ' || 
                   'FOR COLUMNS (LOWER(cust_state_province)) SIZE SKEWONLY' 
);
END;
/


---删除函数表达式扩展统计信息：
BEGIN
  DBMS_STATS.DROP_EXTENDED_STATS(
    'sh',   
    'customers',   
    '(LOWER(cust_state_province))'
);
END;
/