W dniu 2015-01-04 o 13:12, Jonathan Lewis pisze:
I'll try to gather such information, meanwhile developers are trying to rewrite query :) .You might as well capture the full SQL Monitoring output for the completed query. Then I'd check the definition of the view V_SA_tab_personal_ADD to see what it's doing with the "with subquery". I think most of the work relates purely to the number and size of the tablescans of the resultant GTT - but the CPU used still looks a little high (even if it does mange to scan 2 billiong rows in the first 1,000 seconds) so I want to know how it reduces those rows down to one per scan - as indicated by the Rows (Actual) in lines 19 and 18. I'd want the execution plan from v$sql_plan so that I could check the predicate section to see how any predicates were applied. If I were to start running the query again I would use Tanel's code to capture a snapshot of latch activity over a few seconds to see if there were any significant latch gets over and above those related to the buffer cache.
Regards G -- //www.freelists.org/webpage/oracle-l