AWR Performance

This article is a stub. You can help us by expanding it.

Overview

Sometimes, generating AWR reports can be very slow. This article lists some potential remedies.

• Solution 1

Reference: Oracle Support Note 2929370.1

alter session set "_push_join_predicate"=false;
-- OR
alter session set "_optimizer_push_pred_cost_based" = false;

• Solution 2

Reference: Oracle Support Note 2148489.1

DBMS_STATS.DELETE_SYSTEM_STATS('NOWORKLOAD');
DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE => 'NOWORKLOAD');

• Solution 3

Reference: Oracle Support Note 2148489.1

Clean up old AWR snapshots as per the support note. Then run the next SQL snippet

CONNECT / AS SYSDBA
ALTER TABLE WRH$_SEG_STAT_OBJ MOVE;
ALTER INDEX WRH$_SEG_STAT_OBJ_PK REBUILD;
ALTER INDEX WRH$_SEG_STAT_OBJ_INDEX REBUILD;
4) Gather Dictionary and System Statistics:
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
--To gather the fixed objects stats, use the following:
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;