Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SLA Trigger/Procedure

RE: SLA Trigger/Procedure

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Tue, 03 Dec 2002 09:49:07 -0800
Message-ID: <F001.00510F74.20021203094907@fatcity.com>


To be fair, there is some disagreement about adding an index on DUAL. I have not duplicated the scenario that Cary presents and I have been too distracted (writing a paper/presentation for Rachel) to get back to my testing. So take with a small grain of salt.

Dan

-----Original Message-----
Sent: Tuesday, December 03, 2002 7:32 AM To: Multiple recipients of list ORACLE-L

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).
>
>
>
>

>

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

> >>>>
> 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: wisernet100_at_yahoo.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: Dan.Fink_at_mdx.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 Dec 03 2002 - 11:49:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US