Step-By-Step Create OEM Metric Extension - Send Alerts on Long Running SQLs Using Metric Extension
Вставка
- Опубліковано 7 жов 2024
- In this tutorial, I am demonstrating the steps to create Metric Extension in OEM along with constructing the SQL queries to be used and how we can use a Metric Extension to send alerts on long running or under-performing SQLs.
A metric extension is an extension to OEM's default capacity to collect certain metric on its targets. For example, OEM collects metrics like tablespace usage, file system usage, number of error logged in alert.log etc. But it does not automatically collect data on SQLs and alert on long running SQLs. To achieve this we will extend OEM's capacity to collect metric data on SQLs running in the database targets and based on conditions applied, the Metric Extension will send critical, warning or clear alerts.
Please note that the run frequency of the ME should be ideally set to a number between half of the threshold minutes and the full threshold minutes set for detecting long running SQLs. For example, if you are trying to catch SQLs running longer than 10 minutes, then the run frequency of the ME should be a number between 5 and 10 (max). If you set smaller frequency like 5, you are less likely to miss an alert. So it is suggested to set a smaller number as run frequency for the ME.
Please watch the video and leave me comments if you have any.
For the SQL queries used, please download the text file from my google drive:
drive.google.c...
Very well-illustrated video. Thanks for sharing your knowledge with us.
Thanks for your feedback.
Hello there,
Your videos are very helpful, informative and easy to follow. Thank you so much for your clear explanations and exmples.
Do you have a video with a clear example on how to setup and send an SMS notification to a recipient from an incident rule within OEM13c, please? For example, from an incident rule when a metric extention(like a blocking lock on a table) is detected.
Hi @DUMIE63, first of all thanks for watching my video and providing your valuable feedback.
I am dividing you question to different parts so I can answer them individually.
I have a full series named "Mastering OEM" on OEM 13c covering topics from installation to setup incident rules and alerts, patching, upgrade etc. If you want, you may watch the full series using the link below:
ua-cam.com/play/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq.html
Now the question 1: Do you have a video with a clear example on how to setup and send an SMS notification to a recipient from an incident rule within OEM13c, please?
Answer: OEM by default does not send an SMS or place a phone call but it is actually easy to achieve. I am explaining alert/monitoring setup in the OEM series in Part 9 and 10. To send SMS or make a phone call, you have to integrate a third party Paging software like PagerDuty, Opsgenie or other similar Pager solution with OEM. This is actually very simple as these software have the option to create an associated email id like dba_team@your_company.pagerduty.com or dba_team@your_company.opsgenie.com etc and you just need to redirect the alert email generated by OEM to the Pager software. Once the software receives the alert email, it will send the SMS, or place a phone call with machine voice or an email etc as per the configuration you did.
To your second question- incident rule when a metric extension (like a blocking lock on a table) is detected:
Please watch below two of my videos:
Oracle Locks Simplified [Part-I] - Understanding Locks Deadlocks and Blocking Locks
ua-cam.com/video/zbPkyqBPKQ8/v-deo.html
Oracle Locks Simplified [Part-2] - Detecting and Alerting on Blocking Locks using SQL and OEM
ua-cam.com/video/OlGxt-eCkuM/v-deo.html
Hope this will help.
@@YouVolve
Hi there Manash,
Thanks for getting back to me so quickly. I really appreciate it.
I have now managed to get past the issue of OEM13c not being able to send text messages. I now invoke an OS script on the AIX DB server from the OEM13c incident rule. The script then executes and sends a cell phone number to a sms server who will send the SMS to the receiver's (standby person). To test this I have hard coded the cell number into the script.
The problem now is that I need to pass the variable values (cell number, DB name, server name, rule name etc.) to the OS script that I am invoking from within OEM13c. Can you explain how I do that please?
Thanks for your much valued reply in advance.
Hey there Manash.
Never mind, I have worked it all out. Thanks again for the reply earlier.
Take care.
Hi Andre, thanks for the update and glad that it worked.
Very well-illustrated Demo. Thank you for sharing. However, instead of the top 1 if I want all the session that is running more than 15, how do we return all rows? Please let us know. Thank you
Hi @sendilkumar9184, thanks for watching my video and providing your feedback.
You can find out all the sessions running over 15 minutes by simply changing the condition "greater than 10" to "greater than 15" in the query. However the OEM alerts are meant to be short/precise and notify about the situation rather than sending all the details. If you modify the outer query to return a "count(*)" that will tell you how many sessions are running over 15 minutes and based on the returned count you can determine the severity such as "greater than 20" as critical to "greater than 15" as warning etc. If you really wants to see the list of all sessions then better to create a BIP Report (deprecated in OEM 13.5) or a PowerBI dashboard etc. Hope this will help.
Thank you for such a clear and precise video. I implemented this and created my incident rule, the alerts are coming in now. Question please:- we noticed an insert statement that has been running for 7+ hours on one of the nodes but no alert is generated. Please do you know what could be the issue here?
Hi, the 5 conditions to trigger an alert are as below: status='ACTIVE'
and username is not null
and type 'BACKGROUND'
and username not in ('SYS')
and last_call_et/60 > 10. When you are expecting an alert and you know the insert statement is still running, please execute the first SQL I have given in the shared document in a SQL*Plus session and see if that returns anything. I suspect the session May have gone into an inactive state. Please let me know what you find.
Good video
Thanks Shymon
Hi. It's a very informative video. Can you also share the link for the video on how to create an incident rule?
Hi Vishweshwar, thanks for your feedback.
I have a full series in UA-cam (link below) on OEM starting from downloading, installation to configuring OEM in a virtual machine for learning and practice covering topics from very basic to mid-advance level. Already 8 episodes have been uploaded and in the next 2-3 weeks I will cover the incident rule, notification and alerting setup. Please watch the series and you will get everything you need to know..
Link to the series:
ua-cam.com/play/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq.html
thanks for share , please make video how to create incident rule , and send email notification
Thanks Arsala. It is in my To-Do list...
Hi Sir according to you we are getting only one row in result, how can we retrieve all long running sqls according to specified time
@neerusharma8482 - Thanks for your question.
This tutorial is for creating a Metric Extension (ME) for alerting on long running SQLs. An ME can operate on a single value to alert on. It cannot accept multiple values returned by the base SQL. Moreover it does not matter how many SQLs are running longer than the threshold you set. It may be 1 or 10 or even more. All you need is an alert to notify that a DB performance issue is going on.
Now coming to your question about how to get a list of all SQLs running longer.
For that, you have to create a report in BI Publisher Reports, a shell script or any other tool.
You may modify the SQL used in the ME to remove the MAX grouping and select the columns in the SELECT list such as SQL_ID, SQL_Text, Elapsed_Time etc. You can tweak the ME and the SQL used to get a few of the SQL IDs in question etc, but not all the details.
@@YouVolve Thanks for reply, I just started watching you sessions. That is very good
Thanks for your feedback.
Can u pls post event rule creation of this metric extension sir
@muralidharkuthethur9749 - Thanks for watching my video. For the Step-by-Step OEM Incident rule creation please watch my video below.
Setting up OEM Notification, Monitoring and Alerting:
ua-cam.com/video/j9bA-xagpsY/v-deo.html
Below is my UA-cam series on Full OEM 13c course:
Mastering OEM 13c
ua-cam.com/play/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq.html
Hope this will help.
I Followed the same steps and i had created incident rule also but iam unable to get alert mails
can you please reply me
Hi Naveen, thanks for watching my video. I am going to cover the incident rules and alert configuration in the next episode (Part-10) of the series “Mastering OEM”which I am going to uploaded either this week or the next. Please watch that series..
Hi sir , you are really amazing , can we monitor a complete schema long running Sqls for 24 hours?
Thank you
Thanks Salad for your question and watching my video.
Can you please elaborate your question and what you meant by "complete schema long running Sqls for 24 hours"?
@@YouVolve thank you sir for your prompt response, I mean, application schema long running SQLs for one complete day . Can we monitor it , if yes how ?
Hi Salad, I am still not very clear on your requirement but trying to answer based on what I understood.
1) Yes you can monitor SQLs running for 24 hours. If you have very long running SQLs and you expect them to take a long time like 24 hours but want to send an alert if the run time crosses 24 hours then all you need to do is to change the time limit in the SQL query used in the Metric Extension from 10 minutes to 1440 minutes. 1440 minutes equal to 24 hours.
2) You can also monitor SQLs run by a particular schema/user. For example, in the tutorial, I am excluding SQLs run by SYS user. You can add another filter expression in the WHERE clause as "username='YOUR SCHEMA NAME' " to monitor long running SQLs only for that schema.
Hope you got the answers you wanted.
How to create incident rule for it ?
Please share video
Thanks for your interest. There are many requests for this and I will definitely share one.
please share the video for creating incident rule
How to remove OEM alerts which came unnecessary
Thanks for your question.
OEM sends the alert only when certain event/incident takes place.
If you just want to remove one occurrence, you can go to the Incident Manager section for the target and delete the incident. If you want to stop it for all future occurrences, then you have to disable/modify the thresholds for the metric for the alerting conditions or if it is coming from from a Metric Extension, then you have to update the thresholds for the alerting conditions or the SQL query (if used) accordingly.
This will return only one session information. What if there are multiple sessions running long running sql's
Hi, you can modify the SQL to return a comma separated list of all or the top-n session IDs. The ME is meant for alerting on the longest running SQL but you can always use your creativity to enhance it. 😀