You Asked

Hi Tom,

We currently use 8.1.6 (in MTS mode) and we plan to bundle
it with our product. Currently, the init.ora sets the optimizer
mode to CHOOSE. Based on my undestanding, it will default to RULE
if we do not analyze the tables and indexes (which is currently
the case).

My questions are :

(i) Should we use COST based optimizer ? Will it result in better
performance and is it suitable in our kind of configuration ?
At this point, none of our queries use any hints.

(ii) If yes, we will have to provide a script to the customers
to analyze tables and indexes on a routine basis.
How often should it be run ? It's hard to guess how often the
data will be changed and how much data will be changed.
Is there a rule of thumb for determining the optimal frequency of gathering statistics ? Will weekly run be OK ?

(iii) I have read that using DBMS_STATS package is more efficient
than "ANALYZE TABLE compute statistics for table for all indexes". Is it true ? Can you do it for all the tables in
a schema without specifying individual table names ?

Thanks,

Gunjeet

and Tom said...

(i) in the best of cases, it'll go faster or the same. In the worst of cases, it will not for some queries. Whether you use it or not is a function of whether you want to use any of the advanced features like function based indexes, descending indexes, bitmapped indexes, parallel query and so on or not. None of those are available with RBO -- only with CBO.

You can certainly try it -- it is easy enough to evaluate.

(ii) you could schedule a job for them instead. The job will run when you tell it to. Without knowing the update activity, it is difficult to say how long is OK to run without updating the stats. A weekly run will probably suffice to start but you can give them directions on howto reschedule the jobs to run more or less often.

(iii) I have not heard that. It has to do the same work.

Rating

(4 ratings)

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

Comments

Analyzing tables and indexes -- Need some Tips

JP, May 02, 2003 - 8:52 am UTC

Dear Tom

Thanks for your all time great suggestions and help.

I have also read the third point mentioned by Gunjeet from Oracle Manual 'Performance Tuning Guide and Reference'.

However, my question is as follows.

I have few tables that are frequently purged from the application point of view. Basically one application inserts data in those tables and another application reads data from those tables and after doing its work, it deletes all the records from those tables and it happens very frequently.

I am using CBO. Analyse my database on a daily basis. But as CBO use dictionary statistics, always the statistics would not be correct in this case. As for example when I analyse those tables, Might be those tables contains around 10K rows. And after deletion those will be empty. At that moment what Optimiser actually consider? As I know Optimiser will consider analysed statistics i.e. 10K.

Will RBO beneficial for this case? Can I use RULE hint? Or any other alternative way I can take care this type of scenario using CBO. Please give some suggestion.

Thanks in advance.
Best Regards
JP

May 02, 2003 - 9:27 am UTC

As for RMAN -- it is because each block not only has to be read for the incremental -- but it must be inspected to see if we need to copy it -- rather then just bulk read/write -- it *probably* will be slower but as with all things in life, there will be exceptions (backing up over a slow WAN for example -- that might reverse the slow to fast)

As for the empty/full table situation -- sounds like you *meant* to use advanced queues which implements this functionality for you (and would even let you magically add more or less back end processes to process the queue without having to write lots of code yourself to mediate access to the data)....

But if the queries are only against this table (eg: you are not using this table in multi-table statements like joins, subqueries, etc) -- using the RBO or hinting it would be acceptable short term (AQ is the right answer). In 9iR2 you have optimizer_dynamic_sampling that can work wonders as well (search the 9iR2 docs on otn for that if you are interested)

which all tables are right candidate ... for twice a week analyze

Pravesh Karthik from India, May 15, 2005 - 11:35 pm UTC

Tom,

Is there a way to see which tables are getting more updates and inserts ?. we have hundreds of tables and we analyze once in a week. some of the fact tables need to analyzed twice a week. In order to know which all tables are right candidate for analyze twice or thrice a week can you help us in knowing that.

Thanks for your consideration,

Pravesh Karthik

May 16, 2005 - 7:40 am UTC

see the alter table <tname> MONITORING statement in the sql reference manual.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_32a.htm#2085225 <code>

A reader, November 03, 2006 - 5:46 am UTC

November 03, 2006 - 10:41 am UTC

brilliant insight, we'll keep that in mind.

LOL!

Michel Cadot, November 03, 2006 - 10:46 am UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer