AWR Performance: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
|||
| (2 intermediate revisions by the same user not shown) | |||
| Line 7: | Line 7: | ||
* Solution 1 | * Solution 1 | ||
Reference: Oracle Support Note 2929370.1 | |||
<syntaxhighlight lang=sql> | <syntaxhighlight lang=sql> | ||
| Line 12: | Line 14: | ||
-- OR | -- OR | ||
alter session set "_optimizer_push_pred_cost_based" = false; | alter session set "_optimizer_push_pred_cost_based" = false; | ||
</syntaxhighlight> | |||
* Solution 2 | |||
Reference: Oracle Support Note 2148489.1 | |||
<syntaxhighlight lang=sql> | |||
EXEC DBMS_STATS.DELETE_SYSTEM_STATS('NOWORKLOAD'); | |||
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE => 'NOWORKLOAD'); | |||
</syntaxhighlight> | |||
* Solution 3 | |||
Reference: Oracle Support Note 2148489.1 | |||
Clean up old AWR snapshots as per the support note. | |||
Then run the next SQL snippet | |||
<syntaxhighlight lang=sql> | |||
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; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
Latest revision as of 11:35, 23 July 2024
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
EXEC DBMS_STATS.DELETE_SYSTEM_STATS('NOWORKLOAD');
EXEC 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;