garden2.jpg

Gather Stats Samples


Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.

Problem referenced here:

WRI$_OPTSTAT_HISTGRM_HISTORY has grown to 18 GB.

ORA-20005: object statistics are locked

Using dbms_stats:

exec dbms_stats.gather_schema_stats(ownname=> 'IDBASOL' , estimate_percent=> 40 , cascade=> TRUE );

à This will gather stats for schema IDBASOL sampling is 40% and it will do all tables and indexes.

exec dbms_stats.gather_schema_stats(ownname=> 'PA' , estimate_percent=> 40 , cascade=> TRUE );

à This will gather stats for schema PA sampling is 40% and it will do all tables and indexes.

exec dbms_stats.gather_table_stats(ownname=> 'IDBASOL', tabname=> 'BPLAN00', partname=> NULL , estimate_percent=> 30 , cascade=> TRUE );

à This to only analyze one table and all underlining indexes.

exec dbms_stats.gather_schema_stats(ownname=> 'HR' , estimate_percent=> 40 , cascade=>

TRUE , method_opt => 'for all indexed columns SIZE AUTO');

àWith histogram for all indexes.

Analyzing SYS objects:

Gathering statistics on the Data Dictionary in Oracle9i is a supported activity.

If performance (or other) problems are detected following statistics gathering then these should be raised up with Oracle Support for resolution.

In the majority of cases, gathering Data Dictionary statistics should not be necessary (and would not normally be recommended) since the dictionary has been optimized to cater for most common database setups.

If, however, the performance of queries against the data dictionary becomes a issue (for example because the database is made up of an abnormally large number of application objects) then gathering dictionary statistics can be considered.

Data Dictionary Statistics should only be gathered using the DBMS_STATS package.

Typical command for gathering Data Dictionary statistics is

execute dbms_stats.delete_schema_stats('SYS');

execute dbms_stats.gather_schema_stats('SYS', method_opt=>'for all columns size 1', degree=>30,estimate_percent=>100,cascade=>true);

àOn a 11.5.10.2 and 9.2.0.7 database this run for over 20 minutes.

execute dbms_stats.delete_schema_stats('SYSTEM');

execute dbms_stats.gather_schema_stats('SYSTEM',cascade=>true);

10g: EXEC DBMS_STATS.GATHER_DICTIONARY_STATS:

execute dbms_stats.gather_dictionary_stats ();

If monitoring is enabled run the following at regular basis:

First turn monitoring on:

exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING('IDBASOL',TRUE);

exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING('BEN',TRUE);

SQL to verify:

select OWNER,MONITORING,count(*)

from dba_tables

where OWNER='IDBASOL'

group by OWNER,MONITORING;

Stale Statistics:

A table is considered stale if there is a change of more than 10% in the rows (Insert/Update/Delete/Truncate).

Then analyze frequently using either of the following, only stale tables will be analyzed:

exec dbms_stats.gather_schema_stats(ownname=> 'IDBASOL' , options=> 'GATHER AUTO', cascade=> TRUE)

exec dbms_stats.gather_schema_stats(ownname=> 'BEN' , options=> 'GATHER STALE', cascade=> TRUE)

10g forward, if the init parameter statistics_level is set to TYPICAL (typical is default) or All, Oracle tracks changes on all tables and considers the stats stale if changes > 10% of all rows on that table. Dictionary view all_tab_modifications keeps records of the changes. Use this SQL to see the changes:

select * from all_tab_modifications

order by table_owner;

Then if you run gather_schema_stats with the GATHER STALE or GATHER AUTO then Oracle will automatically gather stats on those tables you see in all_tab_modifications

Object statistics are locked:

10g and higher:

ORA-20005: object statistics are locked

For tables that change a lot, you can do a dbms_delete_table_stats and then lock_table_stats to force dynamic sampling without a hint. The problem is, if you dynamic sample, it stores that execution plan and if the table goes from say 1 row to 200,000 then you get stuck and you need to flush your shared_pool or something to make it reparse it. That, or put a literal or something to make the query appear different.

So for tables such as WF_NOTIFICATION_OUT which only has around 10 to 40 rows and is frequently updated, if stats are locked then optimizer will be forced to dynamic sampling without a hint.

To luck or unlock use this:

exec DBMS_STATS.UNLOCK_TABLE_STATS('OWNER', 'TABLE_NAME');

dbms_stats.LOCK_TABLE_STATS and UNLOCK_TABLE_STATS.

Statistics retention:

WRI$_OPTSTAT_HISTGRM_HISTORY has grown to 18 GB.

Display what is locked:

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Verify current stats retention:

select dbms_stats.get_stats_history_retention from dual;

--> This was set to 31 days in PROD.

exec dbms_stats.alter_stats_history_retention(7);

--> I set the stats retention period to 7 days.

To find out the oldest available stats:

select dbms_stats.get_stats_history_availability from dual;

trucate WRI$_OPTSTAT_HISTGRM_HISTORY;

Or use dbms_stats.purge_stats to purge not needed stats:

exec dbms_stats.purge_stats(to_date('01-JAN-13','DD-MON-YY'));

Analyze command:

Analyze command is no longer recommended, Oracle recommends using dbms_stats instead.

Here are a few samples just as a reference.

Analyze sample

à ANALYZE with an ESTIMATE above 50% results in an ANALYZE ... COMPUTE

Analyze table table_name estimate statistics sample 1000 rows;

Analyze table table_name estimate statistics sample 10 percent;

Analyze table table_name COMPUTE STATISTICS

à When using option compute statistics, this will analyze the entire table rather that a sample.

Analyze index index_name compute statistics;

Analyze table table_name compute statistics

For all indexed columns

For table;

ANALYZE TABLE TABLE05 COMPUTE STATISTICS

FOR TABLE FOR ALL INDEXED COLUMNS size 1;

à This analyze the table and all underlining indexes.

ANALYZE TABLE TABLE00 COMPUTE STATISTICS

FOR TABLE FOR ALL INDEXED COLUMNS;

à This analyze the table and all underlining indexes.

ANALYZE TABLE &table_name estimate statistics sample 40 percent

FOR TABLE FOR ALL INDEXED COLUMNS;

à This analyze (estimates) the table and all underlining indexes.

Refrences:

==========

SYSAUX Grows Because Optimizer Stats History is Not Purged [ID 1055547.1]

Suggestions if your SYSAUX Tablespace grows rapidly or too large [ID 1292724.1]

Abnormal High Space Usage in Sysaux Tablespace - Unable to Purge [ID 1360000.1]

Latest Blog Items

email

Thank you for your interest in idbasolutions.

Contact us using this email: contact.us@idbasolutions.com