How can I get stats recommendation from Explain?

UDA
N/A

How can I get stats recommendation from Explain?

My friend told me I could use
DIAGNOSTIC USESTATS ON FOR SESSION;
to get recommended statistics in an Explain Plan for a Query. I have tried this in BTEQ and in Teradata Assistant and not been able to get it to work. Does anyone know how?
Thanks.
9 REPLIES
Enthusiast

Re: How can I get stats recommendation from Explain?

Try "Dianostic HelpStats"
Enthusiast

Re: How can I get stats recommendation from Explain?

except spell it diagnostic
Enthusiast

Re: How can I get stats recommendation from Explain?

in queryman/ SQL assistant I use the following.

diagnostic helpstats on for session
Enthusiast

Re: How can I get stats recommendation from Explain?

As explained by all you could do that.
Otherwise you could use the following bteq example

.RUN FILE /home/tddba/util/logon/logon.txt
.EXPORT REPORT FILE /home/tddba/util/reports/stats.txt

DIAGNOSTIC HELPSTATS ON FOR SESSION;
EXPLAIN
;

.EXPORT RESET
.LOGOFF
.QUIT

This report file will have all the stats recommendation.
I believe V2R6.2 will have this information embedded in QCD database.

Other options is to use Teradata Stats Wizard.

Hope this helps.

Vinay

Re: How can I get stats recommendation from Explain?

hi , i used
DIAGNOSTIC HELPSTATS ON FOR SESSION;
in SQl assistant but m not getting any result
just displying 0 records processed

plz help
shivam
Enthusiast

Re: How can I get stats recommendation from Explain?

After writing DIAGNOSTIC HELPSTATS ON FOR SESSION;
then you have to write
Explain
Sel
* from
Tablename
where a=

then it will give you explain paln of the above query and in the explain plan below you can see in which columns stats should be collected to perform the query to run faster
Teradata Employee

Re: How can I get stats recommendation from Explain?

Just a word of 'caution' about the results you get.

Some of the 'recommendations' may not be appropriate. You still need to 'experiment' with the recommendations you get to see if they actually 'work' - i.e. alter your query plan or (at least) improve your cost projections. Just taking the 'recommendations as as' can be hazardous to one’s health. At least that has been MY experience. i.e. it's not a panacea for getting the right stats and making the query 'run faster'.

Re: How can I get stats recommendation from Explain?

Use

"diagnostic helpstats on for session"

Before taking the Explain plan.
Enthusiast

Re: How can I get stats recommendation from Explain?

I have 100+ tables in my databse. I want to generate stats on the tables basd on index/partition/join and where clause. Is there any table/view prsent in DBC to give me this list or any genralized query.

I know DBQLOBJTBL will give me TypeOfUse. How do I decide out of all records that it gives.