Oracle: How to gather stats in a logical standby database

dataguardoracleoracle-11g-r2performancequery-performancestatistics

I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced.
Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On the Primary the same query executed well after gathering table (DBMS_STATS.GATHER_TABLE_STATS) stats.

How to refresh statistics on the standby-side? The DBMS_STATS.GATHER_TABLE_STATS gives a ORA-16224.

With 

SELECT table_name,last_analyzed FROM dba_tables WHERE table_name IN ('TAB1','TAB2');

I can clearly see that the standby stats are a bit too old 😉

SELECT guard_status FROM v$database;

GUARD_STATUS
------------
STANDBY

Best Answer

You cannot run any DML in a logical standby database unless you deactivate, temporary, the guard feature. To overcome you isse you can do:

SQL> ALTER DATABASE GUARD NONE;
SQL> SELECT GUARD_STATUS FROM V$DATABASE; 
GUARD_STATUS 
-------------------- 
NONE

And then run your dbms_stats.

Related Question