Posted in 2015

Gather stats on tables with data

Sometimes during support of upgrades of apps we have some tasks to gather statistics on tables. I have tried to implement a procedure which is gathering it. The input is the schema name. As I cannot implement procedures just because it makes things easier I have implemented it on a test database and tested it with my own schema. Well, it works.

create or replace PROCEDURE sys$gather_tab_stats(schema_name IN varchar2) as
cursor c1 is select table_name from dba_tables where owner=schema_name ;
cursor_name integer ;
output_tabs user_tables%ROWTYPE ;
rows_processed integer ;
cnt number ;
begin_time number ;
end_time number ;
BEGIN
FOR output_tabs IN c1
LOOP
dbms_output.put_line('Processing table: '||output_tabs.table_name) ;
cursor_name := dbms_sql.open_cursor ;
dbms_sql.parse(cursor_name,'select count(*) from '||schema_name||'.'||output_tabs.table_name||' sample(10)',DBMS_SQL.NATIVE);
dbms_sql.define_column(cursor_name,1,cnt) ;
rows_processed := DBMS_SQL.EXECUTE(cursor_name);
IF DBMS_SQL.FETCH_ROWS(cursor_name)>0
THEN
DBMS_SQL.COLUMN_VALUE(cursor_name, 1, cnt) ;
dbms_output.put_line('COUNT: '||cnt);
IF cnt>0 THEN
begin_time := DBMS_UTILITY.GET_TIME;
DBMS_STATS.GATHER_TABLE_STATS(schema_name,output_tabs.table_name) ;
end_time := DBMS_UTILITY.GET_TIME;
dbms_OUTPUT.PUT_LINE('elapsed time: '||to_char((end_time-begin_time)/100)||' secs.') ;
END IF;
END IF;
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END LOOP ;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
/

Usage:
exec sys$gather_tab_stats('TEST_SCHEMA')

Advertisements

Author:

Database administrator who loves to work with Oracle software. (Sometimes not)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s