Re: Preventative measures against slow V$RMAN_STATUS queries

> Here is what I get with 11.2.0.3 when I do the recommended actions specific
> to SYS.X$KCCRSR (fixed table stats have been gathered):
>
> SQL> exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');

[snip]

> Statistics
> ----------------------------------------------------------
> �������� 26� recursive calls
> ��������� 4� db block gets
> �������� 41� consistent gets

[snip]

> SQL> exec dbms_stats.gather_Fixed_objects_stats()

[snip]

> Statistics
> ----------------------------------------------------------
> ��������� 1� recursive calls
> ��������� 0� db block gets
> ��������� 0� consistent gets

[snip]

> To be honest I'd be worried that deleting the stats (whatever they may be)
> and locking them will bite you at some point down the line.� Since fixed
> tables don't get dynamic sampling in the absence of statistics keeping the
> computed stats would seem to be the best course of action.

I read that dynamic sampling on fixed tables was allowed in 11.2 (bug 6608941).

Also, wouldn't you have to UNLOCK_TABLE_STATS before collecting stats again for it to take effect? I don't know how fixed tables are affected by the buffer cache, but I get similar autotrace results just running the SELECT (against V$RMAN_STATUS) twice in a row, regardless of the state of stats on X$KCCRSR. Thanks!

Rich

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