Need help in pointing out the exact issue leading to high CPU usage to improve query performance.

General
Enthusiast

Need help in pointing out the exact issue leading to high CPU usage to improve query performance.

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_B, TEST_DB.TEST_TABLE.col_C,
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.

Hi all,

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;

EXPLAIN

<report query>

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.

Regards,

Soumik

Tags (1)
2 REPLIES

Re: Need help in pointing out the exact issue leading to high CPU usage to improve query performance.

Hi,

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.

Teradata Employee

Re: Need help in pointing out the exact issue leading to high CPU usage to improve query performance.

Hi Soumik,

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 .....