Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Koshal.

Asked: December 12, 2005 - 11:15 am UTC

Last updated: December 12, 2005 - 2:27 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hi Tom

One of our table is taking long time to analyze 9 hrs.
I want to have all possible statistics in. So I when I am doing Analyze Table t1 compute statistics, it is taking about 9 hrs. We have oracle Parallel Server, is there any way
1. I can run this job in two or more sessions.
2. Can I run analyze in paralle like SQL.



Thanks


and Tom said...

you would use dbms_stats
</code> http://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_stats.htm#999107 <code>

(as you should be - if you have 9i by the way, you cannot have Oracle Parallel Server, you either


a) have parallel query with the enterprise edition
b) have RAC (real application clusters) in a clustered environment)


dbms_stats supports parallel query, analyze does not. Also, if you have a single table that takes 9hours, there is a very very good chance that ESTIMATE is what you meant to use, that you do not need compute

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

koshal, December 12, 2005 - 2:09 pm UTC

Hi Tom
Henceforth I will use only dbms_stats. but out of curiosity I would like to know the following.

1. I thought by having table analyzed with complete statistics, the probability of optimizer choosing the best execution path is more rather than analyzing table with estimate statistics. Is that statement true?

2. On the table I mentioned (126 million rows) , we only do daily inserts/select and we analyze once in a week.
Instead of having estimate statistics how abt analyzing table on monthly basis. Generally on a table with millions of rows The probability of change in execution plan/probability of deriving approriate plan is more with a) Weekly analyze with estimate statistics or b)monthly analyze with compute statistics.

Thanks
Koshal








Thanks
Koshal


Tom Kyte
December 12, 2005 - 2:27 pm UTC

1) but if the table is sooo large, an estimate is probably sufficient. It is a matter of scale.

2) every time you analyze you are hoping plans change. so the probability of a change in plan is in line with the number of times you gather statistics.

You might be very very interested in the book I reference on my home page here by Jonathan Lewis.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_STATS

More on PL/SQL routine DBMS_STATS here