--分析表
REATE OR REPLACE PROCEDURE analyze_tables IS
--rec integer;
v_date1 number(10);
v_date2 number(10);
V_SQL varchar2(512);
v_msg varchar2(512);
BEGIN
FOR rec IN (SELECT table_name FROM user_tables)
LOOP
v_date1 := dbms_utility.get_time;
V_SQL := 'ANALYZE TABLE ' || rec.table_name || ' COMPUTE STATISTICS';
BEGIN
EXECUTE IMMEDIATE V_SQL;
v_msg := V_SQL || ' OK!';
EXCEPTION
WHEN OTHERS THEN
v_msg := V_SQL || ' Error!';
END;
v_date2 := dbms_utility.get_time;
v_msg := v_msg || ' 分析花费时间:' || to_char((v_date2 - v_date1) / 100);
DBMS_OUTPUT.put_line(v_msg);
END LOOP;
END;
----分析索引
CREATE OR REPLACE PROCEDURE analyze_indexs IS v_date1 number(10); v_date2 number(10); V_SQL varchar2(512); v_msg varchar2(512); BEGIN FOR rec IN (SELECT index_name FROM user_indexes) LOOP v_date1 := dbms_utility.get_time; V_SQL := 'ANALYZE INDEX ' || rec.index_name || ' COMPUTE STATISTICS'; BEGIN EXECUTE IMMEDIATE V_SQL; v_msg := V_SQL || ' OK!'; EXCEPTION WHEN OTHERS THEN v_msg := V_SQL || ' Error!'; END; v_date2 := dbms_utility.get_time; v_msg := v_msg || ' 分析花费时间:' || to_char((v_date2 - v_date1) / 100); DBMS_OUTPUT.put_line(v_msg); END LOOP; END;