2. We do an all-AMPs RETRIEVE step from TEST_DB.TEST_TABLE by way of
an all-rows scan with no residual conditions into Spool 5
(all_amps) (compressed columns allowed), which is built
locally on the AMPs. Then we do a SORT to order Spool 5 by
the hash code of (TEST_DB.TEST_TABLE.col_A,
TEST_DB.TEST_TABLE.col_D). The size of Spool 5 is
estimated with high confidence to be 2,277,164,473 rows (
116,135,388,123 bytes). The estimated time for this step is
3 minutes and 46 seconds.
I have a report query that is taking longer time to execute. I believe there is some design issue. I carried out diagnostic helpstats with explain to identify the issue:
DIAGNOSTIC HELPSTATS ON FOR SESSION;
By doing so, i found out the STATS recommendation and performed the same. But this didnt help. From the EXPLAIN plan shown above is the step that is taking longer time and rest of the steps are taking much lesser times.
Could any please help me what could be the probable causes? Let me know if you need the DDL of the table TEST_DB.TEST_TABLE.
Option 1: collect stats on <tablename> column(a,b,c);(Multi column stats)
Option 2: Single column stats always help much better. So collect stats on each column.
the step in your explain is long because it do a sort on 2 billion rows ! Sorting is a very cost computing operation !
Can you show me the overall query and the tables involved ?
You can put show before the query and run it, ie if the query is
select * from t1 joint t2 .....
you can run
show select * from t1 joint t2 .....