How to decide about Stats for new queries

Database
Enthusiast

Re: How to decide about Stats for new queries

"complete table" you mean all the columns of the table ?

No it is not required to collect all the columns of any table.

Only those columns of any table invloved in where , joins..etc

or probably you are asking for table level collect stat statment. This statement is just to refresh the already collected stats.

Enthusiast

Re: How to decide about Stats for new queries

How to decide if (& which) multi column of a table stats need to be collected...Like col1,col2, col3 of a tbl

Enthusiast

Re: How to decide about Stats for new queries

Through explain plan recommondations after executing "diagnostic helpstats on for session" .

Generally multicolumn stats only used when that combination of columns are used in group by clauses, where and join very frequently.

Many times sinnle colum stats can be used by optimizer in place of multi column but multi column stats can't be used by the optimizer when singl column stats required. 

Enthusiast

Re: How to decide about Stats for new queries

Thanks Satya!

To check existing stats do i have to dig down to base table or is there any other easier way with view itself?

Enthusiast

Re: How to decide about Stats for new queries

As queries use views, can i check from views itself to find for which columns stats are already there or not?

Enthusiast

Re: How to decide about Stats for new queries

To check about the existing stats on the tables...

- Can we check from views itself used in the query?

For any new query...

- Wil Running Diagnostic Helpstats give all the stats needed for the query?

Enthusiast

Re: How to decide about Stats for new queries

Stats can be checked using help stats on base tables or using dbc table like statsV , statsinfo.

running diagnostic helpstats give all stats that MAY improve query plan, but only few need to be selected for collection.

Enthusiast

Re: How to decide about Stats for new queries

Right Harpreet but my question was:

To check about the existing stats on the tables...

- Can we check from views itself used in the query?

(As you need to check definition of each view to find base table & then Help Stats Tablename)

Enthusiast

Re: How to decide about Stats for new queries

No you cannot check from view directly. 

Not applicable

Re: How to decide about Stats for new queries

Quite informative!!