[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

[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

_______________________________________________
Spacewalk-list mailing list
Spacewa...@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list

[Velayutham, Prakash]

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