Dirk, I've been looking into the query Tracy found.
On our old system it ran relatively fast. Actually it was still kind of slow, but it seems blazingly fast compared to the hour and 20 minutes or so it takes to run on our new system! As I think Tracy mentioned, performance is fine on the new system if you remove the subquery at the end.
The versions of Oracle are the same and although the init.ora parameters are not identical, they are different in reasonable ways. So I ran some traces and found that the plans were very different:
Old:
Rows Row Source Operation
------- ---------------------------------------------------
46 FILTER
385 NESTED LOOPS
385 HASH JOIN
384 INDEX RANGE SCAN (object id 29508)
25322 TABLE ACCESS FULL PARTIES
768 TABLE ACCESS BY INDEX ROWID ACS_OBJECTS
768 INDEX UNIQUE SCAN (object id 27037)
384 NESTED LOOPS
384 TABLE ACCESS BY INDEX ROWID SURVEY_RESPONSES
12830992 INDEX RANGE SCAN (object id 29670)
338 INDEX UNIQUE SCAN (object id 27037)
New:
Rows Row Source Operation
------- ---------------------------------------------------
46 FILTER
385 NESTED LOOPS
385 HASH JOIN
384 INDEX RANGE SCAN (object id 26840)
25417 TABLE ACCESS FULL PARTIES
768 TABLE ACCESS BY INDEX ROWID ACS_OBJECTS
768 INDEX UNIQUE SCAN (object id 26395)
384 NESTED LOOPS
384 NESTED LOOPS
12334565 VIEW
12334565 SORT GROUP BY
15171456 TABLE ACCESS FULL SURVEY_RESPONSES
12334519 TABLE ACCESS BY INDEX ROWID SURVEY_RESPONSES
12345637 INDEX UNIQUE SCAN (object id 26950)
338 INDEX UNIQUE SCAN (object id 26395)
Yikes! Obviously, I went looking for new or changed views. What I found was that the survey_responses_latest view, which (no surprise) was used in the subquery, has changed.
Old:
create or replace view survey_responses_latest as
select sr.*, o.creation_date,
o.creation_user,
survey_response.initial_user_id(sr.response_id) as initial_user_id
from survey_responses sr,
acs_objects o
where sr.response_id = o.object_id
and latest_response_p = 't';
New:
create or replace view survey_responses_latest as
select sr.*, o.creation_date,
o.creation_user,
survey_response.initial_user_id(sr.response_id) as initial_user_id
from survey_responses sr,
acs_objects o,
(select max(response_id) as response_id
from survey_responses
group by nvl(initial_response_id, response_id)) latest
where nvl(sr.initial_response_id,sr.response_id) = o.object_id
and sr.response_id= latest.response_id;
And when I temporarily replaced the new view with the old one, I got the same results as on the old system.
So I think it is not a problem with subqueries in general, just with *this* subquery. :)
