Forum .LRN Q&A: .LRN v2 Performance

We would like encourage everyone to post their performance issues and experiences with v2 so that we can all learn from each other's experiences.

Valencia, how is your installation going?

Rafael

Actually the CBO should be deterministic. Kinda...it has earned its crystal ball repuation because it is different from minor version to minor version.

You also don't say whether the queries on your new system perform worse?

Alas, stored outlines (asktom.oracle.com and the Kyte book have a lot on it) are not an option because the in subclauses are not using bind variables and there is simply too many different queries executed even with cursor_sharing=force.

(Have you set cursor_sharing = force ?)

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. :)

A guess, the latest_response_p = t probably was the cause of the full table scan:

15171456      TABLE ACCESS FULL SURVEY_RESPONSES

This is because latest_response_p had lots of values that were "t" and doesn't have differing values to really make it and effectively indexed column.  So Oracle scans at the whole table.

You'll notice that I replaced the lastest_response_p = t with

(select max(response_id) as response_id
          from survey_responses
        group by nvl(initial_response_id, response_id)) latest

that worked more effectively.

and getting this speed:
132 ms       0or1row       dbqd.acs-tcl.tcl.acs-permissions-procs.permission::permission_p_not_cached.select_permission_p: 0or1row nsdb0

select 1 from dual
where exists
          ( select 1
                  from acs_object_party_privilege_map ppm
        where ppm.object_id = '1228' and ppm.party_id = '16319' and ppm.privilege = 'dotlrn_browse' )

As you see, the permission call has been changed lately, with 500K entries on acs_permissions you should be fine I think (though it can still be improved), specially since your db server if bigger than ours.

in:

dotlrn_community::list_possible_subcomm_users -subcomm_id subcomm_id

called:

dotlrn_community::list_possible_subcomm_users.select_possible_users

select dotlrn_member_rels_approved.rel_id,
                  dotlrn_member_rels_approved.rel_type,
                  dotlrn_member_rels_approved.role,
                  dotlrn_member_rels_approved.user_id,
                  acs_users_all.first_names,
                  acs_users_all.last_name,
                  acs_users_all.email
            from acs_users_all,
                dotlrn_member_rels_approved,
                dotlrn_communities
            where dotlrn_communities.community_id = :subcomm_id
            and dotlrn_communities.parent_community_id = dotlrn_member_rels_approved.community_id
            and acs_users_all.user_id = dotlrn_member_rels_approved.user_id
            and acs_users_all.user_id not in (select dm.user_id
                                                from dotlrn_member_rels_full dm
                                                where dm.community_id = :subcomm_id)
            order by last_nam

I'm not sure whether ~0.8 secs for ~800 rows is considered still being a problem but I suppose it
can be if the number of members are, say, ten times that of your example.

Anyway, it might be interesting to try and replace the last WHERE clause (the one with the subselect)
with a construct using EXCEPT (Postgres vocabulary) and MINUS (Oracle vocabulary) respectively.

Unless I'm overlooking something here I do believe that an EXCEPT (or MINUS) could be useful here:

EXCEPT acts in the opposite way of a UNION in that it subtracts the rows returned by the second query part
from those returned by the first query part instead of adding them like UNION does. I think it would be analogous to
the current "not in (*subselect*)", which is also aimed to reduce the number of rows, but probably "EXCEPT" does it faster.

Just a thought.

set member_url [acs_community_member_url -user_id $user_id]

to

set member_url "[dotlrn::get_url]/community-member?user_id=$user_id"

Nima, this will break Sloan, and anyone else that has overridden the default CommunityMemberURL in the kernel parameters. This should be clear from the code.

acs_community_member_url needs to be fixed, or made more general.

here is the solution:

Moved out the part

"[subsite::get_element -element url -notrailing][ad_parameter -package_id [ad_acs_kernel_id] CommunityMemberURL]"

ad_proc -public acs_community_member_page {} {  
     @return the url to the community member page  
 } { 
     return "[subsite::get_element -element url -notrailing][ad_parameter \ 
             -package_id [ad_acs_kernel_id] CommunityMemberURL]" 
 }

return "[acs_community_member_page]?[export_vars user_id]" 

set member_page [acs_community_member_page]

set member_url "$member_page?user_id=$user_id"