Ha! I am but can't come up for air most days. Looks like you're all set with
the blog about MEs! Let me know if you hit a snag!
Sent from my iPad
On Dec 7, 2016, at 8:25 PM, Kellyn Pot'Vin-Gorman <dbakevlar@xxxxxxxxx> wrote:
Hi Bill,
Knowing Courtney, she's as busy as I am, saw that you had an answer and was
relieved...:)
Let me know if this satisfies the initial challenge and you feel you're on
your way or you need more.
Thanks,
Kellyn Pot'Vin-Gorman
Technical Intelligence Manager
Delphix
Sent from myMail for iOS
Wednesday, December 7, 2016, 10:24 AM -0700 from William Ndolo Intertek
<william.ndolo@xxxxxxxxxxxx>:
Hi Kellyn,
From my long vacation, may I thank you for this response. I wonder if awesome
Courtney is on this thread J
I would love to understand better the licensing aspect of this.
I think we intend to stay within EM’s repository.
And yes, your 3rd point is something we are aware of.
I have done some testing with the script below and we see some spike in
latency.
WITH SQLM as
(select sql_id,sql_exec_start,sql_exec_id
,MAX(M.user#) as UserNum
,MAX(M.username) as UserName
,MAX(NVL(PX_QCInst_ID,M.inst_id)) as ExecInst
,MAX(NVL(PX_QCsid,M.sid)) as ExecSid
,MAX(NVL(PX_QCsid,M.session_serial#)) as ExecSerial
,MAX(CASE PX_QCsid WHEN null THEN null ELSE M.status END) as Status
,DECODE(count(distinct px_server#),0,'SERIAL','PARALLEL') as PQ_SERIAL
,COUNT(DISTINCT M.inst_id) as instances
,MAX(NVL(PX_MAXDOP,1)) as MaxDOP
,MAX(last_refresh_time) as last_refresh_time
,SUM(elapsed_time/1000000) as DBtimeSecs
,ROUND((MAX(last_refresh_time)-MIN(sql_exec_start))*24*60*60,1) as
ExecElapsedSecs
,ROUND((MAX(last_refresh_time)-MIN(sql_exec_start))*24*60*60 +
SUM(NVL(queuing_time,0)/1000000),1) as TotalElapsedSecs
,ROUND(SUM(NVL(queuing_time,0)/1000000),1) as QueuingSecs
,SUM(cpu_time/1000000) as CPUsecs
,SUM(user_io_wait_time/1000000) as IOsecs
,SUM((application_wait_time+concurrency_wait_time+cluster_wait_time)/1000000)
as WaitSecs
,SUM((plsql_exec_time+java_exec_time)/1000000) as JavaPLSQLsecs
,SUM(buffer_gets) as BuffGets
,SUM(disk_reads) as DiskReads
,SUM(direct_writes) as DirectWrites
from
gv$sql_monitor M
group by sql_exec_id,sql_exec_start,sql_id
)
select sql_id as SQL_ID
,ROUND(SUM(CPUSecs)) as TotalCPUSecs
,SUM(ExecElapsedSecs) as TotalElapsedSecs
-- ,IOSecs
-- ,WaitSecs
,SUM(BuffGets) as TotalBuffGets
,SUM(DiskReads) as TotalDiskReads
,MAX(UserName) as SampleUser
,COUNT(*) as NumExecs
from
SQLM
where
( status like 'EXECUTING%' -- currently executing or
OR (status like 'DONE%' AND last_refresh_time > SYSDATE - 15/(24*60)) --
finished last 15 minutes?
)
group by sql_id
having upper(MAX(Username)) <> 'TOPSQL'
Thanks and best regards,
Bill
From: Kellyn Pot'Vin-Gorman [mailto:dbakevlar@xxxxxxxxx] ;
Sent: Tuesday, November 22, 2016 12:50 PM
To: William Ndolo Intertek <william.ndolo@xxxxxxxxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: OEM Notifications for Long Running Queries and MViews not
Refreshed
But Houston does have the awesome Courtney Llamas, so you do have EM
expertise in the house! :)
I’ve been very busy attempting to write over ever block of memory I have in
my head in my new role at the Technical Intelligence Manager at Delphix, but
I can offer some insight into the opportunity to use EM12c to monitor this:
1. A metric extension will do quite nicely to monitor for both of these.
2. You’ll need to ensure you query this information from the correct metric
within EM’s repository, as a remote call would push you outside of EM’s
“domain” and licensing, if that makes sense.
3. How often you collect the information and how lightweight you make the
query to collect it will deem if it’s too much for a heavily used system.
I do have an view lag script in my list of scripts on my DBAKevlar page,
under Misc. Scripts. There’s a few different ways to look at long running
queries and the decision maker is, “Do you want long running queries total or
execution time changed from average elapsed time?”
Here’s a blog post to take you through getting starting with Metric
Extensions.
Hope this helps!
Kellyn
On Nov 22, 2016, at 10:09 AM, William Ndolo Intertek
<william.ndolo@xxxxxxxxxxxx> wrote:
OEM gurus,
We intend to configure OEM to be able to notify us whenever there are long
running SQL or long running jobs. Please see below.
We are using Enterprise Manager Cloud Control 12c.
Can this be done in a very busy system? If so, can someone share the best
way to do this?
On the other hand, we would like to be notified whenever MViews refresh
stalls for an hour.
Currently we are using notification from alert log. This has worked but only
when ORA- errors are generated.
Any recommendation would be appreciated especially from shops heavily using
OEM for notification.
Kellyn Pot'Vin-Gorman, if I am not wrong, this is your area of expertise. I
remember you from RMOUG when I gave presentation in 2013.
Unfortunately, Houston does not have the level of involvement Denver had.
<image005.jpg>
<image006.jpg>
Thanks and best regards,
Bill
Valued Quality. Delivered.
CONFIDENTIALITY NOTICE
This e-mail may contain confidential or privileged information, if you are
not the intended recipient, or the person responsible for delivering the
message to the intended recipient then please notify us by return e-mail
immediately. Should you have received this e-mail in error then you should
not copy this for any purpose nor disclose its contents to any other person.
Intertek is dedicated to Customer Service and welcomes your feedback. Please
visit http://www.intertek.com/email-feedback/ to send us your suggestions or ;
comments. We thank you for your time.
Except where explicitly agreed in writing, all work and services performed by
Intertek is subject to our Standard Terms and Conditions of Business which
can be obtained at our website: http://www.intertek.com/terms/ Should you ;
have any difficulty obtaining these from the web site, please contact us
immediately and we will send a copy by return.
