How can I get stats recommendation from Explain?

UDA
Highlighted

Re: How can I get stats recommendation from Explain?

You will find recomendation in Explain plan.

Before running explain plan execute below query

diagnostic helpstats on for session; 

 

Now if you do explain on your query  (explain select * from table where condition) you will find something as below. Where it will suggest you to collect stats on few columns as oer your query.

 


Explanation
----------------------------------------------------------------------
1) First, we do a single-AMP RETRIEVE step from SQLJ.store by way of
the unique primary index "SQLJ.store.STORE_ID = 40" with no
residual conditions. The estimated time for this step is 0.00
seconds.
-> The row is sent directly back to the user as the result of
statement 1. The total estimated time is 0.00 seconds.
BEGIN RECOMMENDED STATS FOR FINAL PLAN->
-- "COLLECT STATISTICS COLUMN (STORE_ID) ON SQLJ.store" (High
Confidence)
<- END RECOMMENDED STATS FOR FINAL PLAN