[Spacewalk-list] Query eating Oracle DB TEMP space
Michael Mraka
michael.mraka at redhat.com
Mon Apr 2 11:32:09 UTC 2012
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
More information about the Spacewalk-list
mailing list