Hello All,
I have written following statement to analyse each table in all schema's which i have. I see its very long running process. This i am doing to take row count from all_tables (Exact count)
My questions I was told that, this analyzing will take only first time for a table it takes more time, later times it will start from where the earier analyze happned hence time reduces. But i am unable to find any where this info in docs.
Is there any way I can improve performance with following query to analyze all tables in multiple schema;s.
I tried DBMS_XMLGEN even this taking too much time to gather stats.
SET SERVEROUTPUT ON;
DECLARE
v_table_name varchar2(500);
lv_sql varchar2(500);
BEGIN
FOR v_table_name IN ( SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME NOT like 'ERR%' AND TABLE_NAME NOT LIKE 'T_XXX%')
LOOP
DBMS_OUTPUT.PUT_LINE('STARTED......' || v_table_name.TABLE_NAME );
EXECUTE IMMEDIATE 'ANALYZE TABLE '|| v_table_name.TABLE_NAME || ' COMPUTE STATISTICS FOR TABLE';
DBMS_OUTPUT.PUT_LINE('Completed......' || v_table_name.TABLE_NAME );
END LOOP;
END;