The sample output looks like ...
[EMAIL PROTECTED]> sys
SQL*Plus: Release 9.2.0.2.0 - Production on Tue Jun 3 11:57:51 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
11:57:51 SQL> select * from system.v_analysis_info;
Analysis Information
-----------------------------------------------------------------------------
Group 01 includes 2490 tables, analysis should take approx 01574.93 seconds.
Group 02 includes 65 tables, analysis should take approx 01579.41 seconds.
Group 03 includes 26 tables, analysis should take approx 01578.01 seconds.
Group 04 includes 16 tables, analysis should take approx 01586.48 seconds.
Group 05 includes 9 tables, analysis should take approx 01472.85 seconds.
Group 06 includes 5 tables, analysis should take approx 01594.39 seconds.
Group 07 includes 2 tables, analysis should take approx 01550.56 seconds.
Group 08 includes 1 tables, analysis should take approx 01169.40 seconds.
Group 09 includes 1 tables, analysis should take approx 01749.20 seconds.
Group 10 includes 1 tables, analysis should take approx 01991.25 seconds.
10 rows selected.
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: Jamadagni, Rajendra
Sent: Tuesday, June 03, 2003 12:02 PM
To: '[EMAIL PROTECTED]'
Subject: RE: Schedule Analyze using DBMS_STATS ???
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 !
********************************************************************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
