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;
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;
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;
/
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); |
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.
ReplyDelete#stale
www.ufgop.org
ReplyDeleteThere 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
That is the proper blog for anybody who desires to search out out about this topic. You realize so much its nearly onerous to argue with you (not that I actually would want…HaHa). You positively put a brand new spin on a subject thats been written about for years. Great stuff, simply great! casino slots
ReplyDelete