Dick, I did some work on this some time ago, but never got around to fully implementing it. What it was supposed to do is take a day's worth of stats from each instance and store it in a table called DAILY_STATS. We take hourly stats on each instance, so the procedure takes the stats from today's midnight snapshot, subtracts the value of the stats form yesterdays midnight snapshot, and stores the deltas in DAILY_STATS.
I figured that I might not want to save the values for all the stats, so I created a table called STATS_DICTIONARY which contains the stats which are to be saved - see stats_dictionary.sql for an example. Hopefully this will give you something of use to build upon. Gary Gary Kirsh Next Extent Consulting -----Original Message----- Sent: Friday, January 03, 2003 9:09 AM To: Multiple recipients of list ORACLE-L I also was informed during the iTAR process that they will support a centralized repository, but you'll have to wait till 10i for it. Heck I've haven't gotten a play instance of 9i up yet. 10i, maybe by 2010!!! I really don't want to wait that long!! Dick Goulet ____________________Reply Separator____________________ Author: "Jamadagni; Rajendra" <[EMAIL PROTECTED]> Date: 1/2/2003 12:51 PM Steve, I am leaning more towards Oracle Streams ... Raj ______________________________________________________ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -----Original Message----- Sent: Thursday, January 02, 2003 3:41 PM To: Multiple recipients of list ORACLE-L An Oracle emp told me they plan to support a centralized statspack repository but it's not supported yet. In the stats$snapshot table the snap_id, dbid, and instance_number columns should be unique. The trick is automagically populating the mother of all statspack repositories with a maintenance-free routine. Replication? I've modified some of the statspack tables and it still works. You'll need to study the ddl for the statspack objects of course. So if you have a repository of 1/2 dozen databases is it a statspack sixpack? SQL>desc stats$snapshot Name Null? Type ----------------------------------------- -------- ---------------------------- SNAP_ID NOT NULL NUMBER(6) DBID NOT NULL NUMBER INSTANCE_NUMBER NOT NULL NUMBER SNAP_TIME NOT NULL DATE STARTUP_TIME NOT NULL DATE SESSION_ID NOT NULL NUMBER SERIAL# NUMBER SNAP_LEVEL NUMBER UCOMMENT VARCHAR2(160) EXECUTIONS_TH NUMBER PARSE_CALLS_TH NUMBER DISK_READS_TH NUMBER BUFFER_GETS_TH NUMBER SHARABLE_MEM_TH NUMBER VERSION_COUNT_TH NUMBER ALL_INIT VARCHAR2(5) Steve Orr Bozeman, MT -----Original Message----- <mailto:[EMAIL PROTECTED]> ] Sent: Thursday, January 02, 2003 11:34 AM To: Multiple recipients of list ORACLE-L I am currently working on a design ... basically it is simple to set-up, but the problem I am facing is how to automatically move dataset for one snapshot from prod db to the central db. Raj ______________________________________________________ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -----Original Message----- Sent: Thursday, January 02, 2003 1:14 PM To: Multiple recipients of list ORACLE-L To ALL, We'd like to establish a centralized stats pack repository, but OTS is telling us that it's NOT doable for a couple of reasons. As you can guess I don't believe them and am looking around to see if anyone else has done this before I break out the power tools and start building something on my own. Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.net <http://www.orafaq.net> -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com <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: [EMAIL PROTECTED] (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). <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1"> <TITLE>RE: Centralized StatsPack Repository</TITLE> <META content="MSHTML 5.50.4522.1800" name=GENERATOR></HEAD> <BODY> <DIV><SPAN class=986215120-02012003><FONT face="Courier New" color=#0000ff size=2>Steve,</FONT></SPAN></DIV> <DIV><SPAN class=986215120-02012003><FONT face="Courier New" color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=986215120-02012003><FONT face="Courier New" color=#0000ff size=2>I am leaning more towards Oracle Streams ...</FONT></SPAN></DIV> <DIV><SPAN class=986215120-02012003><FONT face="Courier New" color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=986215120-02012003><FONT face="Courier New" color=#0000ff size=2>Raj</FONT></SPAN></DIV> <DIV><SPAN class=986215120-02012003></SPAN><FONT face="Courier New" size=2>______________________________________________________</FONT></DIV> <P align=left><FONT face="Courier New" size=2>Rajendra Jamadagni MIS, ESPN Inc.</FONT></P> <P align=left><FONT face="Courier New" size=2>Rajendra dot Jamadagni at ESPN dot com</FONT></P> <P align=left><FONT face="Courier New" size=2>Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. </FONT></P> <P align=left><FONT face="Courier New" size=2>QOTD: Any clod can have facts, but having an</FONT> <FONT face="Courier New" size=2>opinion is an</FONT> <FONT face="Courier New" size=2>art!</FONT></P> <BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px"> <DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Orr, Steve [mailto:[EMAIL PROTECTED]]<BR><B>Sent:</B> Thursday, January 02, 2003 3:41 PM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> RE: Centralized StatsPack Repository<BR><BR></FONT></DIV> <P><FONT size=2>An Oracle emp told me they plan to support a centralized statspack repository but it's not supported yet. In the stats$snapshot table the snap_id, dbid, and instance_number columns should be unique. The trick is automagically populating the mother of all statspack repositories with a maintenance-free routine. Replication? I've modified some of the statspack tables and it still works. You'll need to study the ddl for the statspack objects of course.</FONT></P> <P><FONT size=2>So if you have a repository of 1/2 dozen databases is it a statspack sixpack?</FONT> </P><BR><BR> <P><FONT size=2>SQL>desc stats$snapshot</FONT> <BR><FONT size=2> Name   ;& nbsp;   ;&nb sp; & nbsp ; Null? Type</FONT> <BR><FONT size=2> ----------------------------------------- -------- ----------------------------</FONT> <BR><FONT size=2> SNAP_ID &n bs p; &n bsp; NOT NULL NUMBER(6)</FONT> <BR><FONT size=2> DBID   ;& nbsp;   ;&nb sp; & nbsp ; NOT NULL NUMBER</FONT> <BR><FONT size=2> INSTANCE_NUMBER &n bsp; &nbs p; NOT NULL NUMBER</FONT> <BR><FONT size=2> SNAP_TIME &n bsp; &nbs p; NOT NULL DATE</FONT> <BR><FONT size=2> STARTUP_TIME &nb sp ; &nb sp;& nbsp; NOT NULL DATE</FONT> <BR><FONT size=2> SESSION_ID   ;& nbsp;   ;&nb sp; NOT NULL NUMBER</FONT> <BR><FONT size=2> SERIAL# &n bs p; &n bsp; &nbs p;&n bsp; NUMBER</FONT> <BR><FONT size=2> SNAP_LEVEL   ;& nbsp;   ;&nb sp; & nbsp ; NUMBER</FONT> <BR><FONT size=2> UCOMMENT & nb sp; & nbsp ; &nb sp;& nbsp; VARCHAR2(160)</FONT> <BR><FONT size=2> EXECUTIONS_TH &n bs p; &n bsp; &nbs p;&n bsp; NUMBER</FONT> <BR><FONT size=2> PARSE_CALLS_TH & nb sp; & nbsp ; &nb sp;& nbsp; NUMBER</FONT> <BR><FONT size=2> DISK_READS_TH &n bs p; &n bsp; &nbs p;&n bsp; NUMBER</FONT> <BR><FONT size=2> BUFFER_GETS_TH & nb sp; & nbsp ; &nb sp;& nbsp; NUMBER</FONT> <BR><FONT size=2> SHARABLE_MEM_TH &n bsp; &nbs p; &n bsp; NUMBER</FONT> <BR><FONT size=2> VERSION_COUNT_TH   ;& nbsp;   ;&nb sp; NUMBER</FONT> <BR><FONT size=2> ALL_INIT & nb sp; & nbsp ; &nb sp;& nbsp; VARCHAR2(5)</FONT> </P><BR> <P><FONT size=2>Steve Orr</FONT> <BR><FONT size=2>Bozeman, MT</FONT> </P><BR><BR> <P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From: Jamadagni, Rajendra [<A href="mailto:[EMAIL PROTECTED]";>mailto:[EMAIL PROTECTED] </ A>]</FONT> <BR><FONT size=2>Sent: Thursday, January 02, 2003 11:34 AM</FONT> <BR><FONT size=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT size=2>Subject: RE: Centralized StatsPack Repository</FONT> </P><BR> <P><FONT size=2>I am currently working on a design ... basically it is simple to set-up, but the problem I am facing is how to automatically move dataset for one snapshot from prod db to the central db.</FONT></P> <P><FONT size=2>Raj </FONT><BR><FONT size=2>______________________________________________________ </FONT><BR><FONT size=2>Rajendra Jamadagni & nb sp; MIS, ESPN Inc. </FONT><BR><FONT size=2>Rajendra dot Jamadagni at ESPN dot com </FONT><BR><FONT size=2>Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. </FONT><BR><FONT size=2>QOTD: Any clod can have facts, but having an opinion is an art! </FONT></P><BR> <P><FONT size=2>-----Original Message----- </FONT><BR><FONT size=2>From: [EMAIL PROTECTED] [<A href="mailto:[EMAIL PROTECTED]";>mailto:[EMAIL PROTECTED]</A>] </FONT><BR><FONT size=2>Sent: Thursday, January 02, 2003 1:14 PM </FONT><BR><FONT size=2>To: Multiple recipients of list ORACLE-L </FONT><BR><FONT size=2>Subject: Centralized StatsPack Repository </FONT></P><BR> <P><FONT size=2>To ALL, </FONT><BR><FONT size=2> We'd like to establish a centralized stats pack repository, but OTS is </FONT><BR><FONT size=2>telling us that it's NOT doable for a couple of reasons. As you can guess I </FONT><BR><FONT size=2>don't believe them and am looking around to see if anyone else has done this </FONT><BR><FONT size=2>before I break out the power tools and start building something on my own. </FONT><BR><FONT size=2>Dick Goulet </FONT><BR><FONT size=2>-- </FONT><BR><FONT size=2>Please see the official ORACLE-L FAQ: <A target=_blank href="http://www.orafaq.net";>http://www.orafaq.net</A> </FONT><BR><FONT size=2>-- </FONT><BR><FONT size=2>Author: </FONT><BR><FONT size=2> INET: [EMAIL PROTECTED] </FONT><BR><FONT size=2>Fat City Network Services -- 858-538-5051 <A target=_blank href="http://www.fatcity.com";>http://www.fatcity.com</A> </FONT><BR><FONT size=2>San Diego, California -- Mailing list and web hosting services </FONT><BR><FONT size=2>--------------------------------------------------------------------- </FONT><BR><FONT size=2>To REMOVE yourself from this mailing list, send an E-Mail message </FONT><BR><FONT size=2>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in </FONT><BR><FONT size=2>the message BODY, include a line containing: UNSUB ORACLE-L </FONT><BR><FONT size=2>(or the name of mailing list you want to be removed from). You may </FONT><BR><FONT size=2>also send the HELP command for other information (like subscribing). </FONT></P></BLOCKQUOTE></BODY></HTML>
stats_dictionary.sql
Description: Binary data
stats_summary.sql
Description: Binary data
