Best way to analyze across different platforms

[adrianomp]

I'm planning to implement an automated analyze procedure on an environment, and need some directions to decide the best way. AFAIK, there are three methods:

- ANALYZE statement
- DBMS_UTILITY: deprecated on 9i
- DBMS_STATS

However, it is a mixed environment (7.3.4, 8.0.5, 8.1.7 and possibly 9.2 on the future). DBMS_STATS is not available on 7.3.4 and 8.0.5, it would be my choice for all databases otherwise.

I'd like to use the same analyze method on all databases, so the process could be easily replicated to any future instances without my intervention. I'll probably use DBMS_JOB or crontab on Unix.

I'm not sure if it will be possible, can someone give an idea? What analyze method would meet the best possible performance and trustability across these platforms?

[slimdave]

You're going to end up with the lowest common denominator approach, which definately doesn't sound like a good idea. Your requirements on performance and version independence are contradictory

I would define different procedures appropriate to each version -- the versions are backward compatible in the sense that if you upgrade v8 -> v9, your v8 methodology is going to work until you upgrade the analyze procedure to the apropriate methodology anyway.

[marist89]

Yes, I would create a version specific api that knew how to analyze a single table. I would then create another package that knew how to pass a table to the api to analyze.

[adrianomp]

You're going to end up with the lowest common denominator approach, which definately doesn't sound like a good idea

Yeah, that's what I meant, I think there is no "easy" way to achieve everything and get good results.

Worst, I found out that the hardware is not the same, so I can't rely blindly on DBMS_STATS features like parallelism even on newer Oracle versions (some boxes with 2-cpu, some with 4, some with only one).

Thanks for the advice, it seems better to break the routine in two pieces.