and that select 'PING' from dual is very costly. I believe Gaja, Cary, Anjo, and a few others (who were nice enough to include me on the thread) determined that before 9i Oracle takes about 5 LIOs to do a select "anything" from dual. I think it went down to either 3 or 4 in 9i but that's still VERY costly.
Dan Fink has that discussion summarized on his site: http://www.optimaldba.com/internals/oraint_dual.html Rachel --- Mark Leith <[EMAIL PROTECTED]> wrote: > Yup, got that covered :) > > If I may add another point. When dealing with SLA's, you not only > have to > "show" that the database has been servicing users that are connected > (showing database uptime), but also that users can also *connect* to > the > database as well (the listener is servicing requests). > > If you were to go about this with a trigger/procedure that inserts in > to a > table, then this doesn't show that the database was available to > "everybody". > > Typically what we do with monitoring tools is a "connect on ping", so > when > we are checking availability of a database we do a full connect, then > "select 'PING' from dual;". If there are any errors along the way we > search > for the error code, and deal with the appropriate alerts (TNS = > Listener > "problem", ORA = Database "problem"). > > Of course, the problem with doing it this way, is that you are going > to have > to write platform dependant scripts (batches for NT, shell type > scripts for > Unix), and not have a "one for all" with a database based solution. > > Go buy a tool. Let it write to a table/file, alert you AND make the > coffee > for when you get there! ;) > > Mark > > =================================================== > Mark Leith | T: +44 (0)1905 330 281 > Sales & Marketing | F: +44 (0)870 127 5283 > Cool Tools UK Ltd | E: [EMAIL PROTECTED] > =================================================== > http://www.cool-tools.co.uk > Maximising throughput & performance > > -----Original Message----- > Richard > Sent: 03 December 2002 02:14 > To: Multiple recipients of list ORACLE-L > > > Perhaps there is a "poor mans" way of doing this. The startup > trigger > could fire a procedure that inserts a row into a table and then > sleeps for > 1 minute before doing the same again. Effectively it would create a > ping > in the table, which you could then analyze / graph to display > uptimes. > > The next logical step would be to increase the intelligence of the > procedure. The table storing the statistic could consist of two > columns - > uptime and downtime. When the startup trigger fires it creates a new > row > in the table with both uptime and downtime set to sysdate. It then > sleeps > for a minute before updating downtime for the most recent record > (either > remember a primary key or search for max(uptime)). This would be > much > easier to understand when the database was stopped / started. > > Of course depending on your accuracy requirement, granularity could > be > changed to every 5 minutes, 10 minutes, whatever. > > Hopefully that gives some ideas though. Of course the 3rd party > monitors > that Jared mentions are worth considering if the database is > considered > critical. In that case the number one requirement is probably the > ability > to page / SMS / email when it sees the database is down. > > Regards, > Mark. > > > > > Jared.Still@ra > disys.com To: Multiple recipients > of list > ORACLE-L <[EMAIL PROTECTED]> > Sent by: cc: > [EMAIL PROTECTED] Subject: Re: SLA > Trigger/Procedure > om > > > 03/12/2002 > 12:13 > > Please respond > to ORACLE-L > > > > > > > Ethan, > > That records the startup times, but does not record the time > that the database was unavailable. > > What's needed is a 3rd party monitor that is not dependent > on the database being up to record metrics. > > Jared > > > > > > "Post, Ethan" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 12/02/2002 02:33 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject: SLA Trigger/Procedure > > > Just a thought here for a script I think would be handy but I haven't > had > time to write. > > It is would be a simple procedure you could call to get the service > level > for a particular database. I suppose you would have to have some > sort of > way of defining normal outage windows. Basically a startup trigger > would > log the times in a table. You should also check the startup time > against > the last startup time periodically to ensure the trigger always > fires. > Somehow a procedure/function should be able to use this information > to > report the service level for the database within the last > (week/month/quarter/year). > > I suppose I will get around to it eventually but if anyone else wants > to > get > started on it I won't mind! > > Thanks, > Ethan > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Post, Ethan > INET: [EMAIL PROTECTED] > > 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: [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). > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > 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: [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). > > > > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > >>>> > Privileged/Confidential information may be contained in this > message. > === message truncated === __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] 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: [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).
