RE: dba_extents view in Oracle 10.2.0.2
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Jun 2006 08:20:03 -0500
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@acx To
iom.com> <genegurevich@xxxxxxxxxxxxxxxxxxxxx
>, "oracle-l"
06/24/2006 02:01 <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxx>
-------------------------------------
"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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of genegurevich@xxxxxxxxxxxxxxxxxxxxx
> 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
>
>
> --
> //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.
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
