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
Posted in 2015

Monitoring redo generation

Sometimes I am checking how much redo is generated during the day to know if I have enough space in the arch destination during reorgs.

To check redolog generation I use:

break on SUBSTRING skip 1
compute sum label 'TOTAL' of MB on SUBSTRING
set lines 180 pages 999


select count(1) "Rate" ,trunc(FIRST_TIME,'HH24') "By Hour",round(sum(blocks*block_size)/1024/1024,2) MB, trunc(FIRST_TIME,'DD') as substring
from v$archived_log where dest_id=1 group by trunc(first_time,'HH24'),trunc(FIRST_TIME,'DD') order by 2 ;

or:

PROMPT Max redo in MB
with max_day as (
select trunc(FIRST_TIME,'DD') as day from v$archived_log group by trunc(FIRST_TIME,'DD') having count(*)=(select max(cnt) from (select count(*) cnt,trunc(FIRST_TIME,'DD') from v$archived_log group by trunc(FIRST_TIME,'DD')))
)
select sum(blocks*block_size)/1024/1024 "MB per Max Day",max_day.day from v$archived_log,max_day where trunc(FIRST_TIME,'DD')=max_day.day and dest_id=1 group by max_day.day;

Sometimes you have to decide how much storage do you need for a database. You are not an oracle who knows how the application works, about the workload and other topics. Developers should say how much is needed in the beginning. After some time it’s possible even for you:

  • We add 25% more storage, it should be enough for 24 hours.


with l as (select distinct bytes/1024/1024/1024 as siz from v$log),
c as (select max(cnt) cntl from (select count(1) cnt,trunc(FIRST_TIME,'DD') sdate from v$archived_log where dest_id=1 group by trunc(FIRST_TIME,'DD')))
select l.siz*c.cntl*1.25 "Size for arch dest for 24h" from l,c ;