我们一般习惯使用oracle自带的统计信息收集，但很多时候我们会发现，有很多关键的表始终没有被收集过。
connect 用户/密码
grant create any table to 用户;
-- 这一步非常重要，需要显式地赋予用户建表权限
CREATE OR REPLACE PROCEDURE ANALYZE_TB AS
OWNER_NAME VARCHAR2(100);
V_LOG INTEGER;
V_SQL1 VARCHAR2(800);
V_TABLENAME VARCHAR2(50);
CURSOR CUR_LOG IS
SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = 'ANALYZE_LOG';
--1
BEGIN
--DBMS_OUTPUT.ENABLE (buffer_size=>100000);
--1.1
BEGIN
OPEN CUR_LOG;
FETCH CUR_LOG
INTO V_LOG;
IF V_LOG = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE ANALYZE_LOG (USER_NAME VARCHAR(20),OP_TIME CHAR(19) DEFAULT to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),ERROR_TEXT VARCHAR(200),TABLE_NAME VARCHAR(40))';
END IF;
END;
SELECT USER INTO OWNER_NAME FROM DUAL;
V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' || OWNER_NAME || ''',''ANALYZE BEGIN'',''ALL'')';
EXECUTE IMMEDIATE V_SQL1;
sys.dbms_stats.gather_schema_stats(ownname => UPPER(OWNER_NAME),
estimate_percent => 100, --定义收集的百分比
method_opt => 'FOR ALL INDEXED COLUMNS',
cascade => TRUE); --cascade => TRUE,degree=>8 degree定义并行线程数，最大建议不要超过CPU线程数的一半
V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' || OWNER_NAME || ''',''ANALYZE END'',''ALL'')';
EXECUTE IMMEDIATE V_SQL1;
commit;
--1.2 delete tmptbstatitics and lock statistics
BEGIN
for x in (select a.table_name, a.last_analyzed, b.stattype_locked
from user_tables a, user_tab_statistics b
where a.temporary = 'Y'
and a.table_name = b.table_name
and (b.STATTYPE_LOCKED is null OR
a.last_analyzed is not null)) LOOP
IF x.last_analyzed IS NOT NULL THEN
--delete stats
dbms_stats.delete_table_stats(ownname => user,
tabname =>x.table_name,
force => TRUE);
END IF;
IF x.stattype_locked IS NULL THEN
--lock stats
dbms_stats.lock_table_stats(ownname => user,
tabname =>x.table_name);
END IF;
END LOOP;
end;
EXCEPTION
WHEN OTHERS THEN
IF CUR_LOG%ISOPEN THEN
CLOSE CUR_LOG;
END IF;
commit;
end;
/
定义执行计划
VARIABLE JOBNO NUMBER;
VARIABLE INSTNO NUMBER;
BEGIN
SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;
DBMS_JOB.SUBMIT(:JOBNO,'ANALYZE_TB;
',TRUNC(SYSDATE)+1+2/24,'TRUNC(SYSDATE)+2+2/24',TRUE,:INSTNO);
COMMIT;
END;
/
禁用执行计划
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
注：estimate_percent=>dbms_stats.auto_sample_size效率会比null高80%左右
最后，附上DBMS_STATS.GATHER_TABLE_STATS的语法供以后查看：
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2,
estimate_percent NUMBER,
block_sample BOOLEAN,
method_opt VARCHAR2,
degree NUMBER,
granularity VARCHAR2,
cascade BOOLEAN,
stattab VARCHAR2,
statid VARCHAR2,
statown VARCHAR2,
no_invalidate BOOLEAN,
force BOOLEAN);
参数说明:
ownname:要分析表的拥有者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:
for all columns:统计所有列的histograms.
for all indexed columns:统计所有indexed列的histograms.
for all hidden columns:统计你看不到列的histograms
for columns SIZE | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data
degree:决定并行度.默认值为null.
granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.
cascace:是收集索引的信息.默认为falase.
stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force:即使表锁住了也收集统计信息.
执行存储过程
exec ANALYZE_TB
查看最后一次收集时间
select table_name,last_analyzed from all_tables where owner='用户名' order by 2;