QUERY TUNING

Database
Enthusiast

QUERY TUNING

Hi,

I have a query that picks up different columns from different tables by joining them . INNER Join and LEFT outer join are used . 7 tables are used. Now, the query gives me spool space error. But the explain plan shows that it should take 5 secs or so to get executed.

Explanation

---------------------------------------------------------------------------

  1) First, we lock a distinct SAA."pseudo table" for write

     on a RowHash to prevent global deadlock for

     SAA.SALES_AUDIT_RETAIL_INVOICE.

  2) Next, we lock SAA.SALES_AUDIT_RETAIL_INVOICE for write,

     we lock sdb.PII in view

     Sales.PII for access, we lock

     sdb.POS in view Sales.POS for

     access, we lock sdb.INVI in view

     Sales.INVI for access, we lock

     sdb.INCV in view Sales.INCV for access, we

     lock pdb.SAPD_03 in view

     Party.SADPD_CUR for access, and we lock

     mdb.FISCAL_CALENDAR in view master.Fiscal_Calendar for

     access.

  3) We do an all-AMPs RETRIEVE step from sdb.INCV in

     view Sales.INCV by way of a traversal of index # 12 with a

     range constraint of ("(sdb.INCV in view

     Sales.INCV.Field_1058 >= DATE '2011-08-23') AND

     (sdb.INCV in view Sales.INCV.Field_1058 <=

     DATE '2011-09-20')") extracting row ids only into Spool 11

     (all_amps) (compressed columns allowed), which is built locally on

     the AMPs.  Then we do a SORT to order Spool 11 by row id

     eliminating duplicate rows.  The size of Spool 11 is estimated

     with low confidence to be 1 row.  The estimated time for this step

     is 0.01 seconds.

  4) We do an all-AMPs RETRIEVE step from sdb.INCV in

     view Sales.INCV by way of row ids from Spool 11 (Last Use)

     with a residual condition of ("(sdb.INCV in view

     Sales.INCV.Source_Id = 'SR3') AND

     ((sdb.INCV in view

     Sales.INCV.Fin_Company_Cd = '0061') AND

     ((sdb.INCV in view Sales.INCV.ETL_Action_Cd

     > 'D') OR (sdb.INCV in view

     Sales.INCV.ETL_Action_Cd < 'D')))") into Spool 12

     (all_amps) (compressed columns allowed), which is redistributed by

     the hash code of (sdb.INCV.Billing_Dt) to all AMPs.

     Then we do a SORT to order Spool 12 by row hash.  The size of

     Spool 12 is estimated with high confidence to be 1 row (37 bytes).

     The estimated time for this step is 0.04 seconds.

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

     RowHash match scan, which is joined to mdb.FISCAL_CALENDAR

     in view master.Fiscal_Calendar by way of a RowHash match scan with

     a condition of ("(mdb.FISCAL_CALENDAR in view

     master.Fiscal_Calendar.Fiscal_Dt >= DATE '2011-08-23') AND

     (mdb.FISCAL_CALENDAR in view

     master.Fiscal_Calendar.Fiscal_Dt <= DATE '2011-09-20')").  Spool

     12 and mdb.FISCAL_CALENDAR are joined using a merge join,

     with a join condition of ("mdb.FISCAL_CALENDAR.Fiscal_Dt =

     Bill_Dt").  The result goes into Spool 13 (group_amps)

     (compressed columns allowed), which is redistributed by the hash

     code of (sdb.INCV.Source_Id,

     sdb.INCV.Inv_Id) to all AMPs.  Then we do a

     SORT to order Spool 13 by row hash.  The size of Spool 13 is

     estimated with low confidence to be 1 row (45 bytes).  The

     estimated time for this step is 0.01 seconds.

  6) We do a group-AMPs JOIN step from sdb.PART

     in view Sales.PART_cur by way of a RowHash match

     scan with a condition of ("(NOT (sdb.PART

     in view Sales.PART_cur.Addr_Id IS NULL )) AND

     ((sdb.PART in view

     Sales.PART_cur_0315.Partner_Role_Cd = 'AG') AND

     ((sdb.PART in view

     Sales.PART_cur_0315.Source_Id = 'SR3') AND

     ((sdb.PART in view

     Sales.PART_cur_0315.ETL_Action_Cd > 'D') OR

     (sdb.PART in view

     Sales.PART_cur.ETL_Action_Cd < 'D'))))"), which is

     joined to Spool 13 (Last Use) by way of a RowHash match scan

     locking sdb.PART for access.

     sdb.PART and Spool 13 are joined using a

     merge join, with a join condition of ("(Source_Id =

     sdb.PART.Source_Id) AND (Inv_Id =

     sdb.PART.Inv_Id)").  The result goes

     into Spool 14 (all_amps) (compressed columns allowed), which is

     built locally on that AMP.  The size of Spool 14 is estimated with

     no confidence to be 1 row (63 bytes).  The estimated time for this

     step is 0.01 seconds.

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

     all-rows scan, which is joined to sdb.INVI in

     view Sales.INVI by way of an all-rows scan with a

     condition of ("sdb.INVI in view

     Sales.INVI.Source_Id = 'SR3'").  Spool 14 and

     sdb.INVI are joined using a product join, with

     a join condition of ("(sdb.INVI.Inv_Id =

     Inv_Id) AND ((sdb.INVI.Source_Id =

     Source_Id) AND ((Source_Id =

     sdb.INVI.Source_Id) AND (Inv_Id =

     sdb.INVI.Inv_Id )))").  The result goes

     into Spool 15 (all_amps) (compressed columns allowed), which is

     redistributed by the hash code of (

     sdb.PART.Addr_Id,

     sdb.INCV.Source_Id) to all AMPs.  Then we do a SORT

     to order Spool 15 by row hash.  The size of Spool 15 is estimated

     with no confidence to be 1 row (80 bytes).  The estimated time for

     this step is 2.21 seconds.

  8) We do an all-AMPs JOIN step from pdb.SAPD_03 in

     view Party.SADPD_CUR by way of a RowHash match scan

     with a condition of ("(pdb.SAPD_03 in view

     Party.SADPD_CUR.Eff_End_Ts = TIMESTAMP '9999-12-31

     00:00:00') AND ((pdb.SAPD_03 in view

     Party.SADPD_CUR.Region_Cd = 'AB ') AND

     (pdb.SAPD_03 in view

     Party.SADPD_CUR.Source_Id = 'SR3'))"), which is joined

     to Spool 15 (Last Use) by way of a RowHash match scan.

     pdb.SAPD_03 and Spool 15 are joined using a merge

     join, with a join condition of (

     "(pdb.SAPD_03.Source_Id = Source_Id) AND ((Addr_Id

     = pdb.SAPD_03.Addr_Id) AND ((Source_Id =

     pdb.SAPD_03.Source_Id) AND

     (pdb.SAPD_03.Source_Id = Source_Id )))").  The

     result goes into Spool 16 (all_amps) (compressed columns allowed),

     which is duplicated on all AMPs.  The size of Spool 16 is

     estimated with no confidence to be 432 rows (272,592 bytes).  The

     estimated time for this step is 0.04 seconds.

  9) We do an all-AMPs JOIN step from Spool 16 (Last Use) by way of an

     all-rows scan, which is joined to sdb.PII

     in view Sales.PII by way of an all-rows scan

     with a condition of ("sdb.PII in view

     Sales.PII.Source_Id = 'POS'").  Spool 16 and

     sdb.PII are joined using a product join,

     with a join condition of (

     "sdb.PII.Pos_Invoice_Item_Nr =

     Invoice_Item_Nr").  The result goes into Spool 17 (all_amps)

     (compressed columns allowed), which is built locally on the AMPs.

     Then we do a SORT to order Spool 17 by the hash code of (

     sdb.PII.Pos_Inv_Id,

     sdb.PII.Source_Id).  The size of Spool 17

     is estimated with no confidence to be 1,652 rows (1,121,708 bytes).

     The estimated time for this step is 1.36 seconds.

 10) We do an all-AMPs JOIN step from sdb.POS in

     view Sales.POS by way of a RowHash match scan with a

     condition of ("sdb.POS in view

     Sales.POS.Source_Id = 'POS'"), which is joined to

     Spool 17 (Last Use) by way of a RowHash match scan.

     sdb.POS and Spool 17 are joined using a merge

     join, with a join condition of ("(Inv_Id =

     sdb.POS.Inv_Id) AND

     ((sdb.POS.Inv_Id = Inv_Id) AND

     ((Inv_Id = sdb.POS.Inv_Id) AND

     ((Pos_Inv_Id = sdb.POS.Pos_Inv_Id) AND

     (Source_Id = sdb.POS.Source_Id ))))").  The

     result goes into Spool 10 (all_amps) (compressed columns allowed),

     which is built locally on the AMPs.  The size of Spool 10 is

     estimated with no confidence to be 1,652 rows (1,136,576 bytes).

     The estimated time for this step is 0.09 seconds.

 11) We do an all-AMPs SUM step to aggregate from Spool 10 (Last Use)

     by way of an all-rows scan , grouping by field1 (

     sdb.POS.Inv_Id

     ,sdb.POS.Storid

     ,pdb.SAPD_03.Addr

     ,pdb.SAPD_03.Street

     ,pdb.SAPD_03.City

     ,pdb.SAPD_03.Region

     ,pdb.SAPD_03.City

     ,pdb.SAPD_03.TJ_Cd

     ,sdb.INCV.Comp_Cd

     ,sdb.POS.Sales_Org_Cd

     ,mdb.FISCAL_CALENDAR.Fiscal_Period

     ,mdb.FISCAL_CALENDAR.Fiscal_Year

     ,sdb.INCV.Bill_Dt

     ,sdb.POS.Pos_Inv_Id

     ,sdb.INCV.Curr_Cd

     ,sdb.POS.Cust_Exemptn_Nr

     ,pdb.SAPD_03.District

     ,sdb.POS.Override_Cd

     ,sdb.POS.CashierId).  Aggregate

     Intermediate Results are computed globally, then placed in Spool

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

     1,239 rows (3,098,739 bytes).  The estimated time for this step is

     0.04 seconds.

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

     an all-rows scan into Spool 8 (all_amps) (compressed columns

     allowed), which is redistributed by the hash code of (

     sdb.POS.Inv_Id) to all AMPs.  Then we do a

     SORT to order Spool 8 by row hash.  The size of Spool 8 is

     estimated with no confidence to be 1,239 rows (935,445 bytes).

     The estimated time for this step is 0.02 seconds.

 13) We do an all-AMPs MERGE into

     SAA.SALES_AUDIT_RETAIL_INVOICE from Spool 8 (Last Use).

     The size is estimated with no confidence to be 1,239 rows.  The

     estimated time for this step is 1.20 seconds.

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

     in processing the request.

  -> No rows are returned to the user as the result of statement 1.

     The total estimated time is 5.03 seconds.

Pls suggest a method to avoid this error


5 REPLIES
Senior Supporter

Re: QUERY TUNING

check if the row counts in the explain match the reality. 

are stats up to date?

missing stats?

Senior Supporter

Re: QUERY TUNING

if the estimated row counts to not match it is indicating that the optimizer does not have the right infos to come up with a good plan. Missing or wrong stats.

So you need to spend time to recollect the stats and to maybe collect new stats.

This might result in a different explain plan. When the row counts reflect reality - in high not in absolut values - and you still get the spool space error you might need to check if the spoolspace assigned to your user is sufficient.

Senior Supporter

Re: QUERY TUNING

sorry no - locks have no impact on spool consumption.

start reading the manuals.

Enthusiast

Re: QUERY TUNING

Hi,

Try to do collect stats on the columns used in the join.

If you are using SQA [sql assistant] try this,

Diagnostic Helpstats on for session;

execute the above sql and check the explain plan.

Follow the recommended stats suggested by the explain plan and try executing the sql.

Thanks,

Karthik. N

Enthusiast

Re: QUERY TUNING

if  there are any previous queries still running and consumed all the availble  spool sapce and if more spool is required and out of spool space the yongest query will abort . this could be a reson too