Grouper Users - Open Discussion List

["Hyzer, Chris" <>]

I ran:

EXEC DBMS_STATS.gather_schema_stats('AUTHZADM');

 

I found two groups with 37k and 11k, and explained plan this (it took 21 seconds).  There is still a full table scan in there, hmmm

 

 

 

 

From: Shilen Patel <>
Sent: Thursday, October 03, 2019 8:12 AM
To: Hyzer, Chris <>; Morgan, Andrew Jason <>;
Subject: Re: [grouper-users] composite group runs out of TEMP tablespace

 

I would start with gathering stats.  What version of Oracle do you have?

EXEC DBMS_STATS.gather_schema_stats('schema'); 
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm

 

 

I just ran this query using two groups of similar sizes (37K and 18K) and the cost was 66 and no full table scans.  The results are returned in a second.

 

Thanks!

 

- Shilen

 

From: <> on behalf of "Hyzer, Chris" <>
Reply-To: "Hyzer, Chris" <>
Date: Wednesday, October 2, 2019 at 9:43 PM
To: "Morgan, Andrew Jason" <>, "" <>
Subject: RE: [grouper-users] composite group runs out of TEMP tablespace

 

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