Welcome to dbFreaks.com!
FAQFAQ      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

CBO: statistics collection

  
   Database Help (Home) -> Oracle -> Server RSS
Next:  sql memory planning w/ perfmon  
Author Message
wagen123

External


Since: Dec 07, 2004
Posts: 5



(Msg. 1) Posted: Mon Feb 21, 2005 6:06 pm
Post subject: CBO: statistics collection
Archived from groups: comp>databases>oracle>server (more info?)
Oracle8i 8.1.7.4 (64 bit)
Solaris 2.9
OLTP db

We generate statistics once a day using the "analyze table xyz estimate
statistics". Will be switching to DBMS_STATS soon after migrating to
9i/10g. One fine day a specific query which used the index started
doing full table scans. Creating a histogram (10 buckets) fixed the
problem. Looks like skewed data.

There are many queries that search on columns that are much more skewed
than the above example, but we don't see a problem here.

How does one go about determing when to generate statistics using
histograms? What should be the strategy from an overall db perspective?

Any pointers?

Thanks

 >> Stay informed about: CBO: statistics collection 
Back to top
Login to vote
DA Morgan

External


Since: Oct 17, 2004
Posts: 115



(Msg. 2) Posted: Tue Feb 22, 2005 12:05 pm
Post subject: Re: CBO: statistics collection [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
wagen123.DeleteThis@yahoo.com wrote:

 > Oracle8i 8.1.7.4 (64 bit)
 > Solaris 2.9
 > OLTP db
 >
 > We generate statistics once a day using the "analyze table xyz estimate
 > statistics". Will be switching to DBMS_STATS soon after migrating to
 > 9i/10g. One fine day a specific query which used the index started
 > doing full table scans. Creating a histogram (10 buckets) fixed the
 > problem. Looks like skewed data.
 >
 > There are many queries that search on columns that are much more skewed
 > than the above example, but we don't see a problem here.
 >
 > How does one go about determing when to generate statistics using
 > histograms? What should be the strategy from an overall db perspective?
 >
 > Any pointers?
 >
 > Thanks

When there is a defined problem based on testing or user complaints.

--
Daniel A. Morgan
University of Washington
damorgan.DeleteThis@x.washington.edu
(replace 'x' with 'u' to respond)<!-- ~MESSAGE_AFTER~ -->

 >> Stay informed about: CBO: statistics collection 
Back to top
Login to vote
user2118

External


Since: Dec 15, 2004
Posts: 22



(Msg. 3) Posted: Tue Feb 22, 2005 3:48 pm
Post subject: Re: CBO: statistics collection [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
At the risk of trolling, http://www.dba-oracle.com/art_otn_cbo_p4.htm

http://www.ixora.com.au/scripts/query_opt.htm#consider_histogram

There've been discussions here and there about not generating estimated
stats every day, to avoid the problem you experienced. If you know
better than the CBO... why not get full stats less often and lock them
down?

I disagree with the "when there are complaints" reactive style of
tuning. There are enough things to go wrong without additional things
that can be avoided by proper proactive administration. The trick is
to get management to let you administer "properly" without subjecting
anyone to compulsive tuning disorder. I haven't figured out any magic
to reliably perform that trick.

http://www.freelists.org/archives/oracle-l/12-2004/msg01218.html

jg
--
@home.com is bogus.
What was true in the past may not be true in the future.
 >> Stay informed about: CBO: statistics collection 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
statistics. - Hello i am looking at V9 and try to figure out the equivalent command in oracle of update statistics high for table mytable(mycolumn) in informix this will command will produce a distribution of data and will help the optimizer when there are a high..

Statistics/measurements - I have my application running on 10g and want to move to 11g shortly. I setup everything on 11g on a development (identical hardware) sever. Management wants some statistics/measurements showing any differences between the two (if there are any). What....

AQ and JMS Question - Hi, I am integrating with an application which uses a PL/SQL call specification as a method of extending the application. Ultimately I wont to put the parameter on a Queue (backed by Oracle AQ) and have it consumed by Message Driven Beans (which use JMS...

Possible use of a Cursor - I have a complex query that I hope I can explain it well enough for everyone to understand. I have a table that contains information for work instructions. Contained in the table are "MACHINE_PROC" these are groupings of machining processes....

sql query problem related to rownum. please help - i need to do a top-n query after doing a sorting using order by. the query i am using is as below select * from (select * from table order by colA,colB) where rownum < 100 i am having almost 1 lakh records in the table and because of the two levels...
   Database Help (Home) -> Oracle -> Server All times are: Pacific Time (US & Canada) (change)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]