help with sql monitor report

AndrewSayer

kaericn wrote:Thanks for your input!!d) sounds like a good idea! we probably wont put it in for now the perf is good for that sql in our mission critical ETL chain.c)And in the mission critical system we load apl_repair_publish 100 records at a time.and every time, it finishes it delete the rows in apl_repair_publish so the below data set is always very small.and everytime if we join that apl_repair_publish, the result set should be small and in this case it is ZERO rows as we are loading new data.And on this HANG or whatever you call it exercise, we are doing data-fix and we are loading < 999 records at a time. We have finished 254 such batches all with very good perf.and then all of a sudden this issue HAPPENS!!?? So why Oracle all of a sudden changes its mind when everything was running good before ??And more important to us is how do we know Oracle will not change its plan all of a sudden like this case in our mission critical ETL chain ??As like you state it wont help as you stated in b)Our boss is conservative in any code change while it is running good.However, if this happens in our mission critical app, we would get call in 3am in the morning California time...

1. SELECT
2. rsn.repair_note_idASrepair_note_id,
3. rsn.repair_status_idASrepair_status_id,
4. rn.repair_id,
5. rn.type_cd,
6. rn.created_dt,
7. rs.status_cd,
8. rs.status_dt,
9. 'N'new_status,
10. 'N'new_note
11. FROM
12. repair_status_notersn,
13. repair_notern,
14. repair_statusrs,
15. apl_repair_publisharp
16. WHERE
17. rsn.repair_note_id=rn.repair_note_id
18. ANDrsn.repair_status_id=rs.repair_staTUS_IDANDARP.REPAIR_ID=RS.REPAIR_IDANDARP.BATCH_ID=:B3ANDARP.ID>=:B2ANDARP.ID<=:B1

SELECT rsn.repair_note_idASrepair_note_id, rsn.repair_status_idASrepair_status_id, rn.repair_id, rn.type_cd, rn.created_dt, rs.status_cd, rs.status_dt, 'N'new_status, 'N'new_note FROM repair_status_notersn, repair_notern, repair_statusrs, apl_repair_publisharp WHERE rsn.repair_note_id=rn.repair_note_id ANDrsn.repair_status_id=rs.repair_staTUS_ID AND ARP.REPAIR_ID=RS.REPAIR_IDANDARP.BATCH_ID=:B3ANDARP.ID>=:B2ANDARP.ID<=:B1 

As already mentioned, the bad execution plan has been forced by yourselves in some way.

It may not always appear slow because sometimes that plan is fine for the data you have. Oracle has not decided to just be slow. It’s been given different data which when joined to in the way youve forced it ends up with huge result sets early on in the execution - so nested loops have to be executed more until finally it hits a filter/join that removes the majority of the rows.

You can usually see how the plan has been forced when you look at it from the cursor cache (dbms_xplan.display_cursor with appropriate arguments). The notes section will have plenty of clues.

Have you read through the monitor for the slow execution yourself? You can easily see that Oracle is having to cross join two large result sets to get 217 MILLION rows and then execute the rest of your joins until it finally gets to 0 rows. The estimated costs are telling us that the optimizer really doesnt want to use that plan but is being forced to (probably with a base line that you’ve created). As I pointed out in the first reply, that plan would have seemingly executed quickly if there were no rows in TMP which is probably why it was mistakenly seen as the “good” plan.