All Forums UDA
Dino 1 post Joined 03/07
08 Mar 2007
How can I get stats recommendation from Explain?

My friend told me I could useDIAGNOSTIC 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.

RGlass 35 posts Joined 09/04
08 Mar 2007

Try "Dianostic HelpStats"

RGlass 35 posts Joined 09/04
08 Mar 2007

except spell it diagnostic

26 Mar 2007

in queryman/ SQL assistant I use the following.diagnostic helpstats on for session

ChuEhrs

BBR2 96 posts Joined 12/04
27 Mar 2007

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.txtDIAGNOSTIC HELPSTATS ON FOR SESSION; EXPLAIN ;.EXPORT RESET.LOGOFF.QUITThis 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

shivamdixit 1 post Joined 08/10
07 Aug 2010

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

Khushbu 6 posts Joined 08/10
10 Aug 2010

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

Khushbu Agrawal

jkwight 14 posts Joined 06/10
10 Aug 2010

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

JK - TD-PS
Arizona Territories

thakurrabikant 2 posts Joined 09/09
11 Aug 2010

Use

"diagnostic helpstats on for session"

Before taking the Explain plan.

Rabikant Thakur
Email:thakurrabikant@gmail.com

drmkd17 54 posts Joined 10/12
28 Apr 2016

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.
 

You must sign in to leave a comment.