RE: dba_extents view in Oracle 10.2.0.2

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Sat, 24 Jun 2006 14:01:55 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3601EE0EDF@CWYMSX04.Corp.Acxiom.net>

I haven't checked out this bug yet, but a couple of us did come across this problem this past week. We found that having collected dictionary stats, the problem showed up. Deleting them caused the problem to go away (3 hr query vs. 3 second!). Unfortunately, one DBA pointed out that the scheduled "gather_stats_job" job puts stats back on them every night.

I poked around a little further and found that stats on SYS.SEG$ was the main problem. Not sure why that confused the CBO so much, but after deleting stats and locking stats on this table, the problem went away while still allowing stats on all other dictionary objects AND letting the scheduled "gather_stats_job" keep running nightly.

HTH. Dave

Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri_at_acxiom.com>

"When I come home from work and see those little noses pressed against the windowpane, then I know I am a success" - Paul Faulkner
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of genegurevich_at_discoverfinancial.com
> Sent: Friday, June 23, 2006 5:02 PM
> To: oracle-l
> Subject: dba_extents view in Oracle 10.2.0.2
>
> Everybody:
>
> Has anyone experienced performance issues with dba_extents view (bug
> 5029334)? I was told by Oracle that
> the workaround is to create a new view and use it instead of
dba_extents.
> When I asked whether I can just recreate
> the dba_extents view I was told that the results can be far reaching
and
> should be fully tested. I interpret that as
> Oracle does not really know what the results are. I wanted to see if
> anyone has hit this bug and what did you do.
>
> thank you
>
> Gene Gurevich
>
>
> --
> http://www.freelists.org/webpage/oracle-l

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank you.

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jun 24 2006 - 14:01:55 CDT