oracle Extended Statistics 维护

72 阅读 0 评论 48 点赞

我是靠谱客的博主冷酷铃铛，最近开发中收集的这篇文章主要介绍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;
/