AWR Performance

From Dirty Cache Wiki
Revision as of 11:25, 13 June 2024 by Bart (talk | contribs)
Jump to navigation Jump to search

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;