Analyze statistics

[Guest_imported]

This is a real newbie question.
How do find out when the last time the stats were last analyzed? I can find lots of info about how to analyze - but nothing on when they were last analyzed.
thanks in advance.

 

[ThomVF]

You can use the DBMS_STATS package to view the statistics stored in the data dictionary or in a statistics table.

You can also query these data dictionary views for statistics in the data dictionary:

Table stats:
select
user_stats,last_analyzed,num_rows,blocks,avg_row_len
from [user|all|dba]_tables;

Index stats:
SELECT INDEX_NAME "NAME", NUM_ROWS, DISTINCT_KEYS "DISTINCT",
LEAF_BLOCKS, CLUSTERING_FACTOR "CF",
AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY"
FROM [user|all|dba]_INDEXES
WHERE TABLE_NAME =&quot;<table>&quot;
ORDER BY INDEX_NAME;

Column stats:
SELECT COLUMN_NAME, NUM_DISTINCT,
NUM_NULLS, NUM_BUCKETS, DENSITY
FROM [user|all|dba]_TAB_COLUMNS
WHERE TABLE_NAME =&quot;<table>&quot;
ORDER BY COLUMN_NAME;


Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[ThomVF]

You can use the following procedures from within the DBMS_STATS package to view statistics:

DBMS_STATS.GET_COLUMN_STATS
DBMS_STATS.GET_INDEX_STATS
DBMS_STATS.GET_TABLE_STATS

Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[sem]

It depends on the object type you're interested in. In general your query should be like this:

select last_analyze
from [DBA|USER|ALL]_[TABLES|TAB_COLUMNS|INDEXES|...]
where [TABLE|COLUMN|INDEX|...]_NAME=<OBJECT_NAME>

You may add extra conditions

 

[Guest_imported]

Thanks for all your suggestions. You have given me lots to go with.
thanks again.