[Spacewalk-list] Query eating Oracle DB TEMP space
Velayutham, Prakash
Prakash.Velayutham at cchmc.org
Thu Apr 5 14:54:42 UTC 2012
On Apr 2, 2012, at 1:56 PM, Velayutham, Prakash wrote:
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
Hi Michael,
Here is a detailed response from our DBA.
If you remove the first_rows hint from both the old query , the number of blocks retrieved is the same at 5826 versus 108981. This might decrease the usage of TEMP space. We can try it and monitor.
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 = 102
AND prd.primary_xml is null
ORDER BY pkgp.package_id
/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1.56 1.57 0 108981 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.56 1.57 0 108981 0 0
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 = 102
AND prd.primary_xml is null
ORDER BY pkgp.package_id
/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.10 0.09 0 5826 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.10 0.09 0 5826 0 0
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 = 102
AND prd.primary_xml is null)
AND pkgp.capability_id = pkgcap.id
order by pkgp.package_id
/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.08 0.08 0 12606 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.08 0.08 0 12606 0 0
SELECT 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 = 102
AND prd.primary_xml is null)
AND pkgp.capability_id = pkgcap.id
order by pkgp.package_id
/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.06 0.06 0 5826 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.06 0.07 0 5826 0 0
Does that answer your question?
Thanks,
Prakash
Hi,
Just wanted to know if you had any feedback on this response.
Thanks,
Prakash
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20120405/26b0a110/attachment.htm>
More information about the Spacewalk-list
mailing list