RE: dba_extents view in Oracle 10.2.0.2

From: <genegurevich_at_discoverfinancial.com>
Date: Mon, 26 Jun 2006 08:20:03 -0500
Message-ID: <OF713FF9B4.DE33C063-ON86257199.00491300-86257199.00493231@discoverfinancial.com>

Dave,

Thanks for the info. I have deleted the stats from sys.seg$, but I don't see much improvement. I'll try removing them from other tables next.

thank you

Gene Gurevich
Oracle Engineering
224-405-4079

                                                                           
             "Herring Dave -                                               
             dherri"                                                       
             <Dave.Herring_at_acx                                          To 
             iom.com>                  <genegurevich_at_discoverfinancial.com 
                                       >, "oracle-l"                       
             06/24/2006 02:01          <oracle-l_at_freelists.org>            
             PM                                                         cc 
                                                                           
                                                                   Subject 
                                       RE: dba_extents view in Oracle      
                                       10.2.0.2

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 Mon Jun 26 2006 - 08:20:03 CDT