How to decide about Stats for new queries

Database
N/A

How to decide about Stats for new queries

Hello All !

I am bit new to Teradata and started working on it couple of months back.

I working on performance tuning suggestions for some new queries.

Wondering if anybody can help on:

How we can decide for the stats for new queries. I mean, the queries which are not in production yet.

What one should consider to decide about stats, Tips, best practices etc?

Thanks for your help!

Gian

Tags (3)
28 REPLIES

Re: How to decide about Stats for new queries

hi,

For stats ensure they are laways up to date.As per teradata whenever there is 10% data change then we are supposed to update stats.

to find out missing stats for querries use below

EXPLAIN DIAGNOSTIC HELPSTATS ON FOR SESSION;

Mostly the columns which are used in "WHERE" clause should have stats collected.

Other points for performance improvements is avoid casting/dataconversion/string functions in joining.

Ensure tables used in joining have same PI in most cases.

Re: How to decide about Stats for new queries

Adding to what Rupesh said, 

From explain daignostinc helpstats result. You should choose which stats to collect, not all are needed. Also more important are one with igh confidence.

N/A

Re: How to decide about Stats for new queries

Thanks for your responses !

So, stats should be collected for:

- columns in WHERE clause,

- PI columns,

- What else?

I was asking for the new tables & new queries when there is no existing stats.

Is it good to use daignostinc helpstats in this case?

Re: How to decide about Stats for new queries

Below is an extended list of candiate columns of stats collection in general -

where clause columns

Index columns (Preferebly NUPI, NUSI, partitions, JI),

columns which are involved in join conditions, case statements

Once apply to above columsn, see the recommondations of "diagnostic helpstats on for session;" and apply them one by one see the change in the plan in terms of number of steps, confidence levels, counts if you find any improvment keep it or delete and go for next recommondation. that way we should be good in many cases (still it is not 100%  )

Refresh the stats always when 10% of data changed.

N/A

Re: How to decide about Stats for new queries

N/A

Re: How to decide about Stats for new queries

Great !

Thanks Satyanarayana & Dieter!!

N/A

Re: How to decide about Stats for new queries

Could you please elaborate more on:

 "and apply them one by one see the change in the plan in terms of number of steps, confidence levels, counts if you find any improvment keep it or delete and go for next recommondation"

- If improved, it'll reduce the number of steps?

 - Confidence levels anyway will be high (as recently collected). How to decide?

- Counts- Are you refering to row counts?

- What else can help while checking the query performance while testing after making all these changes? 

Thanks !

Re: How to decide about Stats for new queries

Thanks Dieter for the links. They are informative.

Gian,

What I mean in my above post was  - The number of steps may increase or decrease some times. But need to see if any change and the change is positive or not in terms of confidence. 

It is not high confident always even you collect the recommonded stats.  Observe if there is no change then drop that stas. 

In terms of count see if the counts are coming near to the actual counts. if it coming near by actual count then our stats worked.

I see below link also helpful -

http://www.teradatapro.com/teradata-sql-tuning-top-10/

Best of luck !!

N/A

Re: How to decide about Stats for new queries

Thanks Satya.

Wondering if we need to collect stats on the complete tables also (tables in FROM clause) (i hope not but just want to make sure)

OR Just columns involved in the joins?