Analyze versus DBMS_STATS

DBMS_STATS is recommended process or technique to generate stats of the database objects.
Analyze is dangerous to sql statement execution plan.
The basic concern is " Time elapsed in case of DBMS_STATS should be less than ANALYZE"

Table_name is EXT_PUB

SELECT COUNT(*) FROM EXT_PUB ( 210 columns)
157207324
****************
ANALYZE TABLE

Analyze table EXT_PUB estimate statistics sample 20 percent;
Time Elapsed 2:53:01

DBMS_STATS

CASE 1:
20% sample size and paralle degree 3

Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'ORACLE',TABNAME => 'EXT_PUB',ESTIMATE_PERCENT =>20,degree => 3);
Time Elapsed 3:06:13
its taking more time than analyze .

CASE 2:
sample size 20% , method_opt is for all indexed columns , degree value 3, stats generation for all indexes in single statement

Result: Time elapsed reduce by 76% (approx)

Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'ORACLE',TABNAME => 'EXT_PUB',ESTIMATE_PERCENT =>20,method_opt=> 'for all INDEXED COLUMNS size 1',cascade => true,degree=>3);
Time Elapsed 00:44:17

CONCEPT IS : By default DBMS_STATS generates histogram for all the columns under method_opt option so you can change that parameter only for indexed columns .

EXPORT statistics from Prodcution to Test enviromets
e.g copy Stats of gca_ds_prs_ext_pub ( Prod to Test)

exec dbms_stats.create_stat_table(ownname => 'ORACLE',STATTAB => 'NS_STATS_DEV');

1. EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME => 'ORACLE',TABNAME=> ' EXT_PUB',STATTAB => 'NS_STATS_DEV',STATID => 'EXID');

2. export table "NS_STATS_DEV" ( $ exp )
3. Move exp.dmp to TEST
4. IMPORT "NS_STATS_DEV" TABLE ($ imp )

5. SELECT LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME='EXT_PUB';
07:31:43

6. exec dbms_stats.import_table_stats(ownNAME=> 'ORACLE',tabname => ' Ext_pub',stattab => 'ns_stats_dev',STATID => 'EXID');

7.SELECT LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME='EXT_PUB';
07:30:27