Re: find tables/indexes with locked stats

On Nov 20, 11:56 am, Ben <bal..._at_comcast.net> wrote:
> 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
> againstdba_ts_quotasusing 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 againstdba_ts_quotasand if so, how did you solve the issue?
>
> Thanks

Just to document this issue for anyone else that might be having issues with OEM and selects against dba_ts_quotas. I found bug 5350195 in metalink that pointed me to other bugs and issues related to this view but nothing was "Published" on them. After trying their suggestions with no results I opened an SR and got a very helpful analyst that informed me of an issue with that view using a new view and suggested that I recreate the dba_ts_quotas view as it existed in 9.2.x

I've recreated the view and that fixed the issue. Now I just wonder how many other views have been changed and do not perform well. I suspect dba_extents needs to be re-written as well, as a script I had created now takes about 10 times longer than it did pre-upgrade. Received on Tue Dec 04 2007 - 13:19:45 CST