| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SLA Trigger/Procedure
So do a select from a view based on sys.x$dual. That does not have cause
LIO.
I checked a few months ago.
Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent: Tuesday, December 03, 2002 4:32 PM
> 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 <mark_at_cool-tools.co.uk> 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: mark_at_cool-tools.co.uk
> > ===================================================
> > 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_at_ra
> > disys.com To: Multiple recipients
> > of list
> > ORACLE-L <ORACLE-L_at_fatcity.com>
> > Sent by: cc:
> > root_at_fatcity.c 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" <Ethan.Post_at_ps.net>
> > Sent by: root_at_fatcity.com
> > 12/02/2002 02:33 PM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > 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: Ethan.Post_at_ps.net
> >
> > 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).
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> > INET: Jared.Still_at_radisys.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).
> >
> >
> >
> >
> >
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > message.
> > >>>>
> > Privileged/Confidential information may be contained in this
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: adar76_at_inter.net.il 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 Wed Dec 04 2002 - 07:48:46 CST
 |
 |