Pls help to interpret

[Laurel]

Hi Erman!
Can you pls look at this AWR report..
The problem is that Scheduling is running too long  > 10 hours..
Here i run request  for 20 min  and cancelled it taking awr snaps and the main culprit in OEM is this sql with enormous execution times > 3 mln  .. its part of  OE_SCH_CONC_REQUESTS.Request

Anything else you see as a problem in our db ? that could contribute to our issue why its taking so much time..
 fm7rv4gf8akub
SELECT PEGGING_ID ,   IDENTIFIER3, IDENTIFIER2, IDENTIFIER1, SUPPLY_DEMAND_TYPE, INVENTORY_ITEM_ID, CHAR1, ORGANIZATION_ID, SUPPLY_DEMAND_DATE, SUPPLY_DEMAND_QUANTITY, DEPARTMENT_ID, RESOURCE_ID, ORDER_LINE_ID, SUPPLIER_ID, SUPPLIER_SITE_ID, SUPPLIER_ATP_DATE, DEST_INV_ITEM_ID, SUMMARY_FLAG , AGGREGATE_TIME_FENCE_DATE FROM MRP_ATP_DETAILS_TEMPWHERE (( PEGGING_ID <> :B2 AND (:B3 = 2 OR :B3 = 3)) OR (:B3 = 1)) AND RECORD_TYPE IN (3, 4) AND SESSION_ID = :B1 START WITH PEGGING_ID = :B2 AND SESSION_ID = :B1 AND RECORD_TYPE = 3 CONNECT BY PARENT_PEGGING_ID = PRIOR PEGGING_ID AND SESSION_ID = PRIOR SESSION_ID AND RECORD_TYPE IN (3, 4)

Thx alot!
Laurelawr.zip

[ErmanArslansOracleBlog]

Your db is not heavly loaded. Your db is okay.
Your top event is CPU, but you are using 0,4 cpu per sec.
Your I/O subsystem is performing good as well.

That SQL (SELECT PEGGING_ID) you have sent to me is CPU bound and It is executed so frequently.
BEGIN OE_SCH_CONC_REQUESTS.Request takes plenty of time as well. It also spents its time significantly on CPU.
Bytheway, this is not a CPU problem. You have the necessary CPU resources.
You are just using them.
And those 2 session that I mentioned above do lots of "gets".

So, I would concantrate on tracing the related sessions that are executing these sqls.
I would check the execution plan as well.
I would run the BEGIN OE_SCH_CONC_REQUESTS manually with correct arguments and check.
I would go inside the sqls that are present in OE_SCH_CONC_REQUESTS.Request and see if there is a big loop or a heavy sql statement there.. I would try to check the tables from where it gets the data..  Maybe there is something to be done in terms of archiving or purging..