Re: truncate WRI$ tables !

From: Nitin Saxena <ntnsxn7_at_gmail.com>
Date: Wed, 10 Jan 2018 08:54:13 -0800
Message-ID: <CAM+YwA9aHDJEfD_FfrjUAQ0xPTKKxoJWAL8+1Bv-0P2_XYjZ9g_at_mail.gmail.com>

When we set retention to a lower value there will be mass delete happening in background which can cause waits in database. Any thoughts ? Coz this is saving 5+ minutes of downtime of a critical database upgrade.

SQL> execute dbms_stats.purge_stats(sysdate - 7);

Basically this issues a series of delete statements (including a delete on the “stats operation log (*wri$_optstat_opr*)” table that I haven’t previously mentioned) – here’s an extract from an 11g trace file of a call to this procedure (output from a simple grep command): delete /*+ dynamic_sampling(4) */ from
sys.wri$_optstat_tab_history where savtime < :1 and rownum <= NVL(:2, rownum)
delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_ind_history h where savtime < :1 and rownum <= NVL(:2, rownum) delete /*+ dynamic_sampling(4) */ from
sys.wri$_optstat_aux_history where savtime < :1 and rownum <= NVL(:2, rownum)
delete /*+ dynamic_sampling(4) */ from

sys.wri$_optstat_opr                  where start_time < :1 and rownum <=

NVL(:2, rownum)
delete /*+ dynamic_sampling(4) */ from
sys.wri$_optstat_histhead_history where savtime < :1 and rownum <= NVL(:2, rownum)
delete /*+ dynamic_sampling(4) */ from
sys.wri$_optstat_histgrm_history where savtime < :1 and rownum <= NVL(:2, rownum)

On Wed, Jan 10, 2018 at 5:51 AM, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On 01/09/2018 11:40 AM, Stefan Knecht wrote:
>
>> Personally, I'd refrain from just truncating them. I'd instead use
>> dbms_stats.purge_stats to purge the entire history, and then, IF the tables
>> are empty, truncate them.
>>
>> I wouldn't do that. Those tables will be re-populated and the space will
> be allocated again. By leaving the space allocated, one can save some time
> on the next DBMS_STATS runs.
>
> Regards
>
> --
> Mladen Gogala
> Database Consultant
> http://mgogala.freehostia.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Cheers
Nitin

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 10 2018 - 17:54:13 CET