We don't "analyze" schema, we use dbms_stats ...
I wrote a package that analyzes everything using dbms_stats and (for my instance) it runs in 10 parallel streams. It splits all the tables in 10 streams so that all of them take approximately same amount of time.
This package captures the time it takes to analyze each table, everyday and balances the table among 10 parallel streams.
I prefer to use cron, but you can use it with dbms_job as well.
Currently it does only tables (and cascades to indexes automatically). We don't deal with partitions right now, so that needs to be added. After a certain number of rows, it collects stats in parallel too. I am currently working on interfacing it with dba_tab_modifications ... so the logic will decide if the table changes are say < 5%, skip the table from stats collections.
Raj
--------------------------------------------------------------------------------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-----Original Message-----
From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 03, 2003 11:05 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Schedule Analyze using DBMS_STATS ???
Thanks Mark - That is exactly what I was just getting ready to write!
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2
