Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: find tables/indexes with locked stats

Re: find tables/indexes with locked stats

From: SEJ <>
Date: Wed, 5 Dec 2007 19:52:53 +0100
Message-ID: <4756f36a$0$21929$>

Hi maybe ..

Doc ID: Note:468380.1 Type: PROBLEM secure view merging which is causing the change of execution plan when the query is executed as different user.
When a user is not the owner of a view, indexes are not used or the execution plan is not optimal and therefore query performance is impacted.

connect sys as sysdba
alter system set optimizer_secure_view_merging=false;

regards SEJ

"Ben" <> skrev i en meddelelse
> On Nov 20, 11:56 am, Ben <> wrote:
>> On Nov 20, 11:06 am, Ben <> wrote:
>> > 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 Wed Dec 05 2007 - 12:52:53 CST

Original text of this message