[Spacewalk-list] Query eating Oracle DB TEMP space

[Michael Mraka]

Velayutham, Prakash wrote:
% Hello,
% 
% Spacewalk version - 1.6
% OS - CentOS 6 (x86_64)
% 
% One of our Oracle DBAs recently mentioned that a query by Spacewalk is eating up a lot of TEMP space on the database server. Here is the query.
% 
% 
% She gave a tuned version of this query to be implemented, but I wanted to see if this could be implemented upstream.
% 
% SELECT /*+first_rows*/  pkgp.package_id, pkgcap.id, pkgcap.name, pkgcap.version, pkgp.sense
% FROM rhnPackageProvides pkgp,
% rhnPackageCapability  pkgcap
% WHERE pkgp.package_id in (select chpkg.package_id
%                                       from rhnChannelPackage chpkg left join rhnPackageRepodata prd
%                                                 ON prd.package_id = chpkg.package_id where chpkg.channel_id = :1
%                                       AND prd.primary_xml is null)
% AND pkgp.capability_id = pkgcap.id
% order by pkgp.package_id
% 
% I am hoping one of the developers could take a look to see if this is doable.

Hello Prakash,

could you ask your DBA to check whether just removing  /*+first_rows*/
hint also helps?

The query then should be
 SELECT chpkg.package_id, pkgcap.id, pkgcap.name, pkgcap.version, pkgp.sense
   FROM rhnPackageProvides pkgp,
        rhnPackageCapability  pkgcap,
        rhnChannelPackage chpkg
   LEFT JOIN rhnPackageRepodata prd
     ON prd.package_id = chpkg.package_id
  WHERE chpkg.package_id = pkgp.package_id
    AND pkgp.capability_id = pkgcap.id
    AND chpkg.channel_id = :1
    AND prd.primary_xml is null
  ORDER BY pkgp.package_id


Regards,

--
Michael Mráka
Satellite Engineering, Red Hat