Stale optimizer statistics on logical standby

Problem: Stale optimizer statistics for tables & indexes on logical standby

Normally ‘Automatic Optimizer Statistics Collection’ feature introduced in 10g should take care of updating the statistics by scheduled maintenance job in Database control or Grid control.

But in logical data guard without a Grid Control configuration, it is difficult to keep the optimizer statistics updated.

So a small job to refresh the statistics will be helpful.

DECLARE
BEGIN
DBMS_STATS.GATHER_TABLE_STATS
(
OWNNAME => ‘USER’,
TABNAME => ‘TNAME’,
DEGREE => 2 ,
CASCADE => TRUE ,
METHOD_OPT=> ‘FOR ALL INDEXED COLUMNS’
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ERROR :’ SUBSTR(SQLERRM,1,100));
END;
/

Note: DBMS_STATS.GATHER_TABLE_STATS will fail against EXTERNAL tables.

Advertisements
This entry was posted in Oracle Data Guard, Oracle Logical Data Guard and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s