Oracle-L: RE: OEM Events

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Mon, 14 Apr 2003 07:01:44 -0800
Message-ID: <F001.00580647.20030414070144@fatcity.com>

Also, watch the control file I/O with the tablespace full event. We also used to run it every five minutes, but backed it off to 30 mins after watching what the query was doing on our system.

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Monday, April 14, 2003 4:54 AM
To: Multiple recipients of list ORACLE-L

Sunny,

Given the list below, I would say that the real "performance killer" would be the "max_extents" check. This would of course depend upon the number of tables and indexes with your instances schemas, but I would never tell anybody to set that one collection on an interval of 5 minutes. Every 5 hours maybe.. Even better, how about an "Event" that checks for tables or indexes that can't throw 5 extents, or a percentage of extents allocated and check that every 6 hours? "Your mileage may vary"..

What you mention about the tablespace check seems odd to me as well.. I'm no expert on OEM events (maybe someone else can help on that side), but can't you create your own custom event, based upon a script that checks all tablespaces utilisation, and evaluate it on a row by row basis (per tablespace)? Again, if you monitor this by tablespace percentage, how about setting it to 90% full, and doing it on a less frequent basis. The key is in setting the "threshold" (the event trigger) to something that gives you a reasonable amount of time to fix the "problem" before it arises..

And the final thing to take note of, is that each database that you have will have it's own characteristics, there is really no hard "set in stone" guideline for monitoring frequencies. You as the DBA are there to apply the human logic, once getting a feel for how your individual databases grow or behave.

There are a number of example "Collections" that have been written for our own monitoring tool available from:

http://www.cool-tools.co.uk/Support/UDC/Oracle/

They are all SQL based, and have been built by users, or on request by users.. Maybe you can make use of them. :)

HTH 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-----
Sent: 13 April 2003 17:43
To: Multiple recipients of list ORACLE-L

Hello,
Is anyonw aware of performace issues related to setting too many OEM (9.2.0.1) events ???
At the site that I am currently at, they have the following events set on all production databases. The frequenct for ALL these events is 5 minutes probe
tablespace_full
max_extents
process_limit
session_limit
For one thing I am noticing that OEM does not use bind variables (go figure !!!), so if there are a 100 sessions I see 100 statements with the only diff. being sid = ???? . Also if there are say a 100 tablespaces again, there are a 100 statements in the shared pool with the tablespace name being the only difference. In my mind , setting the above events at the frequency specified can cause performance problems, especially if it is a very active database. IS that accurate ?? If so, are there any notes / white papers about this.

Thanks,
Sunny

Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Mark Leith
INET: mark_at_cool-tools.co.uk

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

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------