Explain Plan - How Explain plan decides/show the number of AMPs involved in a query.

Database
Enthusiast

Explain Plan - How Explain plan decides/show the number of AMPs involved in a query.

Hi All,

I tried below for an explain plan of a table Table1 and got some head scratching questions:

Explain Select * from DB1.Table1;

Output:

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

1) First, we lock DB1.Table1 for access.

2) Next, we do an all-AMPs RETRIEVE step from

     DB1.Table1 by way of an all-rows scan

     with no residual conditions into Spool 1, which is built locally

     on the AMPs.  The size of Spool 1 is estimated with high

     confidence to be 141 rows.  The estimated time for this step is

     0.15 seconds.

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

     in processing the request.

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

     statement 1.  The total estimated time is 0.15 seconds.

I need assistence for below questions:

1. Is there anyway to check the number of APMs involved/needed for a query.

2. Its output shows that the "Spool 1" is involved, how it decides that which spool (Spool 1 or Spool 2 or any other spool) will goin to be the part of the query.

3. all-AMPs RETRIEVE, An explain plan will always perform All AMPs retrives or it can perform any other(please suggest if any other exists).

Tags (2)