Tuesday, May 21, 2013

Oracle 10g:Get Stale statistics info in mail

We need to update the statistics of those statistics stale.

For this,Schedule one procedure that mail the info of stale statistics in oracle.

1.Create Procedure for mail the info.

CREATE OR REPLACE PROCEDURE USP_STALE_STATS_INFO
as
vmsg varchar2(10000);
vsql varchar2(5000);
i int;
t int;
begin
vmsg:='<html><table><tr><td ><b>::Stale index statistics info::</b></td></tr>';
i:=0;
t:=0;
--index statistics..
for ind in
(select owner,table_name,index_name
from dba_ind_statistics
where   STALE_STATS='YES' and owner='PIN'
)
loop
i:=i+1;
vsql:='EXEC dbms_stats.gather_index_stats('''||ind.owner||''','''||ind.index_name||''' , estimate_percent=>100);';
vmsg:=vmsg||'<tr><td>'||vsql||'</tr></td>';
end loop;
vmsg:=vmsg||'</table><br />';
vmsg:=vmsg||'<table><tr><td><b>::Stale Table statistics info::</b></td></tr>';
--for table statistics
for tbl in
(select
      owner,TABLE_NAME,(num_rows*avg_row_len)/1024/1024 sizeinmb 
from dba_tab_statistics
where STALE_STATS='YES' and owner='PIN'
)
loop
t:=t+1;
vsql:='EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'''||tbl.owner||''',tabname=>'''||tbl.TABLE_NAME||''',CASCADE=>TRUE,DEGREE=>1,estimate_percent =>100);';
vmsg:=vmsg||'<tr><td>--'||tbl.TABLE_NAME||'::'||to_char(tbl.sizeinmb,'9,999')||' Mbs</td></tr>';
vmsg:=vmsg||'<tr><td>'||vsql||'</tr></td>';
end loop;
vmsg:=vmsg||'</table></html>';

if (i >0 or t>0) THEN
UTL_MAIL.SEND(sender=>'DatabaseExperts@de.com', recipients=>'List of Comma separated email', cc =>'' , subject=>'DatabaseName Stale statistics info.', message => vmsg,  mime_type => 'text/html');
End if;
end;

2.Schedule this procedure by DBMS_Scheduler.

BEGIN
    sys.DBMS_SCHEDULER.CREATE_SCHEDULE (
          
        repeat_interval  => 'FREQ=HOURLY;INTERVAL=4',    
        comments => 'This is job wil mail stale table/indexes info',
        schedule_name  => '"Stale_Schedule"');
       
END;

3.Create DBMS_JOB.

BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"SYSTEM"."Stale_info_job"',
            schedule_name => '"SYSTEM"."Stale_Schedule"',
            job_type => 'STORED_PROCEDURE',
            job_action => '"SYSTEM"."USP_STALE_STATS_INFO"',
            number_of_arguments => 0,
            job_class => '"SYS"."DEFAULT_JOB_CLASS"',
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'This job will mail table/index stale statistics info');

    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
             name => '"SYSTEM"."Stale_info_job"',
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);
 
    SYS.DBMS_SCHEDULER.enable(
             name => '"SYSTEM".""');
END;
/

4.Run the sql statement.

::Stale index statistics info::
EXEC dbms_stats.gather_index_stats('***','*****' , estimate_percent=>100);

::Stale Table statistics info::
--***_T:: 0 Mbs
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'***',tabname=>'**_T',CASCADE=>TRUE,DEGREE=>1,estimate_percent =>100);

2 comments:

  1. A stale article, if you dip it in a good, warm, sunny smile, will go off better than a fresh one that you've scowled upon. see the link below for more info.


    #stale
    www.ufgop.org


    ReplyDelete

  2. There are things in our life that is not eternal thus we stand still and never show weaknesses. This only means that we are strong enough to face everything either it is big or small. Visit my site for more good vibes and inspirational thoughts. Good day!

    n8fan.net

    www.n8fan.net

    ReplyDelete

Dear User,

Thank you very much for your kind response