In several articles I wrote about the statistics and their importance for the proper functioning of the optimizer for costs (CBO). Keep up to date statistics is sometimes a rather complex and tedious task, especially in environments with large data volume and high rate of change. Ensure that each execution of judgments statistics are "fresh" is a challenge for architects and DBA's.
Since 10g is pretty automated this task, since one of the processes running during the maintenance window, is just gathering statistics. To optimize the collection tables are updated only as exchange rate is greater than 10%. Available that charts are outdated by consulting the catalog view DBA_TAB_STATISTICS, where there is a field called STALE_STATS which can take two values \u200b\u200bYES (the board needs new statistics) or NO (the table does not need new statistics). The threshold is fixed in 10g and can not be modified. Since the maintenance window is configured to activate during the night by default, if for example, a process of massive change on a table creates change by more than 10% will not have fresh statistics until the next day. In such cases it is recommended to collect statistics manually operative immediately after the change on the involved tables.
In 11g you can change the threshold level of table schema or the entire base, which can be as sensitive taking statistics as required. In practice I used that feature table granularity only in cases where changes were detected plans certain statements that reference tables with minor changes to 10%. Then I will show how to use the new procedure SET_TABLE_PREFS DBMS_STATS package to change the threshold.
Looking at 11g were added the following procedures to DBMS_STATS package
SET_TABLE_PREFS
SET_SCHEMA_PREFS
SET_DATABASE_PREFS
With
sp's listed above can perform the following 3 new settings:
STALE_PERCENT: To change the threshold that determines when a table does not have its statistics daily.
INCREMENTAL: To optimize the collection on partitioned tables (see item xxx)
PUBLISH: For tests a new set of statistics before publication
I'll show an example to change the STALE_PERCENT one table, consultation on the catalog to make it look as we annotate the changes:
First I create a table and then took it took the statistics manually.
create table t as select * from dba_objects
select count (1) from t
dbms_stats.gather_table_stats (ownname = user, tabname = 'T');
end;
select num_rows, stale_stats from user_tab_statistics WHERE table_name = 'T'
NUM_ROWS: 88538
STALE_STATS: NO
STALE_STATS column allows us to determine if the statistics are fresh or not. A common practice I have seen many times, is to look at the column User_t LAST_ANALYZED view. Clearly, this value may be misleading, because we tend to infer that the oldest has been taking the last table will be more outdated, but ... if the table had no significant changes since the last collection?, then the field will be STALE_STATS LAST_ANALYZED NO and may have several days or even months. The latter does not in any way the table stats are outdated. As a rule, I always recommend looking STALE_STATS column to determine if a table has the correct statistics and only see the LAST_ANALYZED as additional data.
I'm going to create changes of various types to the table so as to generate more than 10% of changes, remember that it is the default tolerance threshold (STALE_TOLERANCE)
September update
object_id = rownum WHERE rownum \u0026lt;= 3000
WHERE rownum \u0026lt;= 3000
insert into t select * from dba_objects
WHERE rownum \u0026lt;= 3000
I'll use the USER_TAB_MODIFICATIONS view that shows the amount of DML's for each table after the last dose of statistics. You can use the info for that table, to know the exchange rate and type of operations, which is very useful to learn more about the proceedings at the database.
SQL> select inserts, updates, deletes from user_tab_modifications WHERE table_name = 'T';
no rows selected
No records to the table, weird, right?, if had recently made important changes. Actually it is not unusual, the issue is that changes are first stored in memory and are "flushed" to disk every 30 minutes. To force the flush do:
begin
dbms_stats.flush_database_monitoring_info;
end;
SQL> select inserts, updates, deletes from user_tab_modifications WHERE table_name = 'T';
INSERTS UPDATES ---- DELETE ------ ---------- ---------- 3000 3000 3000
Now if there are changes, as is expected. Check if the statistics are marked as "old"
select num_rows, stale_stats from user_tab_statistics WHERE table_name = 'T'
NUM_ROWS: 88538
STALE_STATS: YES
Just once impacted the changes in the catalog column is also updated and passed STALE_STATS NO to YES.
With the intro I made above, I can show you how to change the threshold for the table T, so that now instead of taking the global default threshold, use a higher threshold:
dbms_stats.set_table_prefs (user, 'T', 'STALE_PERCENT', '15 ');
end;
redo the inserts, updates and deletes earlier, I do flush the cache to update the catalog and check the table statistics are marked as STALE:
September update object_id t
WHERE rownum = rownum \u0026lt;= 3000
delete t WHERE rownum \u0026lt;= 3000
insert into t select * from dba_objects
WHERE rownum \u0026lt;= 3000
dbms_stats.flush_database_monitoring_info;
end;
select num_rows, stale_stats from user_tab_statistics WHERE table_name = 'T'
NUM_ROWS: 88538
STALE_STATS: NO
As is seen, now statistics for Oracle are not outdated and therefore not collect statistics for the table in the next maintenance window. This new feature allows more granularity to determine when a table needs stats and when not required, thereby minimizing the time of collection, more precisely adapting the process to the needs of each table, schema or database.
0 comments:
Post a Comment