-----自动创建列组 会话一 sys用户登入： 2. Enable workload monitoring. In a different sql*Plus session,connect as SYS and run the following PL/sqlprogram 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',method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY ' || 'FOR COLUMNS (LOWER(cust_state_province)) SIZE SKEWONLY' ); END; / ---删除函数表达式扩展统计信息： BEGIN DBMS_STATS.DROP_EXTENDED_STATS( 'sh','(LOWER(cust_state_province))' ); END; /