Are statistics being gathered for your oracle database?

DBMS_STATS is important when running oracle databases with volatile data.  When I say volatile, I probably like the sound of the term more that how accurate the statement is.  I think perhaps it’s better to say dynamic – OLTP applications by nature site above dynamic databases.  As the data moves about, oracle needs to keep up with this movement so that the queries that it executes against this data are as quick as possible.  Oracle maintains statistics (metdata) on the data within tables to allow it to quickly determine the quickest path to that data and also determine the quickest method of getting the data back to the end user.  It allows oracle to estimate the size of the data coming back too, which  is essential.

So, how do you know that your awesome DBA is doing their job and keeping oracle stats up to date – you check up on them!

Oracle is pretty smart about determining which stats that it is going to update, it’s not going to update all of the statistics all of the time – that would be a waste of time.  It is however probably going to update the stats of the dynamic tables in the DB – for JDE that is generally the largest tables!

A good thing is to check the F0911 or some of their large tables, which you are also listing as part of the review.

100 largest oracle tables and their size (not including their indexes)

SELECT * FROM (

SELECT t1.owner, t1.segment_name, t1.bytes/1024/1024 Size_Mb

FROM dba_segments t1, all_objects t2

WHERE t1.owner = 'PRODDTA' AND t2.object_type = 'TABLE'

AND t1.owner = t2.owner

AND t1.segment_name = t2.object_name

ORDER BY

bytes/1024/1024  DESC ) WHERE ROWNUM <= 100

Then check the last analysed date (lets say for the F0911).

select owner, table_name, last_analyzed, num_rows, sample_size from dba_tables

where last_analyzed is not null

  and table_name = 'F0911' and owner = 'PRODDTA'

There is 100000 ways you could combine the above two to give you all of the data that you need.