Description of problem:
The Systems Groups overview page (/rhn/systems/SystemGroupList.do) times out. Performance degrades steadily as the user (e.g. admins) has the ability to view more groups. The more systems that are in a group requiring errata, the faster the degradation occurs.

Version-Release number of selected component (if applicable):


How reproducible:
Every time

Steps to Reproduce:
1. Have 80+ systems requiring patches.
2. Place systems into multiple groups (make sure one or more groups contains 50+ servers)
3. Modify user to have access to additional group.
4. As user, load system groups page (/rhn/systems/SystemGroupList.do)
5. Repeat 3 and 4 until performance degradation is very noticeable or timeout occurs.
  
Actual results:
Receive message: Apache service is unavailable or has timed out message.

Expected results:
List of system groups

Additional info:
Our DBAs have looked at this and the offending query seems to be within the /usr/share/rhn/lib/rhn.jar at com/redhat/rhn/common/db/datasource/xml/SystemGroup_queries.xml - specifically '<query name="visible_to_user_overview_fast" params="org_id, user_id">'

The queries time out at 15+s per errata lookup for large system groups. After running the query through optimization tools we've come up with the following:

/* Formatted on 10/19/2009 3:47:32 PM (QP5 v5.115.810.9015) */
SELECT   G.ID AS ID,
         G.NAME AS NAME,
         (SELECT   COUNT ( * )
            FROM   rhnUserManagedServerGroups UMSG
           WHERE   UMSG.server_group_id = G.id)
            AS GROUP_ADMINS,
         (SELECT   COUNT ( * )
            FROM   rhnServerGroupMembers SGM
           WHERE   SGM.server_group_id = G.id
                   AND EXISTS
                         (SELECT   1
                            FROM   rhnServerFeaturesView SFV
                           WHERE   SFV.server_id = SGM.server_id
                                   AND SFV.label = 'ftr_system_grouping'))
            AS SERVER_COUNT,
         (  SELECT   DECODE (MAX (DECODE (PS.state,
                                          'OK',
                                          1,
                                          'PENDING',
                                          2,
                                          'UNKNOWN',
                                          3,
                                          'WARNING',
                                          4,
                                          'CRITICAL',
                                          5)),
                             1,
                             'OK',
                             2,
                             'PENDING',
                             3,
                             'UNKNOWN',
                             4,
                             'WARNING',
                             5,
                             'CRITICAL')
                        ST
              FROM   rhn_check_probe CP,
                     rhn_probe_state PS,
                     rhnServerGroupMembership SGM
             WHERE       PS.probe_id = CP.probe_id
                     AND SGM.server_id = CP.host_id(+)
                     AND SGM.GROUP_ID = UMSG.server_group_id
          GROUP BY   SGM.GROUP_ID)
            MONITORING_STATUS,
         (SELECT   1
            FROM   DUAL
           WHERE   EXISTS
                      (SELECT   /*+ NO_QUERY_TRANSFORMATION(SNPC) */  1
                         FROM   rhnErrata E,
                                rhnServerNeededPackageCache SNPC,
                                rhnServerGroupMembers SGM
                        WHERE       E.advisory_type = 'Security Advisory'
                                AND snpc.errata_id = e.id
                                AND snpc.server_id = sgm.server_id
                                AND sgm.server_group_id = G.id))
            AS SECURITY_ERRATA,
         (SELECT   1
            FROM   DUAL
           WHERE   EXISTS
                      (SELECT    /*+ NO_QUERY_TRANSFORMATION(SNPC) */  1
                         FROM   rhnErrata E,
                                rhnServerNeededPackageCache SNPC,
                                rhnServerGroupMembers SGM
                        WHERE       E.advisory_type = 'Bug Fix Advisory'
                                AND snpc.errata_id = e.id
                                AND snpc.server_id = sgm.server_id
                                AND sgm.server_group_id = G.id))
            AS BUG_ERRATA,
         (SELECT   1
            FROM   DUAL
           WHERE   EXISTS
                      (SELECT   /*+ NO_QUERY_TRANSFORMATION(SNPC) */ 1
                         FROM   rhnErrata E,
                                rhnServerNeededPackageCache SNPC,
                                rhnServerGroupMembers SGM
                        WHERE   E.advisory_type =
                                   'Product Enhancement Advisory'
                                AND snpc.errata_id = e.id
                                AND snpc.server_id = sgm.server_id
                                AND sgm.server_group_id = G.id))
            AS ENHANCEMENT_ERRATA
  FROM   rhnServerGroup G, rhnUserManagedServerGroups UMSG
 WHERE   G.ORG_ID = 2 AND UMSG.user_id = 2 AND G.id = UMSG.server_group_id
  AND G.id IN (49, 55, 53, 81, 41, 48, 46, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 410, 411, 412)



OR an optimization from within Oracle:

CREATE OR REPLACE FORCE VIEW SPACEWALK.RHNSERVERNEEDEDPACKAGECACHE
(
   SERVER_ID,
   PACKAGE_ID,
   ERRATA_ID
)
AS
     SELECT                                     /*+ NO_QUERY_TRANSFORMATION */
           server_id, package_id, MAX (errata_id) AS errata_id
       FROM   rhnServerNeededCache
   GROUP BY   server_id, package_id;


I've put the modified query back into the rhn.jar archive and the /rhn/systems/SystemGroupList.do page now loads quickly (< 10s)when displaying all large groups. The page now also loads quickly with the Oracle optimization and no changes to the rhn.jar archive.