AWR dbms_workload_repository ash_global_report_text extract text from output

awroracleperformance

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.

Best Answer

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.

Related Question