Oracle-L: RE: Tricky SQL Question -- Solved

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 07 Mar 2003 05:44:15 -0800
Message-ID: <F001.00563190.20030307054415@fatcity.com>

Jonathan,

Thanks for the tips ... let me see how I incorporate this ... things to do ...

write a *clever* routine to look at sys.mon_mods$ with dbms_stats.flush_database_monitoring_info to decide which tables to analyze in the next session.
Split the groups automatically between two instances ... if once instance is down, other will pick up all streams .. *this is easy to do in pl/sql).

This allows me to have only two scripts to setup analysis on my 10 production and 24 other instances without a major maintenance. Now I am able to *predict* how much time it takes ... based on last analysis ... here is a sample input based on yesterday's data ...

oraclei_at_ariel-CSI2> . oraenv
ORACLE_SID = [CSI2] ? ABC
oraclei_at_ariel-ABC2> sys
SQL*Plus: Release 9.2.0.2.0 - Production on Fri Mar 7 08:36:56 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected.
SQL> set serveroutput on
SQL> exec system.dbpk_statistics.refresh_rank; Ranking based on analyze time is complete. Select from view "V_ANALYSIS_INFO" for an *estimate*.

PL/SQL procedure successfully completed. SQL> set line 200
SQL> select * from system.v_analysis_info;

Analysis Information

-
Group 01 includes 2160 tables, analysis should take approx 00134.10 seconds.
Group 02 includes 413 tables, analysis should take approx 00134.03 seconds.
Group 03 includes 33 tables, analysis should take approx 00130.91 seconds.
Group 04 includes 11 tables, analysis should take approx 00128.63 seconds.
Group 05 includes 4 tables, analysis should take approx 00113.87 seconds.
Group 06 includes 4 tables, analysis should take approx 00152.77 seconds.
Group 07 includes 2 tables, analysis should take approx 00098.63 seconds.
Group 08 includes 2 tables, analysis should take approx 00180.71 seconds.

8 rows selected.

I have configured it to make 8 parallel streams ..

Here are some things that I am doing ...

The information is stored in a index organized table
As soon as the script loads, it loads (bulk collect) the lost of tables belonging to the group specified into an array.
Start executing dbms_stats on the tables in the array based on their parameters, capture elapsed times for analysis
If any analysis errors out, it also captures error message
Update the index organized table with
last analyzed timestamp
time it took to analyze the table
error message if any
uses dbms_stats to get latest rowcount
Exits

The package has procedures to performs the set-up
(tables/view/procedure/package creation). It also does two types of ranks,
first time when analysis times are not available, it groups them by row count. After first analysis, it re-ranks them based on analysis time which is more accurate than row count.

Thanks once again for all the ideas ...
Raj

Rajendra dot Jamadagni at espn dot com
Any views expressed here 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).



text/plain attachment: ESPN_Disclaimer.txt

Received on Fri Mar 07 2003 - 07:44:15 CST