RE: Schedule Analyze using DBMS_STATS ???

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 03 Jun 2003 09:05:05 -0800
Message-ID: <F001.005A90C8.20030603090505@fatcity.com>

The sample output looks like ...

oraclei_at_elara-NCS1> 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-----

Sent: Tuesday, June 03, 2003 12:02 PM
To: 'ORACLE-L_at_fatcity.com'

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 !

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jun 03 2003 - 12:05:05 CDT