Compute statistics
Analyze all tables - can sometimes help performance as Oracle can obtain updated information about your tables. This would help especially on fast-changing tables. Indexes should be analyzed too. Oracle comes with a DBMS_UTILITY package which allows DBA to do this task quickly. Alternatively, DBA can also do 'analyze table TABLE_NAME compute statistics;'
exec dbms_utility.analyze_schema('SOME_SCHEMA', 'COMPUTE');
exec dbms_utility.analyze_schema('SOME_SCHEMA', 'ESTIMATE', estimate_rows => 1024);
exec dbms_utility.analyze_schema('SOME_SCHEMA', 'ESTIMATE', estimate_percent => 10);
Viewing analyze results
First query database block size
Then select analysis results
# assuming block size is 8192 byte
select table_name,
NUM_ROWS,
BLOCKS*8192/1024/1024 as SizeMb,
EMPTY_BLOCKS, LAST_ANALYZED
from dba_tables
where owner = 'SOME_OWNER';
There are no comments on this page. [Add comment]