grouper-users - [grouper-users] composite group runs out of TEMP tablespace

Subject: Grouper Users - Open Discussion List

List archive

From: "Morgan, Andrew Jason" <>
To: "" <>
Subject: [grouper-users] composite group runs out of TEMP tablespace
Date: Thu, 3 Oct 2019 00:22:41 +0000
Arc-authentication-results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=oregonstate.edu; dmarc=pass action=none header.from=oregonstate.edu; dkim=pass header.d=oregonstate.edu; arc=none
Arc-message-signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=S5L4917UhUH7QOedbKC6tpPOimSNkrmocnSEAKVRFQY=; b=Ob6Ddn656rXEJJtiwPhW7JLI59BxUV4RlueqafbOEmXWx3ioY7chAyqxhMjIPIxSLFdX1uSx7dUKMUaaVPDbx8ZJmOAsduHKnXZYYveJE2Zuwjj4G3VrHBXREEQLuie8//yjKSkB5ymsHCMl3wikIWQ834aoEXLaFz+V+bsU2366a5cAXR90iUthQSfmPeCLZft71GsTZsS6ah4jxx9yPiLtIfHEquYb3IsNzQvFFoo4CO5thX/2D2OWmgrBTvD0k+ISh4JR3PA6N7PAj8j6Vy+eIRBGA5EJ9/e5zlvIVOng3WmWOUM0IpDGhnOK1YA3j41Y6797a4wmJcg+lhMenw==
Arc-seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=XFufz5pk2BG0XYaCq6Ryf/LQOPrIqckv8S2e1noL25F3m8mnan4IdcgOpQ6ECu/kMFBRqxN4sETPB+5hiSFFxa7PBsZNRkAql2lEdsp2ohicE7jfybxczTuiRH5shPPCxFz+gCEuQ2ydfFXFrgx6QnwT9U9PzDxJuEASJt+19SxdnbXGkIT8NIAA0rN0ff4t/HnJGOiYTWNWighX5tBXsKC0di3UTejSB7LpojFHlwNmZxNTVyuhmwOpoGUAJoh6KdrBq1Nh73MCcLrrEkno0YD8Xn392vH8UYqTVogCS8FCwQydVBDEtlxpsQDriuST+UY9KiQpstQc/DgA2H/g6w==

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

Attachment: grouper-sql-plan.png
Description: grouper-sql-plan.png

[grouper-users] composite group runs out of TEMP tablespace, Morgan, Andrew Jason, 10/03/2019
- RE: [grouper-users] composite group runs out of TEMP tablespace, Hyzer, Chris, 10/03/2019
- <Possible follow-up(s)>
- Re: [grouper-users] composite group runs out of TEMP tablespace, Shilen Patel, 10/03/2019
  - RE: [grouper-users] composite group runs out of TEMP tablespace, Hyzer, Chris, 10/04/2019