Chris Hyzer added a comment - 06/Jan/12 4:42 AM - edited The lack of parens kills the explain plan in oracle and the performance of large deployments (and is probably wrong logic). Here is the patch: Index: Hib3GroupDAO.java =================================================================== --- Hib3GroupDAO.java (revision 7744) +++ Hib3GroupDAO.java (working copy) @@ -1074,7 +1074,7 @@        hql.append(" where ");      }       - hql.append(" theGroup.nameDb = :value or theGroup.alternateNameDb = :value "); + hql.append(" ( theGroup.nameDb = :value or theGroup.alternateNameDb = :value ) ");            byHqlStatic.createQuery(hql.toString())        .setCacheable(true).setCacheRegion(KLASS + ".FindByNameSecure").options(queryOptions); @@ -2360,13 +2360,13 @@        } else {          sql.append(" where ");        } - sql.append(" theGroup.nameDb in ( "); + sql.append(" ( theGroup.nameDb in ( ");                sql.append(HibUtils.convertToInClause(namesBatch, byHqlStatic)).append(" ) ");          sql.append(" or theGroup.alternateNameDb in ( ");         - sql.append(HibUtils.convertToInClause(namesBatch, byHqlStatic)).append(" ) "); + sql.append(HibUtils.convertToInClause(namesBatch, byHqlStatic)).append(" ) )");          byHqlStatic          .createQuery(sql.toString())

Chris Hyzer added a comment - 06/Jan/12 4:58 AM - edited The problem here is that the problem query looks like this: SELECT DISTINCT group0_.id AS id21_,                 group0_.hibernate_version_number AS hibernate2_21_,                 group0_.last_membership_change AS last3_21_,                 group0_.last_imm_membership_change AS last4_21_,                 group0_.parent_stem AS parent5_21_,                 group0_.creator_id AS creator6_21_,                 group0_.create_time AS create7_21_,                 group0_.modifier_id AS modifier8_21_,                 group0_.modify_time AS modify9_21_,                 group0_.name AS name21_,                 group0_.display_name AS display11_21_,                 group0_.extension AS extension21_,                 group0_.display_extension AS display13_21_,                 group0_.description AS descrip14_21_,                 group0_.context_id AS context15_21_,                 group0_.alternate_name AS alternate16_21_,                 group0_.type_of_group AS type17_21_   FROM grouper_groups group0_, grouper_memberships_all_v membership1_  WHERE ( membership1_.owner_group_id = group0_.id        AND (membership1_.field_id IN                ('0b195b7c-e526-4d40-9098-510e021093c1',                 'd68dad3b-be79-40ce-8128-86503ce21a37',                 '8f59f9fb-0caf-47f9-8fdf-428531f03514',                 '655aea9c-a8c1-429e-be4b-3b22605cf58f',                 'd647453b-db49-474f-834a-52db840377ea',                 '7b83eb56-eea1-4d73-b1b2-5134a799218f'))        AND (membership1_.member_id IN                ('theuserid',                 'GrouperAllId'))        AND membership1_.immediate_mship_enabled = 'T'        AND (group0_.name IN ('somestring2'))        OR group0_.alternate_name IN ('somestring2') ) Note the lack of parens on the OR statement. Here is what it should look like: SELECT DISTINCT group0_.id AS id21_,                 group0_.hibernate_version_number AS hibernate2_21_,                 group0_.last_membership_change AS last3_21_,                 group0_.last_imm_membership_change AS last4_21_,                 group0_.parent_stem AS parent5_21_,                 group0_.creator_id AS creator6_21_,                 group0_.create_time AS create7_21_,                 group0_.modifier_id AS modifier8_21_,                 group0_.modify_time AS modify9_21_,                 group0_.name AS name21_,                 group0_.display_name AS display11_21_,                 group0_.extension AS extension21_,                 group0_.display_extension AS display13_21_,                 group0_.description AS descrip14_21_,                 group0_.context_id AS context15_21_,                 group0_.alternate_name AS alternate16_21_,                 group0_.type_of_group AS type17_21_   FROM grouper_groups group0_, grouper_memberships_all_v membership1_  WHERE ( membership1_.owner_group_id = group0_.id        AND (membership1_.field_id IN                ('0b195b7c-e526-4d40-9098-510e021093c1',                 'd68dad3b-be79-40ce-8128-86503ce21a37',                 '8f59f9fb-0caf-47f9-8fdf-428531f03514',                 '655aea9c-a8c1-429e-be4b-3b22605cf58f',                 'd647453b-db49-474f-834a-52db840377ea',                 '7b83eb56-eea1-4d73-b1b2-5134a799218f'))        AND (membership1_.member_id IN                ('theuserid',                 'GrouperAllId'))        AND membership1_.immediate_mship_enabled = 'T'        AND ( (group0_.name IN ('somestring2'))        OR group0_.alternate_name IN ('somestring2') ) ) The bad query has an explain plan of: 1.2 trillion bytes, 7.5 million cost The good query has an explain plan of: 706 bytes, 161 cost The issue is this query is used when doing a subject search by identifier against the g:gsa source, and when the WS authenticates, it does a subject lookup for the authenticating user by identifier. In Penn's environment it was a 60 second penalty. Then (ever so slightly good news), once Oracle does the query, its significantly improved the next time (if soon after), down to 15 seconds We just put the new classfiles of the WEB-INF/classes/edu/internet2/middleware/grouper/internal/dao/hib3 dir and we are all set...