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