Re: find tables/indexes with locked stats

On Nov 20, 11:06 am, Ben <bal..._at_comcast.net> wrote:
> 10.2.0.2 Ent Ed AIX5L
>
> Can someone direct me to a sql statement to use in finding tables with
> locked stats? I'm aware of dba_tab_statistics.stattype_locked but I'm
> not real sure what it is telling me.
>
> I just queried a table that I thought was locked and it showed 'null',
> then I gathered stats on that table and now queried again and it shows
> 'ALL'.
I need to expound a little more at some other problems related to this. There is documentation on metalink related to OEM queries against dba_ts_quotas using a ton of CPU and taking forever. It's been reported quite frequently but they haven't published the bugs related to it. The only solution I can find is where they suggest to execute dbms_stats.gather_dictionary_stats and hopefully this helps. Well it hasn't helped and in looking at some of the tables that the query uses, I found sys.tsq$. After running the dictionary stats procedure, I looked at that table and the stats haven't been updated for it. I checked the dba_tab_statistics to see if the table stats are locked and they are not according to that view.

Has anyone else had this issue with OEM and it's queries against dba_ts_quotas and if so, how did you solve the issue?

Thanks Received on Tue Nov 20 2007 - 10:56:55 CST