Help with SQL Monitor for long running query

[zstringer]

I have a query that took a very long time. The picture shows information from PLSD SQL Monitor. I'm not sure how to interpret it though. Thanks for your help.

 

[William Robertson]

It's not the PL/SQL Developer SQL Monitor, it's Oracle's. From the image, it looks like the optimiser underestimated the number of rows for each step and therefore chose a Merge Join Cartesian and nested loops, which didn't scale well for the actual number of rows you have. I'd guess the problems started when it estimated one row for RTS_ABCD_STRATIFIED when there are actually 413. Gathering stats on that table, and maybe the others, or maybe the whole schema, might fix it. It could be worth exploring the reason why they were so far off.