Collect stats running for 4hours avg and taking spool 4tb for 400GB table

Database
Enthusiast

Collect stats running for 4hours avg and taking spool 4tb for 400GB table

Hi,

We have a procedure which has COLLECT STATS for 4 tables.All tables are around 350GB.Now the collect stats taking spool of around 4tb and runs avg of 4 hours.Yesterday it took 9 hours.Can sombody help.We do have using sample.pls help to solve it.

COLLECT STATISTICS USING SAMPLE

COLUMN(POSITION_DT ,SCENARIO_NM ,TIME_STEP_DT,RISK_FACTOR_NODE_NM) ,

COLUMN(POSITION_DT),

COLUMN(TIME_STEP_DT),

COLUMN(RISK_FACTOR_NODE_NM)

ON database.table;

12 REPLIES
Senior Apprentice

Re: Collect stats running for 4hours avg and taking spool 4tb for 400GB table

Hi,

Can you provide the explain for your sample shown above?

If you want to reduce elapsed time for this processing then you might consider processing each table in a separate session - i.e. run all 4 at the same time. How much that reduces elapsed time will depend on capacity of your system at the time the jobs run.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Collect stats running for 4hours avg and taking spool 4tb for 400GB table

4TB spool for sample stats on a 350GB table is strange.

What's the datatype of those columns?

And what's dbc.StatsV.SampleSizePct for those tables?

Enthusiast

Re: Collect stats running for 4hours avg and taking spool 4tb for 400GB table

procedure call is not showing much DBQL stats..

Explain call ESA_RISKWATCH_DATA.COLLECT_STAT_RISKWATCH_REFRESH()

1) First, we send the CALL Step to RTS. .

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

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

However is did explain for collects stats for table..

Explain COLLECT STATISTICS USING SAMPLE

COLUMN(POSITION_DT ,SCENARIO_NM ,TIME_STEP_DT,RISK_FACTOR_NODE_NM) ,

COLUMN(POSITION_DT),

COLUMN(TIME_STEP_DT),

COLUMN(RISK_FACTOR_NODE_NM)

ON ESA_RISKWATCH_DATA.RISK_FACTOR_VALUES;

1) First, we lock ESA_RISKWATCH_DATA.RISK_FACTOR_VALUES for access.

2) Next, we do an all-AMPs SUM step to aggregate from

ESA_RISKWATCH_DATA.RISK_FACTOR_VALUES by way of an all-rows scan

with no residual conditions , grouping by field1 (

ESA_RISKWATCH_DATA.RISK_FACTOR_VALUES.POSITION_DT

,ESA_RISKWATCH_DATA.RISK_FACTOR_VALUES.SCENARIO_NM

,ESA_RISKWATCH_DATA.RISK_FACTOR_VALUES.TIME_STEP_DT

,ESA_RISKWATCH_DATA.RISK_FACTOR_VALUES.RISK_FACTOR_NODE_NM).

Aggregate Intermediate Results are computed locally, then placed

in Spool 3. The input table will not be cached in memory, but it

is eligible for synchronized scanning. The aggregate spool file

will not be cached in memory. The size of Spool 3 is estimated

with high confidence to be 20,496,254,429 rows (2,644,016,821,341

bytes). The estimated time for this step is 3 hours and 38

minutes.

3) Then we save the UPDATED STATISTICS for (

'POSITION_DT,SCENARIO_NM,TIME_STEP_DT,RISK_FACTOR_NODE_NM ') from

Spool 3 into Spool 5, which is built locally on a single AMP

derived from the hash of the table id.

4) We do an all-AMPs SUM step to aggregate from Spool 3 by way of an

all-rows scan , grouping by field1 (

ESA_RISKWATCH_DATA.RISK_FACTOR_VALUES.POSITION_DT). Aggregate

Intermediate Results are computed globally, then placed in Spool 8.

The size of Spool 8 is estimated with low confidence to be 554

rows (16,066 bytes). The estimated time for this step is 7

minutes and 45 seconds.

5) Then we save the UPDATED STATISTICS for ('POSITION_DT ') from

Spool 8 (Last Use) into Spool 10, which is built locally on a

single AMP derived from the hash of the table id.

6) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by

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

ESA_RISKWATCH_DATA.RISK_FACTOR_VALUES.TIME_STEP_DT). Aggregate

Intermediate Results are computed globally, then placed in Spool

13. The size of Spool 13 is estimated with low confidence to be

8,056 rows (233,624 bytes). The estimated time for this step is 7

minutes and 45 seconds.

7) Then we save the UPDATED STATISTICS for ('TIME_STEP_DT ') from

Spool 13 (Last Use) into Spool 15, which is built locally on a

single AMP derived from the hash of the table id.

8) We do an all-AMPs SUM step to aggregate from

ESA_RISKWATCH_DATA.RISK_FACTOR_VALUES by way of an all-rows scan

with no residual conditions , grouping by field1 (

ESA_RISKWATCH_DATA.RISK_FACTOR_VALUES.RISK_FACTOR_NODE_NM).

Aggregate Intermediate Results are computed globally, then placed

in Spool 18. The input table will not be cached in memory, but it

is eligible for synchronized scanning. The size of Spool 18 is

estimated with high confidence to be 610 rows (118,950 bytes).

The estimated time for this step is 10 minutes and 38 seconds.

9) Then we save the UPDATED STATISTICS for ('RISK_FACTOR_NODE_NM ')

from Spool 18 (Last Use) into Spool 20, which is built locally on

a single AMP derived from the hash of the table id.

10) We compute the table-level summary statistics from spool 20 and

save them into Spool 21, which is built locally on a single AMP

derived from the hash of the table id.

11) We lock DBC.StatsTbl for write on a RowHash.

12) We do a Single AMP MERGE Update to DBC.StatsTbl from Spool 5 (Last

Use) by way of a RowHash match scan.

13) We do a Single AMP MERGE Update to DBC.StatsTbl from Spool 10

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

14) We do a Single AMP MERGE Update to DBC.StatsTbl from Spool 15

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

15) We do a Single AMP MERGE Update to DBC.StatsTbl from Spool 20

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

16) We do a Single AMP MERGE Update to DBC.StatsTbl from Spool 21

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

17) We spoil the statistics cache for the table, view or query.

18) We spoil the parser's dictionary cache for the table.

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

 

Enthusiast

Re: Collect stats running for 4hours avg and taking spool 4tb for 400GB table

Here is explain for correct table..By mistake i pasted wrong table explain..

Explain COLLECT STATISTICS USING SAMPLE

COLUMN(POSITION_DT ,DEAL_ID ,SCENARIO_NM ,TIME_STEP_DT),

COLUMN(POSITION_DT),

COLUMN(COUNTERPARTY_ID),

COLUMN(TIME_STEP_DT),

COLUMN(DEAL_ID)

ON ESA_RISKWATCH_DATA.DEAL_EXPOSURE;

1) First, we lock ESA_RISKWATCH_DATA.DEAL_EXPOSURE for access.

2) Next, we do an all-AMPs SUM step to aggregate from

ESA_RISKWATCH_DATA.DEAL_EXPOSURE by way of an all-rows scan with

no residual conditions , grouping by field1 (

ESA_RISKWATCH_DATA.DEAL_EXPOSURE.POSITION_DT

,ESA_RISKWATCH_DATA.DEAL_EXPOSURE.DEAL_ID

,ESA_RISKWATCH_DATA.DEAL_EXPOSURE.SCENARIO_NM

,ESA_RISKWATCH_DATA.DEAL_EXPOSURE.TIME_STEP_DT). Aggregate

Intermediate Results are computed locally, then placed in Spool 3.

The input table will not be cached in memory, but it is eligible

for synchronized scanning. The aggregate spool file will not be

cached in memory. The size of Spool 3 is estimated with high

confidence to be 12,418,364,696 rows (1,601,969,045,784 bytes).

The estimated time for this step is 2 hours and 15 minutes.

3) Then we save the UPDATED STATISTICS for (

'POSITION_DT,DEAL_ID,SCENARIO_NM,TIME_STEP_DT ') from Spool 3 into

Spool 5, which is built locally on a single AMP derived from the

hash of the table id.

4) We do an all-AMPs SUM step to aggregate from Spool 3 by way of an

all-rows scan , grouping by field1 (

ESA_RISKWATCH_DATA.DEAL_EXPOSURE.POSITION_DT). Aggregate

Intermediate Results are computed globally, then placed in Spool 8.

The size of Spool 8 is estimated with low confidence to be 554

rows (16,066 bytes). The estimated time for this step is 4

minutes and 42 seconds.

5) Then we save the UPDATED STATISTICS for ('POSITION_DT ') from

Spool 8 (Last Use) into Spool 10, which is built locally on a

single AMP derived from the hash of the table id.

6) We do an all-AMPs SUM step to aggregate from

ESA_RISKWATCH_DATA.DEAL_EXPOSURE by way of an all-rows scan with

no residual conditions , grouping by field1 (

ESA_RISKWATCH_DATA.DEAL_EXPOSURE.COUNTERPARTY_ID). Aggregate

Intermediate Results are computed globally, then placed in Spool

13. The input table will not be cached in memory, but it is

eligible for synchronized scanning. The size of Spool 13 is

estimated with high confidence to be 15 rows (2,805 bytes). The

estimated time for this step is 9 minutes and 32 seconds.

7) Then we save the UPDATED STATISTICS for ('COUNTERPARTY_ID ') from

Spool 13 (Last Use) into Spool 15, which is built locally on a

single AMP derived from the hash of the table id.

8) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by

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

ESA_RISKWATCH_DATA.DEAL_EXPOSURE.TIME_STEP_DT). Aggregate

Intermediate Results are computed globally, then placed in Spool

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

8,123 rows (235,567 bytes). The estimated time for this step is 4

minutes and 42 seconds.

9) Then we save the UPDATED STATISTICS for ('TIME_STEP_DT ') from

Spool 18 (Last Use) into Spool 20, which is built locally on a

single AMP derived from the hash of the table id.

10) We do an all-AMPs SUM step to aggregate from

ESA_RISKWATCH_DATA.DEAL_EXPOSURE by way of an all-rows scan with

no residual conditions , grouping by field1 (

ESA_RISKWATCH_DATA.DEAL_EXPOSURE.DEAL_ID). Aggregate Intermediate

Results are computed globally, then placed in Spool 23. The input

table will not be cached in memory, but it is eligible for

synchronized scanning. The size of Spool 23 is estimated with

high confidence to be 2,792 rows (544,440 bytes). The estimated

time for this step is 9 minutes and 32 seconds.

11) Then we save the UPDATED STATISTICS for ('DEAL_ID ') from Spool 23

(Last Use) into Spool 25, which is built locally on a single AMP

derived from the hash of the table id.

12) We compute the table-level summary statistics from spool 25 and

save them into Spool 26, which is built locally on a single AMP

derived from the hash of the table id.

13) We lock DBC.StatsTbl for write on a RowHash.

14) We do a Single AMP MERGE Update to DBC.StatsTbl from Spool 5 (Last

Use) by way of a RowHash match scan.

15) We do a Single AMP MERGE Update to DBC.StatsTbl from Spool 10

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

16) We do a Single AMP MERGE Update to DBC.StatsTbl from Spool 15

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

17) We do a Single AMP MERGE Update to DBC.StatsTbl from Spool 20

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

18) We do a Single AMP MERGE Update to DBC.StatsTbl from Spool 25

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

19) We do a Single AMP MERGE Update to DBC.StatsTbl from Spool 26

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

20) We spoil the statistics cache for the table, view or query.

21) We spoil the parser's dictionary cache for the table.

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

 

Junior Contributor

Re: Collect stats running for 4hours avg and taking spool 4tb for 400GB table

The first stats on COLUMN(POSITION_DT ,DEAL_ID ,SCENARIO_NM ,TIME_STEP_DT) seems to be the bad one.

The optimizer is using a 100% sample and spool estimation is quite large.

 

You didn't show the data types, but those *_DT columns are probably Dates.

If SCENARIO_NM is a large Varchar it's the root cause, because a Varchar is expanded to it's defined size during aggregation,

i.e. SCENARIO_NM VARCHAR(1000) -> CHAR(1000) 

 

As you probably can't change the data type you might force a sample using

COLLECT STATISTICS USING SAMPLE 2 PERCENT

COLUMN(POSITION_DT ,DEAL_ID ,SCENARIO_NM ,TIME_STEP_DT)

 

Of course you might need to increase the sample size ...

Senior Apprentice

Re: Collect stats running for 4hours avg and taking spool 4tb for 400GB table

Hi,

The scan may be 100% because the optimiser doesn't have enough stats history yet.

 

The 15.10 DDL manual says:

The downgrade approach works as follows.
1 When you initially submit a request to collect sampled statistics, the Optimizer collects full
statistics. That is, the statistics are not sampled.
The Optimizer uses the full statistics to determine when to reduce sampling to a smaller
percentage.
2 On subsequent requests to recollect statistics, the Optimizer collects full statistics until
adequate statistics have been captured to provide a reliable historical record.

 

Also, will a very long VARCHAR column actually be expanded to it's full length? I realise that it does for 'normal' aggregation and that aggregation is involved when collecting statistics, but there is the MAXVALUELENGTH attribute for the Collect Statistics command. This defaults to 25 on TD14.0 and higher, which means that the stats histograms and counts are built on the first 25 bytes of data. Not 100% certain but I think that is what happens.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Junior Contributor

Re: Collect stats running for 4hours avg and taking spool 4tb for 400GB table

Hi Dave,

the truncation of the stats to MAXVALUELENGTH is done after aggregation.

You might spot the same truncated value multiple times in the biased section of SHOW STATS.

 

Btw, I never liked that the optimizer is not doing a SAMPLE when requested for new stats.

I tend to force it because (at least I think) I'm smarter :-)

Enthusiast

Re: Collect stats running for 4hours avg and taking spool 4tb for 400GB table

But we collect stats on these tables daily i.e, we call the procedure having collect stats for 5 tables(one is 600GB and rest are small) almost every other day.So I assume optimizer has enough hist.Why its using 3.5TB of spool whereas tables are small.

 

Is it possible that we might have some system level settings for SAMPLE %.?

What is recommended value for % ,is it 2?

 

Enthusiast

Re: Collect stats running for 4hours avg and taking spool 4tb for 400GB table

yes dnoeth ,The one you mentiond took almost all time and spool rest are taking hardly few mins.As its UPI of the table i took sample 2% and it finished in seconds.

COLUMN(POSITION_DT ,SCENARIO_NM ,TIME_STEP_DT,RISK_FACTOR_NODE_NM) ---AVG  2 hours to complete

Here SCENARIO_NM  is varchar(50),RISK_FACTOR_NODE_NM is varchar(255).