Why should i gather stats for tables/user objects/indexes?
The oracle optimizer generates plans based on the information existing in DBA_* views.When DML's are going on(inserts/updates) a particular table, those changes info need not show effect on these views.
so if you try to perform query on a table now it will generate plans based on old table statistics only. this in turn will lead to poor performance of query. So in order to avoid this we will gather stats of that particular table/schema/index/object.
Example query to gather stats:
EXEC DBMS_STATS.gather_table_stats('arbor','CMF', estimate_percent => 100, cascade => TRUE);
exec dbms_stats.gather_index_stats('arbor', 'EMPLOYEES_PK');
exec dbms_stats.gather_schema_stats(ownname => 'bpread', estimate_percent => 25);
after gathering stats we can see the last_analysed date from dba_tables i.e.DBA_* views got updated now.Now the query performance will be improved very much.
The oracle optimizer generates plans based on the information existing in DBA_* views.When DML's are going on(inserts/updates) a particular table, those changes info need not show effect on these views.
so if you try to perform query on a table now it will generate plans based on old table statistics only. this in turn will lead to poor performance of query. So in order to avoid this we will gather stats of that particular table/schema/index/object.
Example query to gather stats:
EXEC DBMS_STATS.gather_table_stats('arbor','CMF', estimate_percent => 100, cascade => TRUE);
exec dbms_stats.gather_index_stats('arbor', 'EMPLOYEES_PK');
exec dbms_stats.gather_schema_stats(ownname => 'bpread', estimate_percent => 25);
after gathering stats we can see the last_analysed date from dba_tables i.e.DBA_* views got updated now.Now the query performance will be improved very much.
No comments:
Post a Comment