[Spacewalk-list] Query eating Oracle DB TEMP space

[Velayutham, Prakash]

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.

SELECT /*+first_rows*/  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

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.

Thanks,
Prakash
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20120402/9dd8cc6b/attachment.htm>