Re: find tables/indexes with locked stats

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.

Fix
===
connect sys as sysdba
alter system set optimizer_secure_view_merging=false;

http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/3ee2ecec9692457d

regards SEJ

"Ben" <benalvey_at_yahoo.com> skrev i en meddelelse news:7ae31490-8df2-4063-ab34-adc47f2980ee_at_x69g2000hsx.googlegroups.com...
> 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 Wed Dec 05 2007 - 12:52:53 CST