SQL Query Performance - High Spool Space

Database

SQL Query Performance - High Spool Space

Hello Experts,

The following query is taking 200GB Spool space in the system and affects the overall performance.

Any suggestion to improve the performance of the query.

Select *

from AAAA G, BBBBB V, CCCCC          P

where G.Ac =  V.AC

and G.Year   = P.Year

and G.Period = P.Period

and (G.Eff_Date between P.Start_Date and

                           P.End_Date)

order by V.ORY_CD,

         V.UP_CD;

Currently executing step 6 of 11 steps

Step        Est. Time    Actual Time    Est. Rows    Actual Rows    Step Text

1    No      0:00.00    0:00.00    0    1    First, lock $VMS."pseudo table" for read on a row hash.

2    No      0:00.00    0:00.00    0    1    Next, we lock $VMS."pseudo table" for read on a row hash.

3    No      0:00.00    0:00.00    0    30    We lock HIST.WLKR for access, we lock AAAA for access, we lock $VMS.P for read, we lock $VMS.V for read and we lock for access.

4    High    0:00.01    0:00.04    30    30    We do a Single-AMP RETRIEVE step from by way of the unique primary index into Spool 22929, which is duplicated on all AMPs.  

5    No      0:46.09    4:48.68    29330540    87991620    We do an All-AMPs JOIN step from Spool 22929 by way of an all-rows scan, which is joined to table  Spool 22929 and table  are joined using a product join . The result goes into Spool 22928, which is built locally on the AMPs.  

6    No      3:52.39        177461200        We do an All-AMPs JOIN step from Spool 22929 (Last Use) by way of an all-rows scan, which is joined to table . Spool 22929 and table are joined using a product join . The result goes into Spool 22928, which is built locally on the AMPs. This step begins a parallel block of steps.  

6    High    0:00.01    0:00.18    2940    2940    We do an All-AMPs RETRIEVE step from .V by way of an all-rows scan into Spool 22932, which is duplicated on all AMPs. This step ends a parallel block of steps.  

7    Low     0:00.01        900        We do an All-AMPs RETRIEVE step from $VMS.P by way of an all-rows scan into Spool 22933, which is duplicated on all AMPs.  

8    No      0:30.57        5133175        We do an All-AMPs JOIN step from Spool 22932 (Last Use) by way of an all-rows scan, which is joined to Spool 22928. Spool 22932 and Spool 22928 are joined using asingle partition hash join . The result goes into Spool 22934, which is built locally on the AMPs.  

9    No      0:15.33        6695446        We do an All-AMPs JOIN step from Spool 22933 (Last Use) by way of an all-rows scan, which is joined to Spool 22934. Spool 22933 and Spool 22934 are joined using ahash join of 50 partitions. The result goes into Spool 22931, which is built locally on the AMPs.  

10    No      0:00.00        0        We send out an END TRANSACTION step to all AMPs involved in processing the request.

1 REPLY
Senior Apprentice

Re: SQL Query Performance - High Spool Space

A Product Join involving large tables, might be due to the SQL or wrong statistics.

It's hard to tell without additional information like DDL or at least PIs, row counts/statistics. And of course the real query is also helpful.

Dieter