Forum .LRN Q&A: Re: .LRN v2 Performance

Collapse
9: Re: .LRN v2 Performance (response to 1)
Posted by Janine Ohmer on
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. :)

Collapse
10: Re: .LRN v2 Performance (response to 9)
Posted by Dirk Gomez on
Why has the latest_response_p field disapperead? The first query just looks fine.