分析用户表，索引的脚本

用于动态调用,定时分析脚本[@more@]

要先用sys用户登录,授权

GRANT ALL ON dba_segments TO USER;

CREATE OR REPLACE PROCEDURE DBA_ANAYZE_SCHEMA(V_USERNAME VARCHAR2) AS
V_PER NUMBER(3) DEFAULT 100;
V_START NUMBER := DBMS_UTILITY.GET_TIME;
--v_end　 number;
BEGIN
/*********************

该存储过程主要是对表及索引进行分析,

对于包含有子分区sub-partition的表需要注意一下granularity参数。具体参考：

--　 granularity - the granularity of statistics to collect (only pertinent
--　　　if the table is partitioned)
--　　 'DEFAULT' - gather global- and partition-level statistics
--　　 'SUBPARTITION' - gather subpartition-level statistics
--　　 'PARTITION' - gather partition-level statistics
--　　 'GLOBAL' - gather global statistics
--　　 'ALL' - gather all (subpartition, partition, and global) statistics
*******************************/
FOR REC IN (SELECT SEGMENT_NAME,
SEGMENT_TYPE,
CEIL(SUM(BYTES) / 1024 / 1024) SEGMENT_SIZE
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME NOT LIKE 'TMP_%'
AND OWNER = V_USERNAME
GROUP BY SEGMENT_NAME, SEGMENT_TYPE) LOOP
CASE
WHEN REC.SEGMENT_TYPE = 'INDEX' THEN
CASE
WHEN REC.SEGMENT_SIZE <= 100 THEN
V_PER := 100;
WHEN REC.SEGMENT_SIZE <= 300 THEN
V_PER := 50;
ELSE
V_PER := 20;
END CASE;
BEGIN
--delete old schema index statistics;
DBMS_STATS.DELETE_INDEX_STATS(OWNNAME => UPPER(V_USERNAME),
INDNAME => REC.SEGMENT_NAME);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
--analyze index compute statistics;
DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => UPPER(V_USERNAME), --自己改一下
INDNAME => REC.SEGMENT_NAME,
ESTIMATE_PERCENT => V_PER,
DEGREE => 2);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--dbms_output.put_line(rec.segment_name||' '||rec.segment_size||'m '||ceil((dbms_utility.get_time - v_start)/100)||'s');
V_START := DBMS_UTILITY.GET_TIME;
WHEN REC.SEGMENT_TYPE = 'TABLE' THEN
--
CASE
WHEN REC.SEGMENT_SIZE <= 150 THEN
V_PER := 100;
WHEN REC.SEGMENT_SIZE <= 500 THEN
V_PER := 50;
ELSE
V_PER := 20;
END CASE;

BEGIN
--delete table analyze statistics
DBMS_STATS.DELETE_TABLE_STATS(OWNNAME => UPPER(V_USERNAME),
TABNAME => REC.SEGMENT_NAME);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

BEGIN
--analyze table compute statistics;
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => UPPER(V_USERNAME),
TABNAME => REC.SEGMENT_NAME,
ESTIMATE_PERCENT => V_PER,
CASCADE => TRUE,
GRANULARITY => 'ALL',
DEGREE => 2,
METHOD_OPT => 'FOR ALL INDEXED COLUMNS');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- dbms_output.put_line(rec.segment_name||' '||rec.segment_size||'m '||ceil((dbms_utility.get_time - v_start)/100)||'s');
V_START := DBMS_UTILITY.GET_TIME;
WHEN REC.SEGMENT_TYPE = 'TABLE PARTITION' THEN
CASE
WHEN REC.SEGMENT_SIZE <= 150 THEN
V_PER := 100;
WHEN REC.SEGMENT_SIZE <= 500 THEN
V_PER := 50;
ELSE
V_PER := 20;
END CASE;
BEGIN
--delete table analyze statistics
DBMS_STATS.DELETE_TABLE_STATS(OWNNAME => UPPER(V_USERNAME),
TABNAME => REC.SEGMENT_NAME);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
--analyze table compute statistics;
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => UPPER(V_USERNAME),
TABNAME => REC.SEGMENT_NAME,
ESTIMATE_PERCENT => V_PER,
CASCADE => TRUE,
GRANULARITY => 'ALL',
DEGREE => DBMS_STATS.DEFAULT_DEGREE,
METHOD_OPT => 'FOR ALL INDEXED COLUMNS');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

WHEN REC.SEGMENT_TYPE = 'INDEX PARTITION' THEN
CASE
WHEN REC.SEGMENT_SIZE <= 100 THEN
V_PER := 100;
WHEN REC.SEGMENT_SIZE <= 300 THEN
V_PER := 50;
ELSE
V_PER := 20;
END CASE;
BEGIN
--delete old schema index statistics;
DBMS_STATS.DELETE_INDEX_STATS(OWNNAME => UPPER(V_USERNAME),
INDNAME => REC.SEGMENT_NAME);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

BEGIN
--analyze index compute statistics;
DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => UPPER(V_USERNAME), --自己改一下
INDNAME => REC.SEGMENT_NAME,
ESTIMATE_PERCENT => V_PER,
DEGREE => DBMS_STATS.DEFAULT_DEGREE);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--　　　　dbms_output.put_line(rec.segment_name||' '||rec.segment_size||'m '||ceil((dbms_utility.get_time - v_start)/100)||'s');
V_START := DBMS_UTILITY.GET_TIME;
/** WHEN rec.segment_type = 'LOBINDEX' then
v_start := dbms_utility.get_time;
WHEN rec.segment_type = 'LOBSEGMENT' then
v_start := dbms_utility.get_time;**/
END CASE; END LOOP;
END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/60217/viewspace-912616/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/60217/viewspace-912616/