- Administration
- Structure
- Performance
- Backup
- Miscellaneous
- SQL*Net
|
|
Performance - Statistics
Gather database statistics
Gather stats on the entire database...
execute dbms_stats.gather_database_stats;
Or...
execute dbms_stats.gather_database_stats( -
estimate_percent => 1, -
method_opt => 'FOR ALL COLUMNS SIZE 1',-
cascade => TRUE);
|
Gather stats for a single schema...
execute dbms_stats.gather_schema_stats('SCOTT');
Or...
execute dbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
estimate_percent => 1, -
method_opt => 'FOR ALL COLUMNS SIZE 1',-
cascade => TRUE);
You can let oracle come up with the estimate figure by using dbms_stats.auto_sample_size
or...
execute dbms_stats.gather_schema_stats( -
ownname => 'SYS', -
cascade => TRUE);
Table statistics
exec dbms_stats.gather_table_stats('<owner>', '<table_name>');
|
Delete stats
exec dbms_stats.delete_database_stats;
exec dbms_stats.delete_schema_stats('SCOTT');
exec dbms_stats.delete_table_stats('SCOTT', 'EMPLOYEES');
exec dbms_stats.delete_index_stats('SCOTT', 'EMPLOYEES_PK');
|
Gather system stats
execute dbms_stats.gather_system_stats('Start');
Wait for a while - idealy with the database under a typical workload
execute dbms_stats.gather_system_stats('Stop');
To see the current system statistics
select pname
, pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN'
/
|
Export/Import optimizer statistics
Create a table to hold the statistics...
exec dbms_stats.create_stat_table(ownname => 'SYS',-
stattab => 'prod_stats', tblspace => 'USERS');
Populate the table with the current stats...
exec dbms_stats.export_schema_stats(ownname => 'SCOTT',-
statown=>'SYS', stattab=>'prod_stats');
At this point you need to:
1) take an export of the 'prod_stats' table
2) import 'prod_stats' into the target database
3) load the statistics using this command...
exec dbms_stats.import_schema_stats(ownname => 'SCOTT',-
statown=>'SYS', stattab=>'prod_stats');
|
Old style analyze
analyze table employees compute statistics;
analyze table employees estimate statistics sample 100 rows;
analyze table employees estimate statistics sample 15 percent;
analyze index employees_ind compute statistics;
|
Show a count of analyezed tables broken down by schema
set pages 999 lines 100
select a.owner
, a.total_tables tables
, nvl(b.analyzed_tables,0) analyzed
from (select owner
, count(*) total_tables
from dba_tables
group by owner) a
, (select owner
, count(last_analyzed) analyzed_tables
from dba_tables
where last_analyzed is not null
group by owner) b
where a.owner = b.owner (+)
and a.owner not in ('SYS', 'SYSTEM')
order by a.total_tables - nvl(b.analyzed_tables,0) desc
/
|
Show tables that have analyze dates older than today
This is useful if you are running an analyze and want to see how much is left to do
select count(last_analyzed) left_to_do
from dba_tables
where owner = '&schema'
and trunc(last_analyzed) < trunc(sysdate)
order by 1
/
|
|
