AWR dbms_workload_repository ash_global_report_text extract text from output

Question

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.

• Balazs Papp: 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?
• ultimo_frogman: 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?
• ultimo_frogman: 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.

Answer 1

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.

• ultimo_frogman: 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 ?