Grouper Users - Open Discussion List

["Hyzer, Chris" <>]

I only have one full table scan and you have two.  Would gathering stats help?  Shilen do you have ideas? 😊

 

 

 

 

From: <> On Behalf Of Morgan, Andrew Jason
Sent: Wednesday, October 02, 2019 8:23 PM
To:
Subject: [grouper-users] composite group runs out of TEMP tablespace

 

When creating a composite that is an intersection (AND) of 2 groups, we get a SQLexception:

 

  ERROR spi.SqlExceptionHelper:  ORA-01652: unable to extend temp segment by 1024 in tablespace TEMP

 

The groups we are intersecting have approximately 37k and 12k members.

 

Our Oracle DBA showed that the following SQL query ended up consuming all 32GB of the TEMP tablespace doing a sort operation:

 

select distinct member0_.id as col_0_0_
from grouper_members member0_

cross join grouper_memberships_all_v membership1_
cross join grouper_memberships_all_v membership2_
where membership1_.owner_group_id=:1

      and membership2_.owner_group_id=:2

      and member0_.id=membership1_.member_id

      and member0_.id=membership2_.member_id

      and membership1_.field_id=:3

      and membership2_.field_id=:4

      and membership1_.immediate_mship_enabled='T'

      and membership2_.immediate_mship_enabled='T'

      and member0_.subject_source<>:5

 

I have attached a screenshot of the execution plan.

 

We are running Grouper v2.3 api patch 70 on Oracle database.

 

Is this a known issue?  Is there any additional data I can provide that will help us understand why this query is so bad?  🙂

 

Thanks,

 

Andy Morgan

Systems Administrator, Identity & Access Management

Information Services | Oregon State University