[Spacewalk-list] Query eating Oracle DB TEMP space

[Velayutham, Prakash]

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>