Excessive Query Execution

RT Developers
1

We have an RT 4 system with a query that executes 90,000+ an hour,
accounting for approximately 25% of database time. The query is below:

SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM ( SELECT
Groups.id FROM Groups, Principals, CachedGroupMembers WHERE
Principals.Disabled = :“SYS_B_00” AND CachedGroupMembers.Disabled =
:“SYS_B_01” AND Principals.id = Groups.id AND Principals.PrincipalType =
:“SYS_B_02” AND Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = :“SYS_B_03” AND (Groups.Type = :“SYS_B_04” OR
Groups.Type = :“SYS_B_05”) AND ((Groups.Domain = :“SYS_B_06” AND
Groups.Instance = :“SYS_B_07”) OR (Groups.Domain = :“SYS_B_08” AND
Groups.Instance = :“SYS_B_09”)) ) limitquery WHERE rownum <= :“SYS_B_10” )
WHERE limitrownum >= :“SYS_B_11”

Any idea what option is turned on causing this to run so often? Is it
required?

Thanks,

Jeremy

Jeremy Gude
Database / Peoplesoft Administrator
UMBC Division of Information Technology
Business Systems Group
EMAIL: jeremy@umbc.edu
MOBILE: 443.904.5970
PHONE: 410.455.8660

2

We have an RT 4 system with a query that executes 90,000+ an hour,
accounting for approximately 25% of database time. The query is
below: [snip]

It’s a little tough to determine, from all of the placeholders, but that
looks to be “find me 10 groups that this user is a member of.”

Any idea what option is turned on causing this to run so often? Is it
required?

You may need to spin up a development instance with $StatementLog
enabled, and see where it’s being triggered from. It’s a generic enough
query that I can’t guess what is triggering it, offhand.
Do you have any local customizations or extensions installed?

  • Alex