I have a query which is taking more time to run in the LIVE environment.So,i searched in google to optimize the query.I found some of the diagnostic commnads for the Statistics which is recommended by the optimizer attached with the EXPLAIN plan.
ie. Diagnostic helpstats on for session;
I executed the above diagnostics for the recommendation of help stats of a query..PE gives the recommendation to collect statistics for the signle column & Multi column statistics also..
I have collected statistics for the index columns eg. Collect statistics on table_name index(STORE_CODE,PRODUCT_CODE) but the EXPLAIN recommendation for the statistics also says 'Collect statistics on table_name column(STORE_CODE,PRODUCT_CODE)..
Is it necessary to collect stats. for the Mulit-Column statistics even if it is a Composite index columns?
You need to capture the EXPLAIN output before and after the statistics are collected. Ideally you are working in a test environment to avoid impacting other queries that reference the affected columns in a manner that the query plan is at risk for changing.
Once you have the EXPLAINs captured (comparing side-by-side in Excel is handy) you can determine if the statistics have made a significant change to the plan. (Such as eliminating a product join, introducing the use of an index, etc.) Beyond that you can compare the metrics of the queries in DBQL to determine if there is a significant change in the CPU Seconds or IO used to resolve the query.
As for multi-column stats it depends. Care must be taken to order the most unique columns first in COLLECT STATS command. While the number of unique records will be captured there are buckets in the histogram that truncate after 16 bytes.
There are more details stats discussions that can be found in the blogs written by Carrie Ballinger that would be worthwhile reading if you are still unsure how and why statistics affect your plan. Hope this helps get you down the right path.