0

I want to build a scheduled job in Enterprise Manager to trigger every hour the following query:

SELECT output FROM TABLE(dbms_workload_repository.ash_global_report_text(584958394, Null, SYSDATE-1/24, SYSDATE, l_wait_class=>'Scheduler'));

I want to capture Events and the percentage only if I get one with Scheduler resmgr:cpu quantum And then I want to parse this output only out. I get now the big varchar(320) column output when I hit the query, but I want also to build a PL/SQL package where I can add variables inside and also to parse out only the important percentage of the resmgr: cpu quantum event. Other events are not important to me.

Any help would be appreciated.

Kind regards.

3
  • So why dont you just query DBA_HIST_ACTIVE_SESS_HISTORY or V$ACTIVE_SESSION_HISTORY directly? What is the original problem that you tried to solve with this? Aug 5 '20 at 14:28
  • HI. I would like to get a percentage of the usage of DB time , which was consumed with resmgr: cpu time, in an hour. I want to trigger this every hour using metric alarms over Enterprise manager. I want to get something like this.Wait Event Wait Time Summary Avg Wait Time I# Class Event Waits %Timeouts Total(s) Avg Wait %DB time Avg Min Max Std Dev Cnt Scheduler resmgr:cpu quantum 17,768 0.00 75.20 4.23ms 0.74 4.24ms 4.23ms 4.26ms 25.34us 2 Can I use something like IF INSTR (cur_rep.output, 'SQL ordered by Elapsed Time') THEN to extract from the output? Aug 6 '20 at 4:12
  • The ACTIVE_SESSION_HISTORY view shows every second an output. But I would need a percentage in a time frame from one hour. This is where AWR report comes in. Aug 7 '20 at 8:16
0

The DB time can be queried from dba_hist_sys_time_model.

resmgr: cpu quantum can be queried from dba_hist_system_event.

snap_id can be matched to time periods through dba_hist_snapshot.

Instead of generating a report then parsing it, use the above views to query what you need.

1
  • Hi. Seems like a good approach. So this would mean that I need to combine dba_hist_system_event and dba_hist_snapshot to get the results. How can I have this i one query that I trigger, that shows me the past hour of results ? Do you have an example maybe ? Aug 30 '20 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.