Spool space issue

Database
Enthusiast

Spool space issue

Hi, 

Please help me to tune the below query . Its failing with Spool space issue .

WITH     RECURSIVE HIER


RELATION_ID

,TO_ID

,FROM_ID

,LVL


AS ( 

SEL        

RELATION_ID

,TO_ID

,FROM_ID

,1(INTEGER)

FROM  db1.tab1

WHERE FROM_NAME = 'xyz' 

UNION ALL 

SEL        

B.RELATION_ID

,B.TO_ID

,B.FROM_ID

,C.LVL+1

FROM    db1.tab1 B

INNER JOIN HIER C 

ON B.FROM_ID = C.TO_ID 

WHERE  C.LVL< 30


SELECT 

H.FROM_ID AS parent

, H.TO_ID AS child

,H.LVL

FROM HIER H  

GROUP BY 1,2,3

Explain plan :

1) First, we lock a distinct db1."pseudo table" for read on

     a RowHash to prevent global deadlock for

     db1.tab1.

  2) Next, we lock db1.tab1 in view HIER.H for read.

  3) We do an all-AMPs RETRIEVE step from db1.tab1

     in view HIER.H by way of index # 8 "db1.tab1

     in view HIER.H.FROM_NAME = 'xyz'" with no residual

     conditions into Spool 3 (all_amps), which is built locally on the

     AMPs.  The size of Spool 3 is estimated with high confidence to be

     11 rows (6,017 bytes).  The estimated time for this step is 0.04

     seconds.

  4) We do an all-AMPs RETRIEVE step from Spool 3 by way of an all-rows

     scan into Spool 2 (all_amps), which is built locally on the AMPs.

     The size of Spool 2 is estimated with no confidence to be 11 rows

     (6,017 bytes).  The estimated time for this step is 0.02 seconds.

  5) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of

     an all-rows scan with a condition of ("TD_RECURSIVE_REF.LVL <= 29")

     into Spool 4 (all_amps), which is redistributed by the hash code

     of (TO_ID) to all AMPs.  Then we do a SORT to order Spool 4 by row

     hash.  The size of Spool 4 is estimated with no confidence to be

     11 rows (671 bytes).  The estimated time for this step is 0.01

     seconds.

  6) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a

     RowHash match scan, which is joined to db1.B in view

     HIER.H by way of a RowHash match scan with no residual conditions.

     Spool 4 and db1.B are joined using a merge join, with a

     join condition of ("db1.B.FROM_ID = TO_ID").  The result

     goes into Spool 5 (all_amps), which is built locally on the AMPs.

     The size of Spool 5 is estimated with no confidence to be 112 rows

     (61,264 bytes).  The estimated time for this step is 0.03 seconds.

  7) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of

     an all-rows scan into Spool 3 (all_amps), which is built locally

     on the AMPs.  The size of Spool 3 is estimated with no confidence

     to be 5,611 rows (3,069,217 bytes).  The estimated time for this

     step is 0.02 seconds.  If one or more rows are inserted into spool

     3, then go to step 4.

  8) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of

     an all-rows scan into Spool 6 (all_amps), which is built locally

     on the AMPs.  The size of Spool 6 is estimated with no confidence

     to be 5,611 rows (634,043 bytes).  The estimated time for this

     step is 0.02 seconds.

  9) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 6 are sent back to the user as the result of

     statement 1.  The total estimated time is 0.14 seconds.

Thanks,

Meenakshi

2 REPLIES
Enthusiast

Re: Spool space issue

Try to check if there are duplicates on the join columns. If exists, remove those. Ignoring will leave to bad perf.

Try to collect statistics on the join columns. 

Enthusiast

Re: Spool space issue

Thanks for the suggestion. I was able to execute the recursion after removing the duplicates on the joining columns.