Which table to analyze?


This script I wrote in a very pecuiliar scenario. In a SAP system heavily loaded /Huge OLTP scenario. I found that queries are taking long term to execute and one or two of the big table with index has wrong figures in the dictionary. There is a huge difference in the num_row value and select count(*) value for a table and its associated index. And after analysing the statistics one such table I got a difference in performance. So in an aim to find out the probable candidates which we may need to analyze, wrote and used a script.

You may also use/modify/rewrite the script with non warranty clauses for your system as written below.

select to_char(sysdate,'DD-MON-YYYY HH:MM:SS') Start_time from dual;

set serverout on;

Declare

-- Declaration of variables

currcount number;
ndsqry varchar2(500);
errqry varchar2(500);
msg1 varchar2(500);
resqry varchar2(500);

cursor c1 is
select * from dba_tables where
owner='SAPR3' and
num_rows>30000 -- I choosed 30000 records and SAPR3 user, you choose yours.
;

c1_rec c1%ROWTYPE;
currtable varchar2(200);

-- Begin processing

Begin
-- code below creates a result table which contains the evaluation data
Begin
dbms_output.put_line('Test1....');
select nvl(count(*),0) into currcount from dba_tables where table_name = 'SC_RESTAB1';
if currcount = 0 then
dbms_output.put_line('Result table do not exists.. Creating the table');
ndsqry := 'create table sc_restab1 as '||
'Select Owner,table_name,tablespace_name,cluster_name,'||
'iot_name,last_analyzed,num_rows,blocks,empty_blocks,chain_cnt,'||
'iot_type, last_analyzed eval_on,num_rows eval_count '||
'from dba_tables where 2=1';
EXECUTE IMMEDIATE ndsqry;
else
dbms_output.put_line('Result table already exists.. Result Data will be populated there');
end if;
currcount := 0;
Exception
when others then
raise_application_error(-20001,'An error was encountered *1*- '||SQLCODE||' -ERROR- '||SQLERRM);
End;
-- end Creation of result table creation.
-- Creation of error table --
Begin
dbms_output.put_line('Test2....');
select nvl(count(*),0) into currcount from dba_tables where table_name = 'SC_ERRTAB1';
if currcount = 0 then
dbms_output.put_line('Error table do not exists.. Creating the table');
ndsqry := 'create table sc_errtab1 (errtxt varchar2(1000)) ';
EXECUTE IMMEDIATE ndsqry;
else
dbms_output.put_line('Error table already exists.. Error Data will be inserted there');
end if;
Exception
when others then
raise_application_error(-20001,'An error was encountered *2*- '||SQLCODE||' -ERROR- '||SQLERRM);
End;
-- End creation of error table --
open c1;
-- dbms_output.put_line('Test3....');
loop
ndsqry:=''; /* Initialisation of the query string */
currcount:=0;
fetch c1 into c1_rec;
exit when c1%NOTFOUND;
-- checking physical rowcounts in the tables
Begin
currtable:=trim(c1_rec.owner)||'.'||trim(c1_rec.table_name);
ndsqry:= 'SELECT NVL(COUNT(*),0) FROM ' || currtable;
EXECUTE IMMEDIATE ndsqry INTO currcount;
-- dbms_output.put_line(ndsqry|| 'value of currcount > '||currcount);
Exception
When others then
dbms_output.put_line('Testing...');
-- raise_application_error(-20001,'An error was encountered *3*- '||SQLCODE||' -ERROR- '||SQLERRM);
msg1:= c1_rec.owner||'.'||c1_rec.table_name||' >> '||'An error was encountered *3*- '||SQLCODE||' -ERROR- '||SQLERRM;
errqry:= 'insert into SC_ERRTAB1 values ('''||msg1||''')';
EXECUTE IMMEDIATE errqry;
-- dbms_output.put_line(errqry);
End;
-- Inserting result records here --
Begin
resqry:= 'insert into sc_restab1 values(:a,:b,:c,:d,:e,:f,:g,:h,:i,:j,:k,:l,:m)';
EXECUTE IMMEDIATE resqry
using c1_rec.Owner,c1_rec.table_name,c1_rec.tablespace_name,c1_rec.cluster_name,
c1_rec.iot_name,c1_rec.last_analyzed,c1_rec.num_rows,
c1_rec.blocks,c1_rec.empty_blocks,c1_rec.chain_cnt,c1_rec.iot_type,sysdate,currcount;
-- dbms_output.put_line(resqry);

Exception
When others then
-- dbms_output.put_line(resqry);
raise_application_error(-20001,'An error was encountered *4*- '||SQLCODE||' -ERROR- '||SQLERRM);
end;
-- End Insert of result records.
end loop;
close c1;
-- End processing
end;
/
---</pre>
set lines 300
set pages 30000
-- Creations of list.
spool a1.lst
select * from sc_restab1 where num_rows<>eval_count
order by owner,last_analyzed,num_rows;
spool off
select to_char(sysdate,'DD-MON-YYYY HH:MM:SS') End_time from dual;

Advertisements

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