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'.

On Nov 20, 11:06 am, Ben <bal...@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

On Nov 20, 11:56 am, Ben <bal...@comcast.net> wrote:
> On Nov 20, 11:06 am, Ben <bal...@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.

On Nov 20, 11:06 am, Ben <bal...@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'.

Did you think about patching to 10.2.0.3 .... there are a ton of
things whacko with 10.2.0.2.

We are running 10.2.0.3 on all systems currently ... and hoping to see
10.2.0.4 soon.

On Dec 4, 3:17 pm, hpuxrac <johnbhur...@sbcglobal.net> wrote:
> On Nov 20, 11:06 am, Ben <bal...@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'.
>
> Did you think about patching to 10.2.0.3 .... there are a ton of
> things whacko with 10.2.0.2.
>
> We are running 10.2.0.3 on all systems currently ... and hoping to see
> 10.2.0.4 soon.



I wanted to go straight to 10.2.0.3 but we are bound by JDE/PeopleSoft/
Oracle ERP compatibility.

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....e2ecec9692457d

regards SEJ

"Ben" <benalvey@yahoo.com> skrev i en meddelelse
news:7ae31490-8df2-4063-ab34-adc47f2980ee@x69g2000hsx.googlegroups.com...
> On Nov 20, 11:56 am, Ben <bal...@comcast.net> wrote:
>> On Nov 20, 11:06 am, Ben <bal...@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.